Расширение pgpro_usage включено в состав Postgres Pro Enterprise как стандартное расширение. Чтобы задействовать pgpro_usage, выполните следующие действия:

Чтобы корректно удалить расширение pgpro_usage, выполните следующие действия:

Чтобы собирать статистику доступа отношений и вызовов функций в разрезе пользователей, pgpro_usage предоставляет SQL-интерфейс, состоящий из нескольких функций и представлений.

Этот пример демонстрирует использование расширения pgpro_usage.

Сначала создадим расширение:

postgres=# create extension pgpro_usage;
CREATE EXTENSION

Теперь просмотрим статистику использования. Для этого создадим пользователя, таблицу и процедуру, после чего выдадим пользователю доступ к таблице:

postgres=# CREATE USER u1;
CREATE ROLE
postgres=# CREATE TABLE t1(val int);
CREATE TABLE
postgres=# GRANT all on t1 to u1;
GRANT
postgres=# CREATE PROCEDURE p1() AS $$ BEGIN RAISE NOTICE 'Here we are'; END; $$ LANGUAGE plpgsql;
CREATE PROCEDURE

Выведем статистику использования всех таблиц:

postgres=# SELECT * FROM pg_stat_all_tables_per_user;
 userid | username | relid | nspname | relname | n_reads | n_inserts | n_updates | n_deletes | n_truncates | n_grants_given
--------+----------+-------+---------+---------+---------+-----------+-----------+-----------+-------------+----------------
     10 | postgres | 16445 | public  | t1      |       0 |         0 |         0 |         0 |           0 |              1
(1 row)

Сделаем то же самое для всех функций:

postgres=# SELECT * FROM pg_stat_all_functions_per_user;
 userid | username | funcid | nspname |                funcname                | calls | grants_given
--------+----------+--------+---------+----------------------------------------+-------+--------------
     10 | postgres | 16385  | public  | pgpro_usage_reset                      |     0 |            1
     10 | postgres | 16386  | public  | pgpro_usage                            |     0 |            1
     10 | postgres | 16387  | public  | pgpro_usage_full                       |     0 |            1
     10 | postgres | 16397  | public  | pg_stat_get_func_calls_per_user        |     2 |            0
     10 | postgres | 16398  | public  | pg_stat_get_func_grants_given_per_user |     1 |            0
     10 | postgres | 16399  | public  | pg_stat_get_read_req_per_user          |     1 |            0
     10 | postgres | 16400  | public  | pg_stat_get_insert_req_per_user        |     1 |            0
     10 | postgres | 16401  | public  | pg_stat_get_update_req_per_user        |     1 |            0
     10 | postgres | 16402  | public  | pg_stat_get_delete_req_per_user        |     1 |            0
     10 | postgres | 16403  | public  | pg_stat_get_truncate_req_per_user      |     1 |            0
     10 | postgres | 16404  | public  | pg_stat_get_grants_given_per_user      |     1 |            0
(11 rows)

Приведённый выше вывод содержит только действия суперпользователя, который выдал доступ к таблице и несколько раз вызвал функции расширения при отправке запросов к приведённым выше представлениям. Столбец grants_given ненулевой в трёх строках. Они соответствуют отзыву прав у public при создании расширения. Созданный пользователь не включён в статистику, так как он не выполнял никаких действий.

Восполним этот пробел и подключимся к базе данных как пользователь u1.

postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> insert into t1(val) values (1);
INSERT 0 1
postgres=> insert into t1(val) values (2);
INSERT 0 1
postgres=> select * from t1;
 val
-----
   1
   2
(2 rows)
postgres=> call p1();
NOTICE:  Here we are
CALL

Мы выполнили две вставки в таблицу, отправили к ней один запрос и один раз вызвали процедуру. Переключимся обратно на суперпользователя и снова просмотрим статистику:

postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# SELECT * FROM pg_stat_all_tables_per_user;
 userid | username | relid | nspname | relname | n_reads | n_inserts | n_updates | n_deletes | n_truncates | n_grants_given
--------+----------+-------+---------+---------+---------+-----------+-----------+-----------+-------------+----------------
  16444 | u1       | 16445 | public  | t1      |       1 |         2 |         0 |         0 |           0 |              0
     10 | postgres | 16445 | public  | t1      |       0 |         0 |         0 |         0 |           0 |              1
(2 rows)
postgres=# SELECT * FROM pg_stat_all_functions_per_user;
 userid | username | funcid | nspname |                funcname                | calls | grants_given
