Создание объектов

В нашем примере Алиса будет владельцем нескольких объектов в своей схеме. 

postgres=# CREATE ROLE alice LOGIN;

CREATE ROLE

postgres=# CREATE SCHEMA alice;

CREATE SCHEMA

postgres=# GRANT CREATE, USAGE ON SCHEMA alice to alice;

GRANT

postgres=# \c - alice

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

Алиса создает пару таблиц. 

alice=> CREATE TABLE t1(n integer);

CREATE TABLE

alice=> CREATE TABLE t2(n integer, m integer);

CREATE TABLE

Вторая роль, Боб, будет пытаться обращаться к объектам Алисы. 

alice=> \c - postgres

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

postgres=# CREATE ROLE bob LOGIN;

CREATE ROLE

Привилегии

Боб пробует обратиться к таблице t1. 

postgres$ psql -U bob -d access_privileges

bob=> SELECT * FROM alice.t1;

ERROR:  permission denied for schema alice
LINE 1: SELECT * FROM alice.t1;
                      ^

В чем причина ошибки?

У Боба нет доступа к схеме, так как он не суперпользователь, не владелец схемы, и не имеет нужных привилегий. 

postgres=# \dn+ alice

                    List of schemas
 Name  |  Owner   |  Access privileges   | Description 
-------+----------+----------------------+-------------
 alice | postgres | postgres=UC/postgres+| 
       |          | alice=UC/postgres    | 
(1 row)

В каждой строке access privileges отображается роль, ее привилегии и кем они были выданы:

роль=привилегии/кем_предоставлены

Названия привилегий обозначаются одной буквой. Привилегии для схем:

Алисе надо выдать Бобу доступ к своей схеме. 

postgres=# \c - alice

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

alice=> GRANT CREATE, USAGE ON SCHEMA alice TO bob;

WARNING:  no privileges were granted for "alice"
GRANT

Почему привилегия не выдалась?

Потому что у Алиса не является ее владельцем и у нее нет права перевыдачи. 

alice=> \dn+ alice

                    List of schemas
 Name  |  Owner   |  Access privileges   | Description 
-------+----------+----------------------+-------------
 alice | postgres | postgres=UC/postgres+| 
       |          | alice=UC/postgres    | 
(1 row)

Сделаем Алису владельцем схемы: 

alice=> \c - postgres

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

postgres=# ALTER SCHEMA alice OWNER TO alice;

ALTER SCHEMA

postgres=# \dn+ alice

                 List of schemas
 Name  | Owner | Access privileges | Description 
-------+-------+-------------------+-------------
 alice | alice | alice=UC/alice    | 
(1 row)

Теперь Алиса сможет выдать доступ Бобу: 

postgres=# \c - alice

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

alice=> GRANT CREATE, USAGE ON SCHEMA alice TO bob;

GRANT

И Боб снова пытается прочитать таблицу: 

bob=> SELECT * FROM alice.t1;

ERROR:  permission denied for relation t1

В чем причина ошибки на этот раз?

Сейчас у Боба есть доступ к схеме, но нет доступа к самой таблице. 

bob=> \dp alice.t1

                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 alice  | t1   | table |                   |                   | 
(1 row)

Пустое поле access privileges означает, что у владельца есть полный набор привилегий, а кроме него никто не имеет доступа.

Алиса должна дать Бобу доступ на чтение: 

alice=> GRANT SELECT ON t1 TO bob;

GRANT

Посмотрим, как изменились привилегии: 

alice=> \dp t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies 
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   | 
        |      |       | bob=r/alice         |                   | 
(1 row)

Привилегии отображаются в одном и том же формате: роль=привилегии/кем_предоставлены, но в зависимости от типа объекта изменяется их список. Для таблиц это:

На этот раз у Боба все получается: 

bob=> SELECT * FROM alice.t1;

 n 
---
(0 rows)

А, например, добавить строку в таблицу он не сможет: 

bob=> INSERT INTO alice.t1 VALUES (42);

ERROR:  permission denied for relation t1

Некоторые привилегии можно выдать на определенные столбцы: 

alice=> GRANT INSERT(n,m) ON t2 TO bob;

GRANT

alice=> GRANT SELECT(m) ON t2 TO bob;

GRANT

alice=> \dp t2

                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 alice  | t2   | table |                   | n:               +| 
        |      |       |                   |   bob=a/alice    +| 
        |      |       |                   | m:               +| 
        |      |       |                   |   bob=ar/alice    | 
(1 row)

Теперь Боб может добавлять строки в t2: 

bob=> INSERT INTO alice.t2(n,m) VALUES (1,2);

INSERT 0 1

А читать сможет только один столбец: 

bob=> SELECT * FROM alice.t2;

ERROR:  permission denied for relation t2

bob=> SELECT m FROM alice.t2;

 m 
---
 2
(1 row)

Если необходимо, Алиса может выдать Бобу все привилегии, не перечисляя их явно. 

alice=> GRANT ALL ON t1 TO bob;

GRANT

alice=> \dp t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies 
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   | 
        |      |       | bob=arwdDxt/alice   |                   | 
