Перейти к содержимому

16 Занятие 16: Безопасность / Защищенная схема

Исходная страница в нативном Markdown: Безопасность / Защищенная схема
Примечание

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: pgpro_16_vault.html Archive: PGPRO-handouts-16.zip Страниц PDF: 31

Page 1: Безопасность / Защищенная схема

Авторские права

© Postgres Professional, 2023–2026 Авторы: Алексей Береснев, Илья Баштанов, Павел Толмачев, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

Page 2: Темы

  • Разделение ролей
  • Создание защищенной схемы
  • Предоставление доступа к защищаемым данным
  • Логическая копия защищенной схемы и ее восстановление

Page 3: Разделение ролей

Администратор ОС

  • управление подключением суперпользователя Суперпользователь
    • создание административных ролей
    • решение особых задач
  • Административные роли
  • управление СУБД обеспечение работы пользователей Пользователи
    • работа с объектами

Комментарии преподавателя

При развертывании СУБД и на начальных этапах работы с ней активно используется роль суперпользователя. Однако ее использование для выполнения различных рутинных операций по обслуживанию системы в дальнейшем является небезопасным.

Работая под учетной записью суперпользователя, можно создать дополнительные административные роли и делегировать им большую часть решаемых задач по обслуживанию СУБД и обеспечению работы обычных пользователей, после чего отказаться от активного использования роли суперпользователя.

https://postgrespro.ru/docs/enterprise/16/sod-separation-of-duties#SOD-SE PARATION-OF-DUTIES

Для этого администратор ОС может вообще запретить подключение к серверу под учетной записью суперпользователя с помощью правил в файле pg_hba.conf.

При этом в ряде случаев — при выполнении редких и сложных задач, требующих суперпользовательского доступа — администратор ОС может остановить и временно безопасно запустить СУБД в однопользовательском (технологическом) режиме.

https://postgrespro.ru/docs/enterprise/16/restrict-dbms-admin-data-access# REVOKE-SUPERUSER-ACCESS-1

https://postgrespro.ru/docs/enterprise/16/app-postgres#APP-POSTGRES-SI NGLE-USER

Page 4: Защищенная схема

Зона повышенной безопасности на основе схемы

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

Комментарии преподавателя

Данные хранятся в таблицах, которые находятся в схемах баз данных. По умолчанию административные роли имеют доступ к этим данным, что может быть нежелательным. Для защиты чувствительных данных Postgres Pro Enterprise позволяет поместить их в специально созданную защищенную схему и определить правила доступа к ней.

У защищенной схемы есть владелец и выделенный администратор безопасности. Администратор безопасности (менеджер прав доступа) может лишь выдавать разрешения пользователям на доступ к объектам схемы, но сам работать с объектами не может. Владелец схемы, наоборот, может работать с объектами, но не может управлять правами доступа к ним.

https://postgrespro.ru/docs/enterprise/16/ddl-schemas#DDL-SCHEMAS-VA ULT

Предпочтительным является сценарий, когда владельца и администратора безопасности создает суперпользователь. Для этого администратор ОС временно разрешает вход для суперпользователя.

Page 5: Роли

