Querying Audit Results

Prerequisites

• Audit has been enabled.
• Audit of required items has been enabled.
• 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.
• Audit logs are separately recorded on the database nodes.

Background

• Only users with the AUDITADMIN permission can view audit records. For details about database users and how to create users, see Users.
• The SQL function pg_query_audit is provided by the database for audit query. Its syntax is as follows:

  pg_query_audit(timestamptz startime,timestamptz endtime,audit_log)

  startime 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.

  You can use the SQL function pgxc_query_audit to query audit logs of all CNs. Its syntax is as follows:

  pgxc_query_audit(timestamptz startime,timestamptz endtime)

  

  The difference between the values of the startime and endtime parameters indicates the query period, which can be any value ranging from 00:00:00 of the startime parameter to 23:59:59 of the endtime parameter. Therefore, the startime and endtime parameters must be properly set to ensure that the required audit information is displayed.

Procedure

1. Connect to a database. For details, see Connecting to a Database.
2. Run the following command to query the audit record:

   1	openGauss=# SELECT * FROM pg_query_audit('2021-03-03 21:30:00', '2021-03-03 22:00:00') where type = 'login_success' and username = 'user1';

   The command output is similar to the following:

             time          |     type      | result | userid | username | database | client_conninfo | object_name |                     detail_info                      | node_name |
             thread_id            | local_port | remote_port
   ------------------------+---------------+--------+--------+----------+----------+-----------------+-------------+------------------------------------------------------+-----------+--
   -------------------------------+------------+-------------
    2021-03-03 21:30:31+08 | login_success | ok     | 16398  | user1    | postgres | gsql@[local]    | postgres    | login db(postgres) success, SSL=off| cn_5001   | 1
   39634608699136@668093431256149 | 18000      | null
   (1 row)

   This record indicates that user user1 logged in to the postgres database at 2021-03-03 21:30:31+08. 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. Query the audit records of all CNs.

   1	openGauss=# SELECT * FROM pgxc_query_audit('2021-03-03 21:30:00', '2021-03-03 22:00:00') where type = 'login_success' and username = 'user1';

   The command output is similar to the following:

             time          |     type      | result | userid | username | database | client_conninfo | object_name |                     detail_info                      | node_name |
             thread_id            | local_port | remote_port
   ------------------------+---------------+--------+--------+----------+----------+-----------------+-------------+------------------------------------------------------+-----------+--
   -------------------------------+------------+-------------
    2021-03-03 21:30:31+08 | login_success | ok     | 16398  | user1    | postgres | gsql@[local]    | postgres    | login db(postgres) success, SSL=off | cn_5001   | 1
   39634608699136@668093431256149 | 18000      | null
    2021-03-03 21:36:09+08 | login_success | ok     | 16398  | user1    | postgres | gsql@[local]    | postgres    | login db(postgres) success, SSL=off | cn_5003   | 1
   39779716937472@668093769836394 | 18000      | null
   (2 rows)

   The query result shows the successful login records of user1 in to cn_5001 and cn_5003.

   

   SSL information is recorded at the end of the audit log detail_info. SSL=on indicates that the client is connected using SSL, and SSL=off indicates that the client is not connected using SSL.