How to Audit Access in PostgreSQL

How do I audit user access in PostgreSQL using pgAudit?

Records user logins and SQL actions for security and compliance.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is PostgreSQL access auditing?

Access auditing captures login events and SQL statements, letting you answer who touched what and when. With the pgAudit extension you gain fine-grained, SQL-level logs that satisfy SOC, HIPAA, and PCI requirements.

Why choose pgAudit over standard logging?

log_statement records every query, creating noise. pgAudit filters by action class (READ, WRITE, ROLE, DDL) and object, producing audit-ready logs while controlling volume.

How do I enable pgAudit?

1. Install the package: CREATE EXTENSION pgaudit;
2. Add shared_preload_libraries = 'pgaudit' in postgresql.conf and restart.
3. Reload config after setting parameters shown in the syntax block.

How can I log only sensitive ecommerce tables?

Set pgaudit.log_relation = 'Customers,Orders,OrderItems'. PostgreSQL now emits audit rows only when those tables are accessed.

Where do audit entries appear?

Entries flow into the regular PostgreSQL logs with the prefix AUDIT:. Forward them to a centralized system (CloudWatch, Splunk, ELK) for retention and alerting.

How do I find failed login attempts?

Query your log aggregator for connection authorized: user=<> and FATAL: password authentication failed. Combine with pgAudit records to see if a compromised user executed SQL.

Best practices for production deployments

Keep pgaudit.log_parameter = on to capture bind values.
Use log rotation and compression to control disk usage.
Periodically test that required statements are captured by replaying sample queries.

Common pitfalls and fixes

Restart required: editing shared_preload_libraries without restart leaves pgAudit inactive. Bounce the server and verify with SHOW shared_preload_libraries;.

Excessive volume: logging READ against high-traffic tables can flood storage. Limit tables or switch to WRITE,DDL,ROLE only.

Key takeaways

Enable pgAudit, scope tables, forward logs, and periodically review them. You now have traceability for every critical row in your ecommerce database.

Why How to Audit Access in PostgreSQL is important

How to Audit Access in PostgreSQL Example Usage


-- Example: capture a price update on a product
BEGIN;
UPDATE Products SET price = 99.00 WHERE id = 42;
COMMIT;
-- Logs contain:
-- AUDIT: WRITE, TABLE, UPDATE, schema public, table Products, userid 10, statement "UPDATE Products ..."

How to Audit Access in PostgreSQL Syntax


-- Enable pgAudit (superuser)
CREATE EXTENSION pgaudit;

-- postgresql.conf additions
shared_preload_libraries = 'pgaudit'
log_line_prefix = '%m [%p] %u@%d '

-- Fine-grained controls
ALTER SYSTEM SET pgaudit.log = 'read, write, ddl, role';        -- action classes
ALTER SYSTEM SET pgaudit.log_parameter = on;                     -- include bind values
ALTER SYSTEM SET pgaudit.log_relation = 'Customers,Orders,Products,OrderItems';

-- Reload configuration
SELECT pg_reload_conf();

Common Mistakes

Mistake: Adding pgaudit to shared_preload_libraries but forgetting to restart Postgres. Why wrong: The setting is only read at start-up. Fix: Restart the instance and run SHOW shared_preload_libraries to confirm.
Mistake: Logging every READ on high-traffic tables causing huge logs. Why wrong: Disk fills quickly and slows queries. Fix: Limit pgaudit.log to WRITE/DDL or specify only sensitive tables in pgaudit.log_relation.