Updated on 2025-05-29 GMT+08:00

Security Functions

gs_encrypt_aes128(encryptstr,keystr)

Description: Encrypts encryptstr strings using keystr as the encryption password 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.

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
6
gaussdb=# SELECT gs_encrypt_aes128('MPPDB','Asdf1234');

                               gs_encrypt_aes128
-------------------------------------------------------------------------------------
kbJdlK5LSlAzqNjVvuHUPCphlkuSeD5n2Hel0HiGaSbDQdCmsz1Ky5ZneOurUr56/jtE/U+BmCw9BgTR2wjQD9440m8=
(1 row)

An encryption 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_encrypt(encryptstr,keystr, encrypttype)

Description: Encrypts encryptstr strings using keystr as the encryption password and returns encrypted strings based on encrypttype.

Return type: text

Parameter

Type

Description

Value Range

encryptstr

text

Data to be encrypted

-

keystr

text

Encryption password

The value ranges from 8 to 16 bytes and contains at least three types of the following characters: uppercase letters, lowercase letters, digits, and special characters.

encrypttype

text

Encryption/Decryption type (case-insensitive)

aes128, sm4, aes128_cbc_sha256, aes256_cbc_sha256, aes128_gcm_sha256, aes256_gcm_sha256, and sm4_ctr_sm3

Example:

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

An encryption 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.

aes128 and sm4 are compatible with earlier versions. The aes128 algorithm uses the AES-128-CBC mode and performs integrity check with SHA1. The sm4 algorithm uses the SM4-CTR mode and does not perform integrity check.

gs_encrypt_bytea(encryptstr, keystr, encrypttype)

Description: Encrypts encryptstr strings using keystr as the encryption password and returns encrypted strings based on encrypttype.

Return type: bytea

Parameter

Type

Description

Value Range

encryptstr

text

Data to be encrypted

-

keystr

text

Encryption password

The value ranges from 8 to 16 bytes and contains at least three types of the following characters: uppercase letters, lowercase letters, digits, and special characters.

encrypttype

text

Encryption/Decryption type (case-insensitive)

aes128_cbc_sha256, aes256_cbc_sha256, aes128_gcm_sha256, aes256_gcm_sha256, and sm4_ctr_sm3

Example:
gaussdb=#  SELECT gs_encrypt_bytea('MPPDB', 'Asdf1234', 'sm4_ctr_sm3'); 
                                         gs_encrypt_bytea         
 ------------------------------------------------------------------------------------------------------------------
 \x90e286971c2c70410def0a2814af4ac44c737926458b66271d9d1547bc937395ca018d7755672fa9dc3cdc6ec4a76001dc0e137f3bc5c8a5c51143561f1d09a848bfdebfec5e
(1 row)

An encryption 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 keystr as the decryption password and returns decrypted strings. The keystr used for decryption must be consistent with that used for encryption. keystr cannot be empty.

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

Return type: text

Example:

1
2
3
4
5
gaussdb=# SELECT gs_decrypt_aes128('kbJdlK5LSlAzqNjVvuHUPCphlkuSeD5n2Hel0HiGaSbDQdCmsz1Ky5ZneOurUr56/jtE/U+BmCw9BgTR2wjQD9440m8=','Asdf1234');
 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 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 decryption password and returns decrypted strings based on decrypttype. The decrypttype and keystr used for decryption must be consistent with encrypttype and keystr used for encryption. keystr cannot be empty.

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

Return type: text

Parameter

Type

Description

Value Range

decryptstr

text

Data to be decrypted

-

keystr

text

Decryption password

The value ranges from 8 to 16 bytes and contains at least three types of the following characters: uppercase letters, lowercase letters, digits, and special characters.

decrypttype

text

Encryption/Decryption type (case-insensitive)

aes128, sm4, aes128_cbc_sha256, aes256_cbc_sha256, aes128_gcm_sha256, aes256_gcm_sha256, and sm4_ctr_sm3

Example:

gaussdb=# 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.

aes128 and sm4 are compatible with earlier versions. The aes128 algorithm uses the AES-128-CBC mode and performs integrity check with SHA1. The sm4 algorithm uses the SM4-CTR mode and does not perform integrity check.

gs_decrypt_bytea(decryptstr, keystr, decrypttype)

Description: Decrypts decrypt strings using keystr as the decryption password and returns decrypted strings based on decrypttype. The decrypttype and keystr used for decryption must be consistent with encrypttype and keystr used for encryption. keystr cannot be empty.

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

Return type: text

Parameter

Type

