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 ------------+----------+------------------------+---------------+--------+-------------------- dbadmin | gaussdb | 2017-06-02 15:28:34+08 | login_success | ok | gsql@[local] (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 ------------+----------+------------------------+--------------+--------+------------------------- (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 ------------+----------+------------------------+--------------+--------+------------------------- (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 ------------+----------+------------------------+---------------+--------+-------------------- dbadmin | postgres | 2017-06-02 15:28:34+08 | login_success | ok | gsql@[local] | 140311900702464 (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 ------------+----------+------------------------+--------------+--------+------------------------- (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 ------------+----------+------------------------+--------------+--------+------------------------- (0 rows)
pg_query_audit()
Description: Displays audit logs of the CN.
Return type: record
The following table describes return columns.
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 |
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 DDL 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. |
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 |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.