Ролевая модель и права доступа#

Для обеспечения контроля доступа к защищаемым элементам системы применяется система разграничения доступа, основанная на ролях.

Права доступа пользователей устанавливаются парольными политиками в соответствии с ролевой моделью. Управление парольными политиками описано в документе «Руководство по системному администрированию», раздел «Сценарии администрирования», подраздел «Интерфейс управления парольными политиками: PL/pgSQL API»).

Структура ролей#

Рассматриваемые ниже роли можно разделить на:

  • административные, использующиеся для системного управления кластером и прикладного администрирования данных;

  • прикладные, предназначенные для пользователей, отвечающих за наполнение БД данными;

  • специальные, создающиеся для соответствия принятым в организации подходам к аудиту, мониторингу, резервному копированию и восстановлению и др.

При создании кластера инсталлятор создает следующие групповые роли:

Групповая роль

Назначение

db_admin

Административная роль, обладающая привилегией superuser. Владелец TABLESPACE, DATABASE. Выдается администраторам для управления СУБД. На DEV-стендах выдается владельцу БД

as_admin

Административная роль — владелец схемы. Выдается администраторам АС. Используется для создания новых объектов в БД (таблиц, функций, последовательностей и т.п) и их изменения. Является владельцем пользовательской схемы. Также выдается доменным или локальным ТУЗ для инструментов автоматизации

as_TUZ

Прикладная роль для доступа к пользовательским данным. Имеет привилегии для совершение DML-операций с объектами схемы. Работает в пуле соединений АС, а также подключается через pgBouncer

as_admin_read

Административная роль, отличающаяся от as_admin тем, что предназначена только для просмотра

all-sa-pam-group

Выдается учетным записям, используемым для доступа через АС PAM (privileged access management). Используется исключительно для организации аутентификации, конфигурирования pg_hba.conf

Дополнительно создаются следующие специальные инфраструктурные ТУЗ:

Инфраструктурная роль

Назначение

backup_user

ТУЗ с локальной аутентификации для интеграции с СРК

zabbix_oasubd

ТУЗ с локальной аутентификацией для интеграции с системой мониторинга

monitoring_php

ТУЗ с локальной аутентификацией для интеграции с WatchDoc

auditor

ТУЗ с локальной аутентификацией для проведения аудита безопасности

pgbouncer

ТУЗ с локальной аутентификацией для подключения локальной службы PgBouncer

patroni

ТУЗ с локальной аутентификацией для подключения локальной службы Patroni

all-sa-pam19002

ТУЗ с внешней аутентификацией для подключения через АС PAM Администраторов БД

all-sa-pam19002_ro

ТУЗ с внешней аутентификацией для подключения через АС PAM Администраторов систем с привилегиями только на чтение

Создание ролей#

Роли создаются на уровне кластера и действуют на все его базы данных. Инсталляционный скрипт создает роли в соответствии с ролевой моделью (за исключением pgbouncer, patroni и cron). Далее этот способ рассматривается как стандартный.

-- Group Roles
CREATE ROLE db_admin           WITH   INHERIT NOLOGIN CONNECTION LIMIT 5 VALID UNTIL 'infinity' SUPERUSER CREATEROLE CREATEDB;
CREATE ROLE as_admin           WITH NOINHERIT NOLOGIN CONNECTION LIMIT 5 VALID UNTIL 'infinity';
CREATE ROLE as_TUZ             WITH   INHERIT NOLOGIN CONNECTION LIMIT 5 VALID UNTIL 'infinity';
CREATE ROLE as_admin_read      WITH NOINHERIT NOLOGIN CONNECTION LIMIT 5 VALID UNTIL 'infinity';
CREATE ROLE "all-sa-pam-group" WITH NOINHERIT NOLOGIN CONNECTION LIMIT 5 VALID UNTIL 'infinity';

