Skip to content

Latest commit

 

History

History

DDL_log_to_table

README.md

Как в PostgreSQL журналировать DDL команды в таблицу БД? (How log DDL queries to table in PostgreSQL?)

DDL команды обычно логируются в общем большом файловом журнале. У этого подхода следующие недостатки:

  1. Там полно SELECT и DML команд, отыскать там DDL непросто. DDL запросы ещё могут сохраняться в Graylog, но отыскать там DDL тоже непросто.
  2. Там нет чётко выделенного идентификатора объекта БД и события, только тело DDL команды.
  3. Файловые журналы обычно хранятся несколько недель и потом автоматически удаляются.

Логирование DDL команд в отдельной таблице БД позволяет:

  1. Усилить безопасность БД:

    • сохранять легальные изменения схемы БД администраторами БД (DBA), включая ручные (неавтоматические) миграции БД
    • выявить нелегальные изменения схемы БД: прямое выполнения DDL команд мимо миграторов БД; SQL injection

  2. Сохранять следующие события:

    • Создание, удаление, модификация объектов БД (schema, table, view, column, sequence, index, type, function, procedure, domain), включая изменение владельца. При удалении сохраняется информация о всех удалённых зависимых объектах.
    • Наделение и отзыв прав доступа к объектам БД

  3. Иметь возможность получить и предоставить информацию по запросу коллег при возникновении инцидентов, включая нестабильность работы БД. При этом будет возможность получить точную дату-время начала и окончания, а так же длительность выполнения DDL запросов в транзакции.

  4. Знать дату-время создания и обновления объектов БД (к сожалению, в БД такая информация в системных таблицах отсутствует), чтобы потом автоматически их удалять через 1 месяц после создания из схем:

    • migration — здесь хранятся таблицы с данными для возможности для отката ранее накаченных миграций БД
    • unused — здесь временно хранятся неиспользуемые и уже ненужные объекты БД перед их окончательным удалением

  5. Выявить DDL запросы в бизнес-логике приложения (такие запросы должны выполняться под отдельным пользователем?).

  6. Для отдела информационной безопасности появляется возможность интеграции с SIEM в качестве источника данных.

Ограничения

Не журналируется:

  • Создание, удаление, модификация пользователей и ролей, включая изменение атрибутов и пароля. Почему так.
  • Событие ddl_command_start для запросов DROP INDEX CONCURRENTLY …. Почему так.
  • До PostgreSQL 17-й версии не журналируется команда REINDEX. См. https://pgpedia.info/e/event-trigger.html

Cтатистические SQL запросы с примерами

select tag, 
       event, 
       count(*) as total
from db_audit.ddl_log
group by 1, 2
order by 1, 2;
tag event total
ALTER FUNCTION ddl_command_start 15
ALTER FUNCTION ddl_command_end 15
ALTER SCHEMA ddl_command_start 2
ALTER SCHEMA ddl_command_end 2
ALTER SEQUENCE ddl_command_start 4
ALTER SEQUENCE ddl_command_end 4
ALTER TABLE ddl_command_start 79
ALTER TABLE ddl_command_end 79
ALTER TABLE sql_drop 4
ALTER TYPE ddl_command_start 6
ALTER TYPE ddl_command_end 6
ALTER VIEW ddl_command_start 2
ALTER VIEW ddl_command_end 2
COMMENT ddl_command_start 167
COMMENT ddl_command_end 167
CREATE FUNCTION ddl_command_start 34
CREATE FUNCTION ddl_command_end 34
CREATE INDEX ddl_command_start 53
CREATE INDEX ddl_command_end 55
CREATE PROCEDURE ddl_command_start 5
CREATE PROCEDURE ddl_command_end 5
CREATE SCHEMA ddl_command_start 2
CREATE SCHEMA ddl_command_end 2
CREATE TABLE ddl_command_start 23258
CREATE TABLE ddl_command_end 13831
CREATE TABLE AS ddl_command_start 8
CREATE TABLE AS ddl_command_end 8
CREATE TRIGGER ddl_command_start 20
CREATE TRIGGER ddl_command_end 20
CREATE TYPE ddl_command_start 2
CREATE TYPE ddl_command_end 2
CREATE VIEW ddl_command_start 6
CREATE VIEW ddl_command_end 8
DROP FUNCTION ddl_command_start 3
DROP FUNCTION sql_drop 3
DROP INDEX ddl_command_start 10
DROP INDEX sql_drop 10
DROP PROCEDURE ddl_command_start 1
DROP PROCEDURE sql_drop 1
DROP SCHEMA ddl_command_start 1
DROP SCHEMA sql_drop 1
DROP TABLE ddl_command_start 32167
DROP TABLE sql_drop 132
DROP TRIGGER ddl_command_start 16
DROP TRIGGER sql_drop 1
DROP TYPE ddl_command_start 2
DROP TYPE sql_drop 2
DROP VIEW ddl_command_start 1
DROP VIEW sql_drop 4
GRANT ddl_command_start 5
GRANT ddl_command_end 5
REFRESH MATERIALIZED VIEW ddl_command_start 17534
REFRESH MATERIALIZED VIEW ddl_command_end 17534 
select tag,
       lower(object_type) as object_type,
       count(*) as total
from db_audit.ddl_log
where event != 'ddl_command_start'
group by 1, 2
order by 1, 2;
tag object_type total
ALTER FUNCTION function 15
ALTER SCHEMA schema 2
ALTER SEQUENCE sequence 4
ALTER TABLE table 77
ALTER TABLE table column 3
ALTER TABLE table constraint 3
ALTER TYPE type 6
ALTER VIEW view 2
COMMENT function 5
COMMENT procedure 3
COMMENT schema 2
COMMENT table 13
COMMENT table column 132
COMMENT type 7
COMMENT view 5
CREATE FUNCTION function 34
CREATE INDEX index 55
CREATE PROCEDURE procedure 5
CREATE SCHEMA schema 2
CREATE TABLE index 15
CREATE TABLE sequence 28
CREATE TABLE table 13989
CREATE TABLE AS table 8
CREATE TRIGGER trigger 20
CREATE TYPE type 2
CREATE VIEW view 8
DROP FUNCTION function 3
DROP INDEX index 10
DROP PROCEDURE procedure 1
DROP SCHEMA schema 1
DROP TABLE default value 6
DROP TABLE index 34
DROP TABLE sequence 2
DROP TABLE table 18
DROP TABLE table constraint 10
DROP TABLE toast table 10
DROP TABLE trigger 4
DROP TABLE type 48
DROP TRIGGER trigger 1
DROP TYPE type 2
DROP VIEW rule 1
DROP VIEW type 2
DROP VIEW view 1
GRANT sequence 1
GRANT table 4
REFRESH MATERIALIZED VIEW materialized view 17536

Ссылки по теме