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)
- Checks the date, time, and IP address successfully authenticated during the last login.
- 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)
- Checks the date, time, and IP address successfully authenticated during the last login.
- 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: voidFor 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."
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot