How to Audit a PostgreSQL Database

Auditing is a good way to keep your data as safe as possible, and to know what is happening in your databases. It is also required for many security regulations or standards, like PCI – Payment Card Industry. This is not an exception for your PostgreSQL database.

PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions.

Being that said, it should be an option to audit a PostgreSQL database, right? Well, the answer is yes. In this blog, we will see what the pgAudit extension is and how to install and use it in your PostgreSQL database.

What is pgAudit?

The PostgreSQL Audit Extension (pgAudit) provides detailed session and object audit logging via the standard PostgreSQL logging facility.

Basic statement logging can be provided by the standard logging facility with log_statement = all. This is acceptable for monitoring and other basic usages but does not provide the level of detail generally required for auditing. It is not enough to have a list of all the operations performed against the database. It must also be possible to find particular statements that are of interest to an auditor. The standard logging facility shows what the user requested, while pgAudit focuses on the details of what happened while the database was satisfying the request.

How to Install pgAudit on PostgreSQL

For this example, we will use a CentOS 7 installation. At this point, we supposed you have your PostgreSQL database installed, otherwise, you can follow this blog post to have it up and running in an easy way using ClusterControl.

Now, you should have the PostgreSQL repository in your OS, something like this:

$ cat /etc/yum.repos.d/postgresql.repo

# PGDG Red Hat Enterprise Linux / CentOS stable common repository for all PostgreSQL versions

[pgdg-common]

name=PostgreSQL common for RHEL/CentOS $releasever - $basearch

baseurl=http://download.postgresql.org/pub/repos/yum/common/redhat/rhel-$releasever-$basearch

enabled=1

gpgcheck=0

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

# PGDG Red Hat Enterprise Linux / CentOS stable repositories:

[pgdg]

name=PostgreSQL 12 $releasever - $basearch

#baseurl=http://yum.postgresql.org/12/redhat/rhel-$releasever-$basearch

baseurl=http://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch/

enabled=1

gpgcheck=0

[pgdg-source]

name=PostgreSQL 12 $releasever - $basearch - Source

baseurl=http://yum.postgresql.org/srpms/12/redhat/rhel-$releasever-$basearch

enabled=0

gpgcheck=0

If you check the pgaudit packages available, you should have:

pgaudit14_12.x86_64 : PostgreSQL Audit Extension

So, let’s install it:

$ yum install pgaudit14_12

Now, you will need to add it in the postgresql.conf configuration file, located  by default in /var/lib/pgsql/12/data/postgresql.conf, and restart the PostgreSQL service to apply the change.

shared_preload_libraries = 'pgaudit, pg_stat_statements'

After restarting your database service, you need to create the extension:

postgres=# CREATE EXTENSION pgaudit;

CREATE EXTENSION

And now, you can run the following query to check the new extension created:

postgres=# SELECT * FROM pg_available_extensions WHERE name LIKE '%audit%';

  name   | default_version | installed_version |             comment

---------+-----------------+-------------------+---------------------------------

 pgaudit | 1.4.1           | 1.4.1             | provides auditing functionality

(1 row)

pgAudit Configuration

You can verify the current configuration by running the following query:

postgres=# SELECT name,setting FROM pg_settings WHERE name LIKE 'pgaudit%';

            name            | setting

----------------------------+---------

 pgaudit.log                | none

 pgaudit.log_catalog        | on

 pgaudit.log_client         | off

 pgaudit.log_level          | log

 pgaudit.log_parameter      | off

 pgaudit.log_relation       | off

 pgaudit.log_statement_once | off

 pgaudit.role               |

(8 rows)

Let’s see these parameters one by one.

• pgaudit.log: Specifies which classes of statements will be logged by session audit logging. The default is none. Possible values are:
  • 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.
• pgaudit.log_catalog: Specifies that session logging should be enabled in the case where all relations in a statement are in pg_catalog. Disabling this setting will reduce noise in the log from tools like psql and PgAdmin that query the catalog heavily. The default is on.
• pgaudit.log_client: Specifies whether log messages will be visible to a client process such as psql. This setting should generally be left disabled but may be useful for debugging or other purposes. The default is off.
• pgaudit.log_level: Specifies the log level that will be used for log entries. This setting is used for regression testing and may also be useful to end users for testing or other purposes. The default is log.
• pgaudit.log_parameter: Specifies that audit logging should include the parameters that were passed with the statement. When parameters are present they will be included in CSV format after the statement text. The default is off.
• pgaudit.log_relation: Specifies whether session audit logging should create a separate log entry for each relation (TABLE, VIEW, etc.) referenced in a SELECT or DML statement. This is a useful shortcut for exhaustive logging without using object audit logging. The default is off.
• pgaudit.log_statement_once: Specifies whether logging will include the statement text and parameters with the first log entry for a statement/substatement combination or with every entry. Disabling this setting will result in less verbose logging but may make it more difficult to determine the statement that generated a log entry, though the statement/substatement pair along with the process id should suffice to identify the statement text logged with a previous entry. The default is off.
• pgaudit.role: Specifies the master role to use for object audit logging. Multiple audit roles can be defined by granting them to the master role. This allows multiple groups to be in charge of different aspects of audit logging. There is no default.

