Viewing DWS Database Audit Logs

Prerequisites

• The audit function has been enabled by setting audit_enabled. The default value of audit_enabled is ON. To disable audit, set it to OFF by referring to Modifying GUC Parameters of the DWS Cluster.
• The audit items have been configured. For how to enable audit items, see Configuring the Database Audit Logs.
• The database is running properly and a series of addition, modification, deletion, and query operations have been executed in the database. Otherwise, no audit result is generated.
• The audit logs of each database node are recorded separately.
• Only users with the AUDITADMIN permission can view audit records.

Configuring the Database Audit Logs

1. Log in to the DWS console.
2. In the navigation pane on the left, choose Cluster > Cluster List.
3. In the cluster list, click the name of a cluster. In the navigation pane, choose Security Settings.

   By default, Configuration Status is Synchronized, which indicates that the latest database result is displayed.

4. In the Audit Settings area, set the audit items listed in Table 1.
   Figure 1 Audit items
   

   Table 1 Audit items

   Audit Item	Description
   Log Retention Policy	The default value is Space priority. Audit logs will be automatically deleted if the size of audit logs on a single node exceeds 1 GB. This function prevents node faults or low performance caused by high disk space occupied by audit logs.
   Execution Result	Statement execution results to be audited. The options include SUCCESS and ERROR. SUCCESS is selected by default.
   Unauthorized Access	Whether to record unauthorized operations. It is enabled by default.
   DQL Operations	DQL operations to be audited. Only SELECT operations are supported.
   DML Operations	Whether to record INSERT, UPDATE, DELETE, MERGE, and COPY operations on tables. All options are selected by default. UPSERT operations belong to INSERT operations.
   DDL Operations	Whether to record the CREATE, DROP, and ALTER operations of specified database objects. By default, they are not recorded.
   Other	Whether to record other operations. TRANSACTION, USER FUNCTION, and CURSOR operations are selected by default. VACUUM, ANALYZE, SPECIAL FUNCTION, and PREPARE STATEMENT operations are not selected by default. NOTE: 8.1.1.100 and later versions support the Other operations audit item. You are advised to select TRANSACTION. Otherwise, statements in a transaction will not be audited. You are advised to select CURSOR. Otherwise, SELECT statements in a cursor will not be audited. The Data Studio client automatically encapsulates SELECT statements using CURSOR.

   Except the audit items listed in Table 1, key audit items in Table 2 are enabled by default on DWS.

   Table 2 Key audit items

   Parameter	Description
   Key audit items	Records successful and failed logins and logout.
   	Records database startup, stop, recovery, and switchover.
   	Records user locking and unlocking.
   	Records the grants and reclaims of user permissions.
   	Records the audit function of the SET operation.

5. Enable or disable audit log dumps.

   For more information, see Enabling Log Dumps.

6. Click Apply.

   If Configuration Status is Applying, the system is saving the settings.

   When the Configuration Status changes to Synchronized, the settings have been saved and taken effect.

   You can click to refresh the configuration information.

Viewing Database Audit Logs

• Method 1: Audit logs will occupy disk space. To prevent excessive disk usage, DWS supports audit log dumping. You can enable the Log Dump function to dump audit logs to OBS (you need to create an OBS bucket for storing audit logs first). For details about how to view the dumped logs, see Enabling Log Dumps.
• Method 2: Use the Log function of LTS to view or download the collected database audit logs. For details, see Checking Cluster Logs.
• Method 3: Database audit logs are stored in the database by default. After connecting to the cluster, you can use the pg_query_audit function to view the logs. For details, see Using Functions to View Database Audit Logs.

Using Functions to View Database Audit Logs

