GitHub

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

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

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

  • Роль — это либо пользователь, взаимодействующий с кластером Greengage DB, либо группа привилегий, которые могут быть выданы пользователю.

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

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

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

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

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>

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

 — 

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

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

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

  • 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
Предыдущая
Шифрование соединений с базой данных
Следующая
Ограничение доступа пользователей по времени
Открыть страницу поиска