Help Center/ GaussDB/ Developer Guide(Centralized_2.x)/ Database Security Management/ Configuring Database Audit/ Querying Audit Results
Updated on 2023-10-23 GMT+08:00
View PDF
Share

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.

    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-04 08:00:00','2021-03-04 17:00:00');
    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-04 08:00:08+08 | login_success  | ok     | 10     | omm  | postgres | gsql@::1 | postgres    | login db(postgres) success, SSL=off | dn_6001_6002_6003 | 140477687527168@668131208211425 |17778      | 46946

    This record indicates that user omm logs in to database postgres at the time specified by the time column. 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.

    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.