12 Steps to Enabling Audit in PostgreSQL

What is a PostgreSQL Database?

PostgreSQL is an advanced, enterprise-class open-source relational database that supports both SQL (relational) and JSON (non-relational) querying.

It is a highly stable database management system, backed by more than 20 years of community development. This passionate community has contributed to Postgres’ high level of resilience, integrity, and correctness.

What is Auditing?

Database auditing involves observing a database so as to be aware of the actions of database users.

Database administrators (DBAs) and consultants often set up auditing for security purposes. For example, DBAs can conduct an audit and ensure that unauthorized users don’t have access to restricted information outside their permissions.

What is a PostgreSQL Audit?

The PostgreSQL Audit Extension (or pgaudit) provides detailed session and/or object audit logging via the standard logging facility provided by PostgreSQL.

The goal of a PostgreSQL audit is to provide the tools needed to produce audit logs. These logs are often required to pass certain government, financial, or ISO certification audits.

Extension in PostgreSQL

Extensions are modules that supply extra functions, operators, or types. Many extensions are available in Compose PostgreSQL.

How Do You Enable Auditing in PostgreSQL?

Follow these 12 steps below to enable auditing in Postgres. The operating system in the example below is CentOS Linux release 7.9.2009 (Core).

1. Verify the installed postgres packages. 

[root@mysql ~]# rpm -qa | grep postgres
postgresql13-server-13.7-1PGDG.rhel7.x86_64
postgresql13-libs-13.7-1PGDG.rhel7.x86_64
postgresql13-13.7-1PGDG.rhel7.x86_64

2. Verify pgaudit package using yum.

[root@mysql ~]# yum list pgaudit*
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirrors.isu.net.sa
 * epel: mirror.earthlink.iq
 * extras: mirrors.isu.net.sa
 * updates: mirrors.isu.net.sa
Available Packages
pgaudit12_10.x86_64                                                                1.2.4-1.rhel7                                                   pgdg10
pgaudit13_11.x86_64                                                                1.3.4-1.rhel7                                                   pgdg11
pgaudit14_12.x86_64                                                                1.4.3-1.rhel7                                                   pgdg12
pgaudit15_13.x86_64                                                                1.5.2-1.rhel7                                                   pgdg13
pgaudit16_14.x86_64                                                                1.6.2-1.rhel7                                                   pgdg14
pgaudit_analyze.x86_64                                                             1.0.7-1.rhel7                                                   

3. Install pgaudit package based on the compatibility of  PostgreSQL database version 13. 

[root@mysql pgsql]# yum install pgaudit15*
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirrors.isu.net.sa
 * epel: mirror.earthlink.iq
 * extras: mirrors.isu.net.sa
 * updates: mirrors.isu.net.sa
Resolving Dependencies
--> Running transaction check
---> Package pgaudit15_13.x86_64 0:1.5.2-1.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================
 Package                                 Arch                              Version                                    Repository                         Size
==============================================================================================================================================================
Installing:
 pgaudit15_13                            x86_64                            1.5.2-1.rhel7                              pgdg13                             47 k

Transaction Summary
==============================================================================================================================================================
Install  1 Package

Total download size: 47 k
Installed size: 91 k
Is this ok [y/d/N]: y
Downloading packages:
pgaudit15_13-1.5.2-1.rhel7.x86_64.rpm                                                                                                  |  47 kB  00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pgaudit15_13-1.5.2-1.rhel7.x86_64                                                                                                          1/1
  Verifying  : pgaudit15_13-1.5.2-1.rhel7.x86_64                                                                                                          1/1

Installed:
  pgaudit15_13.x86_64 0:1.5.2-1.rhel7

Complete!

4. Add pgaudit into shared_preload_libraries.

[postgres@mysql ~]$ cat /var/lib/pgsql/13/data/postgresql.conf | grep shared_preload_libraries
shared_preload_libraries = 'pgaudit'    # (change requires restart)

5. Restart PostgreSQL Services. 

[root@mysql pgsql]# systemctl restart postgresql-13.service

6.  Create the pgaudit extension.

postgres=# create extension pgaudit;
CREATE EXTENSION
postgres=# \dx
                   List of installed extensions
  Name   | Version |   Schema   |           Description
---------+---------+------------+---------------------------------
 pgaudit | 1.5.2   | public     | provides auditing functionality
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

7. Verify the pgaudit parameters.

postgres=# show pgaudit.
pgaudit.log                 pgaudit.log_client          pgaudit.log_parameter       pgaudit.log_statement_once
pgaudit.log_catalog         pgaudit.log_level           pgaudit.log_relation        pgaudit.role

postgres=# show pgaudit.log;
 pgaudit.log
-------------
 none
(1 row)

8. As you see, the by-default value of log is none. You can set pgaudit.log using the below parameters:

• READ: SELECT and COPY when the source is a relation or a query.
• WRITE: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.
• FUNCTION: Function calls and DO blocks.
• ROLE: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
• DDL: All DDL that is not included in the ROLE class.
• MISC: Miscellaneous commands, e.g., DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
• MISC_SET: Miscellaneous SET commands, e.g., SET ROLE.
• ALL: Include all of the above.

9. Let’s set the value to audit READ and WRITE operations.

postgres=# alter system set pgaudit.log to read,write;
ALTER SYSTEM

postgres=# show pgaudit.log;
 pgaudit.log
-------------
 none
(1 row)

Note: As you see parameter value is not dynamic. So PostgreSQL services restart is required. 

10. Restart PostgreSQL Services

[root@mysql pgsql]# systemctl restart postgresql-13.service
[root@mysql pgsql]# su - postgres
Last login: Thu May 25 22:34:57 IST 2023 on pts/0
[postgres@mysql ~]$ psql
psql (13.7)
Type "help" for help.

postgres=# show pgaudit.log;
 pgaudit.log
-------------
 read, write
(1 row)

11. Let’s create some data to test the audit case for read and write.

postgres=# create table pythian (id int);
CREATE TABLE
postgres=# insert into pythian values (1);
INSERT 0 1
postgres=# table pythian;
 id
----
  1
(1 row)

12. Verify the logs.

[postgres@mysql log]$ tail -25f postgresql-Thu.log

2023-05-25 22:38:52.140 IST [3135] STATEMENT:  create table pythian (id number);
2023-05-25 22:39:01.460 IST [3135] LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,"SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'f', 'v', 'p') AND substring(pg_catalog.quote_ident(c.relname),1,2)='ed' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')
        UNION
        SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,2)='ed' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,2) = substring('ed',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
        UNION
        SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'f', 'v', 'p') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,2)='ed' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,2) = substring('ed',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,2) = substring('ed',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
        LIMIT 1000",<not logged>
2023-05-25 22:39:06.403 IST [3135] LOG:  AUDIT: SESSION,2,1,WRITE,INSERT,,,insert into pythian values (1);,<not logged>
2023-05-25 22:39:11.685 IST [3135] LOG:  AUDIT: SESSION,3,1,READ,SELECT,,,"SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(c.relname),1,2)='ed' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')
        UNION
        SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,2)='ed' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,2) = substring('ed',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
        UNION
        SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,2)='ed' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,2) = substring('ed',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,2) = substring('ed',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
        LIMIT 1000",<not logged>
2023-05-25 22:39:13.046 IST [3135] LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,table pythian;,<not logged>

You’ve now enabled auditing in PostgreSQL in just 12 easy steps.

Conclusion

The overall premise of database auditing is to track the use of database records and the permissions of those who have access to them.

It’s possible to keep track of every action in a database, including which database object or data record was accessed, who did the action, and when it occurred.