PostgreSQL ACL Object

Введение

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

Привилегии DDL разработчику не нужны, несмотря на то, что разработчики часто стремятся выполнять операции ALTER TABLE. Начиная с версии 11 PostgreSQL некорректные ALTER TABLE уже не проходят без явных прав. Административная ответственность за production среду лежит на администраторах, а не разработчиках.

Общие правила

Базовое правило: все объекты всех баз данных принадлежат пользователю postgres.

Если схема данных разворачивается приложением, требуется временно выставить владельцем пользователя приложения на время разворачивания, затем вернуть всё на место.

Функция обновления владельца

CREATE OR REPLACE FUNCTION public.dba_flush_owner (
  set_owner_in text = 'postgres'::text
)
RETURNS void AS
$body$
DECLARE
    schema_name     TEXT;
    schema_owner    TEXT;
    set_owner       TEXT;
    obj_type        TEXT;
    obj_name        TEXT;
    obj_owner       TEXT;
BEGIN
    IF ( SELECT current_user ) <> 'postgres' THEN
        RAISE EXCEPTION 'Only for postgres user';
    END IF;

    FOR schema_name, schema_owner IN
        SELECT "iss"."schema_name", "iss"."schema_owner"
            FROM "information_schema"."schemata" AS iss
    LOOP
        IF schema_name LIKE 'pg_%' OR schema_name = 'information_schema' THEN
            set_owner := 'postgres';
        ELSE
            set_owner := set_owner_in;
        END IF;

        IF set_owner <> schema_owner THEN
            EXECUTE 'ALTER SCHEMA "' || schema_name || '"' ||
                ' OWNER TO ' || set_owner;
        END IF;

        FOREACH obj_type IN
            ARRAY '{table, view, sequence}'::text[]
        LOOP
            FOR obj_name, obj_owner IN
                EXECUTE
                'SELECT "obj"."' || obj_type ||'name", "obj"."' || obj_type || 'owner"
                    FROM "pg_' || obj_type || 's" AS "obj"
                    WHERE "obj"."schemaname" = ''' || schema_name || ''''
            LOOP
                IF set_owner <> obj_owner THEN
                    EXECUTE 'ALTER ' || obj_type  || ' "' || schema_name || '"."' || obj_name || '"' ||
                        ' OWNER TO ' || set_owner;
                END IF;
            END LOOP;
        END LOOP;
    END LOOP;

    REVOKE ALL ON SCHEMA public FROM PUBLIC;
    EXECUTE 'REVOKE ALL ON DATABASE ' || ( SELECT current_database() ) || ' FROM PUBLIC';
END;
$body$
LANGUAGE 'plpgsql';

Вызов функции:

SELECT * FROM public.dba_flush_owner( [ user name ] );

Простые привилегии пользователей

Схема расстановки привилегий для пользователей приложений.

Классификация операций

DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE.

DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE, USAGE.

Для пользователя приложения DDL операции отключаются, оставляются только DML.

Для базы данных

GRANT CONNECT, TEMPORARY
    ON DATABASE my_db
    TO my_role;
REVOKE CREATE
    ON DATABASE my_db
    FROM my_role;

Для схем данных

GRANT USAGE
    ON SCHEMA my_schema
    TO my_role;

REVOKE CREATE
    ON SCHEMA my_schema
    FROM my_role;

Для таблиц и представлений

GRANT SELECT, INSERT, UPDATE, DELETE
    ON ALL TABLES
    IN SCHEMA [ name ]
    TO my_role;

REVOKE TRUNCATE, REFERENCES, TRIGGER
    ON ALL TABLES
    IN SCHEMA [ name ]
    FROM my_role;

Для последовательностей

GRANT ALL PRIVILEGES
    ON ALL SEQUENCES
    IN SCHEMA [ name ]
    TO my_role;

Документация

GRANT
REVOKE

Каскадные привилегии пользователей

Для сложных решений требуются многоуровневые схемы привилегий. Используется параметр SECURITY DEFINER функций — функция вызывается с привилегиями владельца.

Архитектура:

Первый уровень — базовые (атомарные) операции. Создаётся роль project_base, ей предоставляются DML операции с таблицами. Создаются функции базовых операций с владельцем project_base и параметром SECURITY DEFINER.
Второй уровень — бизнес-операции. Создаётся роль project_business, ей предоставляются права на выполнение функций базового уровня.
Третий уровень — приложение. Обычной роли приложения предоставляются права на выполнение бизнес-функций.

Важно отключить привилегию на выполнение функций для группы PUBLIC:

REVOKE ALL ON ALL FUNCTIONS IN SCHEMA [ schema name ] FROM PUBLIC;

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