Роли и привилегии

Андрей Аксенов

Contents

Предварительные требования
Обзор
Управление пользователями
Выдача привилегий с помощью групп
- Группа как контейнер для пользовательских привилегий
- Иерархии групп
Получение информации о ролях
Изменение владельцев объектов
Изменение роли в текущей сессии
Справочник
- Атрибуты ролей
- Привилегии на объекты

Greengage DB предоставляет гибкое управление доступом к различным ресурсам базы данных с помощью SQL-запросов и утилит командной строки. Основные концепции системы управления доступом в Greengage DB следующие:

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

Чтобы выполнить примеры из этой статьи, создайте базу данных, затем создайте две таблицы и заполните их данными, как показано ниже:

script.sql

CREATE DATABASE crm;
\c crm

CREATE SCHEMA sales;

CREATE TABLE sales.orders
(
    order_id     SERIAL,
    customer_id  INTEGER,
    order_date   DATE,
    total_amount DECIMAL(6, 2),
    status       VARCHAR(15)
)
    WITH (appendoptimized = true, orientation = row)
    DISTRIBUTED BY (order_id);

CREATE TABLE sales.customers
(
    customer_id   SERIAL PRIMARY KEY,
    first_name    VARCHAR(25),
    last_name     VARCHAR(25),
    email         VARCHAR(25),
    customer_type VARCHAR(15)
)
    DISTRIBUTED REPLICATED;

INSERT INTO sales.customers (first_name, last_name, email, customer_type)
VALUES ('Andrew', 'Fuller', 'andrew@example.com', 'Regular'),
       ('Michael', 'Suyama', 'michael@testmail.com', 'VIP'),
       ('Robert', 'King', 'robert@demo.org', 'Business');

INSERT INTO sales.orders (customer_id, order_date, total_amount, status)
VALUES (1, '2025-02-01', 120.50, 'Pending'),
       (2, '2025-02-02', 89.99, 'Completed'),
       (3, '2025-02-03', 45.00, 'Shipped'),
       (1, '2025-02-04', 150.75, 'Pending'),
       (2, '2025-02-05', 200.00, 'Completed'),
       (3, '2025-02-06', 99.50, 'Cancelled'),
       (1, '2025-02-07', 75.80, 'Shipped'),
       (2, '2025-02-08', 250.00, 'Completed');

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

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

В только что инициализированной СУБД Greengage заранее определена одна роль суперпользователя. Эта роль имеет то же имя, что и пользователь операционной системы, который инициализировал СУБД. Обычно эта роль называется gpadmin. Чтобы создать другие роли, необходимо сначала подключиться под пользователем gpadmin.

Роли определяются на уровне кластера Greengage DB. Это означает, что они действительны для всех баз данных кластера.

ПРИМЕЧАНИЕ

Чтобы разрешить заданным ролям устанавливать удаленные подключения, необходимо соответствующим образом изменить файл pg_hba.conf.

Роль базы данных может иметь ряд атрибутов, которые определяют, какие операции эта роль может выполнять в базе данных. Примеры таких атрибутов:

LOGIN или NOLOGIN — устанавливает, разрешен ли вход в систему для данной роли. Роль с атрибутом LOGIN можно рассматривать как пользователя.
SUPERUSER или NOSUPERUSER — устанавливает, является ли роль суперпользователем.
PASSWORD <password> — устанавливает пароль роли.

Полный список атрибутов ролей доступен в разделе Атрибуты ролей.

Объект — это защищаемый элемент, к которому может быть предоставлен доступ, например:

схема;
таблица;
столбец;
функция.

На разные типы объектов могут быть выданы разные наборы привилегий.

Привилегии позволяют пользователю выполнять определенные операции с заданными типами объектов, например:

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

Полный список привилегий на объекты доступен в разделе Привилегии на объекты.

Роль может владеть объектами базы данных, такими как таблицы или представления. Владельцем всех объектов, созданных изначально, является пользователь gpadmin.

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

Чтобы создать нового пользователя, используйте команду CREATE ROLE:

CREATE ROLE alice WITH LOGIN;

Атрибут LOGIN отличает пользовательскую роль от групповой.

Чтобы изменить атрибуты пользователя, используйте команду ALTER ROLE. Например, чтобы задать пароль для существующего пользователя, используйте атрибут PASSWORD:

ALTER ROLE alice WITH PASSWORD '123456';

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

ALTER ROLE alice SET search_path TO sales;

