Database auditing enables fine-grained compliance management by auditing database operations, alerting on risky behaviors, and blocking attack attempts. It records, analyzes, and reports user access to the database, helping users generate compliance reports, trace incidents, strengthen internal and external network behavior logging, and improve data asset security.
YMatrix supports two auditing methods: log_XXX parameters and pgAudit.
The log_XXX series of parameters configure logging behavior to monitor and debug database operations. All database activities are logged into the database runtime log, which can then be analyzed for early warning of suspicious or dangerous behavior.
logging_collector -- Enables log collection. Default is off; recommended: on.
log_destination -- Log output format. Default is stderr (errors only). Recommended: csvlog. Options: stderr, csvlog, syslog, eventlog.
log_directory -- Log file directory. Default: $PGDATA/pg_log.
log_filename -- Log file name. Default: postgresql-%Y-%m-%d_%H%M%S.log.
log_file_mode -- Log file permissions. Default: 0600.
log_truncate_on_rotation -- Truncate log file when rotating. Default: off (append). Set to on to overwrite.
log_rotation_age -- Maximum age per log file. Default: 1d. Also supports 1h, 1min, 1s.
log_rotation_size -- Maximum size per log file. Default: 10MB.
log_error_verbosity -- Error message detail level. Default: default. Use verbose for more details.
log_connections -- Log user session login events. Default: off. Recommended: on.
log_disconnections -- Log user session logout events. Default: off. Recommended: on.
log_statement -- Log SQL statements after login:
none -- No statements logged.
ddl -- Log CREATE, DROP, ALTER.
mod -- Log DDL + INSERT, DELETE, UPDATE, TRUNCATE.
all -- Log all statements including SELECT.
log_min_duration_statement = 2s -- Log queries running longer than 2 seconds.
log_checkpoints = on
log_lock_waits = on
deadlock_timeout = 1sBelow is a sample configuration. Add these settings to the postgresql.conf file:
Note!
After modifying thepostgresql.conffile, restart the YMatrix service for changes to take effect.
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 10MB
log_truncate_on_rotation = off
log_statement = 'all'
log_min_duration_statement = 100
log_connections = on
log_disconnections = on
log_lock_waits = onUse the pgAudit extension for more granular auditing. pgAudit provides two audit modes: session and object auditing. The following parameters are available.
pgaudit.log -- Specifies statement classes to include in session audit logs. Default: none. Options:
READ -- SELECT, COPY FROM
WRITE -- INSERT, UPDATE, DELETE, TRUNCATE, COPY TO
FUNCTION -- Function calls and DO blocks
ROLE -- GRANT, REVOKE, CREATE/ALTER/DROP ROLE
DDL -- All DDL except ROLE-related commands
MISC -- DISCARD, FETCH, CHECKPOINT, VACUUM
MISC_SET -- Miscellaneous SET commands (e.g., SET ROLE)
ALL -- Include all above categories
pgaudit.log_catalog -- Audit changes to tables in pg_catalog schema. Default: on.
pgaudit.log_client -- Log client application type. Default: off.
pgaudit.log_level -- Log severity level for audit entries. Default: log.
pgaudit.log_parameter -- Log session-level parameter settings. Default: off.
pgaudit.log_parameter_max_size -- Maximum size (in bytes) of logged parameters. Default: 0.
pgaudit.log_relation -- For session auditing, log one entry per table accessed. Default: off.
pgaudit.log_rows -- Log number of rows affected by a statement. Default: off.
pgaudit.log_statement -- Log statement text. Default: on.
pgaudit.log_statement_once -- For repeated statements, log full details only once. Default: off.
pgaudit.role -- Role used for object-level auditing. No default value.Session Auditing
Session auditing logs detailed records of all statements executed by a user in the backend. Enable it using the pgaudit.log setting.
-- 1. Configure session audit
SET pgaudit.log = 'read, ddl'; -- Log read and DDL operations
-- 2. Perform operations
-- 2.1 Create table
CREATE TABLE account
(
id int,
name text,
password text,
description text
);
-- 2.2 Insert data
INSERT INTO account (id, name, password, description)
VALUES (1, 'user1', 'HASH1', 'blah, blah');
-- 2.3 Query data
SELECT *
FROM account;
-- 3. View logs
-- Since WRITE class is not enabled, INSERT is not logged
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account
(
id int,
name text,
password text,
description text
);,<not logged>
AUDIT: SESSION,2,1,READ,SELECT,,,select *
from account,,<not logged>Object Auditing
Object auditing supports only SELECT, INSERT, UPDATE, and DELETE. It is implemented via the role system. The pgaudit.role setting defines the role used for object-level audit logging. When the audit role has permission (or inherits permission) on a command, the relation (TABLE, VIEW, etc.) is audited.
This example shows how pgAudit logs operations by a specific role (auditor) on specific tables and columns. Note that logging on the account table is controlled at the column level, while logging on account_role_map is at the table level.
-- 1. Configure object audit
-- Set audit role
SET pgaudit.role = 'auditor';
-- 2. Perform operations
-- 2.1 Create table
CREATE TABLE account
(
id int,
name text,
password text,
description text
);
-- 2.2 Grant column-level SELECT privilege to auditor
GRANT SELECT (password)
ON public.account
TO auditor;
-- 2.3 Query data
SELECT id, name
FROM account;
SELECT password
FROM account;
-- 2.4 Grant UPDATE privileges on name and password
GRANT UPDATE (name, password)
ON public.account
TO auditor;
-- 2.5 Update data
UPDATE account
SET description = 'yada, yada';
UPDATE account
SET password = 'HASH2';
-- 2.6 Create related table
CREATE TABLE account_role_map
(
account_id int,
role_id int
);
-- 2.7 Grant table-level SELECT privilege
GRANT SELECT
ON public.account_role_map
TO auditor;
-- 2.8 Join query
SELECT account.password,
account_role_map.role_id
FROM account
INNER JOIN account_role_map
ON account.id = account_role_map.account_id;
-- 3. Log output
AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,select password
from account,<not logged>
AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,update account
set password = 'HASH2',<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account,select account.password,
account_role_map.role_id
from account
inner join account_role_map
on account.id = account_role_map.account_id,<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account_role_map,select account.password,
account_role_map.role_id
from account
inner join account_role_map
on account.id = account_role_map.account_id,<not logged>Combining Object and Session Auditing
Object auditing provides finer granularity than session auditing. Using both together may not always be necessary, but one valid use case is to use session auditing to capture every statement, and supplement it with object auditing for detailed insights on specific relations.
-- 1. Configure session audit
SET pgaudit.log = 'write, ddl'; -- Log write and DDL operations
SET pgaudit.log_relation = ON; -- Enable per-relation session logging
-- 2. Configure object audit
GRANT SELECT, DELETE ON public.account TO auditor; -- Grant permissions to auditor
SET pgaudit.role = 'auditor'; -- Assign audit role
-- 3. Perform operations
-- 3.1 Create table
CREATE TABLE account (
id INT PRIMARY KEY,
name TEXT,
password TEXT,
description TEXT
);
-- 3.2 Insert data
INSERT INTO account (id, name, password, description)
VALUES (1, 'user1', 'HASH1', 'blah, blah');
-- 3.3 Query data
SELECT * FROM account;
-- 3.4 Delete data
DELETE FROM account
WHERE id = 1;
-- 4. View logs
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,CREATE TABLE account (
id INT PRIMARY KEY,
name TEXT,
password TEXT,
description TEXT
);,<not logged>
AUDIT: SESSION,2,1,WRITE,INSERT,TABLE,public.account,INSERT INTO account (id, name, password, description)
VALUES (1, 'user1', 'HASH1', 'blah, blah');,<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account,SELECT *
FROM account;,<not logged>
AUDIT: SESSION,4,1,WRITE,DELETE,TABLE,public.account,DELETE
FROM account WHERE id = 1;<not logged>
AUDIT: OBJECT,4,1,WRITE,DELETE,TABLE,public.account,DELETE
FROM account WHERE id = 1;<not logged>