Security Functions

Security Functions

• gs_encrypt(encryptstr, keystr, cryptotype, cryptomode, hashmethod)

  Description: Encrypts an encryptstr string using the keystr key based on the encryption algorithm specified by cryptotype and cryptomode and the HMAC algorithm specified by hashmethod, and returns the encrypted string. cryptotype can be aes128, aes192, aes256, or sm4. cryptomode is cbc. hashmethod can be sha256, sha384, sha512, or sm3. 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. The keystr length is related to the encryption algorithm and contains 1 to KeyLen bytes. If cryptotype is aes128 or sm4, KeyLen is 16; if cryptotype is aes192, KeyLen is 24; if cryptotype is aes256, KeyLen is 32.

  Return type: text

  Length of the return value: at least 4 x [(maclen + 56)/3] bytes and no more than 4 x [(Len + maclen + 56)/3] bytes, where Len indicates the string length (in bytes) before the encryption and maclen indicates the length of the HMAC value. If hashmethod is sha256 or sm3, maclen is 32; if hashmethod is sha384, maclen is 48; if hashmethod is sha512, maclen is 64. That is, if hashmethod is sha256 or sm3, the returned string contains 120 to 4 x [(Len + 88)/3] bytes; if hashmethod is sha384, the returned string contains 140 to 4 x [(Len + 104)/3] bytes; if hashmethod is sha512, the returned string contains 160 to 4 x [(Len + 120)/3] bytes.

  Example:

  1 2 3 4 5

  SELECT gs_encrypt('GaussDB(DWS)', '1234', 'aes128', 'cbc', 'sha256'); gs_encrypt -------------------------------------------------------------------------------------------------------------------------- AAAAAAAAAACcFjDcCSbop7D87sOa2nxTFrkE9RJQGK34ypgrOPsFJIqggI8tl+eMDcQYT3po98wPCC7VBfhv7mdBy7IVnzdrp0rdMrD6/zTl8w0v9/s2OA== (1 row)

  

  • This function is supported by version 8.1.1 or later clusters.
  • A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record this function in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.
  • Do not use the ge_encrypt and gs_encrypt_aes128 functions for the same data table.
• gs_decrypt(decryptstr, keystr, cryptotype, cryptomode, hashmethod)

  Description: Decrypts a decryptstr string using the keystr key based on the encryption algorithm specified by cryptotype and cryptomode and the HMAC algorithm specified by hashmethod, and returns the decrypted string. The keystr used for decryption must be consistent with that used for encryption. keystr cannot be empty.

  Return type: text

  Example:

  1 2 3 4 5

  SELECT gs_decrypt('AAAAAAAAAACcFjDcCSbop7D87sOa2nxTFrkE9RJQGK34ypgrOPsFJIqggI8tl+eMDcQYT3po98wPCC7VBfhv7mdBy7IVnzdrp0rdMrD6/zTl8w0v9/s2OA==', '1234', 'aes128', 'cbc', 'sha256'); gs_decrypt -------------- GaussDB(DWS) (1 row)

  

  • This function is supported by version 8.1.1 or later clusters.
  • A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record this function in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.
  • This function works with the gs_encrypt function, and the two functions must use the same encryption algorithm and HMAC algorithm.
• gs_encrypt_aes128(encryptstr,keystr)

  Description: Encrypts encryptstr strings using keystr as the key and returns encrypted strings. The length of keystr ranges from 1 to 16 bytes. 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).

  Examples

  1 2 3 4 5 6

  SELECT gs_encrypt_aes128('MPPDB','1234'); gs_encrypt_aes128 ------------------------------------------------------------------------------------- gwditQLQG8NhFw4OuoKhhQJoXojhFlYkjeG0aYdSCtLCnIUgkNwvYI04KbuhmcGZp8jWizBdR1vU9CspjuzI0lbz12A= (1 row)

  

  • A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record this function in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.
  • Do not use the ge_encrypt and gs_encrypt_aes128 functions for the same data table.
• gs_decrypt_aes128(decryptstr,keystr)

  Description: Decrypts a decryptstr string using the keystr key and returns the decrypted string. The keystr used for decryption must be consistent with that used for encryption. keystr cannot be empty.

  Return type: text

  Examples:

  1 2 3 4 5

  SELECT gs_decrypt_aes128('gwditQLQG8NhFw4OuoKhhQJoXojhFlYkjeG0aYdSCtLCnIUgkNwvYI04KbuhmcGZp8jWizBdR1vU9CspjuzI0lbz12A=','1234'); gs_decrypt_aes128 ------------------- MPPDB (1 row)

  

  • A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record this function in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.
  • This function works with the gs_encrypt_aes128 function.
• 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)

• gs_hash(hashstr, hashmethod)

  Description: Obtains the digest string of a hashstr string based on the algorithm specified by hashmethod. hashmethod can be sha256, sha384, sha512, or sm3. This function is supported by version 8.1.1 or later clusters.

  Return type: text

  Length of the return value: 64 bytes if hashmethod is sha256 or sm3; 96 bytes if hashmethod is sha384; 128 bytes if hashmethod is sha512

  Example:

  1 2 3 4 5

  SELECT gs_hash('GaussDB(DWS)', 'sha256'); gs_hash -------------------------------------------------------------------------------------------------- e59069daa6541ae20af7c747662702c731b26b8abd7a788f4d15611aa0db608efdbb5587ba90789a983f85dd51766609 (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 | postgres | 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)

• inet_server_addr()

  Description: Displays the server IP address.

  Return type: inet

  Examples:

  1 2 3 4 5

  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

  Examples:

  1 2 3 4 5

  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 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
  audit_type	text	Audit type
  result	text	Operation result
  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
  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

  For details about how to use the function and details about function examples, see section "Querying Audit Results."

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

  For details about how to use the function, see "Querying Audit Results" in the Developer Guide.

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