(1 row)

Теперь Бобу доступны все действия, например, удаление строк: 

bob=> DELETE FROM alice.t1;

DELETE 0

А удаление самой таблицы? 

bob=> DROP TABLE alice.t1;

ERROR:  must be owner of relation t1

Удалить таблицу может только владелец (или суперпользователь), специальной привилегии для этого не существует.

Групповые привилегии

Пусть Алиса выдаст роли public привилегию изменения t2. 

alice=> GRANT UPDATE ON t2 TO public;

GRANT

alice=> \dp t2

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies 
--------+------+-------+---------------------+-------------------+----------
 alice  | t2   | table | alice=arwdDxt/alice+| n:               +| 
        |      |       | =w/alice            |   bob=a/alice    +| 
        |      |       |                     | m:               +| 
        |      |       |                     |   bob=ar/alice    | 
(1 row)

Пустая роль (слева от знака равенства) обозначает public.

Проверим, сможет ли Боб воспользоваться это привилегией. 

bob=> UPDATE alice.t2 SET n = n + 1;

ERROR:  permission denied for relation t2

В чем причина ошибки?

Дело в том, что перед обновлением t2, сначала необходимо выбрать нужные строки, а для этого требуется привилегия чтения (как минимум, столбца n, который используется в условии). Но Боб имеет право читать только столбец m. 

alice=> GRANT SELECT ON t2 TO bob;

GRANT

alice=> \dp t2

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies 
--------+------+-------+---------------------+-------------------+----------
 alice  | t2   | table | alice=arwdDxt/alice+| n:               +| 
        |      |       | =w/alice           +|   bob=a/alice    +| 
        |      |       | bob=r/alice         | m:               +| 
        |      |       |                     |   bob=ar/alice    | 
(1 row)


bob=> UPDATE alice.t2 SET n = n + 1;

UPDATE 1

Вот теперь у Боба появилась возможность обновления таблицы.

Передача права

Создадим третью роль. Боб попробует передать ей права на таблицу t1, принадлежащую Алисе. 

alice=> \c - postgres

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

postgres=# CREATE ROLE charlie LOGIN;

CREATE ROLE

У Боба есть полный доступ к t1: 

bob=> \dp alice.t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies 
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   | 
        |      |       | bob=arwdDxt/alice   |                   | 
(1 row)

Но он не может передать свои привилегии Чарли: 

bob=> GRANT SELECT ON alice.t1 TO charlie;

WARNING:  no privileges were granted for "t1"
GRANT

Чтобы это было возможно, Алиса должна разрешить Бобу передавать права. 

postgres=# \c - alice

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

alice=> GRANT SELECT,UPDATE ON t1 TO bob WITH GRANT OPTION;

GRANT

alice=> \dp alice.t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies 
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   | 
        |      |       | bob=ar*w*dDxt/alice |                   | 
(1 row)

Звездочки справа от символа привилегии показывают право передачи.

Теперь Боб может поделиться с Чарли привилегиями, в том числе и правом передачи. 

bob=> GRANT SELECT ON alice.t1 TO charlie WITH GRANT OPTION;

GRANT

bob=> GRANT UPDATE ON alice.t1 TO charlie;

GRANT

bob=> \dp alice.t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies 
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   | 
        |      |       | bob=ar*w*dDxt/alice+|                   | 
        |      |       | charlie=r*w/bob     |                   | 
(1 row)

Роль может получить одну и ту же привилегию от разных ролей. Обратите внимание: если привилегия выдается суперпользователем, она выдается от имени владельца: 

alice=> \c - postgres

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

postgres=# GRANT UPDATE ON alice.t1 to charlie;

GRANT

postgres=# \dp alice.t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies 
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   | 
        |      |       | bob=ar*w*dDxt/alice+|                   | 
        |      |       | charlie=r*w/bob    +|                   | 
        |      |       | charlie=w/alice     |                   | 
(1 row)

Роль может отозвать привилегии только у той роли, которой она их непосредственно выдала. Например, Алиса не сможет отозвать право передачи у Чарли, потому что она его не выдавала. 

postgres=# \c - alice

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

alice=> REVOKE GRANT OPTION FOR SELECT ON alice.t1 FROM charlie;

REVOKE

Никакой ошибки не фиксируется, но и права не изменяются: 

alice=> \dp alice.t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies 
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   | 
        |      |       | bob=ar*w*dDxt/alice+|                   | 
        |      |       | charlie=r*w/bob    +|                   | 
        |      |       | charlie=w/alice     |                   | 
(1 row)

В то же время Алиса не может просто так отозвать привилегии у Боба, если он успел передать их кому-либо еще: 

alice=> REVOKE GRANT OPTION FOR SELECT ON alice.t1 FROM bob;

ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.

В таком случае привилегии надо отзывать по всей иерархии передачи с помощью CASCADE. 

alice=> REVOKE GRANT OPTION FOR SELECT ON alice.t1 FROM bob CASCADE;

REVOKE

