Аудит доступа
Отслеживайте, кто что может видеть
«Аудит доступа» — бесплатный урок SQL Academy на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.
Зачем нужен аудит доступа
Знание того, кто, какие данные и когда просматривал, — основа безопасности базы данных. Аудит создает надежный журнал событий, который помогает обнаруживать несанкционированный доступ, расследовать инциденты и выполнять требования нормативного соответствия, такие как GDPR, HIPAA или SOC 2.
В этом уроке Вы научитесь проектировать таблицы аудита, автоматически фиксировать события доступа с помощью триггеров, использовать встроенные функции журналирования PostgreSQL и запрашивать журнал аудита, чтобы ответить на вопрос: кто что может видеть?
Проектирование таблицы журнала аудита
Первый шаг — выделенная таблица, которая записывает каждое значимое событие. Хороший журнал аудита содержит имя таблицы, тип операции, старые и новые значения, пользователя, выполнившего действие, и точную отметку времени.
Приведенный ниже пример создает таблицу общего назначения audit_log, используя столбцы JSONB для хранения снимков строк — это достаточно гибкое решение для любой таблицы без изменений схемы.
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
event_time TIMESTAMPTZ NOT NULL DEFAULT now(),
db_user TEXT NOT NULL DEFAULT current_user,
app_user TEXT,
table_name TEXT NOT NULL,
operation TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE','SELECT')),
row_id BIGINT,
old_data JSONB,
new_data JSONB
);Запись текущего пользователя
PostgreSQL предоставляет несколько встроенных функций для определения того, кто выполняет запрос. current_user возвращает имя роли, действующей после любого SET ROLE. session_user всегда возвращает исходную роль, использованную для входа, независимо от переключения ролей.
В приложениях, которые используют одну общую роль базы данных, но передают пользователя уровня приложения через SET LOCAL app.current_user, это значение можно прочитать с помощью current_setting().
-- Who is the database user right now?
SELECT current_user,
session_user;
-- Read an application-level user injected by the app layer
SELECT current_setting('app.current_user', true) AS app_user;Написание функции триггера аудита
Функция триггера — самый надежный способ фиксировать события изменения данных, поскольку она срабатывает автоматически: код приложения не может ее обойти. Приведенная ниже функция записывает в журнал каждую операцию INSERT, UPDATE и DELETE в любой таблице, к которой она подключена, сохраняя старые и новые значения строк в формате JSONB.
Обратите внимание на использование TG_TABLE_NAME (таблицы, вызвавшей триггер) и row_to_json() для преобразования значений строк в формат, пригодный для хранения.
CREATE OR REPLACE FUNCTION fn_audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
INSERT INTO audit_log (
db_user,
app_user,
table_name,
operation,
row_id,
old_data,
new_data
) VALUES (
current_user,
current_setting('app.current_user', true),
TG_TABLE_NAME,
TG_OP,
COALESCE(NEW.id, OLD.id),
CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD)::JSONB END,
CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW)::JSONB END
);
RETURN NULL;
END;
$$;Подключение триггера к таблице
После создания функции триггера подключите ее к каждой таблице, которую хотите подвергать аудиту, с помощью оператора CREATE TRIGGER. Использование AFTER гарантирует, что данные действительно записаны до создания записи в журнале. Оговорка FOR EACH ROW запускает триггер один раз для каждой измененной строки.
Здесь триггер применяется к гипотетической таблице patients и автоматически записывает каждую операцию INSERT, UPDATE и DELETE.
CREATE TRIGGER trg_audit_patients
AFTER INSERT OR UPDATE OR DELETE
ON patients
FOR EACH ROW
EXECUTE FUNCTION fn_audit_changes();Аудит запросов SELECT
Триггеры изменения данных фиксируют только операции записи. Для аудита доступа для чтения нужен другой подход. Один из вариантов — триггер уровня оператора AFTER SELECT (поддерживается в PostgreSQL 14+ в определенных контекстах). Более распространенный шаблон — явно записывать операции чтения внутри функции или представления, которое оборачивает конфиденциальную таблицу.
Приведенный ниже пример оборачивает конфиденциальную таблицу в функцию, которая записывает в журнал каждое чтение перед возвратом результатов.
CREATE OR REPLACE FUNCTION get_patient_record(p_id INT)
RETURNS SETOF patients
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
-- Log the read access
INSERT INTO audit_log (db_user, app_user, table_name, operation, row_id)
VALUES (
current_user,
current_setting('app.current_user', true),
'patients',
'SELECT',
p_id
);
RETURN QUERY
SELECT * FROM patients WHERE id = p_id;
END;
$$;Встроенное журналирование PostgreSQL
Файл postgresql.conf PostgreSQL предоставляет мощные возможности серверного журналирования, для которых не нужен код приложения. Параметр log_min_duration_statement записывает в журнал любой запрос, выполняющийся дольше заданного порога. Параметры log_connections и log_disconnections фиксируют входы пользователей в систему и выходы из нее.
Приведенный ниже запрос использует системное представление pg_stat_activity, чтобы показать активные сеансы — это простой способ мониторинга доступа в реальном времени.
-- See who is currently connected and what they are running
SELECT pid,
usename AS db_user,
application_name,
client_addr,
state,
query_start,
LEFT(query, 80) AS current_query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start DESC;Запросы к журналу аудита
Журнал аудита ценен только в том случае, если Вы можете эффективно выполнять по нему запросы. К распространённым вопросам относятся: какой пользователь последним обращался к записи, что со временем изменилось в строке и сколько чтений конфиденциальных данных произошло за последние 24 часа.
Приведённый ниже запрос находит всех пользователей, обращавшихся к определённой записи пациента, и сортирует их, начиная с самого недавнего обращения.
SELECT event_time,
db_user,
app_user,
operation,
old_data,
new_data
FROM audit_log
WHERE table_name = 'patients'
AND row_id = 42
ORDER BY event_time DESC
LIMIT 20;Выявление подозрительных моделей доступа
После сбора данных аудита Вы можете писать запросы, выявляющие аномалии. Например, пользователь, который внезапно читает гораздо больше строк, чем обычно, или одна и та же конфиденциальная запись, к которой неоднократно обращались за короткий промежуток времени, могут указывать на попытку вывода данных.
Приведённый ниже запрос подсчитывает события SELECT для каждого пользователя приложения за последний час и выделяет всех, кто прочитал более 100 строк.
SELECT app_user,
COUNT(*) AS records_accessed
FROM audit_log
WHERE operation = 'SELECT'
AND event_time >= now() - INTERVAL '1 hour'
GROUP BY app_user
HAVING COUNT(*) > 100
ORDER BY records_accessed DESC;Защита самого журнала аудита
Журнал аудита, который можно изменять, не заслуживает доверия. Следует ограничить к нему доступ, чтобы обычные пользователи и роли приложений не могли удалять или изменять строки. Самый безопасный подход — предоставить роли приложения только INSERT, а SELECT оставить за выделенной ролью аудитора.
Вы также можете обеспечить неизменяемость с помощью триггера, который вызывает исключение, если кто-либо пытается изменить или удалить строку аудита.
-- Only the app role may insert; nobody may update or delete
REVOKE ALL ON audit_log FROM PUBLIC;
GRANT INSERT ON audit_log TO app_role;
GRANT SELECT ON audit_log TO auditor_role;
-- Trigger to block any tampering
CREATE OR REPLACE FUNCTION fn_protect_audit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION 'audit_log rows are immutable';
RETURN NULL;
END;
$$;
CREATE TRIGGER trg_protect_audit
BEFORE UPDATE OR DELETE ON audit_log
FOR EACH ROW EXECUTE FUNCTION fn_protect_audit();Аудит решений политик RLS
Когда активна безопасность на уровне строк, PostgreSQL просто скрывает строки, не выдавая ошибок. Из-за этого трудно определить, пытался ли пользователь прочитать строку, доступ к которой ему запрещён. Один из подходов — добавить разрешающую политику, которая всегда вставляет запись аудита до того, как ограничивающая политика отфильтрует строки.
Приведённый ниже запрос показывает, как проверить, какие политики RLS существуют для таблицы и к каким ролям они применяются.
-- View all RLS policies on the patients table
SELECT polname AS policy_name,
polcmd AS command,
polroles::TEXT AS applies_to,
polqual::TEXT AS using_expression,
polwithcheck::TEXT AS with_check_expression
FROM pg_policy
WHERE polrelid = 'patients'::REGCLASS
ORDER BY polname;Проверка знаний
Проверьте, насколько хорошо Вы понимаете аудит доступа в SQL.
Итоги урока
В этом уроке Вы узнали, как построить полноценную систему аудита доступа в PostgreSQL:
- Таблица журнала аудита — таблица на основе JSONB, фиксирующая, кто, что и когда сделал.
- Функция триггера — автоматически записывает в журнал события INSERT, UPDATE и DELETE для любой подключённой таблицы с использованием
TG_TABLE_NAME,row_to_json()иcurrent_user. - Аудит чтения — функции для конфиденциальных таблиц, которые записывают в журнал события SELECT перед возвратом данных.
- Встроенный мониторинг —
pg_stat_activityпоказывает активные сеансы; серверное журналирование фиксирует запросы без изменений в коде. - Выявление аномалий — агрегирующие запросы к журналу аудита могут выявлять необычный объём доступа.
- Неизменяемость — отзовите UPDATE/DELETE для таблицы аудита и добавьте блокирующий триггер, чтобы предотвратить несанкционированные изменения.
Грамотно спроектированный журнал аудита — самый надёжный инструмент для ответа на вопрос кто к чему обращался и основа любого расследования, связанного с соблюдением требований или безопасностью.
Часто задаваемые вопросы
Урок «Аудит доступа» бесплатный?
Да — полный текст урока «Аудит доступа» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.
Чему я научусь в уроке «Аудит доступа»?
Отслеживайте, кто что может видеть Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Academy?
Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.
Сколько времени занимает урок «Аудит доступа»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Academy?
Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.