PGaudit and immudb: The Dynamic Duo for Tamper-Proof PostgreSQL Audit Trails

As the amount of data stored in databases continues to grow, it has become increasingly important for companies to ensure the security of their data. One way to do this is by implementing a PostgreSQL audit log. However, simply having an audit log is not enough. It is also important to ensure that the audit log itself is tamper-proof. One way to achieve this is by using PGaudit and immudb together. In this article, I will introduce you to PGaudit and immudb, explain how they work together, and show you how to set them up to create tamper-proof PostgreSQL audit trails.

Introduction to PGaudit and immudb

PGaudit is a PostgreSQL extension that provides detailed audit logging capabilities. It allows you to track and record all database activity, including SELECT, INSERT, UPDATE, and DELETE statements, as well as connections, disconnections, and errors. This information is stored in the PostgreSQL audit log, which is a text file that can be easily read and analyzed.

immudb, on the other hand, is a high-speed, immutable database that provides cryptographic proof of data integrity. It is designed to ensure that data cannot be tampered with, even by those who have access to the database. immudb uses a combination of cryptographic hashing and Merkle trees to ensure that data remains unchanged.

Understanding PGaudit and immudb

PGaudit and immudb are complementary tools that work together to create tamper-proof PostgreSQL audit trails. PGaudit provides detailed audit logging capabilities, while immudb ensures that the audit log itself cannot be tampered with. By combining these two tools, you can create a secure and reliable audit trail that can be used to track and monitor database activity.

How PGaudit and immudb work together

When PGaudit is installed, it creates a new PostgreSQL schema called pgaudit. This schema contains a set of tables and functions that are used to log database activity. When a user performs an action on the database, such as running a SELECT statement, PGaudit logs the details of that action in the pgaudit schema.

Once the data is logged in the pgaudit schema, it can be securely transferred to immudb using a script or immudb-log-audit. These scripts use the immudb API to transfer the data and ensure that it is stored securely. immudb then uses cryptographic hashing and Merkle trees to ensure that the data remains unchanged, even if someone tries to tamper with it.

Setting up PGaudit and immudb

Setting up PGaudit and immudb is a fairly straightforward process. Here are the steps you need to follow:

1. Install PGaudit: Start by installing the PGaudit extension on your PostgreSQL server. This can be done using the PostgreSQL package manager or by downloading the extension from the PostgreSQL website.
2. Configure PGaudit: Once PGaudit is installed, you need to configure it to start logging database activity. This involves creating a new configuration file and specifying which events you want to log. Alternatively, you can use one of the PostgreSQL container images that have PGaudit already configured.
3. Install immudb: Next, you need to install immudb on your server. This can be done by downloading the immudb binary from the immudb website and running it on your server.
4. Configure immudb: Once immudb is installed, you need to configure it to accept data from PGaudit. This involves creating a new database and setting up the necessary user accounts and permissions.
5. Set up the transfer scripts: Finally, you need to set up the script or the application that will transfer data from PGaudit to immudb. In this blog post, we're using the immudb-log-audit project.

Quickstart guide

Start the PostgreSQL server using the Bitnami container image:

docker run -it --rm --name postgres-audit -e ALLOW_EMPTY_PASSWORD=yes -e POSTGRESQL_PGAUDIT_LOG=ALL docker.io/bitnami/postgresql:15

After the start you should see a line similar to:

2023-03-19 11:50:57.082 GMT [1] LOG:  pgaudit extension initialized

Start the immudb server using the binary and a different Postgres port (as immudb supports the Postgres wire protocol):

immudb --pgsql-server-port 13322

Start immudb-log-audit

git clone https://github.com/codenotary/immudb-log-audit.git
cd immudb-log-audit
go build

Create the collection for PGaudit logs:

immudb-log-audit create kv pgaudit --parser pgaudit

INFO[0000] Using default indexes for pgaudit parser      indexes="[statement_id log_timestamp timestamp audit_type class command]"
INFO[0000] Created                                       collection=pgaudit indexes="[statement_id log_timestamp timestamp audit_type class command]"

immudb-log-audit tail docker pgaudit postgres-audit --stdout --stderr --follow

INFO[0000] Docker tail                                   args="[pgaudit postgres-audit]"
INFO[0000] Indexes from immudb                           indexes="[statement_id log_timestamp timestamp audit_type class command]"

Every PGaudit log entry is now written into immudb and indexed.

Analyzing audit data with PGaudit and immudb

Once PGaudit and immudb are set up, you can start analyzing the audit data. This involves using the PostgreSQL audit log to identify patterns and trends in database activity. You can also use immudb to ensure that the audit log itself has not been tampered with.

To query a specific entry by statement number or by type, you can use statement_id or command when running immudb-log-audit read kv. The following example reads all CREATE statements:

immudb-log-audit read kv pgaudit command=CREATE
INFO[0000] Indexes from immudb                           indexes="[statement_id log_timestamp timestamp audit_type class command]"
{"timestamp":"2023-03-19T15:18:02.213706174Z","log_timestamp":"2023-03-19T15:18:02.212Z","audit_type":"SESSION","statement_id":3,"substatement_id":1,"class":"DDL","command":"CREATE DATABASE","statement":"CREATE DATABASE test_database;","parameter":"\u003cnot logged\u003e"}
{"timestamp":"2023-03-19T15:15:41.011894995Z","log_timestamp":"2023-03-19T15:15:41.011Z","audit_type":"SESSION","statement_id":1,"substatement_id":1,"class":"ROLE","command":"CREATE ROLE","statement":"CREATE ROLE rds_pgaudit;","parameter":"\u003cnot logged\u003e"}
{"timestamp":"2023-03-19T15:15:41.011894995Z","log_timestamp":"2023-03-19T15:15:41.011Z","audit_type":"SESSION","statement_id":1,"substatement_id":1,"class":"ROLE","command":"CREATE ROLE","statement":"CREATE ROLE rds_pgaudit;","parameter":"\u003cnot logged\u003e"}
{"timestamp":"2023-03-19T15:18:57.137061842Z","log_timestamp":"2023-03-19T15:18:57.136Z","audit_type":"SESSION","statement_id":6,"substatement_id":1,"class":"DDL","command":"CREATE TABLE","statement":"CREATE TABLE test_table (id int);","parameter":"\u003cnot logged\u003e"}

One of the benefits of using immudb is that it provides cryptographic proof of data integrity. This means that you can be sure that the data in the audit log has not been tampered with, even if someone tries to modify it. This is particularly important in cases where the audit log may be used as evidence in legal proceedings.

Conclusion

PGaudit and immudb are two powerful tools that can be used together to create tamper-proof PostgreSQL audit trails. By combining these tools, you can ensure that your audit log is secure and reliable and that it provides an accurate record of database activity. Setting up PGaudit and immudb is a straightforward process, and once they are set up, you can start analyzing the audit data to identify patterns and trends in database activity. If you are looking for a way to improve the security of your PostgreSQL database, PGaudit and immudb is definitely worth considering.

Call to Action

If you are interested in learning more about PGaudit and immudb, or if you need help setting them up for your PostgreSQL database, please contact us. Our team of experts can help you get started with these powerful tools and ensure that your audit log is secure and reliable.