Audit log - PostgreSQL setup

This page shows how to prepare a PostgreSQL server for the audit log. PostgreSQL 13 and later is supported.

Creating the database and the role

As an administrative user, create a login role named zato_audit_log with a password, and a UTF8 database of the same name owned by that role. The audit log creates its table and indexes the first time a server connects.

Now, point the servers at the database:

export Zato_Audit_Log_DB_Type=postgresql
export Zato_Audit_Log_DB_Host=db.example.com
export Zato_Audit_Log_DB_Port=5432
export Zato_Audit_Log_DB_Username=zato_audit_log
export Zato_Audit_Log_DB_Password=my.secret.password
export Zato_Audit_Log_DB_Name=zato_audit_log

Enabling SSL/TLS on the server

Point PostgreSQL at its certificate files in postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'

The key file must belong to the user PostgreSQL runs as and must not be readable by anyone else:

chown postgres:postgres /var/lib/postgresql/data/server.key
chmod 600 /var/lib/postgresql/data/server.key

To refuse all unencrypted TCP connections, use hostssl instead of host in pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS     METHOD
hostssl zato_audit_log  zato_audit_log  0.0.0.0/0   scram-sha-256

To additionally require a client certificate from the audit log role (mutual TLS):

hostssl zato_audit_log  zato_audit_log  0.0.0.0/0   scram-sha-256  clientcert=verify-ca

Reload the PostgreSQL configuration afterwards.

With the server prepared, enable TLS on the client side:

export Zato_Audit_Log_DB_SSL=on
export Zato_Audit_Log_DB_SSL_CA_File=/path/to/ca.crt

And, when clientcert is in use:

export Zato_Audit_Log_DB_SSL_Cert_File=/path/to/client.crt
export Zato_Audit_Log_DB_SSL_Key_File=/path/to/client.key

All the client-side variables are described in the audit log documentation.

Learn more