--------+----------+--------+---------+----------------------------------------+-------+--------------
     10 | postgres | 16385  | public  | pgpro_usage_reset                      |     0 |            1
     10 | postgres | 16386  | public  | pgpro_usage                            |     0 |            1
     10 | postgres | 16387  | public  | pgpro_usage_full                       |     0 |            1
     10 | postgres | 16397  | public  | pg_stat_get_func_calls_per_user        |     4 |            0
     10 | postgres | 16398  | public  | pg_stat_get_func_grants_given_per_user |     2 |            0
     10 | postgres | 16399  | public  | pg_stat_get_read_req_per_user          |     2 |            0
     10 | postgres | 16400  | public  | pg_stat_get_insert_req_per_user        |     2 |            0
     10 | postgres | 16401  | public  | pg_stat_get_update_req_per_user        |     2 |            0
     10 | postgres | 16402  | public  | pg_stat_get_delete_req_per_user        |     2 |            0
     10 | postgres | 16403  | public  | pg_stat_get_truncate_req_per_user      |     2 |            0
     10 | postgres | 16404  | public  | pg_stat_get_grants_given_per_user      |     2 |            0
  16444 | u1       | 16448  | public  | p1                                     |     1 |            0
(12 rows)

Обе таблицы теперь содержат строки, соответствующие операциям пользователя u1: две вставки и одно чтение таблицы, а также одно выполнение процедуры.

Теперь просмотрим статистику последнего использования. Мы сможем увидеть временные метки, показывающие, когда пользователь выполнил вставку и чтение.

postgres=# select * from pg_stat_all_tables_last_usage;
 userid | username | nspname | relid | relname |           last_read           |         last_insert          | last_update | last_delete | last_truncate
--------+----------+---------+-------+---------+-------------------------------+------------------------------+-------------+-------------+---------------
  16444 | u1       | public  | 16445 | t1      | 2024-11-30 01:06:24.578642+03 | 2024-11-30 01:06:17.03442+03 |             |             |
(1 row)

Дополним статистику чтением от суперпользователя:

postgres=# select * from t1;
 val
-----
   1
   2
(2 rows)
postgres=# select * from pg_stat_all_tables_last_usage;
 userid | username | nspname | relid | relname |           last_read           |         last_insert          | last_update | last_delete | last_truncate
--------+----------+---------+-------+---------+-------------------------------+------------------------------+-------------+-------------+---------------
     10 | postgres | public  | 16445 | t1      | 2024-11-30 01:09:29.994188+03 |                              |             |             |
  16444 | u1       | public  | 16445 | t1      | 2024-11-30 01:06:24.578642+03 | 2024-11-30 01:06:17.03442+03 |             |             |
(2 rows)

Убедимся, что временная метка last_read обновилась:

postgres=# select * from t1;
 val
-----
   1
   2
(2 rows)
postgres=# select * from pg_stat_all_tables_last_usage;
 userid | username | nspname | relid | relname |           last_read           |         last_insert          | last_update | last_delete | last_truncate
--------+----------+---------+-------+---------+-------------------------------+------------------------------+-------------+-------------+---------------
     10 | postgres | public  | 16445 | t1      | 2024-11-30 01:10:28.122489+03 |                              |             |             |
  16444 | u1       | public  | 16445 | t1      | 2024-11-30 01:06:24.578642+03 | 2024-11-30 01:06:17.03442+03 |             |             |
(2 rows)

Просмотрим статистику неиспользуемых прав. Объектов, пользователей и прав может существовать много, поэтому поместим всё, что относится к этому примеру, в отдельную схему. Так будет проще фильтровать результаты. Создаём схему и таблицу внутри неё, выдаём доступ к таблице определённой групповой роли и выдаём членство в этой групповой роли отдельному пользователю:

postgres=# create schema shops;
CREATE SCHEMA
postgres=# create table shops.buildings();
CREATE TABLE
postgres=# create role manager;
CREATE ROLE
postgres=# grant all on schema shops to manager;
GRANT
postgres=# grant all on shops.buildings to manager;
GRANT
postgres=# create user vasya;
CREATE ROLE
postgres=# grant manager to vasya;
GRANT ROLE

Подключимся к базе данных от имени этого пользователя и выполним запрос к таблице:

postgres=# \c - vasya
You are now connected to database "postgres" as user "vasya".
postgres=> select * from shops.buildings;
--
(0 rows)

Теперь переключимся на суперпользователя и просмотрим статистику прав:

postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select * from pgpro_usage_stats_privileges_usage where usename='vasya' and nspname='shops';
 usename |  access  | nspname | targetname | provider | grantee |   kind   | used
---------+----------+---------+------------+----------+---------+----------+------
 vasya   | INSERT   | shops   | buildings  | manager  | manager | RELATION | f
 vasya   | SELECT   | shops   | buildings  | manager  | manager | RELATION | t
 vasya   | UPDATE   | shops   | buildings  | manager  | manager | RELATION | f
 vasya   | DELETE   | shops   | buildings  | manager  | manager | RELATION | f
 vasya   | TRUNCATE | shops   | buildings  | manager  | manager | RELATION | f
(5 rows)

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