1. Use the SQL client tool to connect to the database cluster. For details, see Connecting to a DWS Cluster.
2. Use the pg_query_audit function to query the audit logs of the current CN. The syntax is as follows:

   1	pg_query_audit(timestamptz starttime,timestamptz endtime,audit_log)

   starttime and endtime indicate the start time and end time of the audit record, respectively. audit_log indicates the physical file path of the queried audit logs. If audit_log is not specified, the audit log information of the current instance is queried.

   For example, view the audit records of the current CN node in a specified period.

   1	SELECT * FROM pg_query_audit('2021-02-23 21:49:00','2021-02-23 21:50:00');

   The query result is as follows:

   1 2 3

   begintime | endtime | operation_type | audit_type | result | username | database | client_conninfo | object_name | command_text | detail_info | transaction_xid | query_id | node_name | session_id | local_port | remote_port ---------------------------+---------------------------+----------------+------------+--------+------------+----------+-----------------+-------------+-----------------+------------------------------------------------------------------+-----------------+----------+--------------+------------------------------+------------+------------- 2021-02-23 21:49:57.76+08 | 2021-02-23 21:49:57.82+08 | login_logout | user_login | ok | dbadmin | gaussdb | gsql@[local] | gaussdb | login db | login db(gaussdb) successfully, the current user is: dbadmin | 0 | 0 | coordinator1 | 140324035360512.667403397820909.coordinator1 | 27777 |

   This record indicates that user dbadmin logged in to the gaussdb database at 2021-02-23 21:49:57.82 (GMT+08:00). After the host specified by log_hostname is started and a client is connected to its IP address, the host name found by reverse DNS resolution is displayed following the at sign (@) in the value of client_conninfo.

3. Use the pgxc_query_audit function to query audit logs of all CNs. The syntax is as follows:

   1	pgxc_query_audit(timestamptz starttime,timestamptz endtime)

   For example, view the audit records of all CN nodes in a specified period.

   1	SELECT * FROM pgxc_query_audit('2021-02-23 22:05:00','2021-02-23 22:07:00') where audit_type = 'user_login' and username = 'user1';

   The query result is as follows:

   1 2 3 4

   begintime | endtime | operation_type | audit_type | result | username | database | client_conninfo | object_name | command_text | detail_info | transaction_xid | query_id | node_name | session_id | local_port | remote_port ----------------------------+----------------------------+----------------+------------+--------+----------+----------+-----------------+-------------+--------------+------------------------------------------------------------+-----------------+----------+--------------+----------------------------------------------+------------+------------- 2021-02-23 22:06:22.219+08 | 2021-02-23 22:06:22.271+08 | login_logout | user_login | ok | user1 | gaussdb | gsql@[local] | gaussdb | login db | login db(gaussdb) successfully, the current user is: user1 | 0 | 0 | coordinator2 | 140689577342720.667404382271356.coordinator | 27782 | 2021-02-23 22:05:51.697+08 | 2021-02-23 22:05:51.749+08 | login_logout | user_login | ok | user1 | gaussdb | gsql@[local] | gaussdb | login db | login db(gaussdb) successfully, the current user is: user1 | 0 | 0 | coordinator1 | 140525048424192.667404351749143.coordinator1 | 27777 |

   The query result shows the successful login records of user1 in to CN1 and CN2.

4. Query the audit records of multiple objects.

   1 2	SET audit_object_name_format TO 'all'; SELECT object_name,result,operation_type,command_text FROM pgxc_query_audit('2022-08-26 8:00:00','2022-08-26 22:55:00') where command_text like '%student%';

   The query result is as follows:

   1 2 3 4 5 6 7 8

   object_name | result | operation_type | command_text ------------------------------------------------------------------+--------+----------------+------------------------------------------------------------------------------------------------------------------ -------------------------------------------- student | ok | ddl | CREATE TABLE student(stuNo int, stuName TEXT); studentscore | ok | ddl | CREATE TABLE studentscore(stuNo int, stuscore int); ["public.student_view01","public.studentscore","public.student"] | ok | ddl | CREATE OR REPLACE VIEW student_view01 AS SELECT * FROM student t1 where t1.stuNo in (select stuNo from studentscore t2 where t1.stuNo = t2.stuNo); ["public.student_view01","public.student","public.studentscore"] | ok | dml | SELECT * FROM student_view01;

   In the object_name column, the table, view, and base table associated with the view are displayed.