После создания пользователя вы можете выдать ему необходимые привилегии с помощью команды GRANT:

GRANT USAGE ON SCHEMA sales TO alice;
GRANT SELECT ON TABLE sales.orders TO alice;
GRANT SELECT (customer_type) ON TABLE sales.customers TO alice;

В этом примере пользователь alice получает следующие привилегии:

Доступ к объектам схемы sales.
Чтение любых данных из таблицы orders.
Чтение данных только из столбца customer_type в таблице customers.

Для отзыва привилегий пользователя используйте команду REVOKE:

REVOKE USAGE ON SCHEMA sales FROM alice;
REVOKE SELECT ON TABLE sales.orders FROM alice;
REVOKE SELECT (customer_type) ON TABLE sales.customers FROM alice;

Чтобы удалить пользователя, используйте DROP ROLE:

DROP ROLE alice;

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

Чтобы создать группу для выдачи одинаковых привилегий нескольким пользователям, используйте CREATE ROLE:

CREATE ROLE sales_team;

Чтобы создать обычных пользователей, используйте CREATE ROLE с привилегией LOGIN:

CREATE ROLE alice WITH LOGIN;
CREATE ROLE bob WITH LOGIN;

Выдайте привилегии роли sales_team, используя команду GRANT:

GRANT USAGE ON SCHEMA sales TO sales_team;
GRANT ALL PRIVILEGES ON sales.orders TO sales_team;

Назначьте пользователям роль sales_team с помощью команды GRANT:

GRANT sales_team TO alice, bob;

Чтобы отозвать роль sales_team у пользователей, используйте команду REVOKE:

REVOKE sales_team FROM alice, bob;

В этом разделе описано, как реализовать следующую иерархию ролей:

sales_team
      ├─── sales_team_manager
      │               └─── alice
      └─── sales_team_reader
                      └─── bob

В этом примере пользователи alice и bob наследуют привилегии, выданные ролям sales_team_manager и sales_team_reader соответственно. Оба пользователя также получают привилегии родительской роли sales_team.

Чтобы создать все роли, используйте CREATE ROLE:

CREATE ROLE sales_team;

CREATE ROLE sales_team_manager;
CREATE ROLE sales_team_reader;

CREATE ROLE alice WITH LOGIN;
CREATE ROLE bob WITH LOGIN;

Выдайте привилегии групповым ролям:

GRANT USAGE ON SCHEMA sales TO sales_team;

GRANT ALL PRIVILEGES ON sales.orders TO sales_team_manager;
GRANT SELECT ON TABLE sales.orders TO sales_team_reader;

Затем назначьте роль sales_team ролям sales_team_manager и sales_team_reader:

GRANT sales_team TO sales_team_manager, sales_team_reader;

Наконец, назначьте пользователям роли sales_team_manager и sales_team_reader:

GRANT sales_team_manager TO alice;
GRANT sales_team_reader TO bob;

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

\du

Результат может выглядеть так:

                            List of roles
     Role name      |                   Attributes
--------------------+------------------------------------------------
 alice              |
 bob                |
 gpadmin            | Superuser, Create role, Create DB, Replication
 sales_team         | Cannot login
 sales_team_manager | Cannot login
 sales_team_reader  | Cannot login

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

SELECT *
FROM information_schema.role_table_grants
WHERE grantee = 'sales_team_manager';

Результат может выглядеть следующим образом:

 grantor |      grantee       | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+--------------------+---------------+--------------+------------+----------------+--------------+----------------
 gpadmin | sales_team_manager | crm           | sales        | orders     | INSERT         | NO           | NO
 gpadmin | sales_team_manager | crm           | sales        | orders     | SELECT         | NO           | YES
 gpadmin | sales_team_manager | crm           | sales        | orders     | UPDATE         | NO           | NO
 gpadmin | sales_team_manager | crm           | sales        | orders     | DELETE         | NO           | NO
 gpadmin | sales_team_manager | crm           | sales        | orders     | TRUNCATE       | NO           | NO
 gpadmin | sales_team_manager | crm           | sales        | orders     | REFERENCES     | NO           | NO
 gpadmin | sales_team_manager | crm           | sales        | orders     | TRIGGER        | NO           | NO

Роль может владеть объектами базы данных, такими как таблицы или представления. Данный пример показывает, как назначить владельцем таблицы sales.orders_temp пользователя alice:

ALTER TABLE sales.orders_temp
    OWNER TO alice;

