Начнем с создания роли и базы данных. Чтобы роль смогла подключиться, она должна иметь:
Посмотрим на 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 alice LOGIN PASSWORD 'alicepass';
CREATE ROLE
student=# CREATE DATABASE access_overview;
CREATE DATABASE
Осталось разобраться с привилегией CONNECT. Ее наличие можно проверить с помощью функции:
student=# SELECT has_database_privilege('alice','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 alice;
GRANT
student=# REVOKE CONNECT ON DATABASE access_overview FROM alice;
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 автоматически получает каждый раз, когда создается новая база.
Теперь понятно, откуда у Алисы привилегия CONNECT - она получена от групповой роли public. Попробуем подключиться:
student=# \c "host=localhost user=alice dbname=access_overview password=alicepass"
You are now connected to database "access_overview" as user "alice" on host "localhost" at port "5432".
Пусть теперь Алиса создаст одноименную схему и несколько объектов в ней.
alice=> CREATE SCHEMA alice;
ERROR: permission denied for database access_overview
В чем проблема?
У Алисы нет привилегии для создания схем в БД. Выдадим ее:
alice=> \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 alice;
GRANT
Пробуем еще раз:
student=# \c "host=localhost user=alice dbname=access_overview password=alicepass"
You are now connected to database "access_overview" as user "alice" on host "localhost" at port "5432".
alice=> CREATE SCHEMA alice;
CREATE SCHEMA
Теперь, поскольку Алиса является владельцем своей схемы, она имеет все привилегии на нее и может создавать в ней любые объекты. По умолчанию будет использоваться именно эта схема:
alice=> SELECT current_schemas(true);
current_schemas
---------------------------
{pg_catalog,alice,public}
(1 row)
Создадим две таблицы.
alice=> CREATE TABLE t1(n numeric);
CREATE TABLE
alice=> INSERT INTO t1 VALUES (1);
INSERT 0 1
alice=> CREATE TABLE t2(n numeric, who text DEFAULT current_user);
CREATE TABLE
alice=> INSERT INTO t2(n) VALUES (1);
INSERT 0 1
Теперь создадим другую роль - Боба, который будет обращаться к объектам, принадлежащим Алисе.
alice=> \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 bob LOGIN PASSWORD 'bobpass';
CREATE ROLE
student=# \c "host=localhost user=bob dbname=access_overview password=bobpass"
You are now connected to database "access_overview" as user "bob" on host "localhost" at port "5432".
Попробуем обратиться к таблице t1.
bob=> SELECT * FROM alice.t1;
ERROR: permission denied for schema alice
LINE 1: SELECT * FROM alice.t1;
^
В чем причина ошибки?
Нет доступа к схеме, так как Боб не суперпользователь и не владелец.
bob=> \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
alice | alice | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(2 rows)
Формат вывода привилегий нам уже знаком, единственная новая привилегия здесь:
Кстати, видно, что псевдороль public имеет доступ к схеме public.
Предоставим доступ к схеме для Боба. Это может сделать Алиса, как владелец.
student=# \c "host=localhost user=alice dbname=access_overview password=alicepass"
You are now connected to database "access_overview" as user "alice" on host "localhost" at port "5432".
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)
И снова видим пустое поле, которое обозначает "привилегии по умолчанию". В данном случае это означает, что доступ есть только у владельца.
Предоставим доступ на чтение и изменение:
alice=> GRANT SELECT,UPDATE ON alice.t1 TO bob;
GRANT
А для второй таблицы - доступ на вставку и чтение одного столбца:
alice=> GRANT SELECT(n),INSERT ON alice.t2 TO bob;
GRANT
Посмотрим, как изменились привилегии:
alice=> \dp alice.*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
alice | t1 | table | alice=arwdDxt/alice+| |
| | | bob=rw/alice | |
alice | t2 | table | alice=arwdDxt/alice+| n: +|
| | | bob=a/alice | bob=r/alice |
(2 rows)
Новые обозначения (иногда не вполне очевидные) для привилегий:
Привилегии для столбцов отображаются отдельно.
На этот раз обращение увенчается успехом.
bob=> UPDATE alice.t1 SET n = n + 1;
UPDATE 1
bob=> SELECT * FROM alice.t1;
n --- 2 (1 row)
Другие операции по-прежнему запрещены:
bob=> DELETE FROM alice.t1;
ERROR: permission denied for relation t1
И вторая таблица:
bob=> INSERT INTO alice.t2(n) VALUES (100);
INSERT 0 1
bob=> SELECT n FROM alice.t2;
n ----- 1 100 (2 rows)
А чтение другого столбца запрещено:
bob=> SELECT * FROM alice.t2;
ERROR: permission denied for relation t2
Единственная привилегия для функций - выполнение. Создадим какую-нибудь функцию:
alice=> CREATE FUNCTION f() RETURNS integer AS $$ SELECT count(*)::integer FROM t2; $$ VOLATILE LANGUAGE SQL;
CREATE FUNCTION
Тонкий момент: псевдороль public автоматически получает эту привилегию для любой создаваемой функции. Поэтому, например, Боб может выполнить функцию, которую только что создала Алиса.
Отчасти такая "дыра" компенсируется тем, что по умолчанию (или при явном указании 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 --- 2 (1 row)
Другой доступный вариант - объявить функцию, как работающую с правами создавшего (SECURITY DEFINER):
alice=> CREATE OR REPLACE FUNCTION f() RETURNS integer AS $$ SELECT count(*)::integer FROM t2; $$ SECURITY DEFINER VOLATILE LANGUAGE SQL;
CREATE FUNCTION
В этом случае функция работает в контексте создавшей роли, независимо от того, кто ее вызывает:
bob=> SELECT alice.f();
f --- 2 (1 row)
alice=> SELECT alice.f();
f --- 2 (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)
Однако можно автоматически отзывать эту привилегию с помощью механизма привилегий по умолчанию:
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=> SELECT * FROM alice.t2;
n | who -----+------- 1 | alice 100 | bob (2 rows)
Для примера сделаем так, чтобы роль, читающая таблицу, могла видеть только "свои" строки, в которых поле who содержит ее имя.
alice=> CREATE POLICY who_policy ON alice.t2 USING (who = current_user);
CREATE POLICY
Чтобы защита начала работать, ее необходимо включить:
alice=> ALTER TABLE alice.t2 ENABLE ROW LEVEL SECURITY;
ALTER TABLE
Теперь Боб видит только "свои" строки. Фактически, при выполнении запроса каждая строка проверяется на выполнение указанного в политике предиката.
bob=> SELECT n FROM alice.t2;
n ----- 100 (1 row)
bob=> INSERT INTO alice.t2(n) VALUES (101);
INSERT 0 1
bob=> SELECT n FROM alice.t2;
n ----- 100 101 (2 rows)
На владельца таблицы политики защиты строк обычно не действуют:
alice=> SELECT * FROM alice.t2;
n | who -----+------- 1 | alice 100 | bob 101 | bob (3 rows)
Но Алиса может ограничить и сама себя:
alice=> ALTER TABLE alice.t2 FORCE ROW LEVEL SECURITY;
ALTER TABLE
alice=> SELECT * FROM alice.t2;
n | who ---+------- 1 | alice (1 row)
Конец демонстрации.