Security Functions

Security Functions

• gs_encrypt_aes128(encryptstr,keystr)

  Description: Encrypts encryptstr strings using the key derived from keystr and returns encrypted strings. The value of keystr ranges from 8 to 16 bytes and contains at least three types of the following characters: uppercase letters, lowercase letters, digits, and special characters. Currently, the following types of data can be encrypted: numerals supported in the database; character type; RAW in binary type; and DATE, TIMESTAMP, and SMALLDATETIME in date/time type.

  Return type: text

  Length of the return value: At least 92 bytes and no more than (4*[Len/3]+68) bytes, where Len indicates the length of the data before encryption (unit: byte).

  Example:

  1 2 3 4 5

  openGauss=# SELECT gs_encrypt_aes128('MPPDB','1234@abc'); gs_encrypt_aes128 ---------------------------------------------------------------------------------------------- OF1g3+70oeqFfyKiWlpxfYxPnpeitNc6+7nAe02Ttt37fZF8Q+bbEYhdw/YG+0c9tHKRWM6OcTzlB3HnqvX+1d8Bflo= (1 row)

  

  A password is required during the execution of this function. For security purposes, the gsql tool does not record the SQL statements containing the function name in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.

• gs_decrypt_aes128(decryptstr,keystr)

  Description: Decrypts decrypt strings using the key derived from keystr and returns decrypted strings. The keystr used for decryption must be consistent with that used for encryption. keystr cannot be empty.

  

  This function needs to be used with the gs_encrypt_aes128 encryption function.

  Return type: text

  Example:

  1 2 3 4 5

  openGauss=# SELECT gs_decrypt_aes128('OF1g3+70oeqFfyKiWlpxfYxPnpeitNc6+7nAe02Ttt37fZF8Q+bbEYhdw/YG+0c9tHKRWM6OcTzlB3HnqvX+1d8Bflo=','1234@abc'); gs_decrypt_aes128 ------------------- MPPDB (1 row)

  

  A password is required during the execution of this function. For security purposes, the gsql tool does not record the SQL statements containing the function name in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.

• gs_password_deadline

  Description: Indicates the number of remaining days before the password of the current user expires.

  Return type: interval

  Example:

  1 2 3 4 5

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

• gs_password_notifytime()

  Description: Specifies the number of days prior to password expiration that a user will receive a reminder.

  Return type: int32

• login_audit_messages(BOOLEAN)

  Description: Queries login information about a login user.

  Return type: tuple

  Example:

  • Check the date, time, and IP address of the last successful login.

    1 2 3 4 5

    openGauss=> SELECT * FROM login_audit_messages(true); username | database | logintime | mytype | result | client_conninfo ----------+----------+------------------------+---------------+--------+----------------- omm | postgres | 2020-06-29 21:56:40+08 | login_success | ok | gsql@[local] (1 row)

  • Check the number, date, and time of failed attempts since the previous successful login.

    1 2 3 4 5 6

    openGauss=> SELECT * FROM login_audit_messages(false); username | database | logintime | mytype | result | client_conninfo ----------+----------+------------------------+--------------+--------+------------------- omm | postgres | 2020-06-29 21:57:55+08 | login_failed | failed | [unknown]@[local] omm | postgres | 2020-06-29 21:57:53+08 | login_failed | failed | [unknown]@[local] (2 rows)

• login_audit_messages_pid

  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

  

  When the thread pool is enabled, the backendid obtained in the same session may change due to thread switchover. As a result, the return values are different when the function is called for multiple times. You are advised not to call this function when the thread pool is enabled.

  Example:

  • Check the date, time, and IP address of the last successful login.

    1 2 3 4 5

    openGauss=> SELECT * FROM login_audit_messages_pid(true); username | database | logintime | mytype | result | client_conninfo | backendid ----------+----------+------------------------+---------------+--------+-----------------+----------------- omm | postgres | 2020-06-29 21:56:40+08 | login_success | ok | gsql@[local] | 139823109633792 (1 row)

  • Check the number, date, and time of failed attempts since the previous successful login.

    1 2 3 4 5 6

    openGauss=> SELECT * FROM login_audit_messages_pid(false); username | database | logintime | mytype | result | client_conninfo | backendid ----------+----------+------------------------+--------------+--------+-------------------+----------------- omm | postgres | 2020-06-29 21:57:55+08 | login_failed | failed | [unknown]@[local] | 139823109633792 omm | postgres | 2020-06-29 21:57:53+08 | login_failed | failed | [unknown]@[local] | 139823109633792 (2 rows)