-- TUZs
CREATE USER "all-sa-pam19002"    WITH NOINHERIT CONNECTION LIMIT 5 VALID UNTIL 'infinity'SUPERUSER CREATEROLE CREATEDB;
CREATE USER "all-sa-pam19002_ro" WITH NOINHERIT CONNECTION LIMIT 5 VALID UNTIL 'infinity';
CREATE USER auditor              WITH NOINHERIT PASSWORD 'encriptedpassword';
CREATE USER backup_user          WITH NOINHERIT PASSWORD 'encriptedpassword' REPLICATION;
CREATE USER monitoring_php       WITH NOINHERIT CONNECTION LIMIT 10 PASSWORD 'encriptedpassword' CREATEROLE;
CREATE USER zabbix_oasubd        WITH NOINHERIT CONNECTION LIMIT 10 PASSWORD 'encriptedpassword';

-- Additional settings (audit, RO transaction)
ALTER ROLE as_admin       SET "pgaudit.log"                 TO              'ddl, role, connection, misc_set';
ALTER ROLE auditor        SET default_transaction_read_only TO                                                'on';
ALTER ROLE auditor        SET "pgaudit.log"                 TO 'read, write, ddl, role, connection, misc_set';
ALTER ROLE db_admin       SET "pgaudit.log"                 TO              'ddl, role, connection, misc_set';
ALTER ROLE monitoring_php SET "pgaudit.log"                 TO       'write, ddl, role, connection, misc_set';
ALTER ROLE zabbix_oasubd  SET default_transaction_read_only TO                                                'on';
ALTER ROLE zabbix_oasubd  SET "pgaudit.log"                 TO       'write, ddl, role, connection, misc_set';

Настройка ролей#

Настройка ролей (управление вложенностью ролей и назначение им системных привилегий) производится следующим образом:

-- Role memberships + System objects privileges
-- TUZ for PAM
GRANT "all-sa-pam-group" TO "all-sa-pam19002";
GRANT  db_admin          TO "all-sa-pam19002";
GRANT "all-sa-pam-group" TO "all-sa-pam19002_ro";
GRANT  as_admin_read     TO "all-sa-pam19002_ro";
 
-- as_admin_read group Role
GRANT pg_monitor           TO as_admin_read;
GRANT pg_read_all_settings TO as_admin_read;
GRANT pg_read_all_stats    TO as_admin_read;
GRANT pg_signal_backend    TO as_admin_read;
GRANT pg_stat_scan_tables  TO as_admin_read;

-- monitoring_php
GRANT pg_monitor TO monitoring_php;
GRANT pg_read_all_settings  TO monitoring_php;
GRANT pg_read_all_stats     TO monitoring_php;
GRANT pg_read_server_files  TO monitoring_php;
GRANT pg_signal_backend     TO monitoring_php;
GRANT pg_stat_scan_tables   TO monitoring_php;
GRANT pg_write_server_files TO monitoring_php;
GRANT USAGE ON SCHEMA ext   TO monitoring_php;
GRANT ALL ON FUNCTION ext.get_role_passwd()                TO monitoring_php;
GRANT ALL ON FUNCTION pg_catalog.pg_read_binary_file(text) TO monitoring_php;
GRANT ALL ON FUNCTION pg_catalog.pg_read_file(text)        TO monitoring_php;
GRANT ALL ON FUNCTION pg_catalog.pg_reload_conf()          TO monitoring_php;
 
-- zabbix_oasubd
GRANT pg_monitor           TO zabbix_oasubd;
GRANT pg_read_all_settings TO zabbix_oasubd;
GRANT pg_read_all_stats    TO zabbix_oasubd;

--update 02082022
grant USAGE  ON SCHEMA backup TO zabbix_oasubd;
grant select ON history       to zabbix_oasubd;
 
-- backup_user
GRANT USAGE   ON SCHEMA   pg_catalog                                         TO backup_user;
GRANT EXECUTE ON FUNCTION pg_catalog.current_setting(text)                   TO backup_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_in_recovery()                     TO backup_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_start_backup(text, boolean, boolean) TO backup_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup(boolean, boolean)        TO backup_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text)           TO backup_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_wal()                         TO backup_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_wal_replay_lsn()                TO backup_user;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current()                          TO backup_user;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current_snapshot()                 TO backup_user;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_snapshot_xmax(txid_snapshot)       TO backup_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_control_checkpoint()                 TO backup_user;
 
