Security Functions

gs_password_deadline()

Description: Displays the time before the password of the current account expires. After the password expires, the system prompts the user to change the password. This parameter is related to the GUC parameter password_effect_time.

Return type: interval

Examples:

1
2
3
4
5

SELECT gs_password_deadline();
  gs_password_deadline   
-------------------------
 83 days 17:44:32.196094
(1 row)

gs_password_expiration()

Description: Displays the time before the password of the current account expires. After the password expires, the user cannot log in to the database. This parameter is related to the DDL statement PASSWORD EXPIRATION period for creating a user. The return value of the function is greater than or equal to -1. If PASSWORD EXPIRATION period is not specified during user creation, the default value is -1, indicating that there is no expiration limit.

Return type: interval

Examples:

1
2
3
4
5

SELECT gs_password_expiration();
  gs_password_expiration   
-------------------------
 29 days 23:59:49.731482
(1 row)

login_audit_messages(flag boolean)

Description: Queries login information about a login user.

Return type: tuple

Examples:

• Checks the date, time, and IP address successfully authenticated during the last login.

  1 2 3 4 5

  SELECT * FROM login_audit_messages(true); username | database | logintime | type | result | client_conninfo | session_id ------------+----------+------------------------+---------------+--------+--------------------+----------------------------------------- dbadmin | gaussdb | 2017-06-02 15:28:34+08 | login_success | ok | gsql@[local] | 1663745305.140192538154752.coordinator1 (1 row)

• Checks the date, time, and IP address that failed to be authenticated during the last login.

  1 2 3 4

  SELECT * FROM login_audit_messages(false) ORDER BY logintime desc limit 1; username | database | logintime | type | result | client_conninfo | session_id ------------+----------+------------------------+--------------+--------+-------------------------+------------ (0 rows)

• Checks the number of failed attempts, date, and time since the previous successful authentication.

  1 2 3 4

  SELECT * FROM login_audit_messages(false); username | database | logintime | type | result | client_conninfo | session_id ------------+----------+------------------------+--------------+--------+-------------------------+------------ (0 rows)

login_audit_messages_pid(flag boolean)

Description: Queries login information about a login user. Different from login_audit_messages, this function queries login information based on backendid. Information about subsequent logins of the same user does not alter the query result of previous logins and cannot be found using this function.

Return type: tuple

Examples:

• Checks the date, time, and IP address successfully authenticated during the last login.

  1 2 3 4 5

  SELECT * FROM login_audit_messages_pid(true); username | database | logintime | type | result | client_conninfo | backendid | session_id ------------+----------+------------------------+---------------+--------+--------------------+----------------------------------------- dbadmin | postgres | 2017-06-02 15:28:34+08 | login_success | ok | gsql@[local] | 140311900702464 | 1663745305.140192538154752.coordinator1 (1 row)

• Checks the date, time, and IP address that failed to be authenticated during the last login.

  1 2 3 4

  SELECT * FROM login_audit_messages_pid(false) ORDER BY logintime desc limit 1; username | database | logintime | type | result | client_conninfo | backendid | session_id ------------+----------+------------------------+--------------+--------+-------------------------+------------ (0 rows)

• Checks the number of failed attempts, date, and time since the previous successful authentication.

  1 2 3 4

  SELECT * FROM login_audit_messages_pid(false); username | database | logintime | type | result | client_conninfo | backendid | session_id ------------+----------+------------------------+--------------+--------+-------------------------+------------ (0 rows)

pg_query_audit()

Description: Displays audit logs of the CN.

Return type: record

The following table describes return columns.

pgxc_query_audit()

Description: Displays audit logs of all CNs.

Return type: record

The return fields of this function are the same as those of the pg_query_audit function.

pg_query_audit_details()

Description: Views audit logs and parses the object_name and object_details columns in audit logs from JSON format. This function is supported only by clusters of version 8.2.1.100 or later.

Return type: record

The following table describes return columns.

Examples:

