pgAudit Log Interpretation & Password Protection in PostgreSQL: Part 4 – The Palantír of Logs

Or, “What the Seeing-Stone Shows Us (and How to Avoid Showing It to Saruman)”

Welcome back, wise masters of the database. In our journey so far, we have forged our auditing tools in Part 1, learned the broad strokes of Session Auditing in Part 2, and mastered the precision of Object Auditing in Part 3. Our logs are now dutifully recording the events of our realm.

But generating logs is only half the battle. A log file is like a Palantír, a powerful seeing-stone. It can show you great and terrible things, revealing the truth of events across your entire database. But if you don’t know how to interpret what you see, or if you’re not careful, it can also show dangerous secrets to the wrong people.

Today, we learn to master the Palantír. We will learn how to read our audit logs, understand the stories they tell, and most importantly, ensure they don’t become a security risk by exposing the one secret we must never reveal: a cleartext password.

A Quick Word of Warning (The Disclaimer)

Disclaimer: This article is a technical showcase, not a substitute for professional consultancy. The configurations provided are for educational purposes. They haven’t been battle-tested in a production environment under the watchful eye of a dragon (or your friendly neighborhood compliance auditors). Use these configurations at your own risk.

How to Read and Interpret pgAudit Logs

Before we can interpret our logs, we must find them. On Debian-based systems like Ubuntu, the default location is often /var/log/postgresql/, while on Red Hat-based systems, it might be /var/lib/pgsql/<version>/data/log/. The definitive location is always determined by the log_directory and log_filename parameters in your postgresql.conf file. Once you’ve located them, you’re ready to start deciphering the messages.

The Anatomy of a pgAudit Log

Every pgAudit entry is a structured, comma-delimited line, making it perfect for both human eyes and automated parsing tools. Let’s break down its structure.

Here’s a sample OBJECT audit log from Part 3:

AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.fellowship_missions,"SELECT mission_name, objective FROM fellowship_missions WHERE objective = 'Destroy the One Ring';",<not logged>

Here’s what each field means:

1. Audit Type: OBJECT or SESSION.
2. Statement ID: A unique ID for this statement within the current session.
3. Substatement ID: A unique ID for this specific command within the statement (e.g., for functions).
4. Class: The class of the statement (e.g., READ, WRITE, DDL, ROLE).
5. Command: The specific command that was executed (e.g., SELECT, INSERT, GRANT).
6. Object Type: The type of object affected (e.g., TABLE, VIEW, ROLE).
7. Object Name: The fully-qualified name of the object (e.g., public.fellowship_missions).
8. Statement: The full text of the statement that was executed.
9. Parameter: <not logged> by default. If pgaudit.log_parameter were on, this would contain the statement parameters.

Tools for the Quest

While you can read the log files directly, you’ll often need tools to search and filter them.

• grep (The Ranger’s Tracks): For quick and dirty searches, grep is your best friend. Want to see everything the user gandalf has been up to? It’s as simple as:

  grep "gandalf@middle_earth" /var/log/postgresql/postgresql-16-main.log

• pgBadger: A fantastic open-source log analyzer that can parse and present your logs in a beautiful HTML report.
• Centralized Logging (The Council of Elrond): For serious production environments, you’ll want to ship your logs to a centralized system like the ELK Stack (Elasticsearch, Logstash, Kibana), Splunk, or Graylog. This allows you to search, visualize, and set up alerts on your audit data across your entire infrastructure.

Protecting Cleartext Passwords in Your Logs

You shall not pass(word)! This is the cardinal rule of secure logging. The Palantír can be dangerous, and its greatest peril is showing a secret that can bring down the whole kingdom.

The Great Peril

If you are auditing the ROLE class (which you should be!), a command like this is a potential disaster:

ALTER USER saruman WITH PASSWORD 'iStoleThePalantir123';

While setting log_statement = 'none' is the primary defense, be aware that other native PostgreSQL logging parameters can still betray you. For instance, if you have log_min_duration_statement set to a low value, a slow-running ALTER USER command could still be logged in its entirety, exposing the cleartext password. This reinforces the need for a defense-in-depth approach.

pgAudit is designed to be smart about this, but relying on server-side settings alone is not enough. We must be proactive.

The One True Path: Client-Side Hashing

The safest way to change a password is to never let the cleartext password touch the server’s logs in the first place. The best tool for this is psql‘s built-in \password command.

When you connect via psql and run the command, psql will prompt you for a new password. It then hashes the password on the client side and sends the hashed version to the server. The actual ALTER USER command that gets executed on the server contains the hash, not the cleartext password, making it safe to log.

\password saruman

Recommendation: Mandate that all password changes, whether by users or administrators, are done using tools that hash the password on the client-side, like \password.

The Path to Ruin (What NOT to Do)

It can be tempting to “solve” this problem by simply turning off auditing for the ROLE class. Do not do this.

Disabling ROLE auditing means you will be blind to critical security events like:

• GRANT statements giving a user new permissions.
• REVOKE statements removing permissions.
• A user being elevated to a SUPERUSER.

Losing visibility into privilege changes is a far greater security risk than the password issue, especially when the password issue is so easily solved by using the correct tools.

What’s Next?

We’ve learned to read the seeing-stone and protect its most dangerous secrets. We have a robust session and object auditing system in place. But what if we need to make exceptions? What if we need to tell our all-seeing eye to look away from certain users or activities to reduce noise?

In our (optional) Part 5, we will explore the delicate art of Exclusion & Best Practices, learning how to create selective blind spots without compromising our security posture.

• Series Overview
• Previous: pgAudit Object-Level Auditing in PostgreSQL: Part 3
• Next Up: Part 5: Exclusions & Best Practices