Задание 3: Аудит операций над конкретными объектами

Ведение журнала аудита на уровне объектов осуществляется через систему ролей. Параметр pgaudit.role определяет роль, которая будет использоваться для ведения журнала аудита. Отношение (TABLE, VIEW) будет записываться в журнал аудита, если роль имеет разрешения для выполняемой команды или наследует разрешения от другой роли. Это позволяет эффективно использовать несколько ролей аудита, даже если в любом контексте существует одна главная роль.

Для проверки ролей обратитесь к таблице pg_shadow:

postgres@postgres=# SELECT usename,useconfig FROM pg_shadow WHERE usename = 'student1';

┌──────────┬────────────────────┐
│ usename  │     useconfig      │
├──────────┼────────────────────┤
│ student1 │ {pgaudit.log=READ} │
└──────────┴────────────────────┘
(1 row)

postgres@postgres=# SELECT usename,useconfig FROM pg_shadow WHERE usename = 'student2';

┌──────────┬─────────────────────┐
│ usename  │      useconfig      │
├──────────┼─────────────────────┤
│ student2 │ {pgaudit.log=WRITE} │
└──────────┴─────────────────────┘
(1 row)

Отключите аудит для пользователей student1 и student2:

postgres@postgres=# ALTER ROLE student1 RESET pgaudit.log;

ALTER ROLE

postgres@postgres=# ALTER ROLE student2 RESET pgaudit.log;

ALTER ROLE

postgres@postgres=# SELECT usename,useconfig FROM pg_shadow WHERE usename IN ('student1','student2');

┌──────────┬───────────┐
│ usename  │ useconfig │
├──────────┼───────────┤
│ student1 │           │
│ student2 │           │
└──────────┴───────────┘
(2 rows)

Создайте роль audit для ведения журнала и настройте параметр pgaudit.role с указанием этой роли. Настройки pgaudit.role могут быть изменены только суперпользователем:

postgres@postgres=# CREATE ROLE audit;

CREATE ROLE

postgres@postgres=# ALTER SYSTEM SET pgaudit.role = 'audit';

ALTER SYSTEM

postgres@postgres=# SHOW pgaudit.role;

┌──────────────┐
│ pgaudit.role │
├──────────────┤
│              │
└──────────────┘
(1 row)

При изменении параметра pgaudit.role в конфигурационном файле либо назначения командой ALTER SYSTEM SET pgaudit.role... необходимо перечитать конфигурационные файлы командой SELECT pg_reload_conf(), так как изменяются глобальные значения параметров конфигурации сервера.

postgres@postgres=# SELECT pg_reload_conf();

┌────────────────┐
│ pg_reload_conf │
├────────────────┤
│ t              │
└────────────────┘
(1 row)

postgres@postgres=# SHOW pgaudit.role;

┌──────────────┐
│ pgaudit.role │
├──────────────┤
│ audit        │
└──────────────┘
(1 row)

Создайте таблицу test2:

postgres@postgres=# CREATE TABLE test2 (p int);

CREATE TABLE

Далее необходимо назначить права на таблицу test2 и схему public пользователю, указанному в параметре pgaudit.role

postgres@postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC TO student1;

GRANT

postgres@postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC TO student2;

GRANT

postgres@postgres=# \dp+

                                  Access privileges
┌────────┬───────┬───────┬───────────────────────────┬───────────────────┬──────────┐
│ Schema │ Name  │ Type  │     Access privileges     │ Column privileges │ Policies │
├────────┼───────┼───────┼───────────────────────────┼───────────────────┼──────────┤
│ public │ test1 │ table │ postgres=arwdDxt/postgres↵│                   │          │
│        │       │       │ student1=arwdDxt/postgres↵│                   │          │
│        │       │       │ student2=arwdDxt/postgres │                   │          │
│ public │ test2 │ table │ postgres=arwdDxt/postgres↵│                   │          │
│        │       │       │ student1=arwdDxt/postgres↵│                   │          │
│        │       │       │ student2=arwdDxt/postgres │                   │          │
└────────┴───────┴───────┴───────────────────────────┴───────────────────┴──────────┘
(2 rows)

postgres@postgres=# \dg+

                                                      List of roles
┌────────────────┬────────────────────────────────────────────────────────────┬───────────────────────────┬─────────────┐
│   Role name    │                         Attributes                         │         Member of         │ Description │
├────────────────┼────────────────────────────────────────────────────────────┼───────────────────────────┼─────────────┤
│ audit          │ Cannot login                                               │ {}                        │             │
│ postgres       │ Superuser, Create role, Create DB, Replication, Bypass RLS │ {}                        │             │
│ student1       │                                                            │ {}                        │             │
│ student2       │                                                            │ {}                        │             │
└────────────────┴────────────────────────────────────────────────────────────┴───────────────────────────┴─────────────┘

Следует отметить, что в случае назначения некоторых прав над объектом требуются и другие права, например, для команд UPDATE, DELETE требуется право на SELECT. То есть, если выдать права SELECT над объектом аудитору, при этом не указывая отдельно права UPDATE и DELETE, то при выполнении операций обновления или удаления они также будут отражаться в логе журнала, так как сначала нужно обратиться к тем данным, которые необходимо обновить или удалить.

Включите аудит операций SELECT, INSERT, UPDATE над таблицей test2:
```
postgres@postgres=# GRANT SELECT,INSERT,UPDATE ON TABLE public.test2 to audit;
```
```
GRANT
```

Последовательно выполните операции SELECT, INSERT над таблицами test1, test2 из-под пользователя student1:

