Database Access Auditing: Who Read or Changed What, and When

1. Why database access auditing is not optional polish

Database access auditing answers the decisive questions after a security incident: who accessed which row and when, which value got changed, and does the current state of a table match the documented history. Without systematic database access auditing, these questions remain unanswerable after an incident, because the only available information is the current database state, not the history that led to it.

Regulatory frameworks such as GDPR, PCI-DSS, HIPAA, or SOC 2 explicitly demand traceable database access auditing for personal and financial data. A request under GDPR Article 15 asking who accessed a data subject's data and when simply cannot be answered without functioning auditing, which represents an independent compliance risk, regardless of any concrete security incident.

The value of database access auditing shows up especially with insider threats: an employee with legitimate database access who views or manipulates data for unauthorized reasons leaves no trace in classic security systems such as firewalls or intrusion detection systems, because the access was technically authorized. Only a database-level audit log reliably captures this case.

2. Trigger-based auditing: logging changes without gaps

The classic approach to database access auditing for write operations uses triggers that automatically write an entry to a dedicated audit table on every INSERT, UPDATE, and DELETE. This approach works regardless of the path through which a change occurs, whether via application code, an admin tool, or direct SQL access, because the trigger operates at the database level and cannot be bypassed.

A well-designed audit table for database access auditing stores not only the new value but also the old value before the change, the executing database user, a precise timestamp, and the type of operation. This combination allows every historical change to be fully reconstructed, including the state before the change, which is crucial for forensic analysis after an incident.

-- Audit table capturing old and new values for every change
CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  table_name TEXT NOT NULL,
  operation TEXT NOT NULL,
  row_id INT NOT NULL,
  old_data JSONB,
  new_data JSONB,
  changed_by TEXT NOT NULL DEFAULT current_user,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Trigger function: fires on every write, cannot be bypassed by app code
CREATE OR REPLACE FUNCTION audit_trigger_fn() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (table_name, operation, row_id, old_data)
    VALUES (TG_TABLE_NAME, TG_OP, OLD.id, to_jsonb(OLD));
    RETURN OLD;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (table_name, operation, row_id, old_data, new_data)
    VALUES (TG_TABLE_NAME, TG_OP, NEW.id, to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, operation, row_id, new_data)
    VALUES (TG_TABLE_NAME, TG_OP, NEW.id, to_jsonb(NEW));
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER customers_audit
AFTER INSERT OR UPDATE OR DELETE ON customers
FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();

3. pgAudit and native auditing extensions

Trigger-based database access auditing covers write operations well, but by default does not log pure read operations, because a SELECT does not trigger anything. The PostgreSQL extension pgAudit closes exactly this gap by operating at the statement level, producing detailed logs for SELECT, INSERT, UPDATE, DELETE, as well as DDL and function calls, configurable by class of operation.

pgAudit writes its logs into the regular PostgreSQL log file, enabling central log aggregation via tools such as the ELK stack or Splunk. Its configuration allows granular control over which roles and object types get audited, so database access auditing does not blanket-log every query, which would produce unmanageable log volumes, but instead focuses specifically on sensitive tables and roles.

-- Enable pgAudit extension
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Configure which operation classes get audited globally
-- READ, WRITE, FUNCTION, ROLE, DDL, MISC
ALTER SYSTEM SET pgaudit.log = 'READ, WRITE, ROLE';

-- Log parameter values too, useful for forensic investigation
ALTER SYSTEM SET pgaudit.log_parameter = on;

-- Object-level auditing: only audit access to sensitive tables
ALTER SYSTEM SET pgaudit.role = 'audited_role';
GRANT SELECT ON customers, payments TO audited_role;

-- Reload configuration to apply changes
SELECT pg_reload_conf();

-- Sample pgAudit log entry (in PostgreSQL log file)
-- AUDIT: SESSION,1,1,READ,SELECT,,,SELECT * FROM payments WHERE id = 42,<none>

4. Statement-level vs. row-level auditing

An important design decision in database access auditing concerns granularity: statement-level auditing logs the executed SQL statement as a whole, regardless of how many rows were actually affected. Row-level auditing instead logs every single affected row separately, offering significantly more detailed traceability, but also causing significantly more storage space and processing overhead.

For database access auditing of write operations, row-level auditing is usually the right choice, because you need to know which specific value changed, not just that an UPDATE statement with a certain WHERE condition executed. For database access auditing of read operations, statement-level auditing is often more practical, because row-level logging of every read row quickly turns into an unmanageable data volume with complex JOINs and large result sets.

-- Statement-level: one log entry regardless of rows affected
-- pgAudit default behavior for most operation classes
UPDATE orders SET status = 'shipped' WHERE created_at < now() - interval '7 days';
-- Single audit entry: "UPDATE orders SET status = 'shipped' WHERE ..."
-- Does not show which specific rows or values changed

-- Row-level: one entry per affected row via trigger
-- Shows exact before/after state for every single row
-- See audit_trigger_fn() example above: one INSERT into audit_log
-- per row changed, including old_data and new_data as JSON

-- Practical combination: statement-level for reads, row-level for writes

5. Auditing read operations: the harder half

