PostgreSQL Log Queries and Audit

There are several reasons why you might want an audit trail of users’ activity on a PostgreSQL database:

1. When things go wrong, you need to know what happened and who is responsible
2. You store sensitive data, maybe even PII or PHI
3. You are subject to compliance standards like SOC 2 or PCI DSS

Both application and human access are in-scope. Since application activity can be logged directly within the app, I’ll focus on human access: how to create an audit trail of activity for staff, consultants, and vendors.

In an ideal world, no one would access the database, and all changes would run through a deployment pipeline and be under version control. But that’s never been the case on any team I’ve been a part of. Just finding what went wrong in the code meant connecting to the PostgreSQL database to investigate.

How to log queries in PostgreSQL

I’ve tried 3 methods to track human activities:

1. local PostgreSQL logs
2. open-source proxy
3. StrongDM 🤩

Each has its pros and cons in terms of ease of setup, performance impact, and risk of exploitation. These are not dependent on users' operating systems (Unix, Windows).

Local logging approach

Native PostgreSQL logs are configurable, allowing you to set the logging level differently by role (users are roles) by setting the log_statement parameter to mod, ddl or all to capture SQL statements.

Alter role "TestUser" set log_statement="all"

After the command above you get those logs in Postgres’ main log file. For example, here’s a log entry for a table creation:

This is the first step to creating an audit trail of PostgreSQL logs. Postgres can also output logs to any log destination in CSV by modifying the configuration file -- use the directives log_destination = 'csvfile' and logging_collector = 'on', and set the pg_log directory accordingly in the Postgres config file. (The postgresql.conf file is generally located somewhere in /etc but varies by operating system.) This permits easier parsing, integration, and analysis with Logstash and Elasticsearch with a naming convention for log_filename like postgresql-%y-%m-%d_%h%m%s.log. Once you've made these changes to the config file, don't forget to restart the PostgreSQL service using pg_ctl or your system's daemon management command like systemctl or service.

Improving this approach

For some complex queries, this raw approach may get limited results. Bringing PgAudit in helps to get more details on the actions taken by the operating system and SQL statements. As a crude example let's create 10 tables with a loop like this:

With the standard logging system, this is what is logged:

And with PgAudit enabled:

The log output is obviously easier to parse as it also logs one line per execution, but keep in mind this has a cost in terms of disk size and, more importantly, disk I/O, which can quickly cause noticeable performance degradation even if you take into account the log_rotation_size and log_rotation_age directives in the config file.

Open-source Proxy logging approach

There are multiple proxies for PostgreSQL which can offload the logging from the database. Postgres' documentation has a page dedicated to replication. The most popular option is pg-pool II. I won't go into the details of setting it up as their wiki is pretty exhaustive.

The main advantage of using a proxy is moving the IO for logging out of the DB system. The downside is that it precludes getting pgAudit level log output. In order to get the results of the ddl statements it needs to log within the database server.

If your team rarely executes the kind of dynamic queries made above, then this option may be ideal for you. If you don’t mind some manual investigation, you can search for the start of the action you’re looking into.

🕵 Learn how Coveo gained complete visibility across their entire stack with centralized and granular audit logs and simplified compliance audits.

Hey, you talked about the pros and cons; where are they?

Now that I’ve given a quick introduction to these two methods, here are my thoughts:

The main metric impacting DB performance will be IO consumption, and the most interesting things you want to capture are the log details: who, what, and when.

Obviously, you’ll get more details with pgAudit on the DB server at the cost of more IO and the need to centralize the Postgres log yourself if you have more than one node.

The open-source proxy approach gets rid of the IO problem. Since its sole role is to forward the queries and send back the result, it can more easily handle the IO need to write a lot of files, but you’ll lose a little in query details in your Postgres log. On the other hand, you can log at all times without fear of slowing down the database on a high load.

For specific operations, like bug patching or external auditor access, turning on a more detailed logging system is always a good idea, so keep the option open.

As is often the case with open source software, the raw functionality is available if you have the time and expertise to dedicate to getting it running to your specifications. Configuring Postgres for SSPI or GSSAPI can be tricky, and when you add pg-pool II into the mix, the complexity increases even more.

Why StrongDM brings more

If you’re short on time and can afford to buy vs. build, StrongDM provides a control plane to manage access to every server and database type, including PostgreSQL.

StrongDM logs include:

• permission changes
• queries by user
• ssh commands by user
• RDP commands by user
• kubectl commands by user

For example, to audit permissions across every database & server, execute:

To audit queries across every database type, execute the following:

To export logs, execute the following:

sdm audit queries --from 2019-05-21 --to 2019-05-22 --json -o queries

In addition to logs, StrongDM simplifies access management by binding authentication to your SSO. To onboard or offboard staff, create or suspend a user in your SSO, and you’re done. No more credentials or SSH keys to manage.

StrongDM provides detailed and comprehensive logging, easy log export to your log aggregator or SIEM, and one-click provisioning and deprovisioning with no additional load on your databases.

To start logging PostgreSQL queries today, sign up for a free, 14-day trial.