Database auditing is a compliance-oriented management practice that performs fine-grained auditing of database operations, issues alerts for risky behaviors targeting the database, and blocks attack activities. By recording, analyzing, and reporting user access behaviors to the database, it helps users generate compliance reports and trace incidents back to their root causes after the fact, while also enhancing records of internal and external database network activities to improve data asset security.
YMatrix supports two auditing methods: log_XXX and pgAudit.
The log_xxx series of parameters are used to configure logging behavior, aiding in monitoring and debugging the database runtime. All database operation behaviors are recorded into the database operational logs, which can then be analyzed for early warning detection of dangerous behaviors.
logging_collector -- Whether to enable the log collection switch; default is off, recommended on
log_destination -- Log output type; default is stderr (only error output is logged), recommended csvlog; options include: stderr, csvlog, syslog, and eventlog,
log_directory -- Log directory; default is $PGDATA/pg_log,
log_filename -- Log filename; default is postgresql-%Y-%m-%d_%H%M%S.log
log_file_mode -- Log file permissions; default is 0600
log_truncate_on_rotation -- Default is off; when set to on, log rotation overwrites content (off = append, on = truncate then write)
log_rotation_age -- Maximum duration for a single log file; default is 1d, also supports 1h, 1min, 1s
log_rotation_size -- Maximum size for a single log file; default is 10MB
log_error_verbosity -- Default is default; verbose means more detailed messages
log_connections -- Whether to log when a user session logs in; default off, recommended on
log_disconnections -- Whether to log when a user session logs out; default off, recommended on
log_statement -- Records various operations after a user logs into the database:
none — no logging
ddl — logs create, drop, and alter
mod — logs ddl + insert, delete, update, and truncate
all — logs mod + select
log_min_duration_statement = 2s -- Logs SQL statements exceeding 2 seconds
log_checkpoints = on
log_lock_waits ＝ on
deadlock_timeout ＝ 1sThe following is a configuration example. Add these parameters to the postgresql.conf file:
Note!
After modifying thepostgresql.conffile, you must restart the YMatrix service for the configuration to take effect.
logging_collector = on
log_directory = 'stderr'
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 = onSupported starting from YMatrix version 6.4.6 (inclusive).
Introduce the pgAudit extension for more fine-grained auditing.
-- Modify parameters to add pgaudit
gpconfig -c shared_preload_libraries -v matrixts,matrixmgr,matrixgate,mxstream,telemetry,pgaudit
-- Restart the database after modifying parameters
mxstop -arf
mxstart -a
-- After restarting the database instance
CREATE EXTENSION pgaudit;pgAudit provides two auditing modes: session-level and object-level. The following configurable parameters are available in the database:
pgaudit.log -- Specifies which classes of statements will be logged in session audit logs; default is none. Valid values include:
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 those in ROLE)
MISC (discard, fetch, checkpoint, vacuum)
MISC_SET (Miscellaneous SET commands, e.g. SET ROLE.)
ALL (Include all of the above.)
pgaudit.log_catalog -- Controls whether changes to tables under the pg_catalog schema are audited; default is on.
pgaudit.log_client -- Whether to record client application type; default is off.
pgaudit.log_level -- Logging level for audit logs; default is log.
pgaudit.log_parameter -- Controls whether session-level parameter setting actions are recorded in audit logs; default is off.
pgaudit.log_parameter_max_size -- Maximum size (in bytes) for logged parameters; default is 0.
pgaudit.log_relation -- Controls whether one log entry is generated per table during session auditing; default is off.
pgaudit.log_rows -- Controls whether the number of affected rows is recorded; default is off.
pgaudit.log_statement -- Controls whether statement details are recorded; default is on.
pgaudit.log_statement_once -- Controls whether detailed information for a group of statements is recorded only in the first audit entry; default is off.
pgaudit.role -- Specifies the primary role used for object audit logging; no default value.Session-Level Auditing
Session-level auditing audits all SQL statements executed within a user session that match the specified rules, suitable for security compliance scenarios.
-- 1. Set audit scope
set pgaudit.log = 'read, ddl';
-- 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 the audit scope does not include 'write', the insert operation 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
Audits access only to specific objects, requiring an audit role.
In this example, it demonstrates how the pgAudit extension logs operations by the audit role (auditor) on specific tables and columns.
--1. Create audit role
CREATE ROLE auditor;
--2. Enable object auditing
set pgaudit.role = 'auditor';
--3. Grant object privileges to the audit role
grant select (password) on public.account to auditor;
--4. Perform operations
--4.1 Query the specified password column
postgres=# select password
from account;
password
----------
HASH2
HASH2
HASH2
(3 rows)
--4.2 Query id and name columns
postgres=# select id, name
from account;
id | name
----+-------
1 | user1
1 | user1
1 | user1
(3 rows)
--5. View logs—only access to the password column is audited
"AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.account,""select password from account;"","
,,,,,,,0,,"pgaudit.c",767,
--6. Grant update privilege on the object
grant update (name, password) on public.account to audit;
--6.1 Perform update operation
update account set password = 'HASH2';
--7. View logs
"AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.account,""update account
set password = 'HASH2';"",",,,,,,,0,,"pgaudit.c",767,Object Auditing Combined with Session Auditing
Object audit logging is intended as a finer-grained alternative to pgaudit.log='read,write'. Simply combining them may not be meaningful, but one possible scenario is using session logging to capture every statement and supplementing it with object logging to obtain more detailed information about specific relations.
--1. Create table
create table account_role_map(account_id int,role_id int);
--2. Grant SELECT privilege on the object table to auditor
grant select on public.account_role_map to auditor;
--3. Perform operations
--3.1 With session auditing disabled, one log entry is recorded per table
set pgaudit.log_relation to off;
--3.2 Execute queries
select account.password,account_role_map.role_id \
from account inner join account_role_map \
on account.id = account_role_map.account_id;
select account_role_map.account_id \
from account inner join account_role_map
on account.id = account_role_map.account_id;
--4. View logs
--4.1 The first query accesses both password and account_role_map, so both tables are audited
"AUDIT: OBJECT,9,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
;"",",,,,,,,0,,"pgaudit.c",767,
"AUDIT: OBJECT,9,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
;"",",,,,,,,0,,"pgaudit.c",767,
--4.2 The second query does not access password, so only one audit entry appears
"AUDIT: OBJECT,10,1,READ,SELECT,TABLE,public.account_role_map,""select
account_role_map.account_id
from account
inner join account_role_map
on account.id = account_role_map.account_id
;"",",,,,,,,0,,"pgaudit.c",767,
--4.3 Both SQL statements accessed account_role_map, so both triggered auditing for account_role_map
"AUDIT: OBJECT,10,1,READ,SELECT,TABLE,public.account_role_map,""select
account_role_map.account_id
from account
inner join account_role_map
on account.id = account_role_map.account_id
;"",",,,,,,,0,,"pgaudit.c",767,
"AUDIT: OBJECT,11,1,READ,SELECT,TABLE,public.account_role_map,""select
account_role_map.role_id
from account
inner join account_role_map
on account.id = account_role_map.account_id
;"",",,,,,,,0,,"pgaudit.c",767,