Чтобы поменять владельца объектов в текущей базе данных с alice на bob (включая владельца таблицы sales.orders_temp), используйте команду REASSIGN OWNED:

REASSIGN OWNED BY alice TO bob;

Чтобы удалить объекты, принадлежащие определенной роли, используйте команду DROP OWNED:

DROP OWNED BY bob;

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

Если дочерняя роль создана с атрибутом NOINHERIT.
Даже если дочерняя роль создана с атрибутом INHERIT, следующие атрибуты родительской роли никогда не наследуются: LOGIN, SUPERUSER, CREATEDB, CREATEROLE, CREATEEXTTABLE и RESOURCE QUEUE.

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

В данном примере создаются две роли:

CREATE ROLE dba CREATEROLE;
CREATE ROLE alice WITH LOGIN;
GRANT dba TO alice;

Родительская роль dba. Роль dba создается с атрибутом CREATEROLE, который не наследуется как обычные привилегии.
Дочерняя роль alice.

По умолчанию alice не может создавать другие роли в текущей сессии. Чтобы получить такую возможность, alice необходимо выполнить команду SET ROLE, чтобы получить все привилегии dba:

SET ROLE 'dba';

После этого alice может создавать другие роли:

CREATE ROLE bob WITH LOGIN;

Атрибут Описание Значение по умолчанию

Атрибут	Описание	Значение по умолчанию
SUPERUSER или NOSUPERUSER	Определяет, является ли роль суперпользователем. Только суперпользователь может создать нового суперпользователя	NOSUPERUSER
CREATEDB или NOCREATEDB	Определяет, может ли роль создавать базы данных	NOCREATEDB
CREATEROLE или NOCREATEROLE	Определяет, может ли роль создавать другие роли и управлять ими	NOCREATEROLE
INHERIT или NOINHERIT	Определяет, наследует ли роль привилегии ролей, членом которых она является. Роль с атрибутом `INHERIT` может использовать любые привилегии, выданные ролям, членом которых она является напрямую или косвенно	INHERIT
LOGIN или NOLOGIN	Определяет, разрешено ли роли входить в систему. Роль с атрибутом `LOGIN` можно рассматривать как пользователя. Роли с атрибутом `NOLOGIN` можно рассматривать как группы, которые удобны для управления привилегиями	NOLOGIN
CONNECTION LIMIT <limit>	Определяет максимальное количество одновременных соединений, которые роль может установить. Этот атрибут применим только к пользовательским (`LOGIN`) ролям	-1 (без ограничений)
CREATEEXTTABLE или NOCREATEEXTTABLE	Определяет, может ли роль создавать внешние таблицы. Для роли с атрибутом `CREATEEXTTABLE` тип внешней таблицы по умолчанию — `readable`, а протокол по умолчанию — `gpfdist`. Обратите внимание, что внешние таблицы, использующие протоколы `file` или `execute`, могут создаваться только суперпользователями	NOCREATEEXTTABLE
PASSWORD <password>	Устанавливает пароль для роли. Пароль необходимо задавать только для пользователей, у которых включена аутентификация по паролю. Для других методов аутентификации задание пароля не требуется	NULL
ENCRYPTED или UNENCRYPTED	Определяет, сохраняется ли новый пароль в виде хеш-строки. Если предоставленный пароль уже захеширован, он сохраняется без изменений, независимо от того, указан ли атрибут `ENCRYPTED` или `UNENCRYPTED`. Узнайте больше в статье Хеширование паролей	Определяется параметром конфигурации `password_encryption`
VALID UNTIL <timestamp>	Устанавливает дату и время, после которых пароль роли становится недействительным	Никогда не истекает
RESOURCE QUEUE <queue_name>	Устанавливает для роли очередь ресурсов, используемую для управления нагрузкой. Имейте в виду, что атрибут `RESOURCE QUEUE` не наследуется и должен быть явно задан для каждой пользовательской (`LOGIN`) роли	pg_default
DENY <deny_point_or_interval>	Устанавливает день или день и время для запрета доступа. Узнайте больше в статье Ограничение доступа пользователей по времени	—

SUPERUSER или NOSUPERUSER

Определяет, является ли роль суперпользователем. Только суперпользователь может создать нового суперпользователя

NOSUPERUSER

CREATEDB или NOCREATEDB

Определяет, может ли роль создавать базы данных

NOCREATEDB

CREATEROLE или NOCREATEROLE

Определяет, может ли роль создавать другие роли и управлять ими