postgres@postgres=# \c postgres student1

You are now connected to database "postgres" as user "student1".

student1@postgres=# SELECT * FROM public.test1;

┌───┐
│ p │
├───┤
│ 1 │
│ 2 │
│ 3 │
└───┘
(3 rows)

student1@postgres=# SELECT * FROM public.test2;

┌───┐
│ p │
├───┤
└───┘
(0 rows)

student1@postgres=# INSERT INTO public.test1 values(4);

INSERT 0 1

student1@postgres=# INSERT INTO public.test2 values(1);

INSERT 0 1

Последовательно выполните операции SELECT, INSERT над таблицами test1, test2 из-под пользователя student2:

student1@postgres=# \c postgres student2

You are now connected to database "postgres" as user "student2".

student2@postgres=# SELECT * FROM public.test1;

┌───┐
│ p │
├───┤
│ 1 │
│ 2 │
│ 3 │
│ 4 │
└───┘
(4 rows)

srtudent2@postgres=# SELECT * FROM public.test2;

┌───┐
│ p │
├───┤
│ 1 │
└───┘
(1 row)

student2@postgres=# INSERT INTO public.test1 values(5);

INSERT 0 1

student2@postgres=# INSERT INTO public.test2 values(2);

INSERT 0 1

В журнале аудита обнаруживаются записи AUDIT только для таблицы test2, так как только для нее был включен аудит:

[postgres@ServerName audit]$ less audit-2025-08-22_141535.log

...
2025-08-22 13:58:42.582 MSK 68a84d53.68e psql student1 postgres [local] 1678LOG:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.test2,SELECT * FROM test2;,<not logged>
2025-08-22 13:59:15.559 MSK 68a84d53.68e psql student1 postgres [local] 1678LOG:  AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.test2,INSERT INTO test2 values(1);,<not logged>
2025-08-22 13:59:55.879 MSK 68a84d9e.693 psql student2 postgres [local] 1683LOG:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.test2,SELECT * FROM test2;,<not logged>
2025-08-22 14:00:11.256 MSK 68a84d9e.693 psql student2 postgres [local] 1683LOG:  AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.test2,INSERT INTO test2 values(2);,<not logged>
...

Обратите внимание, что операция DROP доступна только собственнику этой таблицы:

student1@postgres=# DROP TABLE public.test1;

ERROR:  must be owner of table test1

student1@postgres=# \c - student2

You are now connected to database "postgres" as user "student2".

student2@postgres=# DROP TABLE public.test2;

ERROR:  must be owner of table test2

Переключитесь на пользователя postgres:

student2@postgres=# \c - postgres

You are now connected to database "postgres" as user "postgres".

Пользователь postgres является собственником таблицы. Удалите таблицу test1:

postgres@postgres=# DROP TABLE public.test1;

DROP TABLE

Изучите журнал аудита:

[postgres@ServerName audit]$ less audit-2025-08-22_141534.log

...
2025-08-22 13:58:42.582 MSK 68a84d53.68e psql student1 postgres [local] 1678LOG:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.test2,SELECT * FROM test2;,<not logged>
2025-08-22 13:59:15.559 MSK 68a84d53.68e psql student1 postgres [local] 1678LOG:  AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.test2,INSERT INTO test2 values(1);,<not logged>
2025-08-22 13:59:55.879 MSK 68a84d9e.693 psql student2 postgres [local] 1683LOG:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.test2,SELECT * FROM test2;,<not logged>
2025-08-22 14:00:11.256 MSK 68a84d9e.693 psql student2 postgres [local] 1683LOG:  AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.test2,INSERT INTO test2 values(2);,<not logged>
...

Записи об удалении в таблице не появилось. Для журналирования о командах DDL необходимо в конфигурационном файле указать pgaudit.log='DDL'. Полный список классов операторов, событий, функций и команд, которые можно регистрировать в события журнала аудита, указан в документации.

В конфигурационном файле установите параметр pgaudit.log='DDL' и перезагрузите сервер.

Удалите таблицу test2:

postgres@postgres=# DROP TABLE public.test2;

DROP TABLE

postgres@postgres=# \q

Изучите журнал аудита – отображена операция DROP:

2025-08-22 14:25:15.972 MSK,"postgres","postgres",1343663,"[local]",69df8f29.1480af,1,"DROP TABLE",2025-08-22 14:25:07 MSK,5/3,884,LOG,00000,"AUDIT: SESSION,1,1,DDL,DROP TABLE,TABLE,public.test2,drop TABLE public.test2;,<not logged>,DCEF26CBD7BB436BA0998AA157E6AC54,HIGH",,,,,,,,,"psql","client backend",,0

Для отключения аудита над объектом необходимо отозвать права командой REVOKE ALL ON ... FROM ....

Подведем итоги

Вопрос 1

Какой параметр определяет роль, которая будет использоваться для ведения журнала аудита действий над конкретным объектом?

pgaudit.rulepgaudit.logpgaudit.rolepgaudit.useconfig

Вопрос 2

Выберете верное утверждение:

Настраивать аудит действий над объектом могут только суперпользователь и владелец объектаПри изменении настройки роли для ведения журнала аудита над объектом требуется перечитывание конфигурацииПосле перечитывания конфигурации в рамках изменения параметра роли для ведения журнала аудита над объектом требуется также перезагрузка сервераДля отключения аудита над объектом необходимо воспользоваться командой: ALTER ROLE role_name RESET pgaudit.role;

Подведем итоги​

Вопрос 1

Вопрос 2

Подведем итоги