-- zabbix_oasubd
GRANT ALL ON FUNCTION pg_catalog.pg_ls_dir(text) TO zabbix_oasubd;

-- auditor
GRANT SELECT ON TABLE pg_catalog.pg_shadow TO auditor;

Установка связи ролей с объектами БД#

Также инсталляционный скрипт создает стандартные объекты БД Pangolin и назначает ролям привилегии на доступ к ним. Такими объектами являются:

  • табличное пространство — создается на уровне кластера;

  • пользовательская база данных — уровень кластера;

  • схема — уровень определенной базы данных;

  • объекты — создаются на уровне схемы.

Табличное пространство

Табличное пространство создается в директории /pgdata/05/tablespaces/ (может различаться в зависимости от мажорной версии). Владельцем табличного пространства является db_admin, а права на доступ выдается роли as_admin.

-- TABLESPACE
CREATE TABLESPACE <tablespace_name> OWNER db_admin LOCATION '/pgdata/05/tablespaces/';
GRANT ALL ON TABLESPACE <tablespace_name> TO as_admin;

База данных

В дополнение к стандартным базам данных Postgresql (template0, template1, postgres) создается пользовательская БД. Владелец данной БД — db_admin. Права на использование (USAGE) и логин на данную БД есть по умолчанию у всех вновь создаваемых ролей.

-- DATABASE
CREATE DATABASE <database_name> OWNER db_admin TEMPLATE "template0" ENCODING 'UTF-8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' TABLESPACE <tablespace_name>;

Схема и объекты схемы#

Схема создается в текущей базе данных. Схемы нужны для разделения доступов между данными на уровне пользователей и приложений. Существуют схемы для расширений, схема для pgbouncer, пользовательские схемы. В данном документе рассматриваются пользовательские схемы. Владельцем пользовательской схемы является as_admin. Права на использование схемы выдаются роли as_TUZ. Во время инсталляции создается пользовательская схема и назначаются привилегии в соответствии с ролевой моделью (см. ниже).

Для использования схемы предоставляются две привилегии: USAGE и CREATE. Для создания новых объектов в схеме нужна привилегия CREATE. Для обращения к объектам схемы (при условии, что есть права на них) нужна привилегия USAGE. Привилегия CREATE есть только у роли as_admin — выдается с помощью запроса: GRANT ALL PRIVILEGES ON SCHEMA <schema_name> TO as_admin.

Внутри схемы хранятся объекты БД (таблицы, индексы, функции, и т.п). Для каждого типа объекта существует свой набор привилегий. Владельцем объекта становится роль, активная в момент выдачи SQL-запроса на его создание. Так как требуется, чтобы владельцем объектов БД была групповая роль as_admin, то для обеспечения данного условия используются следующие механизмы.

  1. При создании объектов администратор, выполняющий данную задачу, должен выдавать команду SET ROLE as_admin. Для автоматизации некоторых задач, а также в случае, когда другие роли данной УЗ не используются, можно один раз настроить УЗ для работы в роли as_admin, выдав ALTER ROLE <rolename> SET ROLE as_admin. В дальнейшем данная УЗ всегда будет работать как as_admin.

  2. Так как владельцем объектов может быть только групповая роль, в УЗ администраторов, имеющих права на создание объектов отключена опция INHERIT. Для конечных пользователей БД (ТУЗ) отключать INHERIT не требуется — они не имеют привилегии на создание объектов, но зато автоматически наследуют все другие необходимые привилегии.

    Роль as_admin, которая имеет привилегию на создание объектов, нельзя унаследовать (она имеет свойство NOINHERIT). Это связано с тем, что на момент инсталляции неизвестно, какие пользователи будут работать с БД в дальнейшем и какие привилегии им будут назначены. Групповые роли позволяют систематизировать пользовательскую структуру. А для того, чтобы владельцем объекта становилась именно роль as_admin, необходимо явно переключаться на нее с помощью запроса: SET ROLE as_admin.

  3. После создания объекта роль as_admin должна предоставить необходимую привилегию роли, использующей объект (это в первую очередь as_TUZ). Данный процесс можно облегчить, если назначить default privileges создающей роли (as_admin). С помощью default privileges права заранее предоставляются на объекты, которые будут создаваться в будущем в данной схеме. Посмотреть существующие на текущий момент в БД default privileges можно в консоли psql с помощью команды \ddp (см. ниже).

    Инсталляционный скрипт создает схему и назначает привилегии следующим образом:

    \c <database_name>