alice=> \dp alice.t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies 
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   | 
        |      |       | bob=arw*dDxt/alice +|                   | 
        |      |       | charlie=w/bob      +|                   | 
        |      |       | charlie=w/alice     |                   | 
(1 row)

Как видим, у Боба пропало право передачи привилегии, а у Чарли была отозвана и сама привилегия.

Аналогично можно отозвать по иерархии и привилегию. 

alice=> REVOKE UPDATE ON alice.t1 FROM bob CASCADE;

REVOKE

alice=> \dp alice.t1

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies 
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   | 
        |      |       | bob=ardDxt/alice   +|                   | 
        |      |       | charlie=w/alice     |                   | 
(1 row)

Функции

Алиса создает простую функцию, возвращающую число строк в таблице t2: 

alice=> CREATE FUNCTION f() RETURNS integer AS $$
  SELECT count(*)::integer FROM t2;
$$ LANGUAGE SQL;

CREATE FUNCTION

alice=> SELECT f();

 f 
---
 1
(1 row)

Псевдороль public автоматически получает привилегию EXECUTE для любой создаваемой функции. Поэтому, например, Боб может выполнить функцию, которую только что создала Алиса.

Отчасти это компенсируется тем, что по умолчанию (или при явном указании SECURITY INVOKER) функция выполняется с правами и в окружении вызывающей роли, включая путь поиска: 

bob=> SELECT alice.f();

ERROR:  relation "t2" does not exist
LINE 2:   SELECT count(*)::integer FROM t2;
                                        ^
QUERY:  
  SELECT count(*)::integer FROM t2;

CONTEXT:  SQL function "f" during inlining

Поэтому Боб не сможет получить доступ к объектам, на которые ему не выданы привилегии.

Если же Боб создаст свою таблицу t2, функция будет работать с ней для Боба, и с alice.t2 для Алисы: 

bob=> CREATE TABLE t2(n numeric);

CREATE TABLE

bob=> SELECT alice.f();

 f 
---
 0
(1 row)


alice=> SELECT alice.f();

 f 
---
 1
(1 row)

Другой доступный вариант - объявить функцию, как работающую с правами создавшего (SECURITY DEFINER): 

alice=> CREATE OR REPLACE FUNCTION f() RETURNS integer
AS $$
SELECT count(*)::integer FROM t2;
$$ SECURITY DEFINER LANGUAGE SQL;

CREATE FUNCTION

В этом случае функция работает в контексте создавшей роли, независимо от того, кто ее вызывает: 

bob=> SELECT alice.f();

 f 
---
 1
(1 row)


alice=> SELECT alice.f();

 f 
---
 1
(1 row)

В таком случае, конечно, надо внимательно следить за выданными привилегиями. Скорее всего, потребуется отозвать привилегию EXECUTE у роли public и выдавать ее явно только нужным ролям. 

alice=> REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA alice FROM public;

REVOKE

bob=> SELECT alice.f();

ERROR:  permission denied for function f

Дело осложняется тем, что привилегия на выполнение автоматически выдается роли public на каждую вновь создаваемую функцию, и это поведение нельзя изменить. 

alice=> CREATE FUNCTION f_new() RETURNS integer AS $$ SELECT 1; $$ LANGUAGE SQL;

CREATE FUNCTION

bob=> SELECT alice.f_new();

 f_new 
-------
     1
(1 row)

Привилегии по умолчанию

Однако можно автоматически отзывать привилегию EXECUTE с помощью механизма привилегий по умолчанию: 

alice=> ALTER DEFAULT PRIVILEGES
FOR ROLE alice
REVOKE EXECUTE ON FUNCTIONS FROM public;

ALTER DEFAULT PRIVILEGES

alice=> \ddp

           Default access privileges
 Owner | Schema |   Type   | Access privileges 
-------+--------+----------+-------------------
 alice |        | function | alice=X/alice
(1 row)


alice=> DROP FUNCTION f_new();

DROP FUNCTION

alice=> CREATE FUNCTION f_new() RETURNS integer AS $$ SELECT 1; $$ LANGUAGE SQL;

CREATE FUNCTION

bob=> SELECT alice.f_new();

ERROR:  permission denied for function f_new

Аналогично можно настроить дополнительные правила для выдачи привилегий. Пусть Алиса хочет, чтобы Боб автоматически получал право чтения любой таблицы, которую она создает. 

alice=> ALTER DEFAULT PRIVILEGES
FOR ROLE alice
GRANT SELECT ON TABLES TO bob;

ALTER DEFAULT PRIVILEGES

alice=> \ddp

            Default access privileges
 Owner | Schema |   Type   |  Access privileges  
-------+--------+----------+---------------------
 alice |        | function | alice=X/alice
 alice |        | table    | alice=arwdDxt/alice+
       |        |          | bob=r/alice
(2 rows)


alice=> CREATE TABLE t3(n integer);

CREATE TABLE

alice=> \dp t3

                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies 
--------+------+-------+---------------------+-------------------+----------
 alice  | t3   | table | alice=arwdDxt/alice+|                   | 
        |      |       | bob=r/alice         |                   | 
(1 row)

Конец демонстрации.