# Занятие 16: Безопасность / Защищенная схема > Исходная страница в нативном Markdown: Безопасность / Защищенная схема --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [pgpro_16_vault.html](https://edu.postgrespro.ru/16/pgpro-16/pgpro_16_vault.html) > **Archive:** [PGPRO-handouts-16.zip](https://edu.postgrespro.ru/16/PGPRO-handouts-16.zip) > **Страниц PDF:** 31 ## Page 1: Безопасность / Защищенная схема {#source-page-001} ### Авторские права © Postgres Professional, 2023–2026 Авторы: Алексей Береснев, Илья Баштанов, Павел Толмачев, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Разделение ролей - Создание защищенной схемы - Предоставление доступа к защищаемым данным - Логическая копия защищенной схемы и ее восстановление ## Page 3: Разделение ролей {#source-page-003} **Администратор ОС** - управление подключением суперпользователя Суперпользователь - создание административных ролей - решение особых задач - Административные роли - управление СУБД обеспечение работы пользователей Пользователи - работа с объектами ### Комментарии преподавателя При развертывании СУБД и на начальных этапах работы с ней активно используется роль суперпользователя. Однако ее использование для выполнения различных рутинных операций по обслуживанию системы в дальнейшем является небезопасным. Работая под учетной записью суперпользователя, можно создать дополнительные административные роли и делегировать им большую часть решаемых задач по обслуживанию СУБД и обеспечению работы обычных пользователей, после чего отказаться от активного использования роли суперпользователя. PARATION-OF-DUTIES Для этого администратор ОС может вообще запретить подключение к серверу под учетной записью суперпользователя с помощью правил в файле pg\_hba.conf. При этом в ряде случаев — при выполнении редких и сложных задач, требующих суперпользовательского доступа — администратор ОС может остановить и временно безопасно запустить СУБД в однопользовательском (технологическом) режиме. REVOKE-SUPERUSER-ACCESS-1 NGLE-USER ## Page 4: Защищенная схема {#source-page-004} **Зона повышенной безопасности на основе схемы** - инициализируется суперпользователем закрыт доступ для административных ролей Администратор безопасности - выдает разрешения на доступ к объектам схемы не может работать с объектами схемы Владелец схемы - может работать с объектами - не может управлять доступом к объектам ### Комментарии преподавателя Данные хранятся в таблицах, которые находятся в схемах баз данных. По умолчанию административные роли имеют доступ к этим данным, что может быть нежелательным. Для защиты чувствительных данных Postgres Pro Enterprise позволяет поместить их в специально созданную защищенную схему и определить правила доступа к ней. У защищенной схемы есть владелец и выделенный администратор безопасности. Администратор безопасности (менеджер прав доступа) может лишь выдавать разрешения пользователям на доступ к объектам схемы, но сам работать с объектами не может. Владелец схемы, наоборот, может работать с объектами, но не может управлять правами доступа к ним. ULT Предпочтительным является сценарий, когда владельца и администратора безопасности создает суперпользователь. Для этого администратор ОС временно разрешает вход для суперпользователя. ## Page 5: Роли {#source-page-005} ![Source slide page 5: Роли](images/page-005.png){#source-figure-005 caption="Source slide page 5: Роли"} **Суперпользователь** - создает роли - Администратор ОС - запрещает доступ суперпользователя ### Комментарии преподавателя Организацию схемы для размещения защищенных данных начнем с создания ролей суперпользователем. Роль db\_admin будет служить для таких административных задач, как создание баз данных. Далее нам потребуются роли для владельца защищенной схемы (v\_owner) и для администратора безопасности этой схемы (v\_admin). CREATE-MANAGERS-OF-SECURED-SCHEMA Если эти роли были созданы некоторой административной ролью (не суперпользователем), то у них будет право ADMIN OPTION, от которого они сами отказаться не смогут. Чтобы исключить риски, связанные с доверием к указанным ролям, суперпользователю следует это право отозвать. PROTECT-SECURED-SCHEMA-USERS-FROM-ADMINS Наконец, роль v\_user предназначена для обычного пользователя, который должен обращаться к защищенным данным. После создания ролей администратор ОС может запретить суперпользовательское подключение к экземпляру СУБД. ## Page 6: Роли {#source-page-006} Создадим административную роль с правом создания баз данных. Это действие выполнит суперпользователь: ```sql => \conninfo ``` ```text You are connected to database "student" as user "student" via socket in "/tmp" at port "5432". ``` ```sql => CREATE USER db_admin WITH CREATEDB; ``` ```sql CREATE ROLE ``` Можно наделить созданную роль еще рядом полномочий, позволяющих решать нужный круг задач администрирования, но для упрощения демонстрации мы не будем этого делать. Суперпользователь также создает роли владельца защищаемых данных, будущего администратора безопасности и пользователя: ```sql => CREATE USER v_owner; ``` ```sql CREATE ROLE ``` ```sql => CREATE USER v_admin; ``` ```sql CREATE ROLE ``` ```sql => CREATE USER v_user; ``` ```sql CREATE ROLE ``` Чтобы пользователь ОС postgres, под которым работает сервер СУБД, не мог изменить конфигурацию, администратор ОС меняет владельца конфигурационных файлов. ```sh student$ sudo chown -c student: /var/lib/pgpro/ent-16/pg_hba.conf* ``` ```sql 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 все же нужен доступ на чтение и просмотр подкаталога: ```sh student$ sudo chmod -c a+rX /var/lib/pgpro/ent-16/pg_hba.conf{,.d} ``` ```sql 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) ``` Теперь можно запретить доступ суперпользователя к СУБД: ```text /var/lib/pgpro/ent-16/pg_hba.conf.d/pgpro_vault.conf ``` ```text # TYPE DATABASE USER ADDRESS METHOD local all postgres,student reject host all postgres,student all reject ``` Перечитаем конфигурацию и попробуем переподключиться: ```sh student$ sudo systemctl reload postgrespro-ent-16.service ``` ```sql => \c ``` ```sql \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-page-007} ![Source slide page 7: База данных](images/page-007.png){#source-figure-007 caption="Source slide page 7: База данных"} **Суперпользователь** - создает роли - Администратор ОС - запрещает доступ суперпользователя - Административная роль - создает базу ### Комментарии преподавателя На следующем шаге административная роль создает базу данных. ## Page 8: Схема {#source-page-008} ![Source slide page 8: Схема](images/page-008.png){#source-figure-008 caption="Source slide page 8: Схема"} **Суперпользователь** - создает роли - Администратор ОС - запрещает доступ суперпользователя - Административная роль - создает базу - Суперпользователь (временный доступ) - создает схему - назначает администратора безопасности - назначает владельца ### Комментарии преподавателя Теперь уже можно создать зону повышенной безопасности — защищенную схему. Мы будем показывать одну, но, конечно, таких схем может быть несколько, и они могут принадлежать разным владельцам. Команда ALTER SCHEMA ... SECURITY OFFICER TO ... устанавливает для схемы атрибут — роль администратора безопасности. После этого только указанная роль может управлять доступом к схеме и объектам в ней — схема становится защищенной. Атрибут можно сбросить командой ALTER SCHEMA ... RESET SECURITY OFFICER. Тогда схема снова станет незащищенной. Эти действия выполняет суперпользователь, для чего администратор ОС должен обеспечить ему временное подключение к экземпляру. После назначения администратора безопасности можно передать владение схемой выделенной роли, не являющейся суперпользователем. При организации зоны повышенной безопасности можно использовать более простой подход: схему создает ее владелец, а суперпользователь только назначает администратора безопасности. Однако при этом появляется риск передачи привилегий на схему другим ролям до того, как схема станет защищенной. ## Page 9: Защищенная схема {#source-page-009} Подключимся к серверу под административной ролью: ```sh student$ psql -U db_admin -d postgres ``` Создадим базу данных: ```sql db_admin=> CREATE DATABASE pgpro_vault; ``` ```sql CREATE DATABASE ``` Чтобы сделать схему защищенной, нужно назначить для нее администратора безопасности — это может сделать только суперпользователь, для которого доступ отключен. Чтобы он мог выполнить свою работу, можно подготовить скрипт, остановить экземпляр и выполнить команды в однопользовательском режиме: ```text /tmp/v_schema.sql ``` ```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; ``` ```sh student$ sudo systemctl stop postgrespro-ent-16.service ``` ```sh postgres$ /opt/pgpro/ent-16/bin/postgres \ --single \ -D /var/lib/pgpro/ent-16 \ pgpro_vault \ < /tmp/v_schema.sql ``` Снова запустим сервер в обычном режиме, подключимся к базе и посмотрим на схему — она защищенная (столбец Security officer): ```sh student$ sudo systemctl start postgrespro-ent-16.service ``` ```sh student$ psql -U db_admin -d pgpro_vault ``` ```text db_admin=> \dn+ v_schema ``` ```text 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-page-010} ![Source slide page 10: Защищаемые данные](images/page-010.png){#source-figure-010 caption="Source slide page 10: Защищаемые данные"} **Владелец схемы** - размещает данные в схеме ### Комментарии преподавателя Если между созданием схемы и преобразованием ее в защищенную могли быть выполнены какие-либо команды, перед размещением данных в ней нужно убедиться, что к схеме нет доступа у других ролей, кроме владельца схемы и администратора безопасности. Разместить данные в защищенной схеме можно двумя способами: 1. Владелец защищенной схемы может создать объект непосредственно в этой схеме и затем наполнить его данными. s#CREATE-TABLE-IN-SECURED-SCHEMA 2. Можно перенести в защищенную схему уже существующий объект. В этом случае нужно передать его владельцу этой схемы. Однако это возможно, только если владелец объекта входит в роль владельца схемы с параметром членства SET. Владелец же защищенной схемы является независимым — у других административных ролей нет с ним общей роли. Поэтому здесь снова потребуется действие от имени суперпользователя. s#MOVE-TABLE-TO-SECURED-SCHEMA ## Page 11: Доступ к данным {#source-page-011} ![Source slide page 11: Доступ к данным](images/page-011.png){#source-figure-011 caption="Source slide page 11: Доступ к данным"} **Владелец схемы** - размещает данные в схеме - Администратор безопасности - выдает разрешения пользователям ### Комментарии преподавателя Далее разрешения на доступ к объектам защищенной схемы пользователю выдает администратор безопасности. Пользователи с доступом к зоне повышенной безопасности должны быть защищены. У административных ролей не должно быть параметра членства ADMIN в отношении этих пользователей. Если это не так, параметр следует явно отключить. ## Page 12: Данные в защищенной схеме {#source-page-012} У ролей экземпляра не должно быть параметра членства ADMIN в отношении владельца, администратора и других пользователей, имеющих доступ к схеме, во избежание несанкционированного доступа к защищенным данным. Проверим, имеет ли кто-либо такой параметр: ```sql 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; ``` ```text role | member | grantor | admin_option ------+--------+---------+-------------- (0 rows) ``` Никто — это правильно. Теперь владелец схемы создает в защищенной схеме таблицу: ```sh student$ psql -U v_owner -d pgpro_vault ``` ```sql v_owner=> CREATE TABLE v_schema.tbl AS (SELECT 'vault data by ' || current_user AS data); ``` ```sql SELECT 1 ``` Но он не может выдавать права на доступ к данным в защищенной схеме: ```text v_owner=> GRANT ALL ON SCHEMA v_schema TO v_user; ``` ```text WARNING: no privileges were granted for "v_schema" GRANT ``` ```text v_owner=> GRANT ALL ON TABLE v_schema.tbl TO v_user; ``` ```text WARNING: no privileges were granted for "tbl" GRANT ``` Такой доступ выдает администратор безопасности. Обратите внимание, что привилегии выдаются от имени владельца таблицы: ```sh student$ psql -U v_admin -d pgpro_vault ``` ```text v_admin=> GRANT ALL ON SCHEMA v_schema TO v_user; ``` ```text GRANT ``` ```text v_admin=> \dn+ v_schema ``` ```text 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) ``` ```text v_admin=> GRANT ALL ON TABLE v_schema.tbl TO v_user; ``` ```text GRANT ``` ```text v_admin=> \dp v_schema.tbl ``` ```text 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) ``` Теперь пользователь может, например, читать строки таблицы: ```sql db_admin=> \c - v_user ``` ## Page 13: Page 13 {#source-page-013} ![Source slide page 13: Page 13](images/page-013.png){#source-figure-013 caption="Source slide page 13: Page 13"} ```text You are now connected to database "pgpro_vault" as user "v_user". ``` ```sql v_user=> SELECT * FROM v_schema.tbl; ``` ```text data ----------------------- vault data by v_owner (1 row) ``` Сам администратор безопасности не имеет доступа к данным в защищенной схеме: ```sql v_admin=> SELECT * FROM v_schema.tbl; ``` ```text ERROR: permission denied for table tbl ``` И не может выдать права ни себе, ни псевдороли public: ```text v_admin=> GRANT ALL ON TABLE v_schema.tbl TO v_admin, public; ``` ```text GRANT ``` Права остались без изменений: ```text v_admin=> \dp v_schema.tbl ``` ```text 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-page-014} ![Source slide page 14: Логическая копия](images/page-014.png){#source-figure-014 caption="Source slide page 14: Логическая копия"} **Администратор** - выгружает базу и все схемы, кроме защищенной ### Комментарии преподавателя Логическая резервная копия базы с зоной повышенной безопасности создается в несколько этапов без участия суперпользователя. Сначала с помощью pg\_dump администратор базы выгружает все данные, кроме данных защищенной схемы. Для этого используется параметр командной строки утилиты --exclude-schema. BACKUP-APPLICATION-CHANGES ## Page 15: Логическая копия {#source-page-015} ![Source slide page 15: Логическая копия](images/page-015.png){#source-figure-015 caption="Source slide page 15: Логическая копия"} **Администратор** - выгружает базу и все схемы, кроме защищенной - Владелец схемы - выгружает данные защищенной схемы ### Комментарии преподавателя Затем владелец защищенной схемы выгружает данные зоны повышенной безопасности, используя параметры --schema и --no-privileges. ## Page 16: Логическая копия {#source-page-016} ![Source slide page 16: Логическая копия](images/page-016.png){#source-figure-016 caption="Source slide page 16: Логическая копия"} **Администратор** - выгружает базу и все схемы, кроме защищенной - Владелец схемы - выгружает данные защищенной схемы - выгружает права на объекты защищенной схемы ### Комментарии преподавателя Далее владелец защищенной схемы выгружает права доступа к объектам этой схемы. Для этого используются параметры --schema и --privileges-only. ## Page 17: Логическая копия {#source-page-017} Сделаем резервную копию базы данных. Сначала администратор базы данных выгружает все данные, кроме данных защищенной схемы: ```sh student$ /opt/pgpro/ent-16/bin/pg_dump -U db_admin -d pgpro_vault \ --exclude-schema=v_schema \ > ~/tmp/data.sql ``` Затем владелец защищенной схемы выгружает данные этой схемы без прав доступа: ```sh student$ /opt/pgpro/ent-16/bin/pg_dump -U v_owner -d pgpro_vault \ --schema=v_schema \ --no-privileges \ > ~/tmp/v_data.sql ``` После этого он же выгружает права доступа защищенной схемы: ```sh 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: Восстановление {#source-page-018} **Администратор** - восстанавливает базу и схемы, кроме защищенной - v\_db - схема - схема - схема ### Комментарии преподавателя Восстановление также выполняется в несколько этапов. Сначала администратор СУБД восстанавливает базу со всеми данными, кроме данных защищенной схемы. ## Page 19: Восстановление {#source-page-019} **Администратор** - восстанавливает базу и схемы, кроме защищенной - v\_db - Суперпользователь - схема - схема - схема - создает защищенную схему - v\_schema ### Комментарии преподавателя На следующем этапе потребуется участие суперпользователя: он создает в новой базе схему и назначает для нее администратора безопасности, что делает схему защищенной. После этого суперпользователь назначает владельца защищенной схемы. ## Page 20: Восстановление {#source-page-020} ![Source slide page 20: Восстановление](images/page-020.png){#source-figure-020 caption="Source slide page 20: Восстановление"} **Администратор** - восстанавливает базу и схемы, кроме защищенной - Суперпользователь - создает защищенную схему - Владелец схемы - восстанавливает данные защищенной схемы ### Комментарии преподавателя Далее владелец защищенной схемы восстанавливает объекты и данные этой схемы. ## Page 21: Восстановление {#source-page-021} ![Source slide page 21: Восстановление](images/page-021.png){#source-figure-021 caption="Source slide page 21: Восстановление"} **Администратор** - восстанавливает базу и схемы, кроме защищенной - Суперпользователь - создает защищенную схему - Владелец схемы - восстанавливает данные защищенной схемы - Администратор безопасности - восстанавливает права на объекты - защищенной схемы ### Комментарии преподавателя На завершающем этапе администратор безопасности восстанавливает права доступа к объектам защищенной схемы. Приведенный сценарий восстановления не является единственно возможным. В зависимости от задачи можно изменить порядок действий и состав вовлеченных административных ролей. Но в любом случае необходимо участие суперпользователя, как минимум для назначения администратора безопасности схемы. Снова отметим, что назначение администратора безопасности желательно выполнять раньше, чем назначение владельца схемы. ## Page 22: Восстановление логической копии {#source-page-022} Для восстановления защищенной схемы понадобятся два разных администратора: один из них сможет восстановить объекты, а другой — права доступа. Сначала администратор базы данных восстанавливает все данные, кроме защищенной схемы. Мы будем использовать тот же экземпляр, но создадим в нем новую базу: ```sh student$ psql -U db_admin -d pgpro_vault -c 'CREATE DATABASE pgpro_vault_new' ``` ```sql CREATE DATABASE ``` ```sh student$ psql -U db_admin -d pgpro_vault_new -f ~/tmp/data.sql ``` ```text SET SET SET SET SET SET set_config ------------ (1 row) ``` ```text SET SET SET SET SET ``` Администратор ОС временно открывает вход для суперпользователя (переименуем дополнительный файл конфигурации): ```sh student$ mv -v /var/lib/pgpro/ent-16/pg_hba.conf.d/pgpro_vault.{conf,save} ``` ```text 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' ``` ```sh student$ sudo systemctl reload postgrespro-ent-16.service ``` Суперпользователь создает в новой базе схему, назначает ей администратора безопасности (схема становится защищенной) и меняет владельца на v\_owner: ```sh student$ psql -d pgpro_vault_new -c 'CREATE SCHEMA v_schema' ``` ```sql CREATE SCHEMA ``` ```sh student$ psql -d pgpro_vault_new -c 'ALTER SCHEMA v_schema SECURITY OFFICER TO v_admin' ``` ```sql ALTER SCHEMA ``` ```sh student$ psql -d pgpro_vault_new -c 'ALTER SCHEMA v_schema OWNER TO v_owner' ``` ```sql ALTER SCHEMA ``` Также суперпользователь выдает администратору безопасности право обращаться к объектам в защищенной схеме новой базы — оно понадобится при восстановлении привилегий: ```sh student$ psql -d pgpro_vault_new -c 'GRANT USAGE ON SCHEMA v_schema TO v_admin' ``` ```text GRANT ``` Теперь доступ суперпользователя можно закрыть. Снова подключаем дополнительный файл конфигурации: ```sh student$ mv -v /var/lib/pgpro/ent-16/pg_hba.conf.d/pgpro_vault.{save,conf} ``` ```text 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' ``` ```sh student$ sudo systemctl reload postgrespro-ent-16.service ``` В логической копии данных есть команда создания схемы — удалим ее, чтобы исключить ошибку при восстановлении: ```sh student$ sed -i '/CREATE SCHEMA/d' ~/tmp/v_data.sql ``` Владелец защищенной схемы восстанавливает в ней данные: ```sh student$ psql -U v_owner -d pgpro_vault_new -f ~/tmp/v_data.sql ``` ## Page 23: Page 23 {#source-page-023} ```text SET SET SET SET SET SET set_config ------------ (1 row) ``` ```sql SET SET SET SET SET ALTER SCHEMA SET SET CREATE TABLE ALTER TABLE COPY 1 ``` После этого администратор безопасности восстанавливает права доступа для защищенной схемы: ```sh student$ psql -U v_admin -d pgpro_vault_new -f ~/tmp/v_privs.sql ``` ```text SET SET SET SET SET SET set_config ------------ (1 row) ``` ```sql SET SET SET SET SET GRANT GRANT ALTER SCHEMA GRANT ``` Проверим, что получилось: ```sql v_user=> \c pgpro_vault_new ``` ```text You are now connected to database "pgpro_vault_new" as user "v_user". ``` ```text v_user=> \dn+ v_schema ``` ```text 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) ``` ```text v_user=> \dp v_schema.tbl ``` ```text 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 успешно читает данные из таблицы в новой базе: ```sh student$ psql -U v_user -d pgpro_vault_new -c 'SELECT * FROM v_schema.tbl' ``` ```text data ----------------------- vault data by v_owner (1 row) ``` ## Page 24: Page 24 {#source-page-024} ## Page 25: Итоги {#source-page-025} **Безопасная работа с данными подразумевает разделение полномочий, в том числе административных** - Защищенная схема снижает риски, которые не устраняются стандартными возможностями PostgreSQL - При настройке защищенной схемы нужно строго соблюдать порядок действий, чтобы исключить несанкционированный доступ к данным ## Page 26: Практика {#source-page-026} 1. Исследуйте ситуацию, когда административная роль, создавшая пользователя защищенных данных, сохраняет членство в ней с параметром ADMIN. Устраните найденную уязвимость. - 2. Реализуйте сценарий переноса данных, созданных пользователем, не имеющим доступа к защищенной схеме, в уже существующую защищенную схему. Проверьте, что после перемещения данных бывший владелец не будет иметь к ним доступа. - 3. Выключите защиту схемы. Убедитесь, что разделение ролей для управления данными и доступа к ним у незащищенных схем отсутствует. ### Комментарии преподавателя 1. Параметр членства ADMIN дает возможность включения в роль других ролей с правом наследования доступа к объектам. 2. Перенесите таблицу, созданную в схеме public административной ролью, не имеющей доступа к защищенной схеме. ## Page 27: 1. Уязвимость ADMIN OPTION {#source-page-027} Создадим административную роль (администратора СУБД) с правом создания ролей, а также роли владельца защищаемых данных и будущего администратора безопасности. Это делает суперпользователь: ```sql => \conninfo ``` ```text You are connected to database "student" as user "student" via socket in "/tmp" at port "5432". ``` ```sql => CREATE USER dbms_admin WITH CREATEROLE; ``` ```sql CREATE ROLE ``` ```sql => CREATE USER v_owner; ``` ```sql CREATE ROLE ``` ```sql => CREATE USER v_admin; ``` ```sql CREATE ROLE ``` Также он создает базу данных и схему в ней: ```sql => CREATE DATABASE pgpro_vault OWNER dbms_admin; ``` ```sql CREATE DATABASE ``` ```sql => \c pgpro_vault ``` ```text You are now connected to database "pgpro_vault" as user "student". ``` ```sql => CREATE SCHEMA v_schema; ``` ```sql CREATE SCHEMA ``` Назначает администратора безопасности схемы: ```sql => ALTER SCHEMA v_schema SECURITY OFFICER TO v_admin; ``` ```sql ALTER SCHEMA ``` И предоставляет ему право обращаться к объектам в этой схеме: ```text => GRANT USAGE ON SCHEMA v_schema TO v_admin; ``` ```text GRANT ``` А также делает владельцем схемы v\_schema пользователя v\_owner: ```sql => ALTER SCHEMA v_schema OWNER TO v_owner; ``` ```sql ALTER SCHEMA ``` Администратор СУБД создает пользователя, который будет работать с данными в защищенной схеме: ```sql => \c - dbms_admin ``` ```text You are now connected to database "pgpro_vault" as user "dbms_admin". ``` ```sql dbms_admin=> CREATE USER v_user; ``` ```sql CREATE ROLE ``` Владелец защищенной схемы создает в ней таблицу и помещает туда данные: ```sh student$ psql -U v_owner -d pgpro_vault ``` ```sql v_owner=> CREATE TABLE v_schema.tbl ( id int GENERATED ALWAYS AS IDENTITY, note text, who name DEFAULT current_user ); ``` ```sql CREATE TABLE ``` ```sql v_owner=> INSERT INTO v_schema.tbl (note) VALUES ('Line 1'); ``` ```sql INSERT 0 1 ``` Администратор безопасности предоставляет пользователю v\_user доступ к схеме и таблице: ```sh student$ psql -U v_admin -d pgpro_vault ``` ## Page 28: Page 28 {#source-page-028} ![Source slide page 28: Page 28](images/page-028.png){#source-figure-028 caption="Source slide page 28: Page 28"} ```text v_admin=> GRANT ALL ON SCHEMA v_schema TO v_user; ``` ```text GRANT ``` ```text v_admin=> GRANT ALL ON TABLE v_schema.tbl TO v_user; ``` ```text GRANT ``` Получается так: ```text v_admin=> \dn+ v_schema ``` ```text 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) ``` ```text v_admin=> \dp v_schema.tbl ``` ```text 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) ``` Теперь пользователь может работать с данными в защищенной схеме: ```sh student$ psql -U v_user -d pgpro_vault ``` ```sql v_user=> INSERT INTO v_schema.tbl (note) VALUES ('Line 2'); ``` ```sql INSERT 0 1 ``` Администратор dbms\_admin замечает, что может воспользоваться ADMIN OPTION в отношении созданной им роли v\_user: ```text dbms_admin=> \drg ``` ```text List of role grants Role name | Member of | Options | Grantor ------------+-----------+---------+---------- dbms_admin | v_user | ADMIN | postgres (1 row) ``` Обладая параметром членства ADMIN в роли v\_user, администратор dbms\_admin может включить себя в эту роль с параметром INHERIT: ```text dbms_admin=> GRANT v_user TO dbms_admin WITH INHERIT OPTION; ``` ```text GRANT ROLE ``` ```text dbms_admin=> \drg ``` ```text 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: ```sql dbms_admin=> SELECT * FROM v_schema.tbl; ``` ```text id | note | who ----+--------+--------- 1 | Line 1 | v_owner 2 | Line 2 | v_user (2 rows) ``` Чтобы исключить эту уязвимость, суперпользователь отзывает ADMIN OPTION у роли dbms\_admin: ```sql dbms_admin=> \c - postgres ``` ```text You are now connected to database "pgpro_vault" as user "postgres". ``` ## Page 29: Page 29 {#source-page-029} ```sql => REVOKE ADMIN OPTION FOR v_user FROM dbms_admin CASCADE; ``` ```text REVOKE ROLE ``` ```text => \drg v_user ``` ```text List of role grants Role name | Member of | Options | Grantor -----------+-----------+---------+--------- (0 rows) ``` Теперь доступ к данным закрыт: ```sql => \c - dbms_admin ``` ```text You are now connected to database "pgpro_vault" as user "dbms_admin". ``` ```sql dbms_admin=> SELECT * FROM v_schema.tbl; ``` ```sql ERROR: permission denied for schema v_schema LINE 1: SELECT * FROM v_schema.tbl; ^ ``` И исключена возможность включения в роль: ```text dbms_admin=> GRANT v_user TO dbms_admin; ``` ```text 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 и наполняет ее данными: ```sql dbms_admin=> CREATE TABLE tbl_bis ( id int GENERATED ALWAYS AS IDENTITY, note text, who name DEFAULT current_user ); ``` ```sql CREATE TABLE ``` ```sql dbms_admin=> INSERT INTO tbl_bis (note) VALUES ('Line 1'); ``` ```sql INSERT 0 1 ``` В дальнейшем в таблице будут храниться конфиденциальные данные, поэтому стоит задача поместить ее в зону повышенной безопасности. Для этого роль dbms\_admin передаст таблицу во владение пользователю v\_owner, который перенесет ее в свою защищенную схему: ```sql dbms_admin=> ALTER TABLE tbl_bis OWNER TO v_owner; ``` ```text ERROR: must be able to SET ROLE "v_owner" ``` Не получилось, так как для передачи владения нужно, чтобы у обоих пользователей была общая роль. Поэтому владельца меняет суперпользователь: ```sql dbms_admin=> \c - postgres ``` ```text You are now connected to database "pgpro_vault" as user "postgres". ``` ```sql => ALTER TABLE tbl_bis OWNER TO v_owner; ``` ```sql ALTER TABLE ``` Новый владелец перемещает таблицу в защищенную схему: ```sql v_owner=> ALTER TABLE tbl_bis SET SCHEMA v_schema; ``` ```sql ALTER TABLE ``` Администратор безопасности предоставляет доступ к таблице пользователю v\_user: ```sql v_admin=> GRANT SELECT ON TABLE v_schema.tbl_bis TO v_user; ``` ```text GRANT ``` Теперь пользователь работает с данными: ```sql v_user=> SELECT * FROM v_schema.tbl_bis; ``` ## Page 30: Page 30 {#source-page-030} ```text id | note | who ----+--------+------------ 1 | Line 1 | dbms_admin (1 row) ``` А у роли dbms\_admin доступа к таблице уже нет: ```sql => \c - dbms_admin ``` ```text You are now connected to database "pgpro_vault" as user "dbms_admin". ``` ```sql dbms_admin=> SELECT * FROM v_schema.tbl_bis; ``` ```sql ERROR: permission denied for schema v_schema LINE 1: SELECT * FROM v_schema.tbl_bis; ^ ``` ### 3. Схема без защиты Суперпользователь делает схему v\_schema обычной, убрав атрибут security officer: ```sql dbms_admin=> \c - postgres ``` ```text You are now connected to database "pgpro_vault" as user "postgres". ``` ```sql => ALTER SCHEMA v_schema RESET SECURITY OFFICER; ``` ```sql ALTER SCHEMA ``` ```text => \dn+ v_schema ``` ```text 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) ``` Теперь владельцу данных ничего не мешает распоряжаться доступом к ним. Разделения ответственностей ролей больше нет! ```text v_owner=> GRANT USAGE ON SCHEMA v_schema TO dbms_admin; ``` ```text GRANT ``` ```sql v_owner=> GRANT SELECT ON TABLE v_schema.tbl, v_schema.tbl_bis TO dbms_admin; ``` ```text GRANT ``` Администратор СУБД может видеть данные в таблицах схемы v\_schema: ```sql => \c - dbms_admin ``` ```text You are now connected to database "pgpro_vault" as user "dbms_admin". ``` ```sql dbms_admin=> SELECT * FROM v_schema.tbl; ``` ```text id | note | who ----+--------+--------- 1 | Line 1 | v_owner 2 | Line 2 | v_user (2 rows) ``` ```sql dbms_admin=> SELECT * FROM v_schema.tbl_bis; ``` ```text id | note | who ----+--------+------------ 1 | Line 1 | dbms_admin (1 row) ``` ### Уборка Удалим созданную базу данных и роли. ```sql dbms_admin=> \c student student ``` ```text You are now connected to database "student" as user "student". ``` ```sql student=# DROP DATABASE pgpro_vault WITH (FORCE); ``` ## Page 31: Page 31 {#source-page-031} ```sql DROP DATABASE ``` ```sql student=# DROP USER dbms_admin, v_owner, v_admin, v_user; ``` ```sql DROP ROLE ```