A cartoon PostgreSQL elephant character looks at a holographic interface displaying a glowing orange Eye of Sauron, symbolizing the installation of the pgAudit extension.

Installing pgAudit in PostgreSQL Part 1: The Eye of Sauron

By evandroPostgresql, Security

Or, “The Eye of Sauron Sees All… Once You apt install It”

Welcome back, fellow travelers on the digital frontier! After our Epic Quest for Secure Passwords, you’d think we could rest. But in the world of data security, there is no rest. There is only the eternal vigilance of the All-Seeing Eye. 👁️

Today, we embark on a new quest: to implement pgAudit in PostgreSQL. Why? Because simply having a strong fortress gate (authentication) isn’t enough. You need to know what’s happening inside the walls. Who accessed the royal treasury? Who tried to rewrite history (or, you know, the employees table)?

PostgreSQL’s native log_statement setting is… a thing. It’s like strapping a GoPro to a caffeinated squirrel and hoping you capture the one moment a crime was committed. It’s noisy, unstructured, and will log everything, including things you absolutely do not want logged. pgAudit, on the other hand, is a precision instrument. It’s a dedicated, C-written extension that provides the detailed, structured, and granular audit logs you need to satisfy the Watchers on the Wall (or your friendly neighborhood compliance auditors).

This series will guide you through installing, configuring, and mastering pgAudit, turning your database logs from a chaotic mess into a pristine record of every important action. Let’s begin by forging our new ring of power.

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. Don’t come crying to me if you accidentally summon a Balrog in your data center.

Installing pgAudit: You Have My Sword… and My apt-get

So, what’s the first step to get started with pgAudit? Before we can use it, we have to install it. Like any good RPG side quest, this requires a few prerequisites. Make sure your server has the basic development tools ready to go.

Prerequisites:

  • make
  • gcc
  • The correct PostgreSQL development headers for your version (e.g., postgresql-server-dev-16 on Debian/Ubuntu, postgresql16-devel on RHEL/Fedora).

You have two paths before you. Choose wisely.

Path 1: The Path of the Hobbit (Using Your Package Manager)

This is the easy, straightforward path. It’s less work, which means more time for second breakfast. For example, this guide will show you how to install pgAudit on Ubuntu using apt, but the process is similar for other systems. Use your distribution’s package manager to install the pgaudit package that matches your PostgreSQL version.

For Debian/Ubuntu:

sudo apt update && sudo apt install postgresql-16-pgaudit

For RHEL/Fedora/CentOS:

sudo dnf install pgaudit16_16

(Note: The package name can vary depending on the repository, so you might have to search for it with dnf search pgaudit).

Screenshot of a terminal showing the successful installation of the pgAudit extension for PostgreSQL on an Ubuntu system using the 'sudo apt install postgresql-16-pgaudit' command.
Installing pgAudit using the system's package manager is the most straightforward path for Debian and Ubuntu users.

Path 2: The Path of the Dwarf (Forging It from Source)

Sometimes the package you need isn’t available, or you need a very specific version. This is for the adventurers among us who prefer to forge their own tools from the raw source code. It’s more work, but the bragging rights are immense.

First, clone the official pgaudit repository from GitHub.

git clone https://github.com/pgaudit/pgaudit.git

Navigate into the new directory and compile the extension. The USE_PGXS=1 flag tells make to use the PostgreSQL extension building infrastructure.

cd pgaudit
make USE_PGXS=1
sudo make USE_PGXS=1 install

If all goes well, the compiled extension files will be copied to the correct PostgreSQL directory. ✨

Waking the Eye: Configuring pgAudit

Installing the files isn’t enough. We have to tell PostgreSQL to actually load the extension when it starts up. This requires a small but critical change. You can do this in one of two ways.

Method 1: Directly Editing postgresql.conf (The Old-Fashioned Way)

The location of the postgresql.conf file can vary. On Debian-based systems, it’s often at /etc/postgresql/16/main/postgresql.conf. The surest way to find it on any system is to connect to psql and run SHOW config_file;. Once you’ve found it, open the file and find the shared_preload_libraries parameter. Add pgaudit to the comma-separated list.

shared_preload_libraries = 'pg_stat_statements,pgaudit'

Method 2: Using ALTER SYSTEM (The Modern Way)

