Security Functions
Security Functions
gs_encrypt_aes128(encryptstr,keystr)
Description: Encrypts encryptstr strings using keystr as the key 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:
openGauss=# SELECT gs_encrypt_aes128('MPPDB','Asdf1234'); gs_encrypt_aes128 ------------------------------------------------------------------------------------- gwditQLQG8NhFw4OuoKhhQJoXojhFlYkjeG0aYdSCtLCnIUgkNwvYI04KbuhmcGZp8jWizBdR1vU9CspjuzI0lbz12A= (1 row)
NOTE: A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record the function 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 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)
NOTE: A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record the function 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 key and returns decrypted strings. The keystr used for decryption must be consistent with that used for encryption. keystr cannot be empty.
NOTE: This parameter needs to be used with the gs_encrypt_aes128 encryption function.
Return type: text
Example:
openGauss=# SELECT gs_decrypt_aes128('gwditQLQG8NhFw4OuoKhhQJoXojhFlYkjeG0aYdSCtLCnIUgkNwvYI04KbuhmcGZp8jWizBdR1vU9CspjuzI0lbz12A=','1234'); gs_decrypt_aes128 ------------------- MPPDB (1 row)
NOTE: A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record the function 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 those 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)
NOTE: A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record the function 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:
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
Description: Queries login information about a login user.
Return type: tuple
Example:
Check the date, time, and IP address successfully authenticated during the last login.
openGauss=> select * from login_audit_messages(true); username | database | logintime | mytype | result | client_conninfo ----------+----------+------------------------+---------------+--------+----------------- omm | openGauss | 2020-06-29 21:56:40+08 | login_success | ok | gsql@[local] (1 row)
Check the date, time, and IP address that failed to be authenticated during the last login.
openGauss=> select * from login_audit_messages(false) ORDER BY logintime desc limit 1; username | database | logintime | mytype | result | client_conninfo ----------+----------+------------------------+--------------+--------+------------------- omm | openGauss | 2020-06-29 21:57:55+08 | login_failed | failed | [unknown]@[local] (1 row)
Check the number of failed attempts, date, and time since the previous successful authentication.
openGauss=> select * from login_audit_messages(false); username | database | logintime | mytype | result | client_conninfo ----------+----------+------------------------+--------------+--------+------------------- omm | openGauss | 2020-06-29 21:57:55+08 | login_failed | failed | [unknown]@[local] omm | openGauss | 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
Example:
Check the date, time, and IP address successfully authenticated during the last login.
openGauss=> SELECT * FROM login_audit_messages_pid(true); username | database | logintime | mytype | result | client_conninfo | backendid ----------+----------+------------------------+---------------+--------+-----------------+----------------- omm | openGauss | 2020-06-29 21:56:40+08 | login_success | ok | gsql@[local] | 139823109633792 (1 row)
Check the date, time, and IP address that failed to be authenticated during the last login.
openGauss=> SELECT * FROM login_audit_messages_pid(false) ORDER BY logintime desc limit 1; username | database | logintime | mytype | result | client_conninfo | backendid ----------+----------+------------------------+--------------+--------+-------------------+----------------- omm | openGauss | 2020-06-29 21:57:55+08 | login_failed | failed | [unknown]@[local] | 139823109633792 (1 row)
Check the number of failed attempts, date, and time since the previous successful authentication.
openGauss=> SELECT * FROM login_audit_messages_pid(false); username | database | logintime | mytype | result | client_conninfo | backendid ----------+----------+------------------------+--------------+--------+-------------------+----------------- omm | openGauss | 2020-06-29 21:57:55+08 | login_failed | failed | [unknown]@[local] | 139823109633792 omm | openGauss | 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:
openGauss=# SELECT inet_server_addr(); inet_server_addr ------------------ 10.10.0.13 (1 row)
NOTE:
- 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:
openGauss=# SELECT inet_client_addr(); inet_client_addr ------------------ 10.10.0.50 (1 row)
NOTE:
- 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: Views audit logs of the primary database node.
Return type: record
The following table describes return fields.
For details about how to use the function and details about function examples, see Querying Audit Results.
pg_delete_audit
Description: Deletes audit logs in a specified period.
Return value type: void
For details about how to use the function and details about function examples, see Maintaining Audit Logs.