Description

Value Range

decryptstr

bytea

Data to be decrypted

-

keystr

text

Decryption password

The value ranges from 8 to 16 bytes and contains at least three types of the following characters: uppercase letters, lowercase letters, digits, and special characters.

decrypttype

text

Encryption/Decryption type (case-insensitive)

aes128_cbc_sha256, aes256_cbc_sha256, aes128_gcm_sha256, aes256_gcm_sha256, and sm4_ctr_sm3

Example:

gaussdb=# SELECT gs_decrypt_bytea('\x90e286971c2c70410def0a2814af4ac44c737926458b66271d9d1547bc937395ca018d7755672fa9dc3cdc6ec4a76001dc0e137f3bc5c8a5c51143561f1d09a848bfdebfec5e', 'Asdf1234', 'sm4_ctr_sm3');
 gs_decrypt_bytea 
------------------
 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.

aes_encrypt(str, key_str, init_vector)

Description: Encrypts the string str using the encryption password key_str and initialization vector init_vector based on the AES algorithm.

Parameters in the command above are as follows:
  • str: character string to be encrypted. If str is null, the function will return null.
  • key_str: encryption password. If key_str is null, the function will return null. For security purposes, you are advised to use a 128-bit, 192-bit, or 256-bit secure random number as the key character string if the key length is 128 bits, 192 bits, or 256 bits (determined by the value of block_encryption_mode).
  • init_vector: An initialization variable is provided for the required block encryption mode. The length is greater than or equal to 16 bytes. Bytes greater than 16 bytes are automatically ignored. If neither str nor key_str is null, this parameter cannot be null. Otherwise, an error will be reported. For security purposes, you are advised to ensure that the IV for each encryption is unique in OFB mode and that the IV for each encryption is unpredictable in CBC or CFB mode.

Return type: text

Example:

gaussdb=# SELECT aes_encrypt('huwei123','123456vfhex4dyu,vdaladhjsadad','1234567890123456');
 aes_encrypt
-------------
 u*8\x05c?0
(1 row)
  • This function takes effect only when GaussDB is compatible with the MY type (that is, sql_compatibility = 'B'). For other types, this function is not supported.
  • An encryption 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.
  • Do not call this function during operations related to stored procedures, preventing the risk of sensitive information disclosure. In addition, when using the stored procedure that contains the function, you are advised to filter the parameter information of the function before providing the information for external maintenance personnel to locate the fault. Delete the logs after using them.
  • Do not call the function when debug_print_plan is set to on, preventing the risk of sensitive information disclosure. You are advised to filter parameter information of the function in the log files generated when debug_print_plan is set to on before providing the log files to external maintenance engineers for fault locating. After you finish using the logs, delete them as soon as possible.
  • The SQL_ASCII setting is different from other settings. If the character set of the server is SQL_ASCII, the server interprets the byte values 0 to 127 according to the ASCII standard. The byte values 128 to 255 are regarded as the characters that cannot be parsed. If this parameter is set to SQL_ASCII, no code conversion occurs. When this function calls the third-party OpenSSL library, the returned data is non-ASCII data. Therefore, when the character set of the database server is set to SQL_ASCII, the encoding of the client must also be set to SQL_ASCII. Otherwise, an error is reported. The database does not convert or verify non-ASCII characters.

aes_decrypt(pass_str, key_str, init_vector)

Description: Decrypts the string str using the decryption password key_str and initialization vector init_vector based on the AES algorithm.

Parameters in the command above are as follows:
  • pass_str: character string to be decrypted. If pass_str is null, the function will return null.
  • key_str: decryption password. If key_str is null, the function will return null. For security purposes, you are advised to use a 128-bit, 192-bit, or 256-bit secure random number as the key character string if the key length is 128 bits, 192 bits, or 256 bits (determined by the value of block_encryption_mode).
  • init_vector: An initialization variable is provided for the required block decryption mode. The length is greater than or equal to 16 bytes. Bytes greater than 16 bytes are automatically ignored. If neither pass_str nor key_str is null, this parameter cannot be null. Otherwise, an error will be reported. For security purposes, you are advised to ensure that the IV for each encryption is unique in OFB mode and that the IV for each encryption is unpredictable in CBC or CFB mode.

Return type: text

Example:

gaussdb=# SELECT aes_decrypt(aes_encrypt('huwei123','123456vfhex4dyu,vdaladhjsadad','1234567890123456'),'123456vfhex4dyu,vdaladhjsadad','1234567890123456');
 aes_decrypt
-------------
 huwei123
