doc-exports/docs/dws/dev/dws_06_0048.html

Security Functions

• gs_password_deadline()

  Description: Indicates the number of remaining days before the password of the current user 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: Indicates the number of remaining days before the password of the current user expires. After the password expires, the user cannot log in to the database. This parameter is related to the DDL statement PASSWORD EXPIRATION period used for creating a user.

  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 | gaussdb | 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: SETOF 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 1.
  audit_type	text	Audit type. For details, see Table 2.
  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. In versions earlier than 8.1.1, the audit content of this column is contained in detail_info.
  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 1 Operation types

  Operation Type	Description
  none	Indicates that no audit item is configured. If any audit item is configured, none becomes invalid.
  all	Indicates that all operations are audited. This value overwrites the concurrent configuration of any other audit items. Note that even if this parameter is set to all, not all DDL operations are audited. You need to control the object level of DDL operations by referring to audit_system_object.
  login	Indicates that user login operations are audited.
  logout	Indicates that user logout operations are audited.
  database_process	Indicates that database startup, stop, switchover, and recovery 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.
  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.)
  select	Indicates that the SELECT operations are audited.
  copy	Indicates that the COPY operations are audited.
  user function	Indicates that operations related to user-defined functions, stored procedures, and anonymous blocks 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.
  explain	Indicates that the EXPLAIN operations are audited.
  specialfunc	Indicates that special function invoking operations are audited. Special functions include pg_terminate_backend and pg_cancel_backend.
  insert	Indicates that the INSERT operations are audited.
  update	Indicates that the UPDATE operations are audited.
  delete	Indicates that the DELETE operations are audited.
  merge	Indicates that the MERGE operations are audited.
  show	Indicates that the SHOW operations are audited.
  checkpoint	Indicates that the CHECKPOINT operations are audited.
  barrier	Indicates that the BARRIER operations are audited.
  cluster	Indicates that the CLUSTER operations are audited.
  comment	Indicates that the COMMENT operations are audited.
  clean connection	Indicates that the CLEAN CONNECTION operations are audited.
  prepare statement	Indicates that the PREPARE, EXECUTE, and DEALLOCATE operations are audited.
  set constraints	Indicates that the CONSTRAINTS operations are audited.
  cursor	Indicates that cursor operations are audited.

  Table 2 Audit types

  Audit type	Description
  audit_switch	Enables and disables audit logs.
  login_logout	Indicates that successful user logins and user log-outs are audited.
  system	Indicates that system start and stop operations and instance switch operations are audited.
  sql_parse	Parses SQL statements.
  user_lock	Indicates that successful locking and unlocking operations are audited.
  grant_revoke	Indicates that failed granting and reclaiming of a user's permission are audited.
  violation	Indicates that user's access violation operations are audited.
  ddl	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.)
  dml	Indicates that the INSERT, UPDATE, DELETE, and MERGE operations on a specific table are audited.
  internal_event	Indicates that internal events are audited.
  user_func	Indicates that operations related to user-defined functions, stored procedures, and anonymous blocks are audited.
  special_func	Indicates that successful calls to special functions 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	Anonymous block. If anonymous block completed is displayed, the SQL statement is successfully executed.
  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.
  prepare	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.

• 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."

Parent topic: Functions and Operators