Освойте инструменты и лучшие практики DevOps — Центр знаний DevOps Освойте инструменты и лучшие практики DevOps — Центр знаний DevOps

Продвинутое управление доступом на основе ролей в PostgreSQL: защита на уровне строк и управление политиками

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

Расширенное управление доступом на основе ролей в PostgreSQL: безопасность на уровне строк и управление политиками

Безопасность на уровне строк (RLS) в PostgreSQL позволяет вашей базе данных фильтровать строки на основе текущей роли и контекста сессии. Если ваше приложение обслуживает несколько арендаторов или смешивает доступ администратора, менеджера и обычного пользователя, RLS может предотвратить превращение одного пропущенного WHERE tenant_id = ... в утечку данных.

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

Зачем нужна безопасность на уровне строк?

Проблемы традиционного подхода

Фильтрация на уровне приложения:

# Каждый запрос должен помнить о фильтрации
query = "SELECT * FROM documents WHERE tenant_id = %s"
results = db.execute(query, [current_user_tenant_id])

Проблемы:

  • Легко забыть фильтры (уязвимости безопасности)
  • Дублирование кода в приложении
  • Отсутствие защиты при прямом доступе к базе данных
  • Сложные требования к аудиту

Преимущества безопасности на уровне строк

  • Автоматическое применение: Политики применяются прозрачно
  • Централизованные правила: Логика безопасности в одном месте
  • Изоляция арендаторов: Идеально для SaaS-приложений
  • Соответствие аудиту: Встроенные гарантии безопасности
  • Производительность: Эффективное планирование запросов

Базовая настройка RLS

Шаг 1: Включение RLS для таблицы

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    owner_id INTEGER NOT NULL,
    department VARCHAR(50),
    is_public BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Включить безопасность на уровне строк
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Шаг 2: Создание ролей

-- Создание различных ролей пользователей
CREATE ROLE regular_user;
CREATE ROLE department_manager;
CREATE ROLE admin;

-- Предоставление доступа к таблице
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO regular_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO department_manager;
GRANT ALL ON documents TO admin;

Шаг 3: Создание политик

-- Политика: Пользователи видят только свои документы
CREATE POLICY user_own_documents ON documents
    FOR SELECT
    TO regular_user
    USING (owner_id = current_setting('app.user_id')::INTEGER);

-- Политика: Пользователи могут вставлять документы, принадлежащие им
CREATE POLICY user_insert_own ON documents
    FOR INSERT
    TO regular_user
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

-- Политика: Пользователи могут обновлять свои документы
CREATE POLICY user_update_own ON documents
    FOR UPDATE
    TO regular_user
    USING (owner_id = current_setting('app.user_id')::INTEGER)
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

Шаг 4: Установка переменных сессии

В коде приложения:

import psycopg2

# Подключение как роль
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="regular_user",
    password="password"
)

# Установка контекста пользователя
cursor = conn.cursor()
cursor.execute("SELECT set_config('app.user_id', %s, false)", [str(current_user_id)])

# Теперь запросы автоматически фильтруются
cursor.execute("SELECT * FROM documents")
# Возвращает только документы, где owner_id = current_user_id

Мультитенантное SaaS-приложение

Полная настройка мультитенантности

-- Создание таблицы арендаторов
CREATE TABLE tenants (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Создание таблицы пользователей
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    tenant_id INTEGER REFERENCES tenants(id),
    role VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Создание таблицы данных приложения
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL REFERENCES tenants(id),
    customer_name VARCHAR(100),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Включение RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Политика: Пользователи видят только заказы своего арендатора
CREATE POLICY tenant_isolation ON orders
    FOR ALL
    TO public
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::INTEGER);

Интеграция с приложением

Пример на Django:

from django.db import connection

class TenantMiddleware:
    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        if request.user.is_authenticated:
            with connection.cursor() as cursor:
                cursor.execute(
                    "SELECT set_config('app.tenant_id', %s, false)",
                    [str(request.user.tenant_id)]
                )
        
        response = self.get_response(request)
        return response

Пример на Node.js:

const { Pool } = require('pg');
const pool = new Pool();

