How to configure PostgreSQL audit logging

Audit-focused server logs in PostgreSQL provide a durable trail for investigating suspicious logins, tracing administrative activity, and explaining when a change occurred. Consistent logging is also a practical compliance control when application-layer auditing is incomplete.

PostgreSQL writes server messages through its logging subsystem, with behavior controlled by parameters in postgresql.conf. Connection and disconnection events, authentication failures, and (optionally) statement text can be recorded, while log_line_prefix formats each line with identifiers like user, database, session, and client address to support correlation.

Statement logging can generate significant volume and may capture sensitive literals embedded in SQL, so scope and retention must be planned before increasing verbosity. On Ubuntu packaged installs, logs are commonly written under /var/log/postgresql and rotated by logrotate, while the parameters in this procedure are reloadable without a full restart.

1. Print the active postgresql.conf path from the running server.

   $ sudo -u postgres psql -Atc "SHOW config_file;"
   /etc/postgresql/16/main/postgresql.conf

2. Enable connection and disconnection logging.

   $ sudo -u postgres psql -c "ALTER SYSTEM SET log_connections = 'on';"
   ALTER SYSTEM
   $ sudo -u postgres psql -c "ALTER SYSTEM SET log_disconnections = 'on';"
   ALTER SYSTEM

3. Set an audit-friendly log_line_prefix for correlation.

   $ sudo -u postgres psql -c "ALTER SYSTEM SET log_line_prefix = '%m [%p] %c %l %u@%d %r app=%a ';"
   ALTER SYSTEM

   "%c" (session ID) and app=%a (application_name) make it easier to correlate activity across log lines.

4. Use a consistent time zone for log timestamps.

   $ sudo -u postgres psql -c "ALTER SYSTEM SET log_timezone = 'UTC';"
   ALTER SYSTEM

5. Limit statement logging to schema changes for a safer baseline.

   $ sudo -u postgres psql -c "ALTER SYSTEM SET log_statement = 'ddl';"
   ALTER SYSTEM

   Logging statements can expose sensitive literals (tokens, emails, fragments of data) and can grow logs quickly on busy systems, especially with 'mod' or 'all'.

6. Ensure log files are written to the data directory when file-based logs are required.

   $ sudo -u postgres psql -c "ALTER SYSTEM SET logging_collector = 'on';"
   ALTER SYSTEM
   $ sudo -u postgres psql -c "ALTER SYSTEM SET log_directory = 'log';"
   ALTER SYSTEM
   $ sudo -u postgres psql -c "ALTER SYSTEM SET log_filename = 'postgresql-%Y-%m-%d.log';"
   ALTER SYSTEM

   logging_collector is a postmaster setting, so a restart is required before file-based logs appear.

7. Reload PostgreSQL to apply reloadable logging changes.

   $ sudo -u postgres psql -c "SELECT pg_reload_conf();"
    pg_reload_conf
   ----------------
    t
   (1 row)

   Parameters that require a restart are marked with context=postmaster in pg_settings.

   Related: How to manage the PostgreSQL service with systemctl on Linux

8. Confirm the active logging parameters from pg_settings.

   $ sudo -u postgres psql -P pager=off -c "SELECT name, setting FROM pg_settings WHERE name IN ('log_connections','log_disconnections','log_statement','log_line_prefix','log_timezone') ORDER BY name;"
         name       |                         setting
   -----------------+--------------------------------------------------
   log_connections   | on
   log_disconnections| on
   log_line_prefix   | %m [%p] %c %l %u@%d %r app=%a 
   log_statement     | ddl
   log_timezone      | UTC
   (5 rows)

9. Generate a short test session to produce connection and disconnection log entries.

   $ sudo -u postgres psql -d postgres -c "SELECT 1;"
    ?column?
   ----------
           1
   (1 row)

10. Produce a representative DDL entry in the server log.

    $ sudo -u postgres psql -d postgres -c "CREATE TEMP TABLE audit_demo(id integer);"
    CREATE TABLE

11. List the PostgreSQL server log files.

    $ sudo ls -1 /var/log/postgresql
    postgresql-16-main.log
    postgresql-2025-12-29.log

12. Confirm that new audit entries are present in the server log.

    $ sudo tail -n 50 /var/log/postgresql/postgresql-2025-12-29.log
    2025-12-29 08:45:23.223 UTC [5587] 69523fa3.15d3 3 postgres@postgres [local] app=[unknown] LOG:  connection authorized: user=postgres database=postgres application_name=psql
    2025-12-29 08:45:23.227 UTC [5587] 69523fa3.15d3 4 postgres@postgres [local] app=psql LOG:  disconnection: session time: 0:00:00.004 user=postgres database=postgres host=[local]
    2025-12-29 08:45:23.375 UTC [5607] 69523fa3.15e7 4 postgres@postgres [local] app=psql LOG:  statement: CREATE TEMP TABLE audit_demo(id integer);
    2025-12-29 08:45:23.378 UTC [5607] 69523fa3.15e7 5 postgres@postgres [local] app=psql LOG:  disconnection: session time: 0:00:00.003 user=postgres database=postgres host=[local]
    ##### snipped #####