Database Audit

Background

Database security is essential for a database system. GaussDB writes all user operations in the database into audit logs. Database security administrators can use the audit logs to reproduce a series of events that cause faults in the database and identify unauthorized users, unauthorized operations, and the time when these operations are performed.

You need to know the following about the audit function:

The overall audit switch audit_enabled supports dynamic loading. After you change the switch status when the database is running, the change takes effect immediately and you do not need to restart the database. Its default value is on, indicating that the audit function is enabled.
In addition to the overall audit switch, each audit item has an independent switch. An audit item is available only after its own switch is turned on.
The switch of each audit supports dynamic loading. After changing the audit switch status when the database is running, the modification takes effect immediately without restarting the database.

Table 1 lists the audit items supported by GaussDB. For details, see the GUC parameter description in the links.

**Table 1** Audit items
Parameter	Description
User login and logout audit	Parameter: audit_login_logout Its default value is 7, which indicates that the function of user login and logout audit is enabled. 0 indicates that the function of user login and logout audit is disabled. Other values are not recommended.
Database startup, stop, recovery, and switchover audit	Parameter: audit_database_process Its default value is 1, which indicates that the audit of database startup, stop, recovery, and switchover is enabled.
User locking and unlocking audit	Parameter: audit_user_locked Its default value is 1, which indicates that the audit of user locking and unlocking is enabled.
Unauthorized access audit	Parameter: audit_user_violation Its default value is 0, which indicates that the audit of unauthorized access disabled.
Permission granting and revoking audit	Parameter: audit_grant_revoke Its default value is 1, which indicates that the audit of permission granting and revoking is enabled.
Audit of CREATE, ALTER, and DROP operations on database objects	Parameter: audit_system_object Its default value is 67121159, which indicates that only the CREATE, ALTER, and DROP operations on databases, schemas, users, data sources, and node groups are audited.
Audit of INSERT, UPDATE, and DELETE operations on a specific table	Parameter: audit_dml_state Its default value is 0, which indicates that the audit of DML operations (except SELECT) on a specific table is disabled.
SELECT operation audit	Parameter: audit_dml_state_select Its default value is 0, which indicates that the audit of the SELECT operation is disabled.
COPY operation audit	Parameter: audit_copy_exec Its default value is 1, which indicates that the audit of the COPY operation is enabled.
Execution of stored procedures and customized functions	Parameter: audit_function_exec Its default value is 0, which indicates that no execution audit logs of stored procedures and customized functions are recorded.
SET operation audit	Parameter: audit_set_parameter Its default value is 0, which indicates that the audit of the SET operation is disabled.
Transaction ID record	Parameter: audit_xid_info Its default value is 0, which indicates that the function of recording transaction IDs in audit logs is disabled.

Table 2 lists security-related parameters and their default values.

**Table 2** Security-related parameters
Parameter	Description
ssl	Specifies whether the SSL connection is enabled.
require_ssl	Specifies whether the server requires the SSL connection.
ssl_ciphers	Encryption algorithm list supported by the SSL
ssl_cert_file	File containing the SSL server certificate
ssl_key_file	File containing the SSL private key
ssl_ca_file	File containing CA information
ssl_crl_file	File containing CRL information
ssl_cert_notify_time	Specifies the number of days prior to SSL server certificate expiration that a user will receive a reminder.
password_policy	Specifies whether to check the password complexity.
password_reuse_time	Specifies whether to check the reuse days of a new password.
password_reuse_max	Specifies whether to check the reuse times of a new password.
password_lock_time	Duration before a locked account is automatically unlocked
failed_login_attempts	If the number of consecutive login attempts with incorrect passwords reaches this value, the account is locked.
password_encryption_type	Password storage encryption mode
password_min_uppercase	Minimum number of uppercase letters in a password
password_min_lowercase	Minimum number of lowercase letters in a password
password_min_digital	Minimum number of digits in a password
password_min_special	Minimum number of special characters in a password
password_min_length	Minimum password length NOTE: The value of this parameter must be less than or equal to that of password_max_length. Otherwise, a password length error message is displayed upon all password-related operations.
password_max_length	Maximum password length NOTE: The value of this parameter must be greater than or equal to that of password_min_length. Otherwise, a password length error message is displayed upon all password-related operations.
password_effect_time	Password validity period
password_notify_time	Number of days prior to account password expiration that a user is notified
audit_enabled	Specifies whether the audit process is enabled or disabled.
audit_directory	Audit file storage directory
audit_data_format	Audit log file format. Currently, only the binary format is supported.
audit_rotation_interval	Time interval of creating an audit log file. If the difference between the current time and the time when the previous audit log file is created is greater than the value of this parameter, a new audit log file will be generated.
audit_rotation_size	Maximum capacity of an audit log file. If the total number of messages in an audit log exceeds the value of audit_rotation_size, the server will generate a new audit log file.
audit_resource_policy	Policy for determining whether audit logs are preferentially stored by space or time. on indicates that audit logs are preferentially stored by space.
audit_file_remain_time	Minimum duration required for recording audit logs. This parameter is valid only when audit_resource_policy is set to off.
audit_space_limit	Maximum total size of audit log files in a disk
audit_file_remain_threshold	Maximum number of audit files in the audit directory
audit_login_logout	Specifies whether to audit user logins (including login successes and failures) and logouts.
audit_database_process	Specifies whether to audit database startup, stop, switchover, and restoration operations.
audit_user_locked	Specifies whether to audit database user locking and unlocking.
audit_user_violation	Specifies whether to audit beyond-authority operations of a database user.
audit_grant_revoke	Specifies whether to audit user permission granting and reclaiming operations.
audit_system_object	Specifies whether to audit the CREATE, DROP, and ALTER operations on database objects.
audit_dml_state	Specifies whether to audit the INSERT, UPDATE, and DELETE operations on a specific table.
audit_dml_state_select	Specifies whether to audit the SELECT operation.
audit_copy_exec	Specifies whether to audit the COPY operation.
audit_function_exec	Specifies whether to record audit information during execution of stored procedures, anonymous blocks, or customized functions (excluding system functions).
audit_set_parameter	Specifies whether to audit the SET operation.
enableSeparationOfDuty	Specifies whether the separation of duties is enabled.
session_timeout	If the duration of a connection session exceeds the parameter value, the session is automatically disconnected.
auth_iteration_count	Number of iterations during the generation of encrypted information for authentication

Procedure

Connect to a database. For details, see Connecting to a Database.
Check the status of the overall audit switch.
Run the show command to view the value of audit_enabled.
1

openGauss=# SHOW audit_enabled;