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.
NOTE: 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:9 of the endtime parameter. Therefore, the startime and endtime parameters must be properly set to ensure that the required audit information is displayed.

Procedure

Run the following command to connect to the database:

gsql -d postgres -p 8000

postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.

If information similar to the following is displayed, the connection succeeds:

gsql ((openGauss x.x.x build 50dc16a6) compiled at 2020-11-29 05:49:21 commit 1071 last mr 1373)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

Run the following command to query the audit record:

postgres=# 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,the current user is:omm | 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.