Схемы и роли в PostgreSQL: организация доступа к данным

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

Схемы (Schemas)

Схема — пространство имён (namespace) для объектов БД. Таблица orders в схеме public и orders в схеме analytics — разные таблицы.

-- Создать схему
CREATE SCHEMA analytics;
CREATE SCHEMA staging;
CREATE SCHEMA archive;

-- Список схем
\dn  -- в psql
SELECT schema_name FROM information_schema.schemata;

-- Создать таблицу в схеме
CREATE TABLE analytics.daily_metrics (...);

-- Переключить схему поиска
SET search_path = analytics, public;
-- Теперь без префикса daily_metrics → analytics.daily_metrics

Схема public

По умолчанию все таблицы создаются в public. В PostgreSQL 15+ права на public ограничены:

-- PostgreSQL 15+: убрать права создания объектов для всех
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- Дать конкретной роли
GRANT CREATE ON SCHEMA public TO app_role;

Роли и пользователи

В PostgreSQL нет разницы между ролью и пользователем — оба создаются через CREATE ROLE. Пользователь = роль с правом на вход.

-- Создать роль (без возможности входа)
CREATE ROLE readonly_role;

-- Создать пользователя (с возможностью входа)
CREATE ROLE alice WITH LOGIN PASSWORD 'secure_password';

-- Или через CREATE USER (синоним CREATE ROLE ... LOGIN)
CREATE USER bob WITH PASSWORD 'another_password';

-- Список ролей
\du  -- в psql
SELECT rolname, rollogin, rolcreatedb FROM pg_roles;

Иерархия ролей

-- Создать иерархию
CREATE ROLE base_reader;
CREATE ROLE analyst;
CREATE ROLE developer;

-- analyst наследует права base_reader
GRANT base_reader TO analyst;
GRANT base_reader TO developer;

-- Пользователь получает права через роль
GRANT analyst TO alice;
-- Alice теперь имеет права analyst + base_reader

GRANT — выдача прав

Права на таблицы

-- SELECT на одну таблицу
GRANT SELECT ON TABLE users TO readonly_role;

-- Несколько привилегий
GRANT SELECT, INSERT, UPDATE ON TABLE orders TO app_role;

-- Все привилегии
GRANT ALL ON TABLE admin_data TO superadmin_role;

-- Все таблицы в схеме
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

-- Будущие таблицы (DEFAULT PRIVILEGES)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_role;

Права на схемы

-- Без прав на схему пользователь не может читать таблицы в ней!
GRANT USAGE ON SCHEMA analytics TO analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analyst_role;

-- Права на создание объектов в схеме
GRANT CREATE ON SCHEMA staging TO etl_role;

Права на функции

GRANT EXECUTE ON FUNCTION calculate_ltv(integer) TO analyst_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_role;

REVOKE — отзыв прав

-- Отозвать конкретное право
REVOKE INSERT ON TABLE orders FROM app_role;

-- Отозвать всё
REVOKE ALL ON TABLE sensitive_data FROM public_role;

-- Отозвать право роли у пользователя
REVOKE analyst FROM alice;

Типичные паттерны разграничения доступа

Паттерн 1: Аналитики — только чтение

CREATE ROLE analyst_role;

-- Доступ к схемам
GRANT USAGE ON SCHEMA public, analytics TO analyst_role;

-- Только SELECT
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analyst_role;

-- Будущие таблицы
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics GRANT SELECT ON TABLES TO analyst_role;

-- Создать аналитика
CREATE USER data_analyst WITH PASSWORD 'pwd123';
GRANT analyst_role TO data_analyst;

Паттерн 2: Приложение — ограниченный DML

CREATE ROLE app_role;
GRANT USAGE ON SCHEMA public TO app_role;

-- Только нужные операции
GRANT SELECT, INSERT, UPDATE ON TABLE orders TO app_role;
GRANT SELECT, INSERT ON TABLE events TO app_role;
GRANT SELECT ON TABLE users TO app_role;
GRANT UPDATE (last_activity_date, streak_days) ON TABLE users TO app_role;

-- Последовательности для SERIAL
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_role;

CREATE USER app_user WITH PASSWORD 'app_pwd';
GRANT app_role TO app_user;

Паттерн 3: Читаемые схемы, записываемые схемы

-- ETL пишет в staging, аналитики читают из analytics
CREATE SCHEMA staging;
CREATE SCHEMA analytics;

CREATE ROLE etl_role;
GRANT USAGE, CREATE ON SCHEMA staging TO etl_role;
GRANT ALL ON ALL TABLES IN SCHEMA staging TO etl_role;

CREATE ROLE analyst_role;
GRANT USAGE ON SCHEMA analytics TO analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analyst_role;

Row Level Security (RLS)

RLS позволяет ограничить видимость строк в таблице:

-- Включить RLS для таблицы
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Политика: пользователь видит только свои заказы
CREATE POLICY user_orders ON orders
FOR ALL
TO authenticated_role
USING (user_id = current_setting('app.current_user_id')::integer);

-- Полный доступ для суперпользователя
CREATE POLICY admin_all ON orders
FOR ALL
TO admin_role
USING (true);

-- Установить контекст в приложении перед запросом
SET app.current_user_id = '42';
SELECT * FROM orders;  -- Видит только заказы user_id=42

RLS — мощный инструмент для мультитенантных систем.

Проверка прав

-- Права на таблицу
\dp users  -- в psql

-- Или через SQL
SELECT
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'users';

-- Все права текущего пользователя
SELECT current_user, session_user;
SELECT has_table_privilege(current_user, 'orders', 'SELECT');
SELECT has_schema_privilege(current_user, 'analytics', 'USAGE');

Итог: минимальные привилегии

Золотое правило безопасности: principle of least privilege — каждая роль получает только те права, которые ей нужны для работы.

Никогда не давайте приложению права SUPERUSER или CREATEDB без необходимости.