База данных и роли

=> CREATE DATABASE access_privileges;

CREATE DATABASE

=> CREATE USER writer;

CREATE ROLE

=> CREATE USER reader;

CREATE ROLE

Привилегии

=> \c access_privileges

You are now connected to database "access_privileges" as user "postgres".

=> REVOKE ALL ON SCHEMA public FROM public;

REVOKE

=> GRANT ALL ON SCHEMA public TO writer;

GRANT

=> GRANT USAGE ON SCHEMA public TO reader;

GRANT

=> ALTER DEFAULT PRIVILEGES
FOR ROLE writer
GRANT SELECT ON TABLES TO reader;

ALTER DEFAULT PRIVILEGES

=> CREATE ROLE w1 LOGIN IN ROLE writer;

CREATE ROLE

Конструкция IN ROLE сразу же добавляет новую роль в указанную. То есть такая команда эквивалентна:

=> CREATE ROLE r1 LOGIN IN ROLE reader;

CREATE ROLE

=> \c - writer

You are now connected to database "access_privileges" as user "writer".

=> CREATE TABLE t(n integer);

CREATE TABLE

Роль w1 может вставлять строки:

=> \c - w1

You are now connected to database "access_privileges" as user "w1".

=> INSERT INTO t VALUES (42);

INSERT 0 1

Роль r1 может читать таблицу:

=> \c - r1

You are now connected to database "access_privileges" as user "r1".

=> SELECT * FROM t;

 n  
----
 42
(1 row)

Но не может изменить:

=> UPDATE t SET n = n + 1;

ERROR:  permission denied for relation t

Роль w1 может удалить таблицу:

=> \c - w1

You are now connected to database "access_privileges" as user "w1".

=> drop table t;

DROP TABLE