Database Auditing
Overview
Database auditing records database access and operations for security monitoring, troubleshooting, and regulatory compliance. While IvorySQL provides built-in statement logging through standard parameters such as log_statement, built-in logging is coarse-grained and cannot easily track object-level access (such as specific tables or columns).
For fine-grained auditing, IvorySQL supports the PgAudit (pgaudit) extension. PgAudit integrates with the IvorySQL server logging subsystem to generate detailed session and object audit logs across both PostgreSQL-compatible mode (default port 5432) and Oracle-compatible mode (default port 1521).
-
Session Audit Logging: Records all statements executed by a user session that match configured statement classes (such as
READ,WRITE,DDL, orROLE). -
Object Audit Logging: Records operations (
SELECT,INSERT,UPDATE,DELETE) on specific relations (tables, views, columns) based on privileges granted to a designated auditor role (pgaudit.role).
For source compilation and basic extension installation steps, see PgAudit Ecosystem Integration.
Audit Function Configuration
Enabling PgAudit
Before configuring audit rules, load the pgaudit shared library at server startup and create the extension in each database you want to audit.
-
Add
pgaudittoshared_preload_librariesinpostgresql.conf:shared_preload_libraries = 'pgaudit'If
shared_preload_librariesalready contains other modules (for example, IvorySQL compatibility plugins), appendpgauditas a comma-separated entry and restart the IvorySQL server for the change to take effect. -
Restart the IvorySQL instance and create the extension in the target database:
CREATE EXTENSION pgaudit;
Configuring Session Audit Logging
Session auditing logs statements based on categories defined in pgaudit.log.
| Parameter | Default | Description |
|---|---|---|
|
|
Comma-separated list of statement classes to audit: |
|
|
Server log level used for |
|
|
Controls whether statements referencing only system catalogs ( |
|
|
Sends audit log messages to the client session in addition to the server log (usually kept |
|
|
Includes bind/parameter values alongside the SQL statement in audit logs. |
|
|
Maximum byte length logged per parameter value ( |
|
|
Creates a separate audit log entry for each relation (table/view) referenced in a |
|
|
Logs the statement text and parameters only on the first audit entry for a statement/substatement combination. |
Example configuration in postgresql.conf for auditing DDL, privilege changes, and writes while excluding pg_catalog-only queries:
pgaudit.log = 'ddl, role, write'
pgaudit.log_catalog = off
pgaudit.log_parameter = on
pgaudit.log_relation = on
Reload the configuration without restarting the database:
SELECT pg_reload_conf();
Configuring Object Audit Logging
Object auditing targets specific tables, views, or columns by assigning privileges to a dedicated non-login audit role specified by pgaudit.role.
-
Create an audit role and configure
pgaudit.roleinpostgresql.conf:CREATE ROLE auditor NOLOGIN; ALTER SYSTEM SET pgaudit.role = 'auditor'; SELECT pg_reload_conf(); -
Grant the operations you want to audit to
auditor:-- Audit SELECT and INSERT on a sensitive table GRANT SELECT, INSERT ON public.accounts TO auditor; -- Audit UPDATE only on a specific column GRANT UPDATE (balance) ON public.accounts TO auditor;
When any user executes a matching operation on public.accounts, PgAudit emits an OBJECT audit log entry, even if pgaudit.log is none.
Audit Management
Protecting Audit Configuration
-
Restrict superuser access: Only superusers can modify
shared_preload_libraries,pgaudit.role, orpgaudit.logglobally. Do not grant superuser privileges to application accounts. -
Control
SEToverrides: Non-superusers cannot changepgaudit.logorpgaudit.rolein their own sessions, ensuring users cannot disable auditing for their connections. -
Use
postgresql.conforALTER SYSTEM: Persist audit parameters centrally rather than relying on per-database or per-roleALTERsettings unless intentional exceptions are required.
Log Storage and Rotation
PgAudit writes entries prefixed with AUDIT: to the IvorySQL server log. Configure log collection and rotation in postgresql.conf to prevent disk exhaustion and support retention policies:
logging_collector = on
log_directory = 'log'
log_filename = 'ivorysql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_line_prefix = '%m [%p] %q%u@%d/%a '
Reviewing Audit Logs
Audit records follow a structured CSV-like format (AUDIT: <TYPE>,<STATEMENT_ID>,<SUBSTATEMENT_ID>,<CLASS>,<COMMAND>,<OBJECT_TYPE>,<OBJECT_NAME>,<STATEMENT>,<PARAMETER>).
Example log output:
LOG: AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE t1 (id int, name varchar(50));,<none>
LOG: AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.accounts,SELECT balance FROM public.accounts;,<none>
-
Filter
AUDIT:lines using standard log tools (grep "AUDIT:" log/ivorysql-*.log) or forward logs to a centralized SIEM / log aggregation platform for tamper-resistant retention and alerting. -
In Oracle-compatible mode (port
1521), verify bothlog_directorypermissions andpgaudit.log_parametersettings according to your data-masking and compliance requirements, sincepgaudit.log_parameter = onrecords literal parameter values in plaintext.