Write operations can be reliably captured with triggers, but database access auditing for pure read operations is structurally harder, because a SELECT does not trigger anything and the database by default leaves no trace that a specific row was read. Yet exactly this is what makes read operations especially relevant for data breaches: unauthorized copying of customer data leaves no changed rows, only an executed SELECT query.

pgAudit with the READ class enabled solves this problem at the statement level, logging that a query executed, with which parameters, and by whom. For fine-grained row-level read auditing, where exactly which individual rows a SELECT actually returned gets logged, more specialized solutions are needed, such as database proxy systems that transparently capture query traffic and additionally log the returned rows.

6. Protecting audit logs from tampering: immutable storage

An audit log that can be manipulated by the very account whose actions it is supposed to log is worthless for forensic purposes. If an attacker with sufficient privileges changes rows in an application table, they can, without additional protection, also delete or manipulate the corresponding audit entries, rendering the entire database access auditing worthless.

Protection against this manipulation consists of several layers: the audit table gets strictly restricted database user privileges, so that only INSERT, but never UPDATE or DELETE, is allowed, even for the account writing application data. In addition, many systems regularly export audit logs to a separate, immutable storage system such as AWS S3 with Object Lock enabled or a write-once-read-many archive, so that even a fully compromised database server can no longer alter the already exported history.

-- Restrict the audit table to append-only, even for privileged app roles
REVOKE UPDATE, DELETE ON audit_log FROM app_user, app_admin;
GRANT INSERT, SELECT ON audit_log TO app_user;

-- Only a dedicated, rarely used role can perform maintenance
GRANT ALL ON audit_log TO audit_maintenance_role;

-- Additionally: block DELETE and UPDATE at the row level via a rule
CREATE RULE audit_log_no_delete AS ON DELETE TO audit_log DO INSTEAD NOTHING;
CREATE RULE audit_log_no_update AS ON UPDATE TO audit_log DO INSTEAD NOTHING;

-- Periodic export to immutable, external storage (conceptual)
-- pg_dump --table=audit_log | upload to S3 bucket with Object Lock enabled
-- Retention policy prevents deletion even by AWS account administrators

7. Limiting the performance impact of auditing

Database access auditing inevitably impacts performance, because every logged operation means additional write work. With trigger-based row-level auditing, the number of write operations per transaction can double in the worst case, which becomes noticeable on very write-heavy tables. The pragmatic approach limits detailed row-level auditing to genuinely sensitive tables, instead of enabling it system-wide for every table.

Similar considerations apply to pgAudit: statement-level logging of every SELECT query on a heavily used table generates considerable log volume and I/O load from writing to the log file. A sensible strategy restricts pgAudit's READ class to explicitly marked roles or tables containing personal data, while non-critical reporting tables remain without read-level database access auditing to keep overall load within an acceptable range.

8. Alerting: automatically detecting suspicious access patterns

Pure database access auditing without active evaluation is only half the solution: a complete log that nobody looks at only helps with post-incident cleanup, but does not prevent the incident itself. Automated alerting based on the audit data detects suspicious patterns in real time, for instance an account suddenly reading thousands of rows from a customer table, even though its normal access pattern involves only single rows per request.

Typical alerting rules for database access auditing check for access outside usual business hours, sudden mass exports of large data volumes, access from unusual IP addresses, or a service account accessing tables outside its documented scope of responsibility. These rules can be implemented directly as periodic queries against the audit log tables or exported to a SIEM system that detects more complex correlations across multiple data sources.

-- Alerting query: detect unusually large read volume per account
SELECT changed_by, count(*) AS row_count, date_trunc('hour', changed_at) AS hour
FROM audit_log
WHERE operation = 'SELECT'
  AND changed_at > now() - interval '1 hour'
GROUP BY changed_by, hour
HAVING count(*) > 5000;  -- threshold based on normal baseline

-- Alerting query: access outside normal business hours
SELECT * FROM audit_log
WHERE changed_at > now() - interval '1 day'
  AND (extract(hour FROM changed_at) < 6 OR extract(hour FROM changed_at) > 22)
  AND table_name IN ('customers', 'payments');

-- Alerting query: service account touching unexpected tables
SELECT * FROM audit_log
WHERE changed_by = 'catalog_service'
  AND table_name NOT IN ('products', 'categories');

9. Auditing approaches compared

The following table compares the approaches presented for database access auditing by purpose and effort.

Effective database access auditing combines all these building blocks: trigger-based row-level logging for write operations, pgAudit for read operations on sensitive tables, tamper protection through strict privileges and external archiving, and automated alerting that detects anomalies before they escalate into a full data breach.

10. Summary

Database access auditing turns post-incident uncertainty into a traceable record. Trigger-based logging captures write operations without gaps, complete with old and new values, while pgAudit covers the harder half, read operations, at the statement level. Both approaches together close the gap that pure database backups or application logs leave open.

An audit log without tamper protection is worthless, which is why strictly restricted, append-only access and external, immutable archiving belong in every serious implementation. Automated alerting based on the audit data turns database access auditing from pure after-the-fact forensics into active detection, ideally spotting suspicious patterns before an access turns into a full data breach.

11. FAQ: Database Access Auditing