pgAudit Usage

Now we have reviewed the configuration parameters, let’s see an example of how to use it in the real world.

To audit all the reads, writes, and DDL queries, run:

test1=# set pgaudit.log = 'read,write,ddl';

SET

And then, run the following sentences:

test1=# CREATE TABLE table1 (id int, name text);

CREATE TABLE

test1=# INSERT INTO table1 (id, name) values (1, 'name1');

INSERT 0 1

test1=# INSERT INTO table1 (id, name) values (2, 'name2');

INSERT 0 1

test1=# INSERT INTO table1 (id, name) values (3, 'name3');

INSERT 0 1

test1=# SELECT * FROM table1;

 id | name

----+-------

  1 | name1

  2 | name2

  3 | name3

(3 rows)

If you check the PostgreSQL log file, you will see this:

2020-11-20 19:17:13.848 UTC [25142] LOG:  AUDIT: SESSION,3,1,DDL,CREATE TABLE,,,"CREATE TABLE table1 (id int, name text);",

2020-11-20 19:18:45.334 UTC [25142] LOG:  AUDIT: SESSION,4,1,WRITE,INSERT,,,"INSERT INTO table1 (id, name) values (1, 'name1');",

2020-11-20 19:18:52.332 UTC [25142] LOG:  AUDIT: SESSION,5,1,WRITE,INSERT,,,"INSERT INTO table1 (id, name) values (2, 'name2');",

2020-11-20 19:18:58.103 UTC [25142] LOG:  AUDIT: SESSION,6,1,WRITE,INSERT,,,"INSERT INTO table1 (id, name) values (3, 'name3');",

2020-11-20 19:19:07.261 UTC [25142] LOG:  AUDIT: SESSION,7,1,READ,SELECT,,,SELECT * FROM table1;,

Of course, this is a basic example. You can use the configuration parameters described in the previous section to fit your business.

Enabling pgAudit with ClusterControl

Instead of installing and enabling pgAudit manually, another option is to use the ClusterControl CLI to do the job for you. For this, you can run the following command from your ClusterControl server:

$ s9s cluster --setup-audit-logging --cluster-id=ID

Where ID is the PostgreSQL cluster id.

While it is running, you can monitor the status by checking the ClusterControl job. First, you will need the job id, that you can get from the job list:

$ s9s job --list

163  18 RUNNING  test_dba                     admins 19:41:45            90% Setup Audit Logging

Now, check the job details:

$ s9s job --log --job-id=163

Using SSH credentials from cluster.

Cluster ID is 18.

The username is 'root'.

10.10.10.129:5432: Configuring audit logging.

10.10.10.129:5432: Installing 'pgaudit14_12'.

10.10.10.129:5432: Setting pgaudit.log to ROLE,DDL,MISC.

Writing file '10.10.10.129:/var/lib/pgsql/12/data/postgresql.conf'.

10.10.10.129:5432: Restarting PostgreSQL node.

10.10.10.129: waiting for server to shut down.... done

server stopped

waiting for server to start....2020-11-20 19:41:52.069 UTC [25137] LOG:  pgaudit extension initialized

2020-11-20 19:41:52.069 UTC [25137] LOG:  starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

2020-11-20 19:41:52.069 UTC [25137] LOG:  listening on IPv4 address "0.0.0.0", port 5432

2020-11-20 19:41:52.069 UTC [25137] LOG:  listening on IPv6 address "::", port 5432

2020-11-20 19:41:52.080 UTC [25137] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

2020-11-20 19:41:52.102 UTC [25137] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"

2020-11-20 19:41:52.130 UTC [25137] LOG:  redirecting log output to logging collector process

2020-11-20 19:41:52.130 UTC [25137] HINT:  Future log output will appear in directory "log".

 done

server started

10.10.10.129:5432: Waiting for node to be accessible.

10.10.10.129:5432: pgaudit 1.4.1 is enabled.

This action will require a database service restart that will be performed by ClusterControl in the same task. After restarting it, the pgAudit extension is enabled and ready to use:

postgres=# SELECT * FROM pg_available_extensions WHERE name LIKE '%audit%';

  name   | default_version | installed_version |             comment

---------+-----------------+-------------------+---------------------------------

 pgaudit | 1.4.1           | 1.4.1             | provides auditing functionality

(1 row)

That’s it! You can now configure and use pgAudit in the same way that we showed previously.

Conclusion

Auditing is required for many security regulations and it is also useful if you want to know what happened in your database, and when and who was responsible for that.

In this blog, we talked about the pgAudit PostgreSQL extension as a good way to audit your PostgreSQL databases, and we also showed you how to implement it both manually and using the ClusterControl CLI.

Keep in mind that, depending on the configuration, it is possible for pgAudit to generate an enormous volume of data. So, you should be careful to determine what you need to audit and for how long.