In the last article we talked about user privileges and their type, in this article we will continue to talk about Table based privileges.
dbadmin= create table tbl(id int);
ROLLBACK 4367: Permission denied for schema public
dbadmin= c - dbadmin
Password:
You are now connected as user "dbadmin".
dbadmin= grant create on schema public to test_user;
GRANT PRIVILEGE
dbadmin= c - test_user
Password:
You are now connected as user "test_user".
dbadmin= create table tbl(id int);
CREATE TABLE
dbadmin= d tbl;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
public | tbl | id | int | 8 | | f | f |
(1 row)
-- create new schema
dbadmin= create schema ss;
CREATE SCHEMA
-- create a new table
dbadmin= create table ss.tbl1(id int);
CREATE TABLE
-- grant on to test_user on the table
dbadmin= grant all on schema ss to test_user;
GRANT PRIVILEGE
-- connect to test_user
dbadmin= c - test_user
Password:
You are now connected as user "test_user".
-- try to drop the table
dbadmin= drop table ss.tbl1;
ROLLBACK 3989: Must be owner of relation tbl1
dbadmin= alter table ss.tbl1 owner to test_user;
ALTER TABLE
-- query the tables table to now
dbadmin= select table_schema,table_name,owner_name from tables where table_name='tbl1';
-[ RECORD 1 ]+----------
table_schema | ss
table_name | tbl1
owner_name | test_user
dbadmin= c - test_user
Password:
You are now connected as user "test_user".
dbadmin= drop table ss.tbl1;
DROP TABLE
dbadmin= c - test_user
Password:
You are now connected as user "test_user".
dbadmin= select * from public.one;
ERROR 4367: Permission denied for relation one
dbadmin= grant select on public.one to test_user;
GRANT PRIVILEGE
dbadmin= c - test_user
Password:
You are now connected as user "test_user".
dbadmin= select * from public.one;
id
----
1
(1 row)
dbadmin= grant delete on public.one to test_user;
GRANT PRIVILEGE
dbadmin= create table ref(id int references one);
ERROR 4367: Permission denied for relation one
dbadmin= grant references on public.one to test_user;
GRANT PRIVILEGE
dbadmin= c - test_user
Password:
You are now connected as user "test_user".
dbadmin= create table ref(id int references one);
CREATE TABLE