Security Audit

Database audit is a compliance management that conducts fine-grained audits of database operations, alerts the risky behaviors encountered by the database, and blocks attacks. By recording, analyzing and reporting the user's behavior of accessing the database, it can be used to help users generate compliance reports and trace the source of accidents afterwards, and at the same time strengthen the recording of internal and external database network behaviors to improve the security of data assets.

YMatrix auditing methods can be performed through log_XXX and pgAudit:

log_XXX

The log_xxx series parameters are used to configure the behavior of logging and help monitor and debug the database operation. Record all database operation behaviors in the database operation log, and then analyze the log for dangerous behavior warning.

Configuration parameters

logging_collector     --Whether to turn on the log collection switch, default off, recommended on.
log_destination       --Log record type, default is stderr, which only records error output; csvlog is recommended, and it includes: stderr, csvlog, syslog, and eventlog.
log_directory          --Log path, default is $PGDATA/pg_log 
log_filename            --Log name, default is postgresql-%Y-%m-%d_%H%M%S.log
log_file_mode           --Log file type, default is 0600
log_truncate_on_rotation  --Default is off. If set to on, the file content overwrite method is as follows: off means append after, on means clear and then add.
log_rotation_age      --The maximum duration for retaining a single file, defaulting to 1d, also includes 1h, 1min, and 1s.
log_rotation_size       --Retain the maximum size of a single file, with the default being 10MB.
log_error_verbosity    --The default is "default", and "verbose" means long and wordy.
log_connections    --Whether to write a log when the user session logs in, default is off, recommended to be on.
log_disconnections --Whether to write a log when the user session exits, default is off, recommended to be on.
log_statement    --Record various operations of users after logging into the database. 
    none，(do not record)
    ddl(Record create, drop, and alter)
    mod(Record ddl + insert, delete, update, and truncate)
    all(mod+select)
log_min_duration_statement = 2s   --Record SQL statements that exceed 2 seconds
log_checkpoints = on
log_lock_waits ＝ on
deadlock_timeout ＝ 1s

Configuration Example

Here is a configuration example that adds these parameters to the postgresql.conf file:

Notes!
After modifying the postgresql.conf file, you need to restart the YMatrix service to make the configuration 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 = on

## pgAudit

Introduction pgAudit extension for more granular audits. pgAudit provides two audit modes: session and object. The following configurable parameters will exist in the database.

### Configuration parameters

pgaudit.log -- Specify which types of statements the session audit log will record, with the default being none. The available values include the following: 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 to audit changes to tables in the pg_catalog schema, defaulting to on. pgaudit.log_client -- Whether to record the application type of the Client, default off. pgaudit.log_level -- The logging level of audit log records, defaulting to log. pgaudit.log_parameter -- Controls whether the audit log records session-level parameter setting actions, defaulting to off. pgaudit.log_parameter_max_size -- The maximum value of the audit parameter setting, with the unit of byte, defaults to 0. pgaudit.log_relation -- When controlling session auditing, whether to record a log for each table, default is off. pgaudit.log_rows -- Controls whether to record the number of affected records for statements, defaulting to off.
pgaudit.log_statement -- Controls whether to record statement information, default is on. pgaudit.log_statement_once -- Controls whether the execution of a set of statements records detailed information only in the first audit entry, defaulting to off. pgaudit.role -- Specify the primary role for object audit logging, with no default value.

### Configuration Example

**Session Audit**
Session audit logging provides detailed logs of all statements executed by the user on the backend. Use the `pgaudit.log` setting to enable session logging.

-- 1. Configure session auditing set pgaudit.log = 'read, ddl'; -- -- Record read operations and DDL operations

-- 2. Perform operations -- 2.1 Create a 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 the log -- Because the WRITE class is not enabled, the insert statement is not logged AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account ( id int, name text, password text, description text );, AUDIT: SESSION,2,1,READ,SELECT,,,select * from account,,

**Object Audit**

Only SELECT, INSERT, UPDATE, and DELETE commands are supported. Object-level audit logging is implemented through the role system. The `pgaudit.role` setting defines the role that will be used for audit logging. When the audit role has permissions to the executed command or inherits permissions from another role, the relationship (TABLE, VIEW, etc.) is audited logged.

In this example, this example shows how to log operation logs of specific tables and columns with a specific role (auditor) through pgAudit extension. Note that logging on the `account` table is controlled by column-level permissions, while logging on the `account_role_map` table is table-level.

-- 1. Configure object auditing -- Setting up audit roles set pgaudit.role = 'auditor';

-- 2. Perform operations -- 2.1 Create a table create table account ( id int, name text, password text, description text );

-- 2.2 Grant audit role permissions to tables grant select (password) on public.account to auditor;

-- 2.3 Query data select id, name from account;

select password from account;

-- 2.4 Grant audit role update permissions 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 an association table create table account_role_map ( account_id int, role_id int );

-- 2.7 Grant query permissions grant select on public.account_role_map to auditor;

-- 2.8 Joint 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, AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,update account set password = 'HASH2', 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, 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,

**Object Audit and Session Audit**
Object audit logging is intended to be a more fine-grained alternative to `pgaudit.log='read, write'`, and it may not make sense to use them simply in conjunction, but one possible scenario is to use session logging to capture each statement and then use object logging to supplement for more details about a particular relationship.

-- 1. Configure session auditing SET pgaudit.log = 'write, ddl'; -- Record write operations and DDL operations SET pgaudit.log_relation = on; -- Enable object-level auditing

-- 2. Configure object auditing GRANT SELECT, DELETE ON public.account TO auditor; -- Grant audit role permissions on the table SET pgaudit.role = 'auditor'; -- Set up audit roles

-- 3. Perform operations -- 3.1 Create a 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 the log AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,CREATE TABLE account ( id INT PRIMARY KEY, name TEXT, password TEXT, description TEXT );, AUDIT: SESSION,2,1,WRITE,INSERT,TABLE,public.account,INSERT INTO account (id, name, password, description) VALUES (1, 'user1', 'HASH1', 'blah, blah');, AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account,SELECT * FROM account;, AUDIT: SESSION,4,1,WRITE,DELETE,TABLE,public.account,DELETE FROM account WHERE id = 1; AUDIT: OBJECT,4,1,WRITE,DELETE,TABLE,public.account,DELETE FROM account WHERE id = 1;