In Object Audit Logging, retrieval of audit logs is achieved by using roles.
Roles are specified in the role parameter of the option section to retrieve audit logs. If there are privileges for commands executed by a role, or if privileges have been inherited from another role, those command operations are output as audit logs.
For example, after "auditor" is set for the role parameter of the option section, the SELECT and DELETE privileges for the account table are assigned to "auditor". In this case, when SELECT or DELETE is executed for the account table, audit logs are output.
Output format
In Object Audit Logging, audit logs are output in the format below:
AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,SELECT password FROM account;,<not logged>
(1) (2)(3)(4) (5) (6) (7) (8) (9)No | Content |
|---|---|
(1) | Log header Fixed as "AUDIT: OBJECT". |
(2) | Statement ID |
(3) | Substatement ID |
(4) | Class name |
(5) | Command tag |
(6) | Object type |
(7) | Object name |
(8) | SQL If "on" is specified for the log_statement_once parameter of the option section in the pgaudit configuration file, "<previously logged>" is output for the second and subsequent statements. |
(9) | Depending on the log_parameter parameter value of the option section in the pgaudit configuration file, the output content will be as below.
Additionally, if "on" is specified for the log_statement_once parameter of the option section in the pgaudit configuration file, "<previously logged>" is output for the second and subsequent statements. |
Information
If accessing resources that use the features below, the command tag (5) may be output as "???".
INSTEAD OF trigger
RULE
VIEW
Security policy per row
Table inheritance
Example
Below is an example of retrieving logs in Object Audit Logging.
By setting the target for assigning privileges to roles in detail, log output can be controlled.
In the example below, log retrieval of the account table is controlled by the privileges assigned to the columns, however, log retrieval of the account_role_map table is controlled by the privileges assigned to the table.
The role parameter below is specified for the option section in the pgaudit configuration file.
[option] role = 'auditor'
A role is defined for Object Audit Logging.
CREATE USER auditor NOSUPERUSER LOGIN;
Execute the SQL below from the client.
CREATE TABLE account
(
id int,
name text,
password text,
description text
);
GRANT SELECT (password) ON public.account TO auditor;
SELECT id, name FROM account;
SELECT password FROM account;
GRANT UPDATE (name, password) ON public.account TO auditor;
UPDATE account SET description = 'yada, yada';
UPDATE account SET password = 'HASH2';
CREATE TABLE account_role_map
(
account_id int,
role_id int
);
GRANT SELECT ON public.account_role_map TO auditor;
SELECT account.password, account_role_map.role_id
FROM account
INNER JOIN account_role_map ON account.id = account_role_map.account_id;The audit log below can be retrieved.
In the account table, only the operations for columns that privileges have been assigned to are output as logs.
In the account_role_map table, privileges are assigned to the table, so operations performed for the table are output as logs.
AUDIT: OBJECT,4,1,READ,SELECT,TABLE,public.account,SELECT password FROM account;,<not logged>
AUDIT: OBJECT,7,1,WRITE,UPDATE,TABLE,public.account,UPDATE account SET password = 'HASH2';,<not logged>
AUDIT: OBJECT,10,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,10,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>