Updated on 2024-01-25 GMT+08:00

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:

  • Check 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   
    ------------+----------+------------------------+---------------+--------+--------------------
         dbadmin    | gaussdb | 2017-06-02 15:28:34+08 | login_success | ok     | gsql@[local]
    (1 row)
    
  • Check 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     
    ------------+----------+------------------------+--------------+--------+-------------------------
    (0 rows)
    
  • Check 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     
    ------------+----------+------------------------+--------------+--------+-------------------------
    (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:

  • Check 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
    ------------+----------+------------------------+---------------+--------+--------------------
         dbadmin    | postgres | 2017-06-02 15:28:34+08 | login_success | ok     | gsql@[local] | 140311900702464
    (1 row)
    
  • Check 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
    ------------+----------+------------------------+--------------+--------+-------------------------
    (0 rows)
    
  • Check 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
    ------------+----------+------------------------+--------------+--------+-------------------------
    (0 rows)
    

pg_query_audit()

Description: Displays audit logs of the CN.

Return type: record

The following table describes return columns.

Table 1 Fields returned by the pg_query_audit() function

Column

Type

Description

begintime

timestamp with time zone

Operation start time

endtime

timestamp with time zone

Operation end time

operation_type

text

Operation type. For details, see Table 2.

audit_type

text

Audit type. For details, see Table 3.

result

text

Operation result

username

text

Name of the user who performs the operation

database

text

Database name

client_conninfo

text

Client connection information, that is, gsql, JDBC, or ODBC.

object_name

text

Object name

command_text

text

Command used to perform the operation

detail_info

text

Operation details

transaction_xid

text

Transaction ID

query_id

text

Query ID

node_name

text

Node name

thread_id

text

Thread ID

local_port

text

Local port

remote_port

text

Remote port

Table 2 Operation types

Operation Type

Description

audit_switch

Indicates that the operations of enabling and disabling the audit log function are audited.

login_logout

Indicates that user login and log-out operations are audited.

system

Indicates that the system startup, shutdown, and instance switchover operations are audited.

sql_parse

Indicates that SQL statement parsing operations are audited.

user_lock

Indicates that user locking and unlocking operations are audited.

grant_revoke

Indicates that user permission granting and revoking operations are audited.

violation

Indicates that user's access violation operations are audited.

ddl

Indicates that DDL operations are audited. DDL operations are controlled at a fine granularity based on operation objects. Therefore, audit_system_object is used to control the objects whose DDL operations are to be audited. (The audit function takes effect as long as audit_system_object is configured, no matter whether ddl is set.)

dml

Indicates that the DML operations are audited.

select

Indicates that the SELECT operations are audited.

internal_event

Indicates that internal incident operations are audited.

user_func

Indicates that operations related to user-defined functions, stored procedures, and anonymous blocks are audited.

special_func

Indicates that special function invoking operations are audited. Special functions include pg_terminate_backend and pg_cancel_backend.

copy

Indicates that the COPY operations are audited.

set

Indicates that the SET operations are audited.

transaction

Indicates that transaction operations are audited.

vacuum

Indicates that the VACUUM operations are audited.

analyze

Indicates that the ANALYZE operations are audited.

cursor

Indicates that cursor operations are audited.

anonymous_block

Indicates that the anonymous block operations are audited.

explain

Indicates that the EXPLAIN operations are audited.

show

Indicates that the SHOW operations are audited.

lock_table

Indicates that table lock operations are audited.

comment

Indicates that the COMMENT operations are audited.

preparestmt

Indicates that the PREPARE, EXECUTE, and DEALLOCATE operations are audited.

cluster

Indicates that the CLUSTER operations are audited.

constraints

Indicates that the CONSTRAINTS operations are audited.

checkpoint

Indicates that the CHECKPOINT operations are audited.

barrier

Indicates that the BARRIER operations are audited.

cleanconn

Indicates that the CLEAN CONNECTION operations are audited.

seclabel

Indicates that security label operations are audited.

notify

Indicates that the notification operations are audited.

load

Indicates that the loading operations are audited.

Table 3 Audit types

Audit type

Description

audit_open/audit_close

Indicates that the audit type is operations enabling or disabling audit logs.

user_login/user_logout

Indicates that the audit type is operations and users with successful login/logout.

system_start/system_stop/system_recover/system_switch

Indicates that the audit type is system startup, shutdown, and instance switchover.

sql_wait/sql_parse

Indicates that the audit type is SQL statement parsing.

lock_user/unlock_user

Indicates that the audit type is successful user locking and unlocking.

grant_role/grant__role

Indicates that the audit type is user permission granting and revoking.

user_violation

Indicates that the audit type is unauthorized user access operations.

ddl_database_object

Indicates that successful DDL operations are audited. DDL operations are controlled at a fine granularity based on operation objects. Therefore, audit_system_object is used to control the objects whose DDL operations are to be audited. (The audit function takes effect as long as audit_system_object is configured, no matter whether ddl is set.)

For example, ddl_sequence indicates that the audit type is sequence-related operations.

dml_action_insert/dml_action_delete/dml_action_update/dml_action_merge/dml_action_select

Indicates that the audit type is DML operations such as INSERT, DELETE, UPDATE, and MERGE.

internal_event

Indicates that the audit type is internal events.

user_func

Indicates that the audit type is user-defined functions, stored procedures, or anonymous block operations.

special_func

Indicates that the audit type is special function invocation. Special functions include pg_terminate_backend and pg_cancel_backend.

copy_to/copy_from

Indicates that the audit type is COPY operations.

set_parameter

Indicates that the audit type is SET operations.

trans_begin/trans_commit/trans_prepare/trans_rollback_to/trans_release/trans_savepoint/trans_commit_prepare/trans_rollback_prepare/trans_rollback

Indicates that the audit type is transaction-related operations.

vacuum/vacuum_full/vacuum_merge

Indicates that the audit type is VACUUM operations.

analyze/analyze_verify

Indicates that the audit type is ANALYZE operations.

cursor_declare/cursor_move/cursor_fetch/cursor_close

Indicates that the audit type is cursor-related operations.

codeblock_execute

Indicates that the audit type is anonymous blocks.

explain

Indicates that the audit type is EXPLAIN operations.

show

Indicates that the audit type is SHOW operations.

lock_table

Indicates that the audit type is table locking operations.

comment

Indicates that the audit type is COMMENT operations.

prepare/execute/deallocate

Indicates that the audit type is PREPARE, EXECUTE, or DEALLOCATE operations.

cluster

Indicates that the audit type is CLUSTER operations.

constraints

Indicates that the audit type is CONSTRAINTS operations.

checkpoint

Indicates that the audit type is CHECKPOINT operations.

barrier

Indicates that the audit type is BARRIER operations.

cleanconn

Indicates that the audit type is CLEAN CONNECTION operations.

seclabel

Indicates that the audit type is security label operations.

notify

Indicates that the audit type is notification operations.

load

Indicates that the audit type is loading operations.

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_delete_audit()

Description: Deletes audit logs in a specified period.
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."

1
2
SELECT * FROM pg_delete_audit('2023-01-10 17:00:00','2023-01-10 19:00:00');
ERROR:  For security purposes, it is not allowed to manually delete audit logs