Alternatively, you can set this parameter directly from psql using the ALTER SYSTEM command. This is often safer as it avoids manual syntax errors. First, it’s wise to check the current value:

SHOW shared_preload_libraries;

Then, append pgaudit to the existing value. For example, if the output was 'pg_stat_statements', you would run:

ALTER SYSTEM SET shared_preload_libraries = pg_stat_statements,pgaudit;

This command edits the postgresql.auto.conf file, which overrides postgresql.conf. It’s a cleaner way to manage configuration changes made via SQL.

Screenshot of the psql terminal displaying the current value of the shared_preload_libraries parameter in PostgreSQL before the pgAudit extension has been added.
Before altering the system configuration, it's always wise to check the current value of a parameter.
Screenshot of the psql terminal where the 'ALTER SYSTEM SET shared_preload_libraries' command is used to add pgAudit to the PostgreSQL configuration.
The ALTER SYSTEM command is the modern, recommended way to modify PostgreSQL configuration files without risking manual syntax errors.

The Unavoidable Restart

⚠️ CRITICAL STEP: Regardless of which method you choose, this change requires a full restart of the PostgreSQL service. A simple reload (pg_ctl reload) will not work. pgAudit needs to hook into the server process at startup, and PostgreSQL is very particular about this, like a Sith Lord about protocol. Do it wrong, and it will Force-choke your startup process.

The exact service name may vary depending on your operating system and how you installed PostgreSQL. It could be version-specific (like postgresql-16) or generic (like postgresql).

# Use the service name appropriate for your system, e.g., postgresql or postgresql-16
sudo systemctl restart postgresql
sudo systemctl status postgresql
Screenshot of a terminal after restarting the PostgreSQL service using the 'sudo systemctl restart postgresql' command to apply the new pgAudit configuration.
Unlike a simple reload, a full restart is mandatory for PostgreSQL to load new extensions into shared memory.

One Extension to Rule Them All: Enabling the pgAudit Extension in Your Database

The final step is to enable the extension within the specific database(s) you want to audit. Loading the library just makes it available; this command creates the necessary functions and objects for pgAudit to work inside a database.

Connect to your target database using psql or your favorite client:

psql -d my_precious_database

And run the following command:

CREATE EXTENSION pgaudit;

You can verify that the extension was installed correctly by using the \dx command in psql, which lists all installed extensions in the current database.

\dx

You should see pgaudit in the list. With that, the Eye is open. It’s not watching anything specific yet—we haven’t given it any instructions—but it’s installed, loaded, and ready for our command.

Screenshot of the psql terminal showing the 'CREATE EXTENSION pgaudit;' command being executed to enable auditing in a PostgreSQL database.
With the pgAudit library loaded, the final step is to enable the extension within each database you wish to monitor.
Screenshot of the psql terminal showing the output of the \dx command, which lists installed PostgreSQL extensions and confirms that pgAudit is successfully installed.
The \dx command is a quick and easy way to list all installed extensions and confirm that pgAudit is ready to be configured.

What’s Next?

We’ve successfully laid the foundation. The pgAudit extension is now a part of our PostgreSQL instance, ready to serve.

In Part 2 of this series, we’ll teach the Eye what to watch. We will dive into Session Auditing, configuring pgAudit to log entire classes of statements like READ, WRITE, and DDL, and we’ll discuss the crucial settings that keep your logs clean and your passwords out of them.

By evandro

You might also like:

A PostgreSQL elephant dressed as a wizard looks into a PalantĂ­r of logs, shielding its eyes from a dangerous log entry showing a cleartext password, symbolizing the importance of pgAudit password protection.

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

A cartoon PostgreSQL elephant holds up a glowing orange ring with the word 'GRANT' inscribed on it, symbolizing the power of object-level auditing in pgAudit.

pgAudit Object-Level Auditing in PostgreSQL: Part 3 – One GRANT to Rule Them All

A PostgreSQL elephant dressed as Legolas uses a magnifying glass to audit a holographic screen showing hobbits being taken to an INSERT command, symbolizing pgAudit session auditing.

pgAudit Session Auditing in PostgreSQL: Part 2 – My God, It’s Full of Logs!

Leave a Reply

Your email address will not be published. Required fields are marked *