(1 row)
  • This function takes effect only when GaussDB is compatible with the MY type (that is, sql_compatibility = 'B'). For other types, this function is not supported.
  • 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.
  • Do not call this function during operations related to stored procedures, preventing the risk of sensitive information disclosure. In addition, when using the stored procedure that contains the function, you are advised to filter the parameter information of the function before providing the information for external maintenance personnel to locate the fault. Delete the logs after using them.
  • Do not call the function when debug_print_plan is set to on, preventing the risk of sensitive information disclosure. You are advised to filter parameter information of the function in the log files generated when debug_print_plan is set to on before providing the log files to external maintenance engineers for fault locating. After you finish using the logs, delete them as soon as possible.
  • To ensure successful decryption, ensure that the values of block_encryption_mode, key_str and IV are the same as those during encryption.
  • Due to encoding differences, encrypted data cannot be directly copied from the gsql client for decryption. In this scenario, the decryption result may not be the character string before encryption.
  • The SQL_ASCII setting is different from other settings. If the character set of the server is SQL_ASCII, the server interprets the byte values 0 to 127 according to the ASCII standard. The byte values 128 to 255 are regarded as the characters that cannot be parsed. If this parameter is set to SQL_ASCII, no code conversion occurs. When this function calls the third-party OpenSSL library, the returned data is non-ASCII data. Therefore, when the character set of the database server is set to SQL_ASCII, the encoding of the client must also be set to SQL_ASCII. Otherwise, an error is reported. The database does not convert or verify non-ASCII characters.

gs_digest(input_string, hash_algorithm)

Description: Hashes the input string using the specified hash algorithm and returns a hexadecimal number.

Parameters in the command above are as follows:
  • input_string: character string to be hashed. The value cannot be null.
  • hash_algorithm: specifies the hash algorithm. Currently, SHA-256, SHA-384, SHA-512, and SM3 are supported. Both uppercase and lowercase letters are supported. If an unsupported hash algorithm is used, an error is reported.

Return type: text

Example:

1
2
3
4
5
gaussdb=# SELECT pg_catalog.gs_digest('gaussdb', 'sha256');
                            gs_digest
------------------------------------------------------------------
 4dc50d746f4e04f9b446986b34a0050e358fbfb8bc1fba314c54b52a417b0b8e
(1 row)

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
gaussdb=# 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
    gaussdb=> SELECT * FROM login_audit_messages(true);
     username | database |       logintime        |    mytype     | result | client_conninfo
    ----------+----------+------------------------+---------------+--------+-----------------
     omm      | testdb | 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
    gaussdb=>  SELECT * FROM login_audit_messages(false);
     username | database |       logintime        |    mytype    | result |  client_conninfo
    ----------+----------+------------------------+--------------+--------+-------------------
     omm      | testdb | 2020-06-29 21:57:55+08 | login_failed | failed | [unknown]@[local]
     omm      | testdb | 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
    gaussdb=> SELECT * FROM login_audit_messages_pid(true);
     username | database |       logintime        |    mytype     | result | client_conninfo |    backendid
    ----------+----------+------------------------+---------------+--------+-----------------+-----------------
     omm      | testdb | 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
    gaussdb=> SELECT * FROM login_audit_messages_pid(false);
     username | database |       logintime        |    mytype    | result |  client_conninfo  |    backendid
    ----------+----------+------------------------+--------------+--------+-------------------+-----------------
     omm      | testdb | 2020-06-29 21:57:55+08 | login_failed | failed | [unknown]@[local] | 139823109633792
     omm      | testdb | 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
gaussdb=# 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 a local connection is used, the value is empty.

inet_client_addr

Description: Displays the client IP address.

Return type: inet

Example:

1
2
3
4
5
gaussdb=# 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 a local connection is used, the value is empty.

gs_query_audit(starttime, endtime, audit_log)

Description: Views audit logs of the primary database node. In the multi-tenancy scenario, global audit logs are returned if this function is called in a non-PDB, and audit logs related to a PDB are returned if this function is called in the PDB.

Parameters: timestamptz starttime and timestamptz endtime[,audit_log]

Return type: record

The following table describes return columns.

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

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
gaussdb=# SELECT * FROM gs_query_audit('2025-01-14 00:00:00','2025-01-14 00:01:00');
          time          |      type      | result | userid | username  | database  |   client_conninfo   | object_name |                           detail_info                           | node_name |            thread_i
