Querying Audit Results

Prerequisites

• Audit of required items has been enabled. For details about how to enable audit items, see the "Setting Operation Audit" section.
• 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. If an LVS is used to manage the audit logs, you need to locate the specific node where the audit logs are recorded and connect the node to query the corresponding audit logs.

Context

• 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 provided by the database functions as the audit query command:

  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 run the SQL function pgxc_query_audit to query audit logs of all CN nodes. The prototype 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. Use a SQL client to connect to the database.
2. Run the following command to query the audit record:

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

   The query result is as follows.

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

   This record indicates that user dbadmin logged in to the postgres database at 2021-02-23 21:49:57.82+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. Run the following command to query the audit records of all CN nodes:

   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:

            begintime          |          endtime           | operation_type | audit_type | result | username | database | client_conninfo | object_name | command_text |                         detail_info                         | transaction_xid | query_id |  node_name   |            thread_id            | local_port | remote_port 
   ----------------------------+----------------------------+----------------+------------+--------+----------+----------+-----------------+-------------+-----------------+-------------------------------------------------------------+-----------------+----------+--------------+---------------------------------+------------+-------------
    2021-02-23 22:06:22.219+08 | 2021-02-23 22:06:22.271+08 | login_lgout    | user_login | ok     | user1    | postgres | gsql@[local]    | postgres    | login db     | login db(postgres) successfully, the current user is: user1 | 0               | 0        | coordinator2 | 140689577342720@667404382271356 | 27782      | 
    2021-02-23 22:05:51.697+08 | 2021-02-23 22:05:51.749+08 | login_lgout    | user_login | ok     | user1    | postgres | gsql@[local]    | postgres    | login db     | login db(postgres) successfully, the current user is: user1 | 0               | 0        | coordinator1 | 140525048424192@667404351749143 | 27777      | 

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