-- Create schema with grant privileges
CREATE SCHEMA <schema_name> AUTHORIZATION as_admin;

-- as_TUZ
GRANT USAGE                                                  ON SCHEMA <schema_name> TO as_TUZ;
GRANT SELECT, UPDATE, INSERT, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA <schema_name> TO as_TUZ;
GRANT ALL PRIVILEGES ON ALL SEQUENCES                        IN SCHEMA <schema_name> TO as_TUZ;
GRANT EXECUTE ON ALL FUNCTIONS                               IN SCHEMA <schema_name> TO as_TUZ;
GRANT EXECUTE ON ALL ROUTINES                                IN SCHEMA <schema_name> TO as_TUZ;
GRANT EXECUTE ON ALL PROCEDURES                              IN SCHEMA <schema_name> TO as_TUZ;
ALTER DEFAULT PRIVILEGES FOR ROLE as_admin IN SCHEMA <schema_name> GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO as_TUZ;
ALTER DEFAULT PRIVILEGES FOR ROLE as_admin IN SCHEMA <schema_name> GRANT ALL PRIVILEGES ON SEQUENCES                        TO as_TUZ;
ALTER DEFAULT PRIVILEGES FOR ROLE as_admin IN SCHEMA <schema_name> GRANT EXECUTE ON FUNCTIONS                               TO as_TUZ;
ALTER DEFAULT PRIVILEGES FOR ROLE as_admin IN SCHEMA <schema_name> GRANT EXECUTE ON ROUTINES                                TO as_TUZ;
ALTER DEFAULT PRIVILEGES FOR ROLE as_admin IN SCHEMA <schema_name> GRANT USAGE ON TYPES                                     TO as_TUZ;

-- as_admin
GRANT ALL PRIVILEGES                   ON SCHEMA <schema_name>  TO as_admin;
GRANT ALL PRIVILEGES ON ALL TABLES     IN SCHEMA <schema_name>  TO as_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES  IN SCHEMA <schema_name>  TO as_admin;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS  IN SCHEMA <schema_name>  TO as_admin;
GRANT ALL PRIVILEGES ON ALL ROUTINES   IN SCHEMA <schema_name>  TO as_admin;
GRANT ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA <schema_name>  TO as_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE as_admin IN SCHEMA <schema_name> GRANT ALL PRIVILEGES ON TABLES    TO as_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE as_admin IN SCHEMA <schema_name> GRANT ALL PRIVILEGES ON SEQUENCES TO as_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE as_admin IN SCHEMA <schema_name> GRANT ALL PRIVILEGES ON FUNCTIONS TO as_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE as_admin IN SCHEMA <schema_name> GRANT ALL PRIVILEGES ON ROUTINES  TO as_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE as_admin IN SCHEMA <schema_name> GRANT ALL PRIVILEGES ON TYPES     TO as_admin;

-- as_admin_read
GRANT USAGE                    ON SCHEMA <schema_name> TO as_admin_read;
GRANT SELECT ON ALL TABLES     IN SCHEMA <schema_name> TO as_admin_read;
GRANT USAGE ON ALL SEQUENCES   IN SCHEMA <schema_name> TO as_admin_read;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA <schema_name> TO as_admin_read;
ALTER DEFAULT PRIVILEGES FOR ROLE as_admin in SCHEMA <schema_name> GRANT SELECT ON TABLES     TO as_admin_read;
ALTER DEFAULT PRIVILEGES FOR ROLE as_admin in SCHEMA <schema_name> GRANT USAGE ON SEQUENCES   TO as_admin_read;
ALTER DEFAULT PRIVILEGES FOR ROLE as_admin in SCHEMA <schema_name> GRANT EXECUTE ON FUNCTIONS TO as_admin_read;

    В версии скрипта, приведенной выше, присутствует дублирование default privileges обычными привилегиями, выданными с помощью GRANT. Обычные привилегии имеют отношение к уже существующим объектам. Default privileges будут влиять на объекты, создаваемые в будущем.

