Начнем с создания роли и базы данных. Чтобы роль смогла подключиться, она должна иметь:
Посмотрим на pg_hba.conf (только не закомментированные строки):
postgres$ egrep '^[^#]' /etc/postgresql/9.6/main/pg_hba.conf
local all postgres peer local all all peer host all all 127.0.0.1/32 md5 host all all ::1/128 md5
(В зависимости от сборки содержимое файла может отличаться.)
Мы будем использовать подключение по TCP/IP (host) и установим пользователю пароль.
Создаем роль и базу данных. В этой теме нам важно, от имени какой роли выполняются команды, поэтому имя текущей роли вынесено в приглашение.
student=# CREATE ROLE r1 LOGIN PASSWORD 'r1pass';
CREATE ROLE
student=# CREATE DATABASE access_overview;
CREATE DATABASE
Осталось разобраться с привилегией CONNECT. Ее наличие можно проверить с помощью функции:
student=# SELECT has_database_privilege('r1','access_overview','connect');
has_database_privilege ------------------------ t (1 row)
Оказывается, привилегия есть. Откуда она появилась, ведь базу данных мы создали только что?
Проверим доступы для базы данных (столбец Access privileges):
student=# \l access_overview
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+---------+----------+-------------+-------------+-------------------
access_overview | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)
Поле пустое. Это неудобная особенность PostgreSQL: пустое поле означает "привилегии по умолчанию", но какие конкретно это привилегии, нужно просто знать.
Немного схитрим: выдадим и отзовем какую-нибудь привилегию для базы, которой точно не было. Например, так:
student=# GRANT CONNECT ON DATABASE access_overview TO r1;
GRANT
student=# REVOKE CONNECT ON DATABASE access_overview FROM r1;
REVOKE
Теперь посмотрим "проявившиеся" привилегии еще раз:
student=# \l access_overview
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+---------+----------+-------------+-------------+---------------------
access_overview | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/student +
| | | | | student=CTc/student
(1 row)
Привилегии отображаются в формате: роль=привилегии/кем_предоставлены. Каждая привилегия кодируется одним символом, в частности:
Если имя роли опущено, то имеется в виду псевдороль public.
Как видно, полный доступ к БД имеет ее владелец (столбец Owner), а кроме того, public может подключаться и создавать временные таблицы. Эти привилегии public автоматически получает каждый раз, когда создается новая база.
Теперь понятно, откуда у r1 привилегия CONNECT - она получена от групповой роли public. Попробуем подключиться:
student=# \c "host=localhost user=r1 dbname=access_overview password=r1pass"
You are now connected to database "access_overview" as user "r1" on host "localhost" at port "5432".
Пусть теперь роль r1 создаст одноименную схему и несколько объектов в ней.
r1=> CREATE SCHEMA r1;
ERROR: permission denied for database access_overview
В чем проблема?
У роли нет привилегии для создания схем в БД. Выдадим ее:
r1=> \c "dbname=access_overview"
You are now connected to database "access_overview" as user "student" via socket in "/var/run/postgresql" at port "5432".
student=# GRANT CREATE ON DATABASE access_overview TO r1;
GRANT
Пробуем еще раз:
student=# \c "host=localhost user=r1 dbname=access_overview password=r1pass"
You are now connected to database "access_overview" as user "r1" on host "localhost" at port "5432".
r1=> CREATE SCHEMA r1;
CREATE SCHEMA
Теперь, поскольку роль r1 является владельцем своей схемы, она имеет все привилегии на нее и может создавать в ней любые объекты. По умолчанию будет использоваться именно эта схема:
r1=> SELECT current_schemas(true);
current_schemas
------------------------
{pg_catalog,r1,public}
(1 row)
Создадим две таблицы.
r1=> CREATE TABLE t1(n numeric);
CREATE TABLE
r1=> INSERT INTO t1 VALUES (42);
INSERT 0 1
r1=> CREATE TABLE t2(n numeric, who text DEFAULT current_user);
CREATE TABLE
r1=> INSERT INTO t2(n) VALUES (1);
INSERT 0 1
Теперь создадим роль r2, из-под которой будем обращаться к объектам, принадлежащим r1.
r1=> \c "dbname=access_overview"
You are now connected to database "access_overview" as user "student" via socket in "/var/run/postgresql" at port "5432".
student=# CREATE ROLE r2 LOGIN PASSWORD 'r2pass';
CREATE ROLE
student=# \c "host=localhost user=r2 dbname=access_overview password=r2pass"
You are now connected to database "access_overview" as user "r2" on host "localhost" at port "5432".
Попробуем обратиться к таблице t1.
r2=> SELECT * FROM r1.t1;
ERROR: permission denied for schema r1
LINE 1: SELECT * FROM r1.t1;
^
В чем причина ошибки?
Нет доступа к схеме, так как мы не суперпользователь и не владелец.
r2=> \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
r1 | r1 | |
(2 rows)
Формат вывода привилегий нам уже знаком, единственная новая привилегия здесь:
Кстати, видно, что псевдороль public имеет доступ к схеме public.
Предоставим доступ к схеме для r2. Это может сделать r1, как владелец.
student=# \c "host=localhost user=r1 dbname=access_overview password=r1pass"
You are now connected to database "access_overview" as user "r1" on host "localhost" at port "5432".
r1=> GRANT CREATE, USAGE ON SCHEMA r1 TO r2;
GRANT
Попробуем снова обратиться к таблице:
r2=> SELECT * FROM r1.t1;
ERROR: permission denied for relation t1
В чем причина ошибки?
На этот раз у нас есть доступ к схеме, но нет доступа к самой таблице.
r2=> \dp r1.t1
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
r1 | t1 | table | | |
(1 row)
И снова видим пустое поле, которое обозначает "привилегии по умолчанию". В данном случае это означает, что доступ есть только у владельца.
Предоставим доступ на чтение и изменение:
r1=> GRANT SELECT,UPDATE ON r1.t1 TO r2;
GRANT
А для второй таблицы - доступ на вставку и чтение одного столбца:
r1=> GRANT SELECT(n),INSERT ON r1.t2 TO r2;
GRANT
Посмотрим, как изменились привилегии:
r1=> \dp r1.*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
r1 | t1 | table | r1=arwdDxt/r1 +| |
| | | r2=rw/r1 | |
r1 | t2 | table | r1=arwdDxt/r1 +| n: +|
| | | r2=a/r1 | r2=r/r1 |
(2 rows)
Новые обозначения (иногда не вполне очевидные) для привилегий:
Привилегии для столбцов отображаются отдельно.
На этот раз обращение увенчается успехом.
r2=> UPDATE r1.t1 SET n = n + 1;
UPDATE 1
r2=> SELECT * FROM r1.t1;
n ---- 43 (1 row)
Другие операции по-прежнему запрещены:
r2=> DELETE FROM r1.t1;
ERROR: permission denied for relation t1
И вторая таблица:
r2=> INSERT INTO r1.t2(n) VALUES (2);
INSERT 0 1
r2=> SELECT n FROM r1.t2;
n --- 1 2 (2 rows)
А чтение другого столбца запрещено:
r2=> SELECT * FROM r1.t2;
ERROR: permission denied for relation t2
Привилегию можно выдать с правом управления привилегией (дальнейшей ее перевыдачи третьим ролям). Попробуем:
r1=> GRANT SELECT, DELETE ON t2 TO r2 WITH GRANT OPTION;
GRANT
В списке привилегий право управления обозначается звездочкой справа от символа самой привилегии.
r1=> \dp t2
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
r1 | t2 | table | r1=arwdDxt/r1 +| n: +|
| | | r2=ar*d*/r1 | r2=r/r1 |
(1 row)
Право управления можно отозвать отдельно от привилегии:
r1=> REVOKE GRANT OPTION FOR DELETE ON t2 FROM r2;
REVOKE
r1=> \dp t2
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
r1 | t2 | table | r1=arwdDxt/r1 +| n: +|
| | | r2=ar*d/r1 | r2=r/r1 |
(1 row)
При отзыве самой привилегии, право управления, конечно, тоже отзывается:
r1=> REVOKE SELECT ON t2 FROM r2;
REVOKE
r1=> \dp t2
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
r1 | t2 | table | r1=arwdDxt/r1 +| |
| | | r2=ad/r1 | |
(1 row)
Единственная привилегия для функций - выполнение. Создадим какую-нибудь функцию:
r1=> CREATE FUNCTION f() RETURNS integer AS $$ SELECT count(*)::integer FROM r1.t2; $$ VOLATILE LANGUAGE SQL;
CREATE FUNCTION
Тонкий момент: псевдороль public автоматически получает эту привилегию для любой создаваемой функции. Поэтому, например, r2 может выполнить функцию, которую мы только что создали.
Отчасти такая "дыра" компенсируется тем, что по умолчанию функция выполняется с правами вызывающего:
r2=> SELECT r1.f();
ERROR: permission denied for relation t2 CONTEXT: SQL function "f" statement 1
Поэтому роль r2 не сможет получить доступ к объектам, на которые ей не выданы привилегии.
Более того, функция вызывается в окружении вызывающей роли, включая путь поиска. Это свойство позволяет писать "универсальные" функции. Уберем явное имя схемы:
r1=> CREATE OR REPLACE FUNCTION f() RETURNS integer AS $$ SELECT count(*)::integer FROM t2; $$ SECURITY INVOKER VOLATILE LANGUAGE SQL;
CREATE FUNCTION
Фраза SECURITY INVOKER подразумевается по умолчанию.
Если r2 создаст свою таблицу t2, функция будет работать с любой из них, в зависимости от вызвавшего:
r2=> CREATE TABLE t2(n numeric);
CREATE TABLE
r2=> SELECT r1.f();
f --- 0 (1 row)
r1=> SELECT r1.f();
f --- 2 (1 row)
Другой доступный вариант - объявить функцию, как работающую с правами создавшего (SECURITY DEFINER):
r1=> CREATE OR REPLACE FUNCTION f() RETURNS integer AS $$ SELECT count(*)::integer FROM t2; $$ SECURITY DEFINER VOLATILE LANGUAGE SQL;
CREATE FUNCTION
В этом случае функция работает в контексте создавшей ее роли, независимо от того, кто ее вызывает:
r2=> SELECT r1.f();
f --- 2 (1 row)
r1=> SELECT r1.f();
f --- 2 (1 row)
В таком случае, конечно, надо внимательно следить за выданными привилегиями. Скорее всего, потребуется отозвать EXECUTE у роли public и выдавать ее явно только нужным ролям.
r1=> REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA r1 FROM public;
REVOKE
r2=> SELECT r1.f();
ERROR: permission denied for function f
Дело осложняется тем, что привилегия на выполнение автоматически выдается роли public на каждую вновь создаваемую функцию, и это поведение нельзя изменить.
r1=> CREATE FUNCTION f_new() RETURNS integer AS $$ SELECT 1; $$ LANGUAGE SQL;
CREATE FUNCTION
r2=> SELECT r1.f_new();
f_new
-------
1
(1 row)
Однако можно автоматически отзывать эту привилегию с помощью механизма привилегий по умолчанию:
r1=> ALTER DEFAULT PRIVILEGES FOR ROLE r1 REVOKE EXECUTE ON FUNCTIONS FROM public;
ALTER DEFAULT PRIVILEGES
r1=> \ddp
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+----------+-------------------
r1 | | function | r1=X/r1
(1 row)
r1=> DROP FUNCTION f_new();
DROP FUNCTION
r1=> CREATE FUNCTION f_new() RETURNS integer AS $$ SELECT 1; $$ LANGUAGE SQL;
CREATE FUNCTION
r2=> SELECT r1.f_new();
ERROR: permission denied for function f_new
Политики защиты позволяют разграничить доступ к таблице по строкам в зависимости от текущей роли.
r1=> SELECT * FROM r1.t2;
n | who ---+----- 1 | r1 2 | r2 (2 rows)
Для примера сделаем так, чтобы роль, читающая таблицу, могла видеть только "свои" строки, в которых поле who содержит имя текущей роли.
r1=> CREATE POLICY who_policy ON r1.t2 USING (who = current_user);
CREATE POLICY
Чтобы защита начала работать, ее необходимо включить:
r1=> ALTER TABLE r1.t2 ENABLE ROW LEVEL SECURITY;
ALTER TABLE
И вернем роли r2 доступ к таблице на чтение:
r1=> GRANT SELECT ON r1.t2 TO r2;
GRANT
Теперь роль r2 видит только "свои" строки. Фактически, при выполнении запроса каждая строка проверяется на выполнение указанного в политике предиката.
r2=> SELECT * FROM r1.t2;
n | who ---+----- 2 | r2 (1 row)
r2=> INSERT INTO r1.t2(n) VALUES (3);
INSERT 0 1
r2=> SELECT * FROM r1.t2;
n | who ---+----- 2 | r2 3 | r2 (2 rows)
На владельца таблицы политики защиты строк обычно не действуют:
r1=> SELECT * FROM r1.t2;
n | who ---+----- 1 | r1 2 | r2 3 | r2 (3 rows)
Но можно ограничить и самого себя:
r1=> ALTER TABLE r1.t2 FORCE ROW LEVEL SECURITY;
ALTER TABLE
r1=> SELECT * FROM r1.t2;
n | who ---+----- 1 | r1 (1 row)
Конец демонстрации.