SQL-Ex blog

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

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

Использование привилегий по умолчанию для управления миграцией

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



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

Во-первых, это означает, что много ролей имеют привилегию CREATE в каждой схеме. Как обсуждалось в предыдущих статьях, создатели объектов являются также владельцами по умолчанию и имеют на объект привилегию, подобную суперпользователю. Более того, наличие привилегии на создание объектов в схеме создает потенциальную проблему безопасности, причина, по которой привилегия CREATE была удалена из роли public в PostgreSQL 15.

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

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



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

Создание высокоуровневых групповых ролей

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

Помните, что роли создаются на уровне экземпляра. Следовательно, если вы создаете роль "только_на_чтение", она может быть использована для управления доступом только на чтение (SELECT) в каждой базе данных. То же самое может быть сделано с различными видами привилегий от DML-доступа (‘INSERT', ‘DELETE', ‘UPDATE') вплоть до операций DDL (CREATE).

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

• Следующие скрипты представляют собой модифицированную версию этого ответа на Stack Overflow для использования в качестве шаблона. Я дал его в прошлом году и думаю, что он дает хорошее направление для простого процесса установки привилегий в базе данных PostgreSQL.

Групповые роли экземпляра PostgreSQL

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

/*

* Этот скрипт следует выполнить один раз для каждого экземпляра PostgreSQL

* для создания групповых ролей, которые будут/могут использоваться во всех базах данных

* для доступа ddl/dml/read_only 

* 

* В вашей ситуации вам может потребоваться создать больше групповых ролей,

* чтобы обеспечить лучшую детализацию привилегий.

*/

CREATE ROLE ddl_grp WITH NOLOGIN;

CREATE ROLE dml_grp WITH NOLOGIN;

CREATE ROLE read_only_grp WITH NOLOGIN;

Создание этих ролей не делает еще ничего существенного. Напротив, эти роли становятся механизмом для управления другими привилегиями по мере создания базы данных и объектов схемы. Несмотря на то, что вы не можете подключиться к базе данных непосредственно с помощью этих ролей ('WITH NOLOGIN'), они все же могут владеть объектами и создавать привилегии по умолчанию.

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

• Админы объектов: этим ролям позволено создавать объекты в базе данных. Создание объектов - это действие DDL, поэтому мы назовем эту роль 'ddl_grp'.


• Админы данных: этим ролям позволено модифицировать данные и связанные с ними объекты с помощью операторов DML, поэтому мы называем эту роль ‘dml_grp'.


• Только чтение: этим ролям позволено выбирать данные из указанных схем или таблиц. Мы назвали эту роль ‘read_only_grp'.

Примеры пользовательских ролей (например, 'dev_admin1', ‘flyway', ‘dev1', ‘report_usr' и т.д.) должны существовать к моменту предоставления членства в групповой роли. Важно, что любая роль, которой предоставлено членство в роли ‘ddl_grp', будет иметь возможность выполнять скрипты, которые создают или изменяют объекты в базе данных, как ‘ddl_grp'.

/*

 *  Теперь предоставим доступ каждой роли в соответствии с производственными

 * функциям. Опять таки, это лишь идеи, чтобы начать.

 * Может потребоваться больше ролей "групп".

 * 

 * Это примеры ролей, которые могут получить доступ. Хорошим примером роли, 

 * которая должна быть "ddl_grp", является роль, которая используется

 * инструментами DevOps типа Flyway, чтобы выполнять

 * скрипты переноса. Это гарантирует, что владельцем всех объектов

 * является эта группа, и все пользователи получают доступ.

 */

GRANT ddl_grp TO dev_admin1, flyway;

GRANT dml_grp TO dev1, dev2;

GRANT read_only_grp TO report_usr1, report_usr2;

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

Привилегии в отдельной базе данных

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

Мы можем выполнять этот скрипт всякий раз, когда на кластере создается новая база данных. Или, используя роль суперпользователя (superuser), вы можете модифицировать шаблонную базу данных (обычно называемую ‘template1') на вашем экземпляре PostgreSQL. Любые настройки, которые применимы к шаблонной базе данных, будут автоматически устанавливать эти привилегии в каждой базе данных, которая создается на базе этого шаблона.

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

Отмена всех привилегий у PUBLIC

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

/*

 * Выполните этот скрипт на КАЖДОЙ базе данных под суперпользователем

 * или пользователем, который имеет привилегии CREATEROLE и другой GRANT.

 * В службе DBaaS типа Amazon RDS это может быть пользователь

 * который предоставлен вам для администрирования

 */

-- Для всех современных версий PostgreSQL. Это

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

-- специальная привилегия CONNECT

REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

 

-- Запретит создавать объекты в схеме public

-- без предоставленной привилегии. По умолчанию в PG15+

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Обратите внимание на один нюанс. Мы удаляем ‘ALL' (все) привилегии из 'PUBLIC' на уровне базы данных, а не на уровне схемы базы данных. Если мы также удалим все привилегии у схемы, то обычные команды PostgreSQL не будут работать для многих пользователей без повторной установки дополнительных привилегий.

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

Предоставление базовых привилегий

Всем ролям необходима возможность подключаться к базе данных и (обычно) иметь доступ к временным таблицам. Однако без дополнительных привилегий типа USAGE и SELECT эти роли фактически ничего не могут пока делать в базе данных.

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

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

GRANT CONNECT, TEMPORARY 

	ON DATABASE mysuperdb TO ddl_grp, dml_grp, read_only_grp;

Права на использование и другие привилегии для каждой групповой роли

Теперь у нас все хорошо. Для каждой базы данных мы можем решить, какие роли могут использовать объекты базы данных ('USAGE'), и что они могут делать в схеме. Как отмечалось выше, этот пример лишь показывает, как предоставить привилегии на схему public. Если ваше приложение имеет другие схемы (которые, вероятно, есть!), вам нужно предоставить соответствующие разрешения на каждую схему.

Во-первых, нашей роли 'ddl_grp' (и всем членам) разрешено использовать и создавать объекты в схеме и любые последовательности. Если имеются такие объекты, как TABLE, SEQUENCE, TYPE, DOMAIN и т.д., вам потребуется выполнять оператор GRANT всякий раз для установки на них существующих привилегий, поскольку привилегии по умолчанию действуют только на вновь создаваемые объекты. В примере ниже я показываю предоставление привилегий на существующие последовательности и таблицы.

/*

 * Ниже мы представляем привилегию usage на схему приложения с именем 'myapp'.

 * Если ваше приложение имеет другую схему, вам нужно будет

 * внести соответствующие изменения. 

 * 

 * Имена нескольких схем можно разделять запятой

 */

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

-- новые объекты в схеме. Поскольку эта группа будет владельцем объектов,

-- они смогут потом их модифицировать.

GRANT USAGE, CREATE

    ON SCHEMA myapp TO ddl_grp;

 -- изменение привилегий для любой существующей таблицы и последовательностей

GRANT ALL

    ON ALL TABLES IN SCHEMA myapp TO ddl_grp;

 GRANT ALL

    ON ALL SEQUENCES IN SCHEMA myapp TO ddl_grp;

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

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


• Ни роль 'ddl_grp' не имеет каких-либо присвоенных привилегий по умолчанию, ни другие роли, которые являются ее членами. Это обусловлено том, что эта группа будет владельцем объекта, любая другая роль-член будет автоматически наследовать привилегии владения. Больше ничего не требуется.

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

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

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

Вариант 1: SET ROLE перед присвоением привилегий

/*

 * Наконец, под пользователем, который будет выполнять скрипты миграции (и

 * является членом группы DDL), установите привилегии доступа по умолчанию.

 */

SET ROLE ddl_grp;

ALTER DEFAULT PRIVILEGES IN SCHEMA myapp

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dml_grp;

ALTER DEFAULT PRIVILEGES IN SCHEMA myapp

GRANT SELECT ON TABLES TO read_only_grp;

ALTER DEFAULT PRIVILEGES IN SCHEMA myapp

GRANT USAGE, SELECT ON SEQUENCES TO dml_grp, read_only_grp;

ALTER DEFAULT PRIVILEGES IN SCHEMA myapp

GRANT UPDATE ON SEQUENCES TO dml_grp;

Вариант 2: присвоение привилегий другой роли

/*

* Наконец, под пользователем, который имеет членство в групповой роли, 

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

 */

ALTER DEFAULT PRIVILEGES FOR ROLE ddl_grp IN SCHEMA myapp

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dml_grp;

ALTER DEFAULT PRIVILEGES FOR ROLE ddl_grp IN SCHEMA myapp

GRANT SELECT ON TABLES TO read_only_grp;

ALTER DEFAULT PRIVILEGES FOR ROLE ddl_grp IN SCHEMA myapp

GRANT USAGE, SELECT ON SEQUENCES TO dml_grp, read_only_grp;

ALTER DEFAULT PRIVILEGES FOR ROLE ddl_grp IN SCHEMA myapp

GRANT UPDATE ON SEQUENCES TO dml_grp;

Проверьте, что привилегии установлены корректно

Последний шаг - это проверка, что привилегии по умолчанию установлены и готовы выполнять свою работу. Самый простой способ увидеть результат вашей работы - воспользоваться командой ‘\ddp’ в ‘psql’.



Это помогает нам проверить, что роль 'ddl_grp' установила привилегии по умолчанию для всех таблиц и последовательностей в схеме public.

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

Объединяем во время миграции

Теперь давайте посмотрим, как вы могли бы это использовать на практике в производственной базе данных (например).

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

Наверху любого миграционного скрипта мы сначала устанавливаем роль на 'ddl_grp', чтобы любое создание DDL приводило к тому, чтобы эта группа была владельцем объектов, и привилегии по умолчанию вступят в силу и будут применяться правильно! Простой пример может выглядеть подобным образом:

/*

 * Сначала устанавливаем сессионную роль на 

 * роль группы владения более высокого уровня

 */

SET ROLE ddl_grp;

-- Создаем объект

CREATE TABLE test (

    col1 text null,

    col2 int null

);

Как только миграция была выполнена, вы можете быстро проверить, что привилегии были применены корректно, с помощью команды '\dp' в '\psql'.



Успешно! В последующем, поскольку 'ddl_grp' используется для создания таблиц и последовательностей (или любых других объектов, для которых вы установили привилегии по умолчанию), другие роли будут иметь определенный вами доступ, и в целом бремя управления спадает!

Заключение

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

Ссылки по теме
1. Основы PostgreSQL: начала работы с psql
2. Привилегии и роли в SQL Server, Oracle и PostgreSQL. Часть 1
3. Основы PostgreSQL: роли и привилегии