Published at Jul 10, 2024
Start supercharging your PostgreSQL today.
Written by Ana Tavares, Sarah Conway, and Doug Ortiz
Every developer working with customer data—especially sensitive data—knows that maintaining stringent security and compliance standards is critical. Among various measures, database audit logging plays a vital role in adding an extra layer of security.
Audit logging, also known as auditing or audit trail logging, is the process of recording a detailed log of all activities and transactions performed within a system or application, particularly focusing on access and changes to data. In the context of databases, it involves tracking and documenting every action that occurs.
If you’re a PostgreSQL user, one of the most effective ways to do audit logging is using pgAudit. This open-source extension for PostgreSQL enables detailed logging of database operations. In this blog post, we’ll explain why audit logging is crucial and how to do it in PostgreSQL using pgAudit.
The pgAudit extension is now available to all Timescale Cloud customers directly within the Timescale console, providing detailed database session and/or object audit logging in the Timescale logs. Otherwise, we’ll walk you through installing pgAudit on your local installation later on in this article. Keep reading for more information!
The terms "audit log" and "activity log" are often used interchangeably, but they have distinct differences in terms of their focus, purpose, and content.
Audit logs are primarily used to ensure security, maintain compliance with regulations, and provide a verifiable record of actions. They are designed to track who did what, when, and how in detail. They also ensure that every action taken by users or administrators is recorded, providing a trail that can be audited to hold individuals accountable.
On the other hand, activity logs are used to monitor a system's normal operations, tracking user activities and system events to understand system health, behavior, and performance. They additionally provide insights into how users interact with the system, which can help improve user experience and optimize system performance.
Database audit logging involves recording the actions performed on the database. Many organizations operate under strict security policies that mandate logging all operations at the database level. In PostgreSQL (and TimescaleDB), this includes but is not limited to, SQL queries, data modifications, and login attempts. Database audit logging helps monitor user activities, detect suspicious actions, and ensure compliance with regulations such as GDPR, HIPAA, and others.
If you’re a PostgreSQL (or TimescaleDB) user, the open-source extension pgAudit helps fulfill these requirements by providing a detailed audit trail of database activities. This is particularly crucial for industries subject to rigid regulatory standards, such as healthcare, finance, and government sectors.
This is exactly the case here at Timescale, where we now provide pgAudit support to serve cloud customers who have stricter security and compliance requirements, such as the need to log all operations done at the database level. As an example, integrating pgAudit directly into our UI is a significant step for us toward delivering HIPAA support because the extension’s detailed logging capabilities ensure that all database interactions are recorded. This provides the necessary audit trails required for HIPAA compliance.
To provide a chronological record of events that can be used for security monitoring, compliance, troubleshooting, and performance analysis, audit logging includes the following key components:
Event capture: Audit logs capture specific events, such as user logins, logouts, SQL queries, data changes, and modifications to the database schema.
Timestamping: Each logged event is associated with a precise timestamp, indicating when the event occurred.
User identification: Logs include information about the user who performed the action, providing accountability and traceability.
Action details: The logs detail the nature of the action. This includes the type of operation (e.g., SELECT, INSERT, UPDATE, DELETE), the affected data or objects, and the outcome of the action.
Contextual information: Additional context, such as the source IP address, application name, and session ID, may be recorded to provide a comprehensive understanding of the event.
In short, these are the components that will be kept in your PostgreSQL audit table. Now, let’s see how you can create it using pgAudit.
PgAudit is a PostgreSQL extension that allows administrators to audit database activities at both the session and object levels. Since our goal is to save developers’ time, we’ll first show you how to easily enable it in Timescale Cloud (you can create a free account and take it for a spin for 30 days to try our automated data partitioning, hybrid row-columnar storage, incremental up-to-date materializations, and advanced data compression techniques).
If you don’t mind the added work, we’ll then show you how to configure it in vanilla PostgreSQL or self-hosted TimescaleDB.
Enabling and configuring pgAudit in your Timescale database is straightforward. The default configuration parameters require superuser access and can be set through the Timescale service interface. Follow these steps to configure pgAudit:
Click on the Services link on the left-hand navigation.
Click on the service you wish to configure.
Navigate to the Service information section in your Timescale interface.
Click on the Operations tab, followed by the Database Parameters link on the left-hand Operations navigation, and finally, the Advanced Parameters tab on the Database parameters section.
Search for the ‘pgaudit’ extension in the Database parameters list.
Add the values you want to set in the ‘pgaudit.log’ and ‘pgaudit.log_client' common parameters.
For detailed instructions and configuration options, check the pgAudit documentation.
To maximize the utility of your audit logs, you can export them to CloudWatch. This allows you to retain the logs for extended periods and leverage CloudWatch's monitoring and alerting capabilities. This blog post will guide you on integrating Amazon CloudWatch with your Timescale service.
In this section, we’ll cover:
Installing pgaudit
Setting up your environment
Configuring pgaudit for the first time
First, ensure you have PostgreSQL and the necessary development tools installed on your system.
For Ubuntu/Debian:
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib postgresql-server-dev-all
For CentOS/RHEL:
sudo yum install postgresql-server postgresql-contrib postgresql-devel
Afterward, verify your PostgreSQL installation by checking the current psql version.
psql -V
You should see a result similar to the following:
psql (PostgreSQL) 16.3 (Ubuntu 16.3-0ubuntu0.24.04.1)
The pgaudit extension might be available through your package manager. Alternatively, you can compile it from source.
Installing via Package Manager
For Ubuntu/Debian:
sudo apt-get install postgresql-XX-pgaudit # replace XX with your PostgreSQL version
For CentOS/RHEL:
sudo yum install pgaudit
1. Edit postgresql.conf: Locate the postgresql.conf file, usually found in the PostgreSQL data directory.
As the root user, add or modify the following configuration settings:
shared_preload_libraries = 'pgaudit'
Optionally, you can add more pgaudit configurations like:
pgaudit.log = 'all, -misc'
More configurations can be found within the official pgAudit documentation.
2. Restart PostgreSQL: Restart the PostgreSQL service to apply the changes.
sudo systemctl restart postgresql
1. Connect to your PostgreSQL instance:
sudo -su postgres
psql
2. Create the extension in the desired database:
CREATE EXTENSION pgaudit;
To verify that pgaudit is installed and configured correctly, you can run the following query:
SELECT * FROM pg_extension WHERE extname = 'pgaudit';
Or alternatively, use the shortcut:
\dx pgaudit
You should see an entry for pgaudit.
You can configure pgaudit to log different types of activities. For example:
ALTER SYSTEM SET pgaudit.log = 'read, write';
After making changes to postgresql.conf or using ALTER SYSTEM, always remember to reload or restart PostgreSQL as your system user.
sudo systemctl reload postgresql
Here's an example of how you might use pgaudit to log SELECT statements on a specific table:
1. Enable auditing for a specific table:
CREATE TABLE example_table (id serial PRIMARY KEY, data text);
2. Run a SELECT statement:
SELECT * FROM example_table;
Check your PostgreSQL logs to see the auditing information recorded by pgaudit. In Timescale Cloud, it’s as easy as navigating to the Logs tab within your service.
Once you have selected the Timescale service you want to inspect the logs, click the Logs tab for the service.
And here are your logs.
You can locate your log directory using the following:
SHOW log_directory;
If it’s a relative path, it’ll be relative to the root path for the data directory that you identified earlier.
On many systems, particularly Linux distributions, PostgreSQL logs are often stored in:
/var/log/postgresql/
That's it! It took quite a few steps, but now you have successfully installed and configured pgaudit in your PostgreSQL database.
Implementing audit logging with pgAudit is a significant step towards enhancing your database security and compliance posture.
If you want a high-performance but sturdy database as the foundation for your audit logging (delivered as a service), you can try Timescale Cloud for free for 30 days.
It combines all of your PostgreSQL faves—a wide ecosystem of tools and connectors, full SQL support, and battle-tested reliability—with a hard-working feature set that takes PostgreSQL to the next level for time series, events, analytics, and even AI. And with the pgAudit extension (among many others) available by default, it’s production-ready for even your most sensitive datasets.