The pgpro_usage extension is a built-in extension included into Postgres Pro Enterprise. To enable pgpro_usage, complete the following steps:

To properly uninstall pgpro_usage, complete the following steps:

To collect per-user statistics on accessing relations and calling functions, pgpro_usage provides an SQL interface that consists of several functions and views.

This example illustrates usage of the pgpro_usage extension.

First we create the extension:

postgres=# create extension pgpro_usage;
CREATE EXTENSION

Now let's try out the usage statistics. To do this, we create a user, table, and procedure and give the user access to the table:

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

Let's output all the usage statistics for all tables:

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)

Do so for all functions:

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)

In the above output, you can only see superuser operations, who once granted access on a table and several times called the extension functions when querying the above views. The grants_given column is non-zero in three rows. They correspond to revoking permissions from public during creation of the extension. The newly created user is not included in the statistics since he did nothing.

Let's fill this gap and connect to the database as the u1 user.

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

We did two inserts in the table, queried it once, and once called the procedure. Let's switch back to the superuser and look at the statistics again:

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)

Both tables now include rows corresponding to the operations of the u1 user: two inserts and a read of the table and one execution of the procedure.

Now let's look at the last-use statistics. We will be able to see timestamps showing when the user performed the inserts and the read.

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)

Let's extend this statistics with a read by a superuser:

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)

Let's make sure that the last_read timestamp gets updated:

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)

Now let's consider the statistics on unused privileges. Since there can be quite a lot of objects, users, and privileges, let's put everything related to this example in a separate schema to make it easier to filter the future result. We create a schema and a table in it, grant access on the table to a certain group role, and grant membership in this group role to a specific user:

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

Let's connect to the database as this user and query the table:

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

Now let's switch to the superuser and look at the statistics on privileges:

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)

These statistics show that the user vasya has full access to the shops.buildings table, and he got all the access privileges indirectly, as a memeber of the manager role. It is also clear that among the privileges, vasya only used SELECT. Note also that the statistics on privileges do not show superuser privileges: evidently, superuser has full access.