Help Center > > Developer Guide> Database Security Management> Viewing Audit Information

Viewing Audit Information

Updated at:Mar 13, 2020 GMT+08:00

Prerequisites

  • Auditing of required items has been enabled. For details about how to enable auditing, see Configuring the Database Audit Log.
  • 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: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

  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('2015-07-15 08:00:00','2015-07-15 09:47:33');
    
    The query result is as follows.
              time          |     type      | result | username |    database    | client_conninfo |  object_name   |                          detail_info                | node_name |            thread_id            | local_port | remote_port
    ------------------------+---------------+--------+----------+----------------+-----------------+----------------+---------------------------------------------------------------+-----------+---------------------------------+------------+-------------
     2015-07-15 08:03:55+08 | login_success | ok     | dbadmin | postgres       | gs_clean@::1    | postgres       | login db(postgres) success,the current user is:dbadmin       | cn_5003   | 139808902997776@490233835920483 | 9000       | 55805

    This audit record indicates that user dbadmin logged in to the postgres database at 08:03:55+08 on July 15, 2015. 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('2019-01-10 17:00:00','2019-01-10 19:00:00') where type = 'login_success' and username = 'user1';
    

    The query result is as follows:

              time          |     type      | result | username | database | client_conninfo | object_name |                     detail_info                      |  node_name   |            thread_id            | 
    local_port | remote_port 
    ------------------------+---------------+--------+----------+----------+-----------------+-------------+------------------------------------------------------+--------------+---------------------------------+-
    -----------+-------------
     2019-01-10 18:06:08+08 | login_success | ok     | user1    | postgres | gsql@[local]    | postgres    | login db(postgres) success,the current user is:user1 | coordinator1 | 139965149210368@600429968516954 | 
    17560      | null
     2019-01-10 18:06:22+08 | login_success | ok     | user1    | postgres | gsql@[local]    | postgres    | login db(postgres) success,the current user is:user1 | coordinator1 | 139965149210368@600429982697548 | 
    17560      | null
     2019-01-10 18:06:54+08 | login_success | ok     | user1    | postgres | gsql@[local]    | postgres    | login db(postgres) success,the current user is:user1 | coordinator2 | 140677694355200@600430014804280 | 
    17562      | null
    (3 rows)

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

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel