Overview
Background
Database security is essential for a database system. openGauss writes all user operations in the database to 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 describes the audit items supported by openGauss.
Table 1 Audit items
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. | |
Parameter: audit_database_process Its default value is 1, which indicates that the audit of database startup, stop, recovery, and switchover is enabled. | |
Parameter: audit_user_locked Its default value is 1, which indicates that the audit of user locking and unlocking is enabled. | |
Parameter: audit_user_violation Its default value is 0, which indicates that the audit of unauthorized access disabled. | |
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 a database object | Parameter: audit_system_object Its default value is 12295, which indicates that the CREATE, ALTER, and DROP operations only on databases, schemas, users, and data sources 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. |
Parameter: audit_dml_state_select Its default value is 0, which indicates that the audit of SELECT operations is disabled. | |
Parameter: audit_copy_exec Its default value is 1, which indicates that the audit of COPY operations is enabled. | |
Parameter: audit_function_exec Its default value is 0, which indicates that no execution audit logs of stored procedures and customized functions are recorded. | |
Parameter: audit_set_parameter Its default value is 1, which indicates that the audit of SET operations is enabled. | |
Parameter: audit_xid_info Its default value is 0, which indicates that the function of recording transaction IDs in audit logs is disabled. |
For details about security-related parameters, see Table 2.
Table 2 Security-related parameters
Specifies whether to check the reuse days of a new password. | |
Specifies whether to check the reuse times of a new password. | |
If the number of consecutive login attempts with incorrect passwords reaches this value, the account is locked. | |
Number of days prior to account password expiration that a user is notified | |
Audit log file format. Currently, only the binary format is supported. | |
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 audit_rotation_interval, a new audit log file will be generated. | |
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. | |
Policy for determining whether audit logs are preferentially stored by space or time. on indicates that audit logs are preferentially stored by space. | |
Minimum duration required for recording audit logs. This parameter is valid only when audit_resource_policy is set to off. | |
Specifies whether to audit user logins (including login successes and failures) and logouts. | |
Specifies whether to audit database startup, stop, switchover, and restoration operations. | |
Specifies whether to audit database user locking and unlocking. | |
Specifies whether to audit beyond-authority operations of a database user. | |
Specifies whether to audit user permission granting and reclaiming operations. | |
Specifies whether to audit the CREATE, DROP, and ALTER operations on database objects. | |
Specifies whether to audit the INSERT, UPDATE, and DELETE operations on a specific table. | |
Specifies whether to record audit information during execution of stored procedures, anonymous blocks, or customized functions (excluding system functions). | |
If the duration of a connection session exceeds the parameter value, the session is automatically disconnected. | |
Number of iterations during the generation of encrypted information for authentication |
Procedure
Log in as the OS user omm to the primary node of the database.
Run the following command to connect to the database:
gsql -d postgres -p 8000
postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.
If information similar to the following is displayed, the connection succeeds:
gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. openGauss=#
Check the status of the overall audit switch.
Run the show command to view the value of audit_enabled.
openGauss=# SHOW audit_enabled;
If off is displayed, run the \q command to exit the database and go to the next step. If on is displayed, no further action is required.
Run the following command to enable the audit function. The parameter settings take effect immediately.
gs_guc set -N all -I all -c "audit_enabled=on"
Configure specific audit items.
NOTE:
- After the audit function is enabled, user operations can be recorded into an audit file.
- The default parameter value of each audit item meets security standards. You can enable audit functions as needed, but system performance may be affected.
For example, you can enable the audit switch of the CREATE, DROP, and ALTER operations on all database objects. The methods of modifying other configuration items are the same:
gs_guc reload -N all -I all -c "audit_system_object=12295"
audit_system_object indicates the switch of the audit item, and 12295 indicates the value of the audit switch.