async function setTenantContext(tenantId) {
    const client = await pool.connect();
    try {
        await client.query('SELECT set_config($1, $2, false)', ['app.tenant_id', String(tenantId)]);
        return client;
    } catch (err) {
        client.release();
        throw err;
    }
}

// Использование
app.use(async (req, res, next) => {
    if (req.user) {
        req.dbClient = await setTenantContext(req.user.tenantId);
    }
    next();
});

Продвинутые шаблоны политик

1. Иерархический контроль доступа

-- Пользователи могут видеть документы своего отдела и публичные
CREATE POLICY department_access ON documents
    FOR SELECT
    TO regular_user
    USING (
        department = current_setting('app.user_department', true)
        OR is_public = true
        OR owner_id = current_setting('app.user_id')::INTEGER
    );

2. Доступ на основе времени

CREATE TABLE subscriptions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    content TEXT,
    valid_from TIMESTAMP NOT NULL,
    valid_until TIMESTAMP NOT NULL
);

ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;

-- Политика: Пользователи видят только активные подписки
CREATE POLICY active_subscriptions ON subscriptions
    FOR SELECT
    TO public
    USING (
        user_id = current_setting('app.user_id')::INTEGER
        AND NOW() BETWEEN valid_from AND valid_until
    );

3. Доступ на основе ролей со сложной логикой

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    owner_id INTEGER,
    team_id INTEGER,
    visibility VARCHAR(20) -- 'private', 'team', 'public'
);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Сложная политика видимости
CREATE POLICY project_visibility ON projects
    FOR SELECT
    TO public
    USING (
        CASE current_setting('app.user_role', true)
            WHEN 'admin' THEN true
            WHEN 'manager' THEN (
                team_id = current_setting('app.user_team_id')::INTEGER
                OR visibility = 'public'
            )
            ELSE (
                owner_id = current_setting('app.user_id')::INTEGER
                OR visibility = 'public'
            )
        END
    );

4. Списки общего доступа

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    owner_id INTEGER NOT NULL
);