Мониторинг привилегий#

При просмотре привилегий, определенных в кластере, отталкиваются от объекта или пользователя. И те, и другие привязаны к конкретной базе данных (\c "база даных").

  1. Объектные привилегии можно просматривать с помощью команд psql:

    • на схему: \dn+ "схема" (например, \dn sch1);

    • на таблицу: \dp+ "схема"."таблица", \dp+ "схема".*, \dp+ *."таблица";

    • default privileges: \ddp.

    Результат выводится с использованием аббревиатур: r - SELECT, a - INSERT, w - UPDATE, d - DELETE, D - TRUNCATE, x - REFERENCES, t - TRIGGER, C - CREATE, c - CONNECT, T - TEMPORARY, X - EXECUTE, U - USAGE.

    Например, привилегии на пользовательскую схему, таблицу и default privileges выглядят так:

    db1=> \dn+ sch1
                    List of schemas
Name |  Owner   |    Access privileges     | Description 
------+----------+--------------------------+-------------
sch1 | as_admin | as_admin=UC/as_admin    +| 
    |          | as_tuz=U/as_admin       +| 
    |          | as_admin_read=U/as_admin | 

db1=> \dp+ sch1.test
                                Access privileges
Schema | Name | Type  |     Access privileges     | Column privileges | Policies 
--------+------+-------+---------------------------+-------------------+----------
sch1   | test | table | as_admin=arwdDxt/as_admin+|                   | 
        |      |       | as_tuz=arwdD/as_admin    +|                   | 
        |      |       | as_admin_read=r/as_admin  |                   | 

db1=> \ddp
                Default access privileges
Owner   | Schema |   Type   |     Access privileges     
----------+--------+----------+---------------------------
as_admin | sch1   | function | as_admin=X/as_admin      +
        |        |          | as_tuz=X/as_admin        +
        |        |          | as_admin_read=X/as_admin
as_admin | sch1   | sequence | as_admin=rwU/as_admin    +
        |        |          | as_tuz=rwU/as_admin      +
        |        |          | as_admin_read=U/as_admin
as_admin | sch1   | table    | as_admin=arwdDxt/as_admin+
        |        |          | as_tuz=arwdD/as_admin    +
        |        |          | as_admin_read=r/as_admin
as_admin | sch1   | type     | as_admin=U/as_admin      +
        |        |          | as_tuz=U/as_admin

  2. Пользовательские привилегии можно просматривать с помощью SQL-запросов, обратившись к таблицам в схеме information_schema и предварительно подключившись к соответствующей базе данных. Например:

    postgres=# \c db1
You are now connected to database "db1" as user "postgres".

db1=# SELECT * FROM information_schema.table_privileges WHERE grantee <> 'postgres' AND grantee <> 'PUBLIC';
grantor  |    grantee    | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
----------+---------------+---------------+--------------+------------+----------------+--------------+----------------
as_admin | as_admin      | db1           | sch1         | test       | INSERT         | YES          | NO
...

Создание конечных пользователей#

В соответствии с описанными выше механизмами при создании конечных пользователей «Администраторов» им намеренно выставляется привилегия NOINHERIT, так как требуется чтобы владельцами создаваемых объектов БД были групповые роли:

-- Создание Администратора СУБД
CREATE USER user_db WITH ENCRYPTED PASSWORD 'xxxxxxxxxxxx' NOINHERIT;
GRANT db_admin TO user_db;

-- Создание Администратора АС
CREATE USER user_as WITH ENCRYPTED PASSWORD 'xxxxxxxxxxxx' NOINHERIT;
GRANT as_admin TO user_as;

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

-- Создание ТУЗов
CREATE USER user_tuz WITH ENCRYPTED PASSWORD 'xxxxxxxxxxxxx' INHERIT;
GRANT as_TUZ TO user_tuz;