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. The audit function takes effect only after its own switch is enabled.
  • The switch of each audit supports dynamic loading. After changing the switch status of an audit item when the database is running, the modification takes effect immediately and you do not need to restart the database.

Table 1 describes the audit items supported by openGauss.

Table 1 Audit items

Configuration Item

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 is 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 12295, which indicates that the CREATE, ALTER, and DROP operations only 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 0, which indicates that the audit of the COPY operation is disabled.

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 1, which indicates that the audit of the SET operation is enabled.

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

Table 2 Security-related parameters and their default values

Parameter

Default Value

Description

ssl

on

Specifies whether the SSL connection is enabled.

require_ssl

off

Specifies whether the server requires the SSL connection.

ssl_ciphers

ALL

Encryption algorithm list supported by the SSL

ssl_cert_file

server.crt

File containing the SSL server certificate

ssl_key_file

server.key

File containing the SSL private key

ssl_ca_file

cacert.pem

File containing CA information

ssl_crl_file

NULL

File containing CRL information

password_policy

1

Specifies whether to check the password complexity.

password_reuse_time

60

Specifies whether to check the reuse days of a new password.

password_reuse_max

0

Specifies whether to check the reuse times of a new password.

password_lock_time

1

Duration before a locked account is automatically unlocked

failed_login_attempts

10

If the number of consecutive login attempts with incorrect passwords reaches this value, the account is locked.

password_encryption_type

2

Password storage encryption mode

password_min_uppercase

0

Minimum number of uppercase letters in a password

password_min_lowercase

0

Minimum number of lowercase letters in a password

password_min_digital

0

Minimum number of digits in a password

password_min_special

0

Minimum number of special characters in a password

password_min_length

8

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

32

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

90

Password validity period

password_notify_time

7

Number of days prior to account password expiration that a user is notified

audit_enabled

on

Specifies whether the audit process is enabled or disabled.

audit_directory

pg_audit

Audit file storage directory

audit_data_format

binary

Audit log file format. Currently, only the binary format is supported.

audit_rotation_interval

1d

Time interval of creating an audit log file. If the interval between the creation time of the last audit log file and the current time exceeds the parameter value, the server generates a new audit log file.

audit_rotation_size

10MB

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

on

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

90

Minimum duration required for recording audit logs. This parameter is valid only when audit_resource_policy is set to off.

audit_space_limit

1GB

Maximum total size of audit log files in a disk

audit_file_remain_threshold

1048576

Maximum number of audit files in the audit directory

audit_login_logout

7

Specifies whether to audit user logins (including login successes and failures) and logouts.

audit_database_process

1

Specifies whether to audit database startup, stop, switchover, and restoration operations.

audit_user_locked

1

Specifies whether to audit database user locking and unlocking.

audit_user_violation

0

Specifies whether to audit unauthorized access of database users.

audit_grant_revoke

1

Specifies whether to audit user permission granting and reclaiming operations.

audit_system_object

12295

Specifies whether to audit the CREATE, DROP, and ALTER operations on database objects.

audit_dml_state

0

Specifies whether to audit the INSERT, UPDATE, and DELETE operations on a specific table.

audit_dml_state_select

0

Specifies whether to audit the SELECT operation.

audit_copy_exec

0

Specifies whether to audit the COPY operation.

audit_function_exec

0

Specifies whether to record audit information during execution of stored procedures, anonymous blocks, or customized functions (excluding system functions).

audit_set_parameter

1

Specifies whether to audit the SET operation.

enableSeparationOfDuty

off

Specifies whether the separation of duties is enabled.

session_timeout

10min

If the duration of a connection session exceeds the parameter value, the session is automatically disconnected.

auth_iteration_count

10000

Number of iterations during the generation of encrypted information for authentication

Procedure

  1. Log in as the OS user omm to the primary node of the database.

  2. 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 1.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
    Non-SSL connection (SSL connection is recommended when requiring high-security)
    Type "help" for help.
    
    postgres=# 
    
  3. Check the status of the overall audit switch.

    1. Run the show command to view the value of audit_enabled.

      postgres=# SHOW audit_enabled;
      

      If the status is off, run the \q command to exit the database.

    2. 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"
      
  4. 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 other audit functions, but doing so may affect the system performance.

    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 similar to it.

    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.

Feedback
编组 3备份
    openGauss 2024-05-25 00:42:51
    cancel