NOCREATEROLE

INHERIT или NOINHERIT

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

INHERIT

Определяет, разрешено ли роли входить в систему. Роль с атрибутом LOGIN можно рассматривать как пользователя. Роли с атрибутом NOLOGIN можно рассматривать как группы, которые удобны для управления привилегиями

NOLOGIN

CONNECTION LIMIT <limit>

Определяет максимальное количество одновременных соединений, которые роль может установить. Этот атрибут применим только к пользовательским (LOGIN) ролям

-1 (без ограничений)

CREATEEXTTABLE или NOCREATEEXTTABLE

Определяет, может ли роль создавать внешние таблицы. Для роли с атрибутом CREATEEXTTABLE тип внешней таблицы по умолчанию — readable, а протокол по умолчанию — gpfdist. Обратите внимание, что внешние таблицы, использующие протоколы file или execute, могут создаваться только суперпользователями

NOCREATEEXTTABLE

PASSWORD <password>

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

NULL

ENCRYPTED или UNENCRYPTED

Определяет, сохраняется ли новый пароль в виде хеш-строки. Если предоставленный пароль уже захеширован, он сохраняется без изменений, независимо от того, указан ли атрибут ENCRYPTED или UNENCRYPTED. Узнайте больше в статье Хеширование паролей

Определяется параметром конфигурации password_encryption

VALID UNTIL <timestamp>

Устанавливает дату и время, после которых пароль роли становится недействительным

Никогда не истекает

RESOURCE QUEUE <queue_name>

Устанавливает для роли очередь ресурсов, используемую для управления нагрузкой. Имейте в виду, что атрибут RESOURCE QUEUE не наследуется и должен быть явно задан для каждой пользовательской (LOGIN) роли

pg_default

DENY <deny_point_or_interval>

Устанавливает день или день и время для запрета доступа. Узнайте больше в статье Ограничение доступа пользователей по времени

—

Тип объекта Привилегии

Тип объекта	Привилегии
Таблицы, внешние таблицы, представления	`SELECT` `INSERT` `UPDATE` `DELETE` `REFERENCES` `TRIGGER` `TRUNCATE` `ALL`
Столбцы	`SELECT` `INSERT` `UPDATE` `REFERENCES` `ALL`
Последовательности	`USAGE` `SELECT` `UPDATE` `ALL`
Базы данных	`CREATE` `CONNECT` `TEMPORARY` или `TEMP` `ALL`
Домены	`USAGE` `ALL`
Обертки внешних данных (Foreign Data Wrappers)	`USAGE` `ALL`
Внешние серверы (Foreign Servers)	`USAGE` `ALL`
Функции	`EXECUTE` `ALL`
Процедурные языки	`USAGE` `ALL`
Схемы	`CREATE` `USAGE` `ALL`
Табличные пространства	`CREATE` `ALL`
Типы	`USAGE` `ALL`
Протоколы	`SELECT` `INSERT` `ALL`

Таблицы, внешние таблицы, представления

SELECT
INSERT
UPDATE
DELETE
REFERENCES
TRIGGER
TRUNCATE
ALL

Столбцы

SELECT
INSERT
UPDATE
REFERENCES
ALL

Последовательности

USAGE
SELECT
UPDATE
ALL

Базы данных

CREATE
CONNECT
TEMPORARY или TEMP
ALL

Домены

USAGE
ALL

Обертки внешних данных (Foreign Data Wrappers)

USAGE
ALL

Внешние серверы (Foreign Servers)

USAGE
ALL

Функции

EXECUTE
ALL

Процедурные языки

USAGE
ALL

Схемы

CREATE
USAGE
ALL

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

CREATE
ALL

Типы

USAGE
ALL

Протоколы

SELECT
INSERT
ALL

Шифрование соединений с базой данных

Ограничение доступа пользователей по времени

Роли и привилегии

Предварительные требования

Обзор

Роли: пользователи и группы

Атрибуты ролей

Типы объектов

Привилегии на объекты

Владельцы объектов

Управление пользователями

Создание пользователей

Изменение атрибутов

Установка пользовательских настроек по умолчанию

Выдача привилегий

Отзыв привилегий

Удаление пользователей

Выдача привилегий с помощью групп

Группа как контейнер для пользовательских привилегий

Иерархии групп

Получение информации о ролях

Изменение владельцев объектов

Изменение роли в текущей сессии

Справочник

Атрибуты ролей

Привилегии на объекты