1. Query the column names of all objects and object types used in the statement.

   1 2	SET audit_object_details = on; SELECT object_name,object_details,result_rows,error_code,command_text FROM pg_query_audit('2023-05-12-03 8:00:00','2023-05-12 22:55:00') where command_text like '%student%';

   The query result is as follows:

   1 2 3 4 5 6

   object_name | object_details | result_rows | error_code | command_text ------------------------------------------------------------------+------------------------------------------------------------------------------------------+-------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------- student | | 0 | | CREATE TABLE student(stuNo int, stuName TEXT); studentscore | | 0 | | CREATE TABLE studentscore(stuNo int, stuscore int); ["public.student_view01","public.studentscore","public.student"] | | 0 | | CREATE OR REPLACE VIEW student_view01 AS SELECT * FROM student t1 where t1.stuNo in (select stuNo from studentscore t2 where t1.stuNo = t2.stuNo); ["public.student_view01","public.student","public.studentscore"] | {"public.student":[{"stuno":"6"},{"stuname":"2"}],"public.studentscore":[{"stuno":"6"}]} | 0 | | SELECT * FROM student_view01

2. The object_details column in the query result displays the columns involved in the execution and their usage types. The columns are recorded in JSON format.
   You can use the pg_query_audit_details function to parse the object_name and object_details columns.

   1	SELECT database,object_name,column_name,type_of_use,use_type FROM pg_query_audit_details('2021-02-03 8:00:00','2024-02-03 22:55:00','current') where command_text like '%student%';

   The query result is as follows:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

   database | object_name | column_name | type_of_use | use_type ----------+-----------------------+-------------+-------------+-------------------- gaussdb | student | | 2 | Access gaussdb | | | 2 | Access gaussdb | studentscore | | 2 | Access gaussdb | | | 2 | Access gaussdb | public.student_view01 | | 2 | Access gaussdb | public.studentscore | | 2 | Access gaussdb | public.student | | 2 | Access gaussdb | | | 2 | Access gaussdb | public.student | stuno | 6 | Access,Conditional gaussdb | public.student | stuname | 2 | Access gaussdb | public.studentscore | stuno | 6 | Access,Conditional gaussdb | public.student_view01 | | 2 | Access gaussdb | public.student | | 2 | Access gaussdb | public.studentscore | | 2 | Access gaussdb | | | 2 | Access (15 rows)

pg_delete_audit()

Description: Deletes audit logs generated in a specified period. This function has been deprecated in 8.1.3 and later cluster versions.
Return type: void

For database security concerns, this function is unavailable. If you call it, the following message is displayed: "ERROR: For security purposes, it is not allowed to manually delete audit logs."

create_audit_csv_foregion_table()

Description: Creates a partitioned foreign table for reading audit logs. You can invoke this function to generate the audit log foreign table pgxc_audit_logs. By reading pgxc_audit_logs, you can read audit logs stored on OBS. This function is supported only by clusters of 8.2.1.300 or later.

Input parameter:

• obs_server: OBS server name. The value is of the text type.
• file_path: OBS file path. The value is of the text type.

Return type: record

Examples:

-- Create an OBS server.
CREATE SERVER obs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS (
   address 'obs.xxx.xxx.xxx.com:xxx',
   encrypt 'true',
   access_key 'xxxxxxxxx',
   secret_access_key 'xxxxxxxxxxxxxx',
   type 'obs' );
-- Invoke this function to generate a foreign table for audit logs.
SELECT * FROM pg_catalog.create_audit_csv_foregion_table('obs_server','/obs-audit/test/');
 create_audit_csv_foregion_table
---------------------------------
 t
(1 row)
-- Read audit logs stored on OBS.
SELECT * FROM pgxc_audit_logs where year=2023 and month=7 and date=24;
 year | month | date |         begintime          |          endtime           | operation_type |    audit_type     | result | username | database | client_conninfo |          object_name           | object_details |command_text| detail_info | transaction_xid |      query_id      | node_name |             session_id             | local_port | remote_port | result_rows | error_code
------+-------+------+----------------------------+----------------------------+----------------+-------------------+--------+----------+----------+-----------------+--------------------------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-----------------+--------------------+-----------+------------------------------------+------------+-------------+-------------+------------
 2023 | 7     | 24   | 2023-07-24 09:56:40.727+08 | 2023-07-24 09:56:42.215+08 | dml            | dml_action_select | ok     | dbadmin  | gaussdb  | gsql@[local]    | public.pgxc_audit_logs         |                | select * f
rom pgxc_audit_logs where year=2023 and date=24 and month=7;