• inet_server_addr

  Description: Displays the server IP address.

  Return type: inet

  Example:

  1 2 3 4 5

  openGauss=# SELECT inet_server_addr(); inet_server_addr ------------------ 10.10.0.13 (1 row)

  

  • The client IP address 10.10.0.50 and server IP address 10.10.0.13 are used as an example.
  • If the database is connected to the local PC, the value is empty.
• inet_client_addr

  Description: Displays the client IP address.

  Return type: inet

  Example:

  1 2 3 4 5

  openGauss=# SELECT inet_client_addr(); inet_client_addr ------------------ 10.10.0.50 (1 row)

  

  • The client IP address 10.10.0.50 and server IP address 10.10.0.13 are used as an example.

  • If the database is connected to the local PC, the value is empty.
• pg_query_audit

  Description: Displays audit logs of the current CN.

  Return type: record

  The following table describes return fields.

  Name	Type	Description
  time	timestamp with time zone	Operation time
  type	text	Operation
  result	text	Operation result
  userid	oid	User ID
  username	text	Name of the user who performs the operation
  database	text	Database name
  client_conninfo	text	Client connection information
  object_name	text	Object name
  detail_info	text	Operation details
  node_name	text	Node name
  thread_id	text	Thread ID
  local_port	text	Local port
  remote_port	text	Remote port

• 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

• alldigitsmasking

  Description: Specifies the internal function of the masking policy, which is used to anonymize all characters.

  Parameter: col text, letter character default '0'

  Return type: text

• creditcardmasking

  Description: Specifies the internal function of the masking policy, which is used to anonymize all credit card information.

  Parameter: col text, letter character default 'x'

  Return type: text

• randommasking

  Description: Specifies the internal function of the masking policy. The random policy is used.

  Parameter: col text

  Return type: text

• fullemailmasking

  Description: Specifies the internal function of the masking policy, which is used to anonymize the text before the last period (.) except the at sign (@).

  Parameter: col text, letter character default 'x'

  Return type: text

• basicemailmasking

  Description: Specifies the internal function of the masking policy, which is used to anonymize the text before the first at sign (@).

  Parameter: col text, letter character default 'x'

  Return type: text

• shufflemasking

  Description: Specifies the internal function of the masking policy, which is used to sort characters out of order.

  Parameter: col text

  Return type: text

• regexpmasking

  Description: Specifies the internal function of the masking policy, which is used to replace characters using a regular expression.

  Parameter: col text, reg text, replace_text text, pos INTEGER default 0, reg_len INTEGER default -1

  Return type: text

• gs_encrypt(encryptstr,keystr, encrypttype)

  Description: Encrypts encryptstr strings using keystr as the key and returns encrypted strings based on encrypttype. The value of keystr contains 8 to 16 bytes and at least three types of the following characters: uppercase letters, lowercase letters, digits, and special characters. The value of encrypttype can be aes128 or sm4.

  Return type: text

  Example:

  openGauss=#  SELECT gs_encrypt('MPPDB','Asdf1234','sm4'); 
           gs_encrypt         
   ------------------------------
   ZBzOmaGA4Bb+coyucJ0B8AkIShqc
  (1 row)

  

  A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record the SQL statements containing the function name in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.

• gs_decrypt(decryptstr,keystr,decrypttype)

  Description: Decrypts decrypt strings using keystr as the key and returns decrypted strings based on decrypttype. The decrypttype and keystr used for decryption must be consistent with encrypttype and keystr used for encryption. The value of keystr cannot be empty. The value of decrypttype can be aes128 or sm4.

  This function needs to be used with the gs_encrypt encryption function.

  Return type: text

  Example:

  openGauss=# SELECT gs_decrypt('ZBzOmaGA4Bb+coyucJ0B8AkIShqc','Asdf1234','sm4');
   gs_decrypt 
  ------------
   MPPDB
  (1 row)

  

  A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record the SQL statements containing the function name in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.