Database Audit Logging: Tracking Every Change in MySQL and PostgreSQL

Table of Contents

The 2 AM Production Crisis

Early in my career, I spent a frantic Friday night staring at a production database where 50,000 customer records had simply vanished. We restored the backup within an hour, but the mystery remained. Nobody knew who triggered the deletion, when it happened, or how it bypassed our application logic. Was it a rogue script, a disgruntled employee, or a junior dev who forgot a WHERE clause?

Standard database logs are great for catching syntax errors or slow queries. However, they rarely tell the full story of data manipulation. This is why Audit Logging is critical. It creates a forensic trail of security-related events, linking every INSERT, UPDATE, and DELETE to a specific user and timestamp. If you handle sensitive information, auditing isn’t a luxury. It is a mandatory requirement for compliance standards like GDPR, HIPAA, or PCI-DSS.

Audit Logging vs. Standard Logging

Before we dive into the configuration, we need to distinguish between the different logs your database generates. Most systems produce a general log and an error log, but audit logging is far more surgical. It answers the specific questions of “Who, What, and When.”

General Logs: These record every single connection and query. They bloat quickly—sometimes growing by 10GB or more per day—and can degrade performance by 15-20%.
Binary Logs (MySQL) / WAL (PostgreSQL): These are designed for replication and recovery. While they contain data changes, they are binary files and extremely difficult for humans to read.
Audit Logs: These are optimized for security. They capture specific data access and schema modifications without the massive overhead of general logging.

My goal is to help you build a system that captures these events while keeping your database performance impact under 5%.

Implementing Audit Logging in MySQL

MySQL Community Edition lacks a built-in audit plugin; that is typically reserved for the Enterprise version. Fortunately, the MariaDB Audit Plugin serves as a powerful, open-source alternative. It works seamlessly with standard MySQL 5.7 and 8.0 installations.

1. Installing the Audit Plugin

First, identify your MySQL plugin directory by running this command in your SQL shell:

SHOW VARIABLES LIKE 'plugin_dir';

Download the server_audit.so (Linux) or server_audit.dll (Windows) file from a trusted MariaDB repository and move it into that folder. Once the file is placed, activate it through the console:

INSTALL PLUGIN server_audit SONAME 'server_audit.so';

2. Defining the Audit Policy

An active plugin does nothing until you configure it. I recommend tracking both DDL (Data Definition Language like CREATE/DROP) and DML (Data Manipulation Language like INSERT/UPDATE). Add these settings to your my.cnf file to ensure they persist after a reboot:

[mysqld]
server_audit_logging = ON
server_audit_events = 'CONNECT,QUERY,TABLE'
server_audit_query_log_limit = 1024
server_audit_file_path = /var/log/mysql/audit.log
server_audit_file_rotate_size = 100M
server_audit_file_rotations = 5

After a service restart, your audit.log will record every query. A typical entry will look like this:

20231027 14:05:01,localhost,root,localhost,5,12,QUERY,,'DELETE FROM customers WHERE id = 101',0

Setting Up Audit Logging in PostgreSQL

PostgreSQL users should turn to pgAudit. It is the industry standard for Postgres because it provides granular session and object-level logging that the default logging facility cannot match.

1. Installation

On Ubuntu or Debian systems, install the package matching your Postgres version:

sudo apt-get install postgresql-15-pgaudit

2. Configuration

PostgreSQL must load the pgAudit library at startup. Open your postgresql.conf file and update the following parameters:

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl'
pgaudit.log_catalog = off
pgaudit.log_parameter = on
pgaudit.log_relation = on

The pgaudit.log = 'write, ddl' setting is the “sweet spot.” It captures all modifications and schema changes without flooding your logs with thousands of routine SELECT queries. Restart your Postgres service to apply these changes.

3. Activating the Extension

Finally, enable the extension within your specific database:

CREATE EXTENSION pgaudit;

To test it, create a dummy table. Your Postgres log (usually found in /var/log/postgresql/) will now contain structured entries detailing exactly which user executed the command.

Managing the Log Deluge

Audit logs can grow massive very quickly. On a high-traffic system, I have seen logs hit 50GB in less than 24 hours. This volume creates a challenge: how do you actually analyze the data?

Auditors often require these logs in specific formats like JSON for ingestion into SIEM tools. If you need to quickly transform these logs for a report or a dashboard, toolcraft.app/en/tools/data/csv-to-json is a handy utility. Since it processes data entirely in your browser, your sensitive audit records never leave your local machine.

Hard-Earned Rules for Audit Logs

Setting up the logs is only the beginning. Proper management prevents the logs from becoming a liability or crashing your server. Follow these guidelines:

Enforce Log Rotation: Never let a log file grow indefinitely. Configure rotation to keep 7 days of logs locally and archive older files to encrypted cloud storage like AWS S3.
Filter Strategically: Do not log SELECT statements on high-traffic tables unless you are in a high-security environment. The noise will bury the important “write” events you actually need.
Lock Down Permissions: Audit logs are a roadmap for attackers. Ensure log files are readable only by the database service and the system administrator.
Monitor Disk I/O: Auditing adds a write operation for every tracked query. Use a tool like Prometheus to monitor disk latency and ensure logging isn’t bottlenecking your application.

Final Thoughts

Audit logging is often ignored until a crisis makes it indispensable. By implementing the MariaDB Audit Plugin or pgAudit, you move from guesswork to certainty. You gain a definitive, timestamped record of every change in your system. Start by logging DDL changes to track schema updates, then expand to DML logging as you get a handle on your storage requirements.