Source slide page 5: Роли{#source-figure-005 caption=“Source slide page 5: Роли”}

Суперпользователь

  • создает роли
  • Администратор ОС
    • запрещает доступ суперпользователя

Комментарии преподавателя

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

Роль db_admin будет служить для таких административных задач, как создание баз данных.

Далее нам потребуются роли для владельца защищенной схемы (v_owner) и для администратора безопасности этой схемы (v_admin).

https://postgrespro.ru/docs/enterprise/16/restrict-dbms-admin-data-access# CREATE-MANAGERS-OF-SECURED-SCHEMA

Если эти роли были созданы некоторой административной ролью (не суперпользователем), то у них будет право ADMIN OPTION, от которого они сами отказаться не смогут. Чтобы исключить риски, связанные с доверием к указанным ролям, суперпользователю следует это право отозвать.

https://postgrespro.ru/docs/enterprise/16/restrict-dbms-admin-data-access# PROTECT-SECURED-SCHEMA-USERS-FROM-ADMINS

Наконец, роль v_user предназначена для обычного пользователя, который должен обращаться к защищенным данным.

После создания ролей администратор ОС может запретить суперпользовательское подключение к экземпляру СУБД.

Page 6: Роли

Создадим административную роль с правом создания баз данных. Это действие выполнит суперпользователь:

=> \conninfo
You are connected to database "student" as user "student" via socket in "/tmp" at port
"5432".
=> CREATE USER db_admin WITH CREATEDB;
CREATE ROLE

Можно наделить созданную роль еще рядом полномочий, позволяющих решать нужный круг задач администрирования, но для упрощения демонстрации мы не будем этого делать. Суперпользователь также создает роли владельца защищаемых данных, будущего администратора безопасности и пользователя:

=> CREATE USER v_owner;
CREATE ROLE
=> CREATE USER v_admin;
CREATE ROLE
=> CREATE USER v_user;
CREATE ROLE

Чтобы пользователь ОС postgres, под которым работает сервер СУБД, не мог изменить конфигурацию, администратор ОС меняет владельца конфигурационных файлов.

student$ sudo chown -c student: /var/lib/pgpro/ent-16/pg_hba.conf*
changed ownership of '/var/lib/pgpro/ent-16/pg_hba.conf' from postgres:postgres to
student:student
changed ownership of '/var/lib/pgpro/ent-16/pg_hba.conf.d' from postgres:postgres to
student:student

Но postgres все же нужен доступ на чтение и просмотр подкаталога:

student$ sudo chmod -c a+rX /var/lib/pgpro/ent-16/pg_hba.conf{,.d}
mode of '/var/lib/pgpro/ent-16/pg_hba.conf' changed from 0640 (rw-r-----) to 0644
(rw-r--r--)
mode of '/var/lib/pgpro/ent-16/pg_hba.conf.d' changed from 0750 (rwxr-x---) to 0755
(rwxr-xr-x)

Теперь можно запретить доступ суперпользователя к СУБД:

/var/lib/pgpro/ent-16/pg_hba.conf.d/pgpro_vault.conf
# TYPE  DATABASE  USER              ADDRESS  METHOD
local   all       postgres,student           reject
host    all       postgres,student  all      reject

Перечитаем конфигурацию и попробуем переподключиться:

student$ sudo systemctl reload postgrespro-ent-16.service
=> \c
\connect: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  pg_hba.conf
rejects connection for host "[local]", user "student", database "student", no encryption

Суперпользователь подключиться не может.

Page 7: База данных

Source slide page 7: База данных{#source-figure-007 caption=“Source slide page 7: База данных”}

Суперпользователь

  • создает роли
  • Администратор ОС
    • запрещает доступ суперпользователя
  • Административная роль
    • создает базу

Комментарии преподавателя

На следующем шаге административная роль создает базу данных.

Page 8: Схема

Source slide page 8: Схема{#source-figure-008 caption=“Source slide page 8: Схема”}

Суперпользователь

  • создает роли
  • Администратор ОС
    • запрещает доступ суперпользователя
  • Административная роль
    • создает базу
  • Суперпользователь (временный доступ)
    • создает схему
    • назначает администратора безопасности
    • назначает владельца

Комментарии преподавателя

Теперь уже можно создать зону повышенной безопасности — защищенную схему. Мы будем показывать одну, но, конечно, таких схем может быть несколько, и они могут принадлежать разным владельцам.

Команда ALTER SCHEMA … SECURITY OFFICER TO … устанавливает для схемы атрибут — роль администратора безопасности. После этого только указанная роль может управлять доступом к схеме и объектам в ней — схема становится защищенной.

Атрибут можно сбросить командой ALTER SCHEMA … RESET SECURITY OFFICER. Тогда схема снова станет незащищенной.

https://postgrespro.ru/docs/enterprise/16/sql-alterschema

Эти действия выполняет суперпользователь, для чего администратор ОС должен обеспечить ему временное подключение к экземпляру.

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

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

Page 9: Защищенная схема

Подключимся к серверу под административной ролью:

student$ psql -U db_admin -d postgres

Создадим базу данных:

db_admin=> CREATE DATABASE pgpro_vault;
CREATE DATABASE

Чтобы сделать схему защищенной, нужно назначить для нее администратора безопасности — это может сделать только суперпользователь, для которого доступ отключен. Чтобы он мог выполнить свою работу, можно подготовить скрипт, остановить экземпляр и выполнить команды в однопользовательском режиме:

/tmp/v_schema.sql
-- схема для защищаемых данных
CREATE SCHEMA v_schema;
-- администратор безопасности
ALTER SCHEMA v_schema SECURITY OFFICER TO v_admin;
-- право обращаться к объектам схемы
GRANT USAGE ON SCHEMA v_schema TO v_admin;
-- владелец данных
ALTER SCHEMA v_schema OWNER TO v_owner;
student$ sudo systemctl stop postgrespro-ent-16.service
postgres$ /opt/pgpro/ent-16/bin/postgres \
  --single \
  -D /var/lib/pgpro/ent-16 \
  pgpro_vault \
< /tmp/v_schema.sql

Снова запустим сервер в обычном режиме, подключимся к базе и посмотрим на схему — она защищенная (столбец Security officer):

student$ sudo systemctl start postgrespro-ent-16.service
student$ psql -U db_admin -d pgpro_vault
db_admin=> \dn+ v_schema
                             List of schemas
   Name   |  Owner  | Security officer | Access privileges  | Description
----------+---------+------------------+--------------------+-------------
 v_schema | v_owner | v_admin          | v_owner=UC/v_owner+|
          |         |                  | v_admin=U/v_owner  |
(1 row)

Теперь только владелец схемы сможет управлять данными и только администратор безопасности схемы сможет управлять доступом к этим данным. Другие пользователи доступа к схеме не имеют.

Page 10: Защищаемые данные

Source slide page 10: Защищаемые данные{#source-figure-010 caption=“Source slide page 10: Защищаемые данные”}

Владелец схемы

  • размещает данные в схеме

Комментарии преподавателя

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

Разместить данные в защищенной схеме можно двумя способами:

  1. Владелец защищенной схемы может создать объект непосредственно в этой схеме и затем наполнить его данными.

https://postgrespro.ru/docs/enterprise/16/restrict-dbms-admin-data-acces s#CREATE-TABLE-IN-SECURED-SCHEMA

  1. Можно перенести в защищенную схему уже существующий объект. В этом случае нужно передать его владельцу этой схемы. Однако это возможно, только если владелец объекта входит в роль владельца схемы с параметром членства SET. Владелец же защищенной схемы является независимым — у других административных ролей нет с ним общей роли. Поэтому здесь снова потребуется действие от имени суперпользователя.

https://postgrespro.ru/docs/enterprise/16/restrict-dbms-admin-data-acces s#MOVE-TABLE-TO-SECURED-SCHEMA

Page 11: Доступ к данным

Source slide page 11: Доступ к данным{#source-figure-011 caption=“Source slide page 11: Доступ к данным”}

Владелец схемы

  • размещает данные в схеме
  • Администратор безопасности
    • выдает разрешения пользователям

Комментарии преподавателя

Далее разрешения на доступ к объектам защищенной схемы пользователю выдает администратор безопасности.

Пользователи с доступом к зоне повышенной безопасности должны быть защищены. У административных ролей не должно быть параметра членства ADMIN в отношении этих пользователей. Если это не так, параметр следует явно отключить.

Page 12: Данные в защищенной схеме

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

db_admin=> SELECT rn.rolname AS role, mn.rolname member, gn.rolname grantor, am.admin_option
FROM pg_auth_members am
  JOIN pg_roles rn ON rn.oid = am.roleid
  JOIN pg_roles mn ON mn.oid = am.member
  JOIN pg_roles gn ON gn.oid = am.grantor
WHERE rn.rolname IN ('v_user','v_admin','v_owner')
  AND am.admin_option
ORDER BY member;
 role | member | grantor | admin_option
------+--------+---------+--------------
(0 rows)

Никто — это правильно. Теперь владелец схемы создает в защищенной схеме таблицу:

student$ psql -U v_owner -d pgpro_vault
v_owner=> CREATE TABLE v_schema.tbl AS (SELECT 'vault data by ' || current_user AS data);
SELECT 1

Но он не может выдавать права на доступ к данным в защищенной схеме:

v_owner=> GRANT ALL ON SCHEMA v_schema TO v_user;
WARNING:  no privileges were granted for "v_schema"
GRANT
v_owner=> GRANT ALL ON TABLE v_schema.tbl TO v_user;
WARNING:  no privileges were granted for "tbl"
GRANT

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

student$ psql -U v_admin -d pgpro_vault
v_admin=> GRANT ALL ON SCHEMA v_schema TO v_user;
GRANT
v_admin=> \dn+ v_schema
                             List of schemas
   Name   |  Owner  | Security officer | Access privileges  | Description
----------+---------+------------------+--------------------+-------------
 v_schema | v_owner | v_admin          | v_owner=UC/v_owner+|
          |         |                  | v_admin=U/v_owner +|
          |         |                  | v_user=UC/v_owner  |
(1 row)
v_admin=> GRANT ALL ON TABLE v_schema.tbl TO v_user;
GRANT
v_admin=> \dp v_schema.tbl
                                Access privileges
  Schema  | Name | Type  |    Access privileges    | Column privileges | Policies
----------+------+-------+-------------------------+-------------------+----------
 v_schema | tbl  | table | v_owner=arwdDxt/v_owner+|                   |
          |      |       | v_user=arwdDxt/v_owner  |                   |
(1 row)

Теперь пользователь может, например, читать строки таблицы:

db_admin=> \c - v_user

Page 13: Page 13

Source slide page 13: Page 13{#source-figure-013 caption=“Source slide page 13: Page 13”}

You are now connected to database "pgpro_vault" as user "v_user".
v_user=> SELECT * FROM v_schema.tbl;
         data
-----------------------
 vault data by v_owner
(1 row)

Сам администратор безопасности не имеет доступа к данным в защищенной схеме:

v_admin=> SELECT * FROM v_schema.tbl;
ERROR:  permission denied for table tbl

И не может выдать права ни себе, ни псевдороли public:

v_admin=> GRANT ALL ON TABLE v_schema.tbl TO v_admin, public;
GRANT

Права остались без изменений:

v_admin=> \dp v_schema.tbl
                                Access privileges
  Schema  | Name | Type  |    Access privileges    | Column privileges | Policies
----------+------+-------+-------------------------+-------------------+----------
 v_schema | tbl  | table | v_owner=arwdDxt/v_owner+|                   |
          |      |       | v_user=arwdDxt/v_owner  |                   |
(1 row)

Page 14: Логическая копия

Source slide page 14: Логическая копия{#source-figure-014 caption=“Source slide page 14: Логическая копия”}

Администратор

  • выгружает базу и все схемы, кроме защищенной

Комментарии преподавателя

Логическая резервная копия базы с зоной повышенной безопасности создается в несколько этапов без участия суперпользователя.

Сначала с помощью pg_dump администратор базы выгружает все данные, кроме данных защищенной схемы. Для этого используется параметр командной строки утилиты –exclude-schema.

https://postgrespro.ru/docs/enterprise/16/restrict-dbms-admin-data-access# BACKUP-APPLICATION-CHANGES

Page 15: Логическая копия

Source slide page 15: Логическая копия{#source-figure-015 caption=“Source slide page 15: Логическая копия”}

Администратор

  • выгружает базу и все схемы, кроме защищенной
  • Владелец схемы
    • выгружает данные защищенной схемы

Комментарии преподавателя

Затем владелец защищенной схемы выгружает данные зоны повышенной безопасности, используя параметры –schema и –no-privileges.

Page 16: Логическая копия

Source slide page 16: Логическая копия{#source-figure-016 caption=“Source slide page 16: Логическая копия”}

Администратор

  • выгружает базу и все схемы, кроме защищенной
  • Владелец схемы
    • выгружает данные защищенной схемы
    • выгружает права на объекты защищенной схемы

Комментарии преподавателя

Далее владелец защищенной схемы выгружает права доступа к объектам этой схемы. Для этого используются параметры –schema и –privileges-only.

Page 17: Логическая копия

Сделаем резервную копию базы данных. Сначала администратор базы данных выгружает все данные, кроме данных защищенной схемы:

student$ /opt/pgpro/ent-16/bin/pg_dump -U db_admin -d pgpro_vault \
--exclude-schema=v_schema \
> ~/tmp/data.sql

Затем владелец защищенной схемы выгружает данные этой схемы без прав доступа:

student$ /opt/pgpro/ent-16/bin/pg_dump -U v_owner -d pgpro_vault \
--schema=v_schema \
--no-privileges \
> ~/tmp/v_data.sql

После этого он же выгружает права доступа защищенной схемы:

student$ /opt/pgpro/ent-16/bin/pg_dump -U v_owner -d pgpro_vault \
--schema v_schema \
--privileges-only \
> ~/tmp/v_privs.sql

Page 18: Восстановление

Администратор

  • восстанавливает базу и схемы, кроме защищенной
  • v_db
  • схема
  • схема
  • схема

Комментарии преподавателя

Восстановление также выполняется в несколько этапов.

Сначала администратор СУБД восстанавливает базу со всеми данными, кроме данных защищенной схемы.

Page 19: Восстановление

Администратор

  • восстанавливает базу и схемы, кроме защищенной
  • v_db
  • Суперпользователь
    • схема
    • схема
    • схема
    • создает защищенную схему
    • v_schema

Комментарии преподавателя

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

Page 20: Восстановление

Source slide page 20: Восстановление{#source-figure-020 caption=“Source slide page 20: Восстановление”}

Администратор

  • восстанавливает базу и схемы, кроме защищенной
  • Суперпользователь
    • создает защищенную схему
  • Владелец схемы
    • восстанавливает данные защищенной схемы

Комментарии преподавателя

Далее владелец защищенной схемы восстанавливает объекты и данные этой схемы.

Page 21: Восстановление

Source slide page 21: Восстановление{#source-figure-021 caption=“Source slide page 21: Восстановление”}

Администратор

  • восстанавливает базу и схемы, кроме защищенной
  • Суперпользователь
    • создает защищенную схему
  • Владелец схемы
    • восстанавливает данные защищенной схемы
  • Администратор безопасности
    • восстанавливает права на объекты
    • защищенной схемы

Комментарии преподавателя

На завершающем этапе администратор безопасности восстанавливает права доступа к объектам защищенной схемы.

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

Снова отметим, что назначение администратора безопасности желательно выполнять раньше, чем назначение владельца схемы.

Page 22: Восстановление логической копии

Для восстановления защищенной схемы понадобятся два разных администратора: один из них сможет восстановить объекты, а другой — права доступа. Сначала администратор базы данных восстанавливает все данные, кроме защищенной схемы. Мы будем использовать тот же экземпляр, но создадим в нем новую базу:

student$ psql -U db_admin -d pgpro_vault -c 'CREATE DATABASE pgpro_vault_new'
CREATE DATABASE
student$ psql -U db_admin -d pgpro_vault_new -f ~/tmp/data.sql
SET
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
SET
SET
SET
SET
SET

Администратор ОС временно открывает вход для суперпользователя (переименуем дополнительный файл конфигурации):

student$ mv -v /var/lib/pgpro/ent-16/pg_hba.conf.d/pgpro_vault.{conf,save}
renamed '/var/lib/pgpro/ent-16/pg_hba.conf.d/pgpro_vault.conf' ->
'/var/lib/pgpro/ent-16/pg_hba.conf.d/pgpro_vault.save'
student$ sudo systemctl reload postgrespro-ent-16.service

Суперпользователь создает в новой базе схему, назначает ей администратора безопасности (схема становится защищенной) и меняет владельца на v_owner:

student$ psql -d pgpro_vault_new -c 'CREATE SCHEMA v_schema'
CREATE SCHEMA
student$ psql -d pgpro_vault_new -c 'ALTER SCHEMA v_schema SECURITY OFFICER TO v_admin'
ALTER SCHEMA
student$ psql -d pgpro_vault_new -c 'ALTER SCHEMA v_schema OWNER TO v_owner'
ALTER SCHEMA

Также суперпользователь выдает администратору безопасности право обращаться к объектам в защищенной схеме новой базы — оно понадобится при восстановлении привилегий:

student$ psql -d pgpro_vault_new -c 'GRANT USAGE ON SCHEMA v_schema TO v_admin'
GRANT

Теперь доступ суперпользователя можно закрыть. Снова подключаем дополнительный файл конфигурации:

student$ mv -v /var/lib/pgpro/ent-16/pg_hba.conf.d/pgpro_vault.{save,conf}
renamed '/var/lib/pgpro/ent-16/pg_hba.conf.d/pgpro_vault.save' ->
'/var/lib/pgpro/ent-16/pg_hba.conf.d/pgpro_vault.conf'
student$ sudo systemctl reload postgrespro-ent-16.service

В логической копии данных есть команда создания схемы — удалим ее, чтобы исключить ошибку при восстановлении:

student$ sed -i '/CREATE SCHEMA/d' ~/tmp/v_data.sql

Владелец защищенной схемы восстанавливает в ней данные:

student$ psql -U v_owner -d pgpro_vault_new -f ~/tmp/v_data.sql

Page 23: Page 23

SET
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
SET
SET
SET
SET
SET
ALTER SCHEMA
SET
SET
CREATE TABLE
ALTER TABLE
COPY 1

После этого администратор безопасности восстанавливает права доступа для защищенной схемы:

student$ psql -U v_admin -d pgpro_vault_new -f ~/tmp/v_privs.sql
SET
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
SET
SET
SET
SET
SET
GRANT
GRANT
ALTER SCHEMA
GRANT

Проверим, что получилось:

v_user=> \c pgpro_vault_new
You are now connected to database "pgpro_vault_new" as user "v_user".
v_user=> \dn+ v_schema
                             List of schemas
   Name   |  Owner  | Security officer | Access privileges  | Description
----------+---------+------------------+--------------------+-------------
 v_schema | v_owner | v_admin          | v_owner=UC/v_owner+|
          |         |                  | v_admin=U/v_owner +|
          |         |                  | v_user=UC/v_owner  |
(1 row)
v_user=> \dp v_schema.tbl
                                Access privileges
  Schema  | Name | Type  |    Access privileges    | Column privileges | Policies
----------+------+-------+-------------------------+-------------------+----------
 v_schema | tbl  | table | v_owner=arwdDxt/v_owner+|                   |
          |      |       | v_user=arwdDxt/v_owner  |                   |
(1 row)

Пользователь v_user успешно читает данные из таблицы в новой базе:

student$ psql -U v_user -d pgpro_vault_new -c 'SELECT * FROM v_schema.tbl'
         data
-----------------------
 vault data by v_owner
(1 row)

Page 24: Page 24

Page 25: Итоги

Безопасная работа с данными подразумевает разделение полномочий, в том числе административных

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

Page 26: Практика

  1. Исследуйте ситуацию, когда административная роль, создавшая пользователя защищенных данных, сохраняет членство в ней с параметром ADMIN. Устраните найденную уязвимость.
    1. Реализуйте сценарий переноса данных, созданных пользователем, не имеющим доступа к защищенной схеме, в уже существующую защищенную схему. Проверьте, что после перемещения данных бывший владелец не будет иметь к ним доступа.
    1. Выключите защиту схемы. Убедитесь, что разделение ролей для управления данными и доступа к ним у незащищенных схем отсутствует.

Комментарии преподавателя

  1. Параметр членства ADMIN дает возможность включения в роль других ролей с правом наследования доступа к объектам.

  2. Перенесите таблицу, созданную в схеме public административной ролью, не имеющей доступа к защищенной схеме.

Page 27: 1. Уязвимость ADMIN OPTION

Создадим административную роль (администратора СУБД) с правом создания ролей, а также роли владельца защищаемых данных и будущего администратора безопасности. Это делает суперпользователь:

=> \conninfo
You are connected to database "student" as user "student" via socket in "/tmp" at port
"5432".
=> CREATE USER dbms_admin WITH CREATEROLE;
CREATE ROLE
=> CREATE USER v_owner;
CREATE ROLE
=> CREATE USER v_admin;
CREATE ROLE

Также он создает базу данных и схему в ней:

=> CREATE DATABASE pgpro_vault OWNER dbms_admin;
CREATE DATABASE
=> \c pgpro_vault
You are now connected to database "pgpro_vault" as user "student".
=> CREATE SCHEMA v_schema;
CREATE SCHEMA

Назначает администратора безопасности схемы:

=> ALTER SCHEMA v_schema SECURITY OFFICER TO v_admin;
ALTER SCHEMA

И предоставляет ему право обращаться к объектам в этой схеме:

=> GRANT USAGE ON SCHEMA v_schema TO v_admin;
GRANT

А также делает владельцем схемы v_schema пользователя v_owner:

=> ALTER SCHEMA v_schema OWNER TO v_owner;
ALTER SCHEMA

Администратор СУБД создает пользователя, который будет работать с данными в защищенной схеме:

=> \c - dbms_admin
You are now connected to database "pgpro_vault" as user "dbms_admin".
dbms_admin=> CREATE USER v_user;
CREATE ROLE

Владелец защищенной схемы создает в ней таблицу и помещает туда данные:

student$ psql -U v_owner -d pgpro_vault
v_owner=> CREATE TABLE v_schema.tbl (
  id int GENERATED ALWAYS AS IDENTITY,
  note text,
  who name DEFAULT current_user
);
CREATE TABLE
v_owner=> INSERT INTO v_schema.tbl (note) VALUES ('Line 1');
INSERT 0 1

Администратор безопасности предоставляет пользователю v_user доступ к схеме и таблице:

student$ psql -U v_admin -d pgpro_vault

Page 28: Page 28

Source slide page 28: Page 28{#source-figure-028 caption=“Source slide page 28: Page 28”}

v_admin=> GRANT ALL ON SCHEMA v_schema TO v_user;
GRANT
v_admin=> GRANT ALL ON TABLE v_schema.tbl TO v_user;
GRANT

Получается так:

v_admin=> \dn+ v_schema
                             List of schemas
   Name   |  Owner  | Security officer | Access privileges  | Description
----------+---------+------------------+--------------------+-------------
 v_schema | v_owner | v_admin          | v_owner=UC/v_owner+|
          |         |                  | v_admin=U/v_owner +|
          |         |                  | v_user=UC/v_owner  |
(1 row)
v_admin=> \dp v_schema.tbl
                                Access privileges
  Schema  | Name | Type  |    Access privileges    | Column privileges | Policies
----------+------+-------+-------------------------+-------------------+----------
 v_schema | tbl  | table | v_owner=arwdDxt/v_owner+|                   |
          |      |       | v_user=arwdDxt/v_owner  |                   |
(1 row)

Теперь пользователь может работать с данными в защищенной схеме:

student$ psql -U v_user -d pgpro_vault
v_user=> INSERT INTO v_schema.tbl (note) VALUES ('Line 2');
INSERT 0 1

Администратор dbms_admin замечает, что может воспользоваться ADMIN OPTION в отношении созданной им роли v_user:

dbms_admin=> \drg
             List of role grants
 Role name  | Member of | Options | Grantor
------------+-----------+---------+----------
 dbms_admin | v_user    | ADMIN   | postgres
(1 row)

Обладая параметром членства ADMIN в роли v_user, администратор dbms_admin может включить себя в эту роль с параметром INHERIT:

dbms_admin=> GRANT v_user TO dbms_admin WITH INHERIT OPTION;
GRANT ROLE
dbms_admin=> \drg
                List of role grants
 Role name  | Member of |   Options    |  Grantor
------------+-----------+--------------+------------
 dbms_admin | v_user    | INHERIT, SET | dbms_admin
 dbms_admin | v_user    | ADMIN        | postgres
(2 rows)

Теперь у него есть такой же доступ к данным в защищенной схеме, как у доверенного пользователя v_user:

dbms_admin=> SELECT * FROM v_schema.tbl;
 id |  note  |   who
----+--------+---------
  1 | Line 1 | v_owner
  2 | Line 2 | v_user
(2 rows)

Чтобы исключить эту уязвимость, суперпользователь отзывает ADMIN OPTION у роли dbms_admin:

dbms_admin=> \c - postgres
You are now connected to database "pgpro_vault" as user "postgres".

Page 29: Page 29

=> REVOKE ADMIN OPTION FOR v_user FROM dbms_admin CASCADE;
REVOKE ROLE
=> \drg v_user
            List of role grants
 Role name | Member of | Options | Grantor
-----------+-----------+---------+---------
(0 rows)

Теперь доступ к данным закрыт:

=> \c - dbms_admin
You are now connected to database "pgpro_vault" as user "dbms_admin".
dbms_admin=> SELECT * FROM v_schema.tbl;
ERROR:  permission denied for schema v_schema
LINE 1: SELECT * FROM v_schema.tbl;
                      ^

И исключена возможность включения в роль:

dbms_admin=> GRANT v_user TO dbms_admin;
ERROR:  permission denied to grant role "v_user"
DETAIL:  Only roles with the ADMIN option on role "v_user" may grant this role.

2. Перенос данных в защищенную схему

Администратор СУБД создает новую таблицу в схеме public и наполняет ее данными:

dbms_admin=> CREATE TABLE tbl_bis (
  id int GENERATED ALWAYS AS IDENTITY,
  note text,
  who name DEFAULT current_user
);
CREATE TABLE
dbms_admin=> INSERT INTO tbl_bis (note) VALUES ('Line 1');
INSERT 0 1

В дальнейшем в таблице будут храниться конфиденциальные данные, поэтому стоит задача поместить ее в зону повышенной безопасности. Для этого роль dbms_admin передаст таблицу во владение пользователю v_owner, который перенесет ее в свою защищенную схему:

dbms_admin=> ALTER TABLE tbl_bis OWNER TO v_owner;
ERROR:  must be able to SET ROLE "v_owner"

Не получилось, так как для передачи владения нужно, чтобы у обоих пользователей была общая роль. Поэтому владельца меняет суперпользователь:

dbms_admin=> \c - postgres
You are now connected to database "pgpro_vault" as user "postgres".
=> ALTER TABLE tbl_bis OWNER TO v_owner;
ALTER TABLE

Новый владелец перемещает таблицу в защищенную схему:

v_owner=> ALTER TABLE tbl_bis SET SCHEMA v_schema;
ALTER TABLE

Администратор безопасности предоставляет доступ к таблице пользователю v_user:

v_admin=> GRANT SELECT ON TABLE v_schema.tbl_bis TO v_user;
GRANT

Теперь пользователь работает с данными:

v_user=> SELECT * FROM v_schema.tbl_bis;

Page 30: Page 30

 id |  note  |    who
----+--------+------------
  1 | Line 1 | dbms_admin
(1 row)

А у роли dbms_admin доступа к таблице уже нет:

=> \c - dbms_admin
You are now connected to database "pgpro_vault" as user "dbms_admin".
dbms_admin=> SELECT * FROM v_schema.tbl_bis;
ERROR:  permission denied for schema v_schema
LINE 1: SELECT * FROM v_schema.tbl_bis;
                      ^

3. Схема без защиты

Суперпользователь делает схему v_schema обычной, убрав атрибут security officer:

dbms_admin=> \c - postgres
You are now connected to database "pgpro_vault" as user "postgres".
=> ALTER SCHEMA v_schema RESET SECURITY OFFICER;
ALTER SCHEMA
=> \dn+ v_schema
                             List of schemas
   Name   |  Owner  | Security officer | Access privileges  | Description
----------+---------+------------------+--------------------+-------------
 v_schema | v_owner |                  | v_owner=UC/v_owner+|
          |         |                  | v_admin=U/v_owner +|
          |         |                  | v_user=UC/v_owner  |
(1 row)

Теперь владельцу данных ничего не мешает распоряжаться доступом к ним. Разделения ответственностей ролей больше нет!

v_owner=> GRANT USAGE ON SCHEMA v_schema TO dbms_admin;
GRANT
v_owner=> GRANT SELECT ON TABLE v_schema.tbl, v_schema.tbl_bis TO dbms_admin;
GRANT

Администратор СУБД может видеть данные в таблицах схемы v_schema:

=> \c - dbms_admin
You are now connected to database "pgpro_vault" as user "dbms_admin".
dbms_admin=> SELECT * FROM v_schema.tbl;
 id |  note  |   who
----+--------+---------
  1 | Line 1 | v_owner
  2 | Line 2 | v_user
(2 rows)
dbms_admin=> SELECT * FROM v_schema.tbl_bis;
 id |  note  |    who
----+--------+------------
  1 | Line 1 | dbms_admin
(1 row)

Уборка

Удалим созданную базу данных и роли.

dbms_admin=> \c student student
You are now connected to database "student" as user "student".
student=# DROP DATABASE pgpro_vault WITH (FORCE);

Page 31: Page 31

DROP DATABASE
student=# DROP USER dbms_admin, v_owner, v_admin, v_user;
DROP ROLE