CREATE TABLE document_shares (
    document_id INTEGER REFERENCES documents(id),
    shared_with_user_id INTEGER,
    permission VARCHAR(20), -- 'read', 'write'
    PRIMARY KEY (document_id, shared_with_user_id)
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Политика: Пользователи видят свои или общие документы
CREATE POLICY document_access ON documents
    FOR SELECT
    TO public
    USING (
        owner_id = current_setting('app.user_id')::INTEGER
        OR id IN (
            SELECT document_id 
            FROM document_shares 
            WHERE shared_with_user_id = current_setting('app.user_id')::INTEGER
        )
    );

-- Политика: Пользователи могут изменять свои документы или те, к которым есть доступ на запись
CREATE POLICY document_modify ON documents
    FOR UPDATE
    TO public
    USING (
        owner_id = current_setting('app.user_id')::INTEGER
        OR id IN (
            SELECT document_id 
            FROM document_shares 
            WHERE shared_with_user_id = current_setting('app.user_id')::INTEGER
              AND permission = 'write'
        )
    );

Административный доступ и RLS

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

Вариант 1: Атрибут роли BYPASSRLS

CREATE ROLE admin_user WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE admin_user BYPASSRLS;

Используйте это экономно. BYPASSRLS является мощным и не должен предоставляться обычным ролям приложения.

Вариант 2: Разрешающие политики для администраторов

-- Создание разрешающей политики для роли администратора
CREATE POLICY admin_all_access ON documents
    FOR ALL
    TO admin
    USING (true)
    WITH CHECK (true);

Вариант 3: Принудительный RLS для владельцев таблиц

ALTER TABLE documents FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY заставляет владельцев таблиц также следовать политикам. Обычно это безопаснее для таблиц, принадлежащих приложению. Для обслуживания используйте отдельную контролируемую роль вместо отключения RLS в ad hoc скрипте.

Типы политик: USING vs WITH CHECK

Предложение USING

Определяет, какие существующие строки видны/изменяемы:

CREATE POLICY select_own ON documents
    FOR SELECT
    USING (owner_id = current_setting('app.user_id')::INTEGER);

-- Пользователь может только SELECT строки, где owner_id совпадает

Предложение WITH CHECK

Определяет, какие новые/обновленные строки разрешены:

CREATE POLICY insert_own ON documents
    FOR INSERT
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

-- Пользователь может только INSERT строки, где owner_id совпадает

Комбинированные политики

CREATE POLICY update_own ON documents
    FOR UPDATE
    USING (owner_id = current_setting('app.user_id')::INTEGER)
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

Соображения производительности

1. Индексация столбцов политик

-- Создание индексов для столбцов, используемых в политиках
CREATE INDEX idx_documents_owner ON documents(owner_id);
CREATE INDEX idx_documents_tenant ON documents(tenant_id);
CREATE INDEX idx_documents_department ON documents(department);

2. Анализ планов запросов

EXPLAIN ANALYZE
SELECT * FROM documents
WHERE title LIKE '%report%';

-- Проверьте, эффективно ли применяется фильтр политики

3. Будьте осторожны с подзапросами в политиках

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

Рискованно без индексов:

CREATE POLICY slow_policy ON documents
    USING (
        id IN (
            SELECT document_id
            FROM shares
            WHERE user_id = current_setting('app.user_id')::INTEGER
        )
    );

Часто понятнее с поддерживающими индексами:

CREATE POLICY faster_policy ON documents
    USING (
        EXISTS (
            SELECT 1 FROM shares 
            WHERE document_id = documents.id 
              AND user_id = current_setting('app.user_id')::INTEGER
        )
    );

Создайте индексы, такие как shares(user_id, document_id), и проверьте план с помощью EXPLAIN (ANALYZE, BUFFERS).

Мониторинг и аудит

Просмотр активных политик

-- Список всех политик RLS
SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies
ORDER BY tablename, policyname;

Аудит изменений политик

Отслеживайте DDL политик в миграциях и журналах аудита базы данных. Если ваша среда использует триггеры событий, тщательно тестируйте их на вашей версии PostgreSQL и включайте события CREATE POLICY, ALTER POLICY и DROP POLICY. Не полагайтесь на непроверенный фрагмент триггера для доказательства соответствия.

Тестирование эффективности политик

-- Создание тестовой функции
CREATE OR REPLACE FUNCTION test_user_access(
    test_user_id INTEGER,
    test_tenant_id INTEGER
)
RETURNS TABLE(document_id INTEGER, title VARCHAR) AS $$
BEGIN
    -- Установка переменных сессии
    PERFORM set_config('app.user_id', test_user_id::text, false);
    PERFORM set_config('app.tenant_id', test_tenant_id::text, false);
    
    -- Возврат доступных документов
    RETURN QUERY SELECT id, documents.title FROM documents;
END;
$$ LANGUAGE plpgsql SECURITY INVOKER;

-- Тестирование доступа
SELECT * FROM test_user_access(123, 5);

Устранение неполадок

Проблема 1: Политики не применяются

Проверьте, включен ли RLS:

SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public' AND tablename = 'documents';

Включите, если отключен:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Проблема 2: Переменные сессии не установлены

Отладка текущих настроек:

SHOW app.user_id;
SHOW app.tenant_id;

-- Или используйте current_setting
SELECT current_setting('app.user_id', true);

Проблема 3: Ухудшение производительности

Определение медленных политик:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM documents WHERE title = 'test';

-- Ищите операции "Filter" с условиями политик

Решение: Упростите политики или добавьте индексы

Проблема 4: Неожиданный отказ в доступе

Тестирование как конкретный пользователь:

SET ROLE regular_user;
SELECT set_config('app.user_id', '123', false);

SELECT * FROM documents;
-- Показывает, что этот пользователь может видеть

RESET ROLE;

Лучшие практики

  1. Всегда устанавливайте переменные сессии: Убедитесь, что значения current_setting() заполнены
  2. Индексируйте столбцы политик: Создавайте индексы для всех столбцов, используемых в USING/WITH CHECK
  3. Тщательно тестируйте: Проверяйте политики с разными ролями и сценариями
  4. Держите политики простыми: Сложная логика влияет на производительность
  5. Используйте EXPLAIN: Анализируйте планы запросов с включенным RLS
  6. Документируйте политики: Комментируйте логику политик для удобства сопровождения
  7. Разделяйте задачи: Используйте разные политики для SELECT, INSERT, UPDATE, DELETE
  8. Мониторьте производительность: Настройте оповещения для медленных запросов

Вывод

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

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