d            | local_port | remote_port 
------------------------+----------------+--------+--------+-----------+-----------+---------------------+-------------+-----------------------------------------------------------------+-----------+--------------------
-------------+------------+-------------
 2025-01-14 00:00:00+08 | internal_event | ok     | 0      | [unknown] | [unknown] | [unknown]@[unknown] | file        | create a new audit file                                         | datanode  | 139815485437696@790099200629980 | 45002      | (null)
 2025-01-14 00:00:59+08 | login_success  | ok     | 10     | omm       | postgres  | SPM Polling@[local] | postgres    | login db(postgres) success,the current user is:omm, SSL=off    | datanode  | 139814503380736@790099259651348 | 45002      | null
 2025-01-14 00:00:59+08 | user_logout    | ok     | 10     | omm       | postgres  | SPM Polling@[local] | postgres    | logout db(postgres) success                                     | datanode  | 139814503380736@790099259659065 | 45002      | null
 2025-01-14 00:00:59+08 | login_success  | ok     | 10     | omm       | postgres  | SPM Polling@[local] | postgres    | login db(postgres) success,the current user is:omm, SSL=off    | datanode  | 139814385874688@790099259667405 | 45002      | null
 2025-01-14 00:00:59+08 | user_logout    | ok     | 10     | omm       | postgres  | SPM Polling@[local] | postgres    | logout db(postgres) success                                     | datanode  | 139814385874688@790099259669311 | 45002      | null
(5 rows)

pg_query_audit(starttime, endtime, audit_log)

Description: Views audit logs of the primary database node. In the multi-tenancy scenario, global audit logs are returned if this function is called in a non-PDB, and audit logs related to a PDB are returned if this function is called in the PDB.

Parameters: timestamptz starttime and timestamptz endtime[,audit_log]

Return type: record

The following table describes return columns.

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

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
gaussdb=# SELECT * FROM pg_query_audit('2025-01-14 00:00:00','2025-01-14 00:01:00');
          time          |      type      | result | userid | username  | database  |   client_conninfo   | object_name |                           detail_info                           | node_name |            thread_i
d            | local_port | remote_port 
------------------------+----------------+--------+--------+-----------+-----------+---------------------+-------------+-----------------------------------------------------------------+-----------+--------------------
-------------+------------+-------------
 2025-01-14 00:00:00+08 | internal_event | ok     | 0      | [unknown] | [unknown] | [unknown]@[unknown] | file        | create a new audit file                                         | datanode  | 139815485437696@790099200629980 | 45002      | (null)
 2025-01-14 00:00:59+08 | login_success  | ok     | 10     | omm       | postgres  | SPM Polling@[local] | postgres    | login db(postgres) success,the current user is:omm, SSL=off    | datanode  | 139814503380736@790099259651348 | 45002      | null
 2025-01-14 00:00:59+08 | user_logout    | ok     | 10     | omm       | postgres  | SPM Polling@[local] | postgres    | logout db(postgres) success                                     | datanode  | 139814503380736@790099259659065 | 45002      | null
 2025-01-14 00:00:59+08 | login_success  | ok     | 10     | omm       | postgres  | SPM Polling@[local] | postgres    | login db(postgres) success,the current user is:omm, SSL=off    | datanode  | 139814385874688@790099259667405 | 45002      | null
 2025-01-14 00:00:59+08 | user_logout    | ok     | 10     | omm       | postgres  | SPM Polling@[local] | postgres    | logout db(postgres) success                                     | datanode  | 139814385874688@790099259669311 | 45002      | null
(5 rows)

gs_delete_audit(starttime, endtime)

Description: Deletes audit logs in a specified period. In the multi-tenancy scenario, global audit logs in a specified period can be deleted when this function is called in a non-PDB, and audit logs related to a PDB in a specified period can be deleted when this function is called in the PDB.

Parameters: timestamptz starttime and timestamptz endtime

Return type: void

Example:

gaussdb=# SELECT * FROM gs_delete_audit('2025-01-14 00:00:00','2025-01-14 00:01:00');
 gs_delete_audit 
-----------------

(1 row)

pg_delete_audit(starttime, endtime)

Description: Deletes audit logs in a specified period. In the multi-tenancy scenario, global audit logs in a specified period can be deleted when this function is called in a non-PDB, and audit logs related to a PDB in a specified period can be deleted when this function is called in the PDB.

Parameters: timestamptz starttime and timestamptz endtime

Return type: void

Example:

gaussdb=# SELECT * FROM pg_delete_audit('2025-01-14 00:00:00','2025-01-14 00:01:00');
 pg_delete_audit 
-----------------

(1 row)

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