SQL-Ex blog

Кто владеет объектами базы данных?

В PostgreSQL роль, которая создает объект (таблицу, представление, функцию и т.д.), становится его владельцем. Это может измениться впоследствии, но изначально владельцем является создатель. Мы можем увидеть владельца объектов в базе данных, используя интерактивный терминал psql или выполнив запрос к таблицам pg_catalog, которые соответствуют типу объектов.

SET ROLE user1; --выдаем себя за пользователя user1

CREATE TABLE public.example_tbl (

  id INT NOT NULL,

  notes TEXT NULL

);

SET ROLE none;

В psql используйте мета-команду "describe":

\d

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



Как видно владелец таблицы установлен в user1, поскольку эта роль создала ее.

Мета-команда \d в psql выполняет под капотом следующий запрос, чтобы показать нам список отношений (включая созданную нами таблицу) и владельца каждого отношения.

SELECT n.nspname as "Schema",

  c.relname as "Name",

  CASE c.relkind 

  	WHEN 'r' THEN 'table' 

  	WHEN 'v' THEN 'view' 

  	WHEN 'm' THEN 'materialized view' 

  	WHEN 'i' THEN 'index' 

  	WHEN 'S' THEN 'sequence' 

  	WHEN 't' THEN 'TOAST table' 

  	WHEN 'f' THEN 'foreign table' 

  	WHEN 'p' THEN 'partitioned table' 

  	WHEN 'I' THEN 'partitioned index' END as "Type",

  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"

FROM pg_catalog.pg_class c

     LEFT JOIN pg_catalog.pg_namespace n 

         ON n.oid = c.relnamespace

     LEFT JOIN pg_catalog.pg_am am 

         ON am.oid = c.relam

WHERE c.relkind IN ('r','p','v','m','S','f','')

      AND n.nspname <> 'pg_catalog'

      AND n.nspname !~ '^pg_toast'

      AND n.nspname <> 'information_schema'

  AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;

Как вы можете увидеть, каталоги в PostgreSQL содержат массу полезной информации. Чтобы научиться запрашивать ее эффективно, потребуется время и практика. Вот почему мета-команды psql особенно полезны и популярны среди разработчиков PostgreSQL. Мы работаем над статьей "что вам нужно знать о psql", но пока проверьте psql-tips.org на предмет полезных советов.

Я уже слышу, что вы думаете.

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

Это зависит...

Есть три главных момента, которые нужно понимать о владении объектами:

1. Только superuser или владелец объекта (таблицы, функции, процедуры, последовательности и т.д.) может изменять/удалять (ALTER/DROP) объект.


2. Только superuser или владелец объекта может изменить (ALTER) владение объектом.


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

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

Установка пользователей и групп для демонстрации

Для следующих примеров мы будем предполагать, что ваш экземпляр PostgreSQL имеет обычный принципал суперпользователя с именем postgres. Теперь позаимствуем из первой статьи установку двух пользователей-разработчиков и группу разработки для более легкого управления привилегиями.

--ЗАМЕЧАНИМЕ: не выполняйте подобный тестовый код на кластере, который

--содержит персональную информацию, особенно если кластер

--может быть доступен в интернет

CREATE ROLE devgrp WITH NOLOGIN;

CREATE ROLE dev1 WITH LOGIN PASSWORD 'secretpw' IN ROLE devgrp;

CREATE ROLE dev2 WITH LOGIN PASSWORD 'secretpw' IN ROLE devgrp;

-- Это позволит нашим разработчикам создавать объекты

-- в схеме public

GRANT CREATE ON SCHEMA public TO devgrp;

-- Только для примеров. Вам следует выборочно предоставлять

-- привилегии в соответствии с потребностями

GRANT ALL ON SCHEMA public TO devgrp;

GRANT ALL ON ALL TABLES IN SCHEMA public TO devgrp;

Теперь проверим с помощью psql, что пользователи являются частью группы:

\du

Получим следующее:



После выполнения этого кода SQL база данных содержит двух разработчиков, которые могут авторизоваться в базе данных, и каждый является членом групповой роли devgrp. Мы предоставили привилегии группе, что позволяет ее членам создавать объекты в схеме public, и они имеют все основные привилегии DML на все таблицы.

Теперь давайте рассмотрим, как преодолеть пару распространенных проблем безопасности в PostgreSQL, наблюдая за тем, как эта команда разработчиков начинает реализовывать новую функцию.

Проблема #1: изменение объекта

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

-- как суперпользователь 'postgres' мы можем установить

-- сессию под любой ролью

SET ROLE dev1;

CREATE TABLE user_social (

   user_id INT NOT NULL,

   twitter_handle TEXT NULL,

   facebook_handle TEXT NULL );

В psql используйте мета-команду “describe”:

\d

Результат:



Как ожидалось, таблица была создана, и ей владеет dev1. Когда разработчики приступят к работе над этой функцией, они быстро поймут, что популярность новой социальной сети растет, и им необходимо отслеживать дескрипторы ее пользователей. dev2 предлагает добавить новый столбец, чтобы дело двигалось вперед.

