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. 

PostgreSQL Database Consulting

Looking to innovate through PostgreSQL?