Database Activity Monitoring for PostgreSQL and MySQL: pgAudit, General Query Log, and Cloud-Native DAM Options
Retool's new app builder is where AI-generated code ships safely
Building apps with AI is easy. Getting them to production safely is another story.
PostgreSQL and MySQL do not enable query audit logging by default, which means the most common forensic question in a database-related security incident — what queries were run and by whom — cannot be answered. The database logs the connection error and the system logs the CPU spike, but the actual query text that exported the customer table is not recorded anywhere.
Enabling pgAudit for PostgreSQL or the Audit Log Plugin for MySQL addresses this gap before the incident, at the cost of additional log volume and a small performance overhead. The investment is justified by the regulatory requirements (SOC 2, PCI DSS, HIPAA all require database access logging) and the forensic value of having a complete query record available when an investigation requires it.
Lightweight audit configuration for production databases
The goal of the initial audit configuration is to capture the security-relevant events — connections, schema changes, and privilege modifications — without the 10-25% throughput hit that comes from enabling full DML session logging on a busy OLTP database. pgAudit's session and object modes give you the control to start narrow and expand scope incrementally. This section covers setting pgaudit.log to connection, ddl, and role as the starting point, validating that the configuration is actually capturing what you expect by running test queries and verifying the log output, and then evaluating whether object-mode logging of specific sensitive tables should be added before enabling full DML logging. Staging environment testing of DML log volume is a prerequisite before enabling it on any production instance.
Start with connection, DDL, and privilege events before enabling DML logging
For the initial pgAudit or MySQL Audit Plugin deployment on production databases, configure the minimum audit scope that captures security-relevant events without impacting database performance: pgaudit.log = 'connection, ddl, role' for PostgreSQL captures all login and logout events, all schema change statements (CREATE TABLE, DROP INDEX, ALTER COLUMN), and all privilege grant and revoke statements. This configuration typically adds less than 1% overhead on OLTP workloads and generates a manageable log volume. Enable DML logging (pgaudit.log adds 'dml' or 'write') only for specific sensitive tables using pgAudit object logging on those tables, not for all tables across the database. Assess DML logging volume in a staging environment before enabling on production to confirm the log output rate is within your log shipping and storage capacity.
Forward audit logs to a system where database administrators cannot modify them
Configure PostgreSQL and MySQL audit log forwarding to a centralized logging system before the logs have compliance or investigation value. On self-hosted databases, use Filebeat or Fluentd agents on the database server to tail the audit log file and forward entries to an Elasticsearch or Splunk indexer. Configure the agent to send to a separate logging account or server where the database team's credentials do not have write or delete access. On AWS RDS, export logs to CloudWatch Logs and restrict CloudWatch log group deletion using IAM policies that deny logs:DeleteLogGroup and logs:DeleteLogStream for the database team's IAM roles. The separation between log producers and log administrators is the control that makes audit logs useful for internal investigation and external compliance audit.
Cloud-native DAM options and vendor tool considerations
AWS RDS and Google Cloud SQL both include audit logging capabilities built into the managed service, which eliminates the need to manage the pgAudit extension on self-hosted infrastructure and integrates natively with CloudWatch Logs and Cloud Logging for centralized log storage. For most organizations running on a single cloud database platform, the cloud-native audit logging plus a SIEM integration covers the compliance and forensic investigation requirements without a dedicated DAM product. This section helps you decide when the built-in options are sufficient and when a dedicated DAM tool like Imperva or IBM Guardium adds value, specifically for real-time query pattern alerting, behavioral analytics, or multi-platform environments mixing Oracle, SQL Server, and open-source databases.
Use RDS CloudWatch integration for AWS deployments before evaluating third-party DAM tools
AWS RDS PostgreSQL and MySQL include pgAudit and the Audit Log Plugin respectively at no additional charge, with automatic export to CloudWatch Logs. This provides connection-level and query-level audit logging, log retention configured at the CloudWatch log group level, and CloudWatch Logs Insights for query-based log analysis. For most organizations, RDS audit logging plus CloudWatch Logs is sufficient for compliance and security investigation use cases without requiring a dedicated DAM tool. Evaluate dedicated DAM tools (Imperva SecureSphere, IBM Guardium, McAfee Database Activity Monitoring) when you need real-time alerting on specific query patterns (such as alerting when a SELECT returns more than 1,000 rows from a sensitive table), behavioral analytics on query patterns, or cross-database platform consolidation for environments with Oracle, DB2, and SQL Server in addition to PostgreSQL and MySQL.
Validate audit log completeness by running test queries and confirming log entries
After enabling database audit logging, validate that the log is capturing what you expect before relying on it for compliance or investigation. Run a test connection from a non-standard IP, execute a SELECT on a sensitive table, execute a DROP TABLE in a test schema and roll back the transaction, and execute a GRANT statement. Verify that each of these appears in the audit log with the correct user, timestamp, and query text. Also verify that the log entries are appearing in the forwarded log destination (CloudWatch, Splunk, ELK) within the expected latency — audit logs that are stored only on the database server and not forwarded are inaccessible if the server is compromised and shut down during an incident. Run this validation monthly or after any configuration change to the audit log setup.
Briefings like this, every morning before 9am.
Threat intel, active CVEs, and campaign alerts, distilled for practitioners. 50,000+ subscribers. No noise.
The bottom line
Database audit logging for PostgreSQL and MySQL starts with pgAudit (session mode for compliance, object mode for sensitive table monitoring) and the MySQL Audit Log Plugin, configured with connection and DDL events as the minimum scope and DML logging added selectively for high-sensitivity tables. Forward audit logs to a centralized logging system where the database team cannot modify the log entries. On AWS RDS, the built-in pgAudit and MySQL Audit Log Plugin with CloudWatch Logs export provides the logging foundation without additional tooling. Validate completeness with test queries after enabling, and establish a log retention period that meets your compliance requirements — PCI DSS requires one year of cardholder data environment audit log retention.
Frequently asked questions
How do I enable pgAudit for PostgreSQL to log query activity?
Install pgAudit by adding shared_preload_libraries = 'pgaudit' to postgresql.conf and restarting PostgreSQL (requires a server restart — plan for a maintenance window). Install the pgAudit extension in the database: CREATE EXTENSION pgaudit; Set the audit log classes in postgresql.conf with pgaudit.log = 'ddl, role, connection' for a balanced starting configuration that logs schema changes, privilege grants and revocations, and connection events without logging every DML statement. Increase to pgaudit.log = 'ddl, dml, role, connection' to include DML (INSERT, UPDATE, DELETE, SELECT) statements with the full query text — this generates significantly higher log volume. The pgAudit output appears in the standard PostgreSQL log file alongside other log messages. Filter it out using log_line_prefix to tag pgAudit entries and forward only tagged lines to your SIEM.
How do I configure pgAudit object logging to monitor specific table access?
pgAudit object logging monitors access to specific database objects (tables, views, sequences) rather than logging all session queries. Enable object logging by setting pgaudit.log = 'object' (or in addition to other classes) and creating an audit role: CREATE ROLE pgaudit_role; GRANT SELECT ON customers TO pgaudit_role; GRANT INSERT, UPDATE, DELETE ON orders TO pgaudit_role; SET pgaudit.role = 'pgaudit_role'; With object logging enabled and the audit role configured, pgAudit logs any query that accesses tables granted to the audit role, regardless of which user ran the query. This allows targeted monitoring of sensitive tables (customers, payment_cards, medical_records) without logging the full query stream from all sessions, keeping log volume manageable on high-throughput production databases.
How do I enable MySQL audit logging without MySQL Enterprise Edition?
MySQL Community Edition does not include the Enterprise Audit Log Plugin, but MariaDB Audit Plugin (server_audit) can be installed on MySQL 5.7 and 8.0 as a community alternative. Download the MariaDB Audit Plugin shared library (server_audit.so) compatible with your MySQL version and copy it to the MySQL plugin directory. Load the plugin with INSTALL PLUGIN server_audit SONAME 'server_audit.so'; Configure the plugin in my.cnf: server_audit_logging = ON, server_audit_events = CONNECT,QUERY,TABLE, server_audit_output_type = FILE, server_audit_file_path = /var/log/mysql/audit.log, server_audit_file_rotations = 7. Alternatively, on AWS RDS MySQL, enable audit logging by setting the audit_log parameter to ON in a custom parameter group — RDS MySQL includes the MySQL Enterprise Audit Log Plugin for all editions at no additional charge.
How do I enable database audit logging on Amazon RDS for PostgreSQL?
Enable pgAudit on RDS PostgreSQL by adding pgaudit to the shared_preload_libraries parameter in a custom DB parameter group (this requires creating a non-default parameter group and associating it with the RDS instance — changing shared_preload_libraries requires a reboot). Set pgaudit.log, pgaudit.log_catalog, and pgaudit.log_parameter in the same parameter group. After applying the parameter group and rebooting the instance, install the extension in each database: CREATE EXTENSION pgaudit; Configure log export to CloudWatch Logs by enabling the postgresql log export under the RDS instance configuration, which makes pgAudit output available in CloudWatch Logs under the /aws/rds/instance/your-instance-name/postgresql log group. Set CloudWatch Logs retention on the log group to 90 days or longer for compliance requirements.
What database activity should I audit for security monitoring purposes?
Security-relevant database activity to audit includes: all connection events (successful logins and failed login attempts) to detect unauthorized access attempts and credential stuffing against the database, DDL statements (CREATE, DROP, ALTER TABLE) to detect schema changes that were not part of a change management process, privilege grants and revocations (GRANT, REVOKE) to detect unauthorized privilege escalation, SELECT statements on tables containing PII, financial records, or credentials to detect data exfiltration queries (filter to high-sensitivity tables using pgAudit object logging to keep volume manageable), and DML statements (INSERT, UPDATE, DELETE) on audit control tables or system tables to detect evidence tampering. For PCI DSS compliance, audit all access to cardholder data tables. For HIPAA compliance, audit all access to tables containing protected health information.
What is the performance impact of enabling database audit logging?
The performance impact of database audit logging depends on the logging scope and the database workload. pgAudit session logging of all statements (pgaudit.log = all) on a write-heavy OLTP database can reduce throughput by 10-25% due to the synchronous disk write for each logged statement. Start with connection and DDL logging only (pgaudit.log = connection, ddl) which has minimal performance impact (under 1% on typical workloads) and then evaluate the impact of adding DML logging. MySQL General Query Log similarly has a 5-10% throughput impact on write-heavy workloads. Mitigation options: use asynchronous logging where supported, log to a fast SSD or RAM disk partition, filter aggressively by statement class using object logging rather than session logging, and for very high-throughput databases consider a dedicated read replica with audit logging enabled that receives SELECT traffic while the primary handles write traffic.
How do I analyze database audit logs to investigate a security incident?
For security investigation using database audit logs, the initial triage focuses on three questions: who connected, when, and from where — review connection events filtered to the time window of the suspected incident to identify unusual source IPs, previously unseen usernames, or connections at unusual hours. Second, what did they access — filter pgAudit or MySQL audit logs by the session user and time window to see every table accessed and every query executed during the session. Third, what did they take — look for SELECT statements on sensitive tables with large result set sizes or queries with no WHERE clause that would return all rows. In Splunk, search the audit log index with index=db_audit source_user=suspect_user earliest=-24h to retrieve the full session activity. Export the raw query log for the session to a forensic evidence package for the incident record.
Sources & references
Free resources
Critical CVE Reference Card 2025–2026
25 actively exploited vulnerabilities with CVSS scores, exploit status, and patch availability. Print it, pin it, share it with your SOC team.
Ransomware Incident Response Playbook
Step-by-step 24-hour IR checklist covering detection, containment, eradication, and recovery. Built for SOC teams, IR leads, and CISOs.
Get threat intel before your inbox does.
50,000+ security professionals read Decryption Digest for early warnings on zero-days, ransomware, and nation-state campaigns. Free, daily, no spam.
Unsubscribe anytime. We never sell your data.
Founder & Cybersecurity Evangelist, Decryption Digest
Cybersecurity professional with expertise in threat intelligence, vulnerability research, and enterprise security. Covers zero-days, ransomware, and nation-state operations for 50,000+ security professionals every morning.
Win a $2,495 Black Hat pass.
Full-access to Black Hat USA 2026 in Las Vegas. Subscribe free to enter.