SET ROLE dev2;

ALTER TABLE user_social ADD COLUMN mastodon_handle TEXT NULL;

Это приведет к появлению следующей ошибки:

ERROR: must be owner of table user_social
(должен быть владельцем таблицы user_social)

Уже этот простой пример помогает понять, почему владение объектом является таким важным в вашей схеме PostgreSQL. Нет привилегии, которая могла быть предоставлена второму разработчику, чтобы позволить ему выполнять модификацию таблицы. Изменение объекта является неотъемлемой привилегией, принадлежащей владельцу (или суперпользователю).

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

Наиболее общее решение - установить владение всеми объектам согласованной роли, а затем предоставлять членство в этой роли пользователям, которым необходимо модифицировать объекты. В нашем примере подходящим выбором является роль devgrp, поскольку все разработчики есть члены этой роли. В более сложных средах и структурах команд разработки вам, вероятно, придется создать больше групп для надлежащего управления владением и доступом. Я представлю начальный шаблон для управления группами в конце статьи.

Для демонстрации примера, использующего нашу маленькую команду разработки, мы можем поменять владельца этой таблицы на группу, членами которой являются все разработчики, в нашем случае это роль devgrp. Как только владелец поменяется, dev2 должен быть способен изменять таблицу, поскольку он член группы.

-- как суперпользователь 'postgres' ИЛИ владелец объекта

ALTER TABLE user_social OWNER TO devgrp;

SET ROLE dev2;

ALTER TABLE user_social ADD COLUMN mastodon_handle TEXT NULL;

SELECT * FROM user_social;

Вывод запроса:



Как альтернатива, вы можете временно установить роль вашей сессии на общую роль владельца до создания объекта (в предположении, что вы член этой роли). Любые объекты, которые создаются, будут принадлежать роли, действующей на момент создания. Для демонстрации я удалю таблицу и снова повторю этот процесс, но теперь с установкой роли до создания таблицы.

-- как dev1 ли суперпользователь 'postgres'

DROP TABLE user_social;

-- как dev1 ли суперпользователь 'postgres' мы можем установить

-- сессию, выдав себя за роль devgrp

SET ROLE devgrp;

CREATE TABLE user_social (

   user_id INT NOT NULL,

   twitter_handle TEXT NULL,

   facebook_handle TEXT NULL );

В psql используйте мета-команду “describe”:

\d

Теперь увидим:



И теперь под вторым разработчиком

SET ROLE dev2;

ALTER TABLE user_social ADD COLUMN mastodon_handle TEXT NULL;

SELECT * FROM user_social;

SET ROLE none; --в противном случае очень легко забыть контекст вашей роли

На выходе получим:



Ключевой момент состоит в том, что владение является существенным элементом в управлении и изменении объектов в базе данных PostgreSQL. Устанавливаете ли вы роли до создания объектов, или меняете владение постфактум, только члены роли, которая владеет объектом, могут менять объект.

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

Проблема #2: привилегии на объект по умолчанию

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

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

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

-- как суперпользователь или роль, которая имеет атрибут CREATEROLE

CREATE ROLE rptusr WITH LOGIN PASSWORD 'secretpw';

-- Установить сессию на новую роль

SET ROLE rptusr;

-- Подсчитать число пользователей, которые имеют дескрипторы для Mastodon

SELECT count(*) FROM user_social 

WHERE mastodon_handle IS NOT NULL;

Это вызывает ошибку:

ERROR: permission denied for table user_social
(отсутствует разрешение на таблицу user_social)

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

-- Как суперпользователь или владелец требуемого объекта

GRANT SELECT ON TABLE user_social TO rptusr;

-- Установить сессию для роли rptusr

SET ROLE rptusr;

-- Считаем число пользователей, которые имеют дескрипторы для Mastodon

SELECT count(*) FROM user_social 

WHERE mastodon_handle IS NOT NULL;

SET ROLE none;

Получаем:



В первой статье этой серии мы обращались к процессу, известному как Principle of Least Privilege (принцип минимальных привилегий), гарантирующего пользователям только минимально необходимые права. Установка разрешений, объект за объектом, быстро станет утомительной задачей.

Добавление групповой роли никак тут не помогает, поскольку остаются те же самые проблемы. Привилегии предоставляются только для объектов, существующих на момент выполнения оператора GRANT. Иначе говоря, GRANT не является действием на будущее. Напротив, нам требуется способ, заставляющий PostgreSQL применять привилегии всякий раз, когда создается объект.

Введите привилегии по умолчанию.

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

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

Сначала с помощью psql проверим, что пока нет привилегий доступа по умолчанию:

\ddp

На моем демо-сервере показано отсутствие привилегий доступа по умолчанию.



Затем мы установим контекст безопасности на группу, для которой мы хотим установить привилегии по умолчанию. Эти привилегии будут применяться, когда они создают новые объекты в определенных схемах.

-- Как роль, которая будет создавать объекты, создаем 

-- привилегии по умолчанию

SET ROLE devgrp;

ALTER DEFAULT PRIVILEGES IN SCHEMA public

	GRANT SELECT ON TABLES TO rptusr;

SET ROLE none;

Снова проверим, были ли созданы привилегии по умолчанию:

\ddp

Получаем:



Видим, что привилегии доступа по умолчанию были созданы с предоставлением привилегии SELECT (чтение) роли rptusr для любых таблиц, которые создаются в схеме public. Для проверки работы мы можем теперь создать новую таблицу и попытаться выбрать из нее данные под rptusr без дополнительного использования операторов GRANT.

-- Как роль devgrp, которая будет владельцем таблицы

SET ROLE devgrp;

CREATE TABLE rpt_log (

id int NOT NULL,

rpt_date timestamptz NOT NULL,

notes TEXT null

);

SET ROLE rptusr;

-- выборка из таблицы для проверки, что эти привилегии

-- применяются корректно

SELECT * FROM rpt_log;

Получаем:



Успешно! devgrp смог создать таблицу, а новый rptusr смог сделать выборку из нее без ошибок. Поскольку devgrp является тем, кто создает владеет таблицами (объект нашего примера), rptusr сможет делать выборку из них.

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

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

Сначала проверим текущие привилегии доступа по умолчанию, используя psql:

\ddp

Получим:



Теперь мы сбросим текущую конфигурацию, прежде чем создавать новую.

-- REVOKE - отменим текущие привилегии доступа по умолчанию для отдельного пользователя

ALTER DEFAULT PRIVILEGES IN SCHEMA public

	REVOKE SELECT ON TABLES FROM rptusr;

Проверим, что привилегии доступа удалены:

\ddp

Получаем:



Теперь создадим новую группу и настроим безопасность:

-- Создаем новую групповую роль только на чтение

CREATE ROLE read_only WITH NOLOGIN;

-- Grant на select всех текущих таблиц в схеме public

-- Помните: это только для имеющихся таблиц, а не тех, которые появятся в будущем

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- Grant на членство в роли read_only

GRANT read_only TO rptusr;

-- Теперь создаем те же самые привилегии доступа по умолчанию так,

-- чтобы роль, которая будет создавать объекты, создавала

-- привилегии по умолчанию

SET ROLE devgrp;

ALTER DEFAULT PRIVILEGES IN SCHEMA public

	GRANT SELECT ON TABLES TO read_only;

И снова проверим, были ли созданы привилегии по умолчанию:

\ddp

Это вернет следующее:



Теперь любые таблицы, которые создает пользователь devgrp будут иметь эту привилегию доступа по умолчанию, и все члены роли read_only смогут делать выборку данных.

Что касается пользователей только-на-чтение, PostgreSQL 14+ предоставляет новые роли по умолчанию для более легкого управления доступа к табличным данным. Имеется достаточно много нюансов в их корректной установке в многопользовательской среде, которые потребуют отдельной статьи в ближайшем будущем.

Управление владением и привилегиями в масштабе

На протяжении этих двух статей мы рассмотрели много вопросов, связанных с ролями и безопасностью в PostgreSQL.

• PostgreSQL имеет роли. Пользователи и роли являются синонимами.


• По соглашению пользовательские роли могут авторизоваться, а групповые роли - нет.


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


• Доступ к данным и объектам должен предоставляться (или отменяться) для каждой роли, за исключением владельца объектов или суперпользователя.


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


• Все роли имеют автоматическое неотменяемое членство в роли PUBLIC.


• Владельцы объекта (или суперпользователь) - это единственные роли, кто может менять (ALTER) или удалять (DROP) объект.


• Каждая роль может создать привилегии доступа по умолчанию для любых созданных ей объектов (например, таблиц) так, чтобы другие роли автоматически имели доступ, а не применяли привилегии всякий раз, когда объект создается.

Это много информации для размышления и применения, если безопасность PostgreSQL для вас в новинку, что затрудняет управление привилегиями PostgreSQL в большой команде. Полезно на первом шаге иметь план или начальный шаблон для наиболее простого управления привилегиями и владения объектами в PostgreSQL. Мне часто встречались команды, которые начинали проект без понимания внедрения в долгосрочной перспективе, не имея плана безопасности, засастую потому, что они не вполне понимали обсуждаемые здесь вещи.

Итак, вот пример роли и настройки безопасности, которые с которых следует начинать работу. Используйте те знания, которые мы тут обсудили (и официальную документацию), в сочетании с требованиями вашей организации, чтобы изменить и создать схему, которая подходит именно вам.

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

Заключение

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

Ссылки по теме

1. Безопасность SQL Server - модель безопасности с использованием определяемых пользователем ролей


2. Привилегии и роли в SQL Server, Oracle и PostgreSQL. Часть 1