MogDB
Ecological Tools
Doc Menu

Configuring Database Audit

Overview

Background

Database security is essential for a database system. MogDB 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](#Table 3.1.1) describes the audit items supported by MogDB.

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](#Table 3.1.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 mogdb -p 8000

    mogdb 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 ((MogDB 1.1.0 build 5be05d82) 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.
        
    mogdb=# 
  3. Check the status of the overall audit switch.

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

      mogdb=# 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.

    img 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.

Querying Audit Results

Prerequisites

  • Audit has been enabled.
  • Audit of required items has been enabled.
  • The database is running properly and a series of addition, modification, deletion, and query operations have been executed in the database. Otherwise, no audit result is generated.
  • Audit logs are separately recorded on the database nodes.

Background

  • Only users with the AUDITADMIN permission can view audit records. For details about database users and how to create users, see Users.
  • The SQL function pg_query_audit is provided by the database for audit query. Its syntax is as follows:

    pg_query_audit(timestamptz startime,timestamptz endtime,audit_log)

    startime and endtime indicate the start time and end time of the audit record, respectively. audit_log indicates the physical file path of the queried audit logs. If audit_log is not specified, the audit log information of the current instance is queried.

    img NOTE: The difference between the values of the startime and endtime parameters indicates the query period, which can be any value ranging from 00:00:00 of the startime parameter to 23:59:9 of the endtime parameter. Therefore, the startime and endtime parameters must be properly set to ensure that the required audit information is displayed.

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 mogdb -p 8000

    mogdb 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 ((MogDB 1.1.0 build 5be05d82) 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.
        
    mogdb=# 
  3. Run the following command to query the audit record:

    mogdb=# SELECT * FROM pg_query_audit('2015-07-15 08:00:00','2015-07-15 09:47:33');

    The command output is similar to the following:

              time          |     type      | result | username |    database    | client_conninfo |  object_name   |                          detail_info                | node_name |            thread_id            | local_port | remote_port
    ------------------------+---------------+--------+----------+----------------+-----------------+----------------+---------------------------------------------------------------+-----------+---------------------------------+------------+-------------
    2015-07-15 08:03:55+08 | login_success | ok     | omm | mogdb       | gsql@::1    | mogdb       | login db(mogdb) success,the current user is:omm       | dn_5003   | 139808902997776@490233835920483 | 9000       | 55805

    This audit record indicates that user omm logged in to the mogdb database at 08:03:55+08 on July 15, 2015. After the host specified by log_hostname is started and a client is connected to its IP address, the host name found by reverse DNS resolution is displayed following the at sign (@) in the value of client_conninfo.

Maintaining Audit Logs

Prerequisites

You have the audit permission.

Background

  • [Table 1](#Table 3.3.1) lists the configuration parameters related to audit logs and the parameter descriptions.

    Table 1 Configuration parameters of audit logs

    Parameter Description Default Value
    audit_directory Audit file storage directory /var/log/mogdb/User name/pg_audit
    audit_resource_policy Policy for saving audit logs on (indicating that the space configuration policy is used)
    audit_space_limit Maximum storage space occupied by audit files 1GB
    audit_file_remain_time Minimum period for storing audit log files 90
    audit_file_remain_threshold Maximum number of audit files in the audit directory 1048576

    img NOTE:

    If gs_om is used for MogDB deployment, audit logs are stored in /var/log/mogdb/Username/pg_audit.

  • The pg_delete_audit function is used to delete audit logs and is an internal SQL function of the database. Its syntax is as follows:

    pg_delete_audit(timestamp startime,timestamp endtime)

    startime and endtime indicate the audit record start time and end time, respectively.

  • Audit content is commonly recorded to database tables or OS files. [Table 2](#Table 3.3.2) lists the advantages and disadvantages of the two record methods.

    Table 2 Comparison between the two record methods

    Mode Advantage Disadvantage
    Record in tables Users do not need to maintain audit logs. Any users having certain permissions to access database objects can access the audit tables. If a user illegally performs operations on the audit tables, the audit records may become inaccurate.
    Record in OS files This method has higher security because a user with the permission to access the database may not have the permission to access the OS files. Users need to maintain audit logs.

    For database security purposes, MogDB adopts the second method to save audit results for reliability.

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 mogdb -p 8000

    mogdb 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 ((MogDB 1.1.0 build 5be05d82) 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.
        
    mogdb=# 
  3. Select a method to maintain audit logs.

    • Automatic deletion of audit logs

      If the storage space occupied by audit files or the number of audit files reaches the upper limit, the system automatically deletes the oldest audit files and records deletion information to audit logs.

      img NOTE: By default, the allowed maximum storage space that can be occupied by audit files is set to 1024 MB. Users can set the value as required.

      Configure the allowed maximum size of storage space occupied by audit files (audit_space_limit).

      1. View the current value.

        mogdb=# SHOW audit_space_limit;
         audit_space_limit
        -------------------
         1GB
        (1 row)

        If the command output is not 1 GB (1024 MB), run the \q command to exit the database.

      2. Run the following command to set the parameter to its default value 1024MB:

        gs_guc reload -N all -I all -c "audit_space_limit=1024MB"

      Configure the maximum number of audit files (audit_file_remain_threshold).

      1. View the current value.

        mogdb=# SHOW audit_file_remain_threshold;
         audit_file_remain_threshold
        -----------------------------
         1048576
        (1 row)

        If the command output is not 1048576, run the \q command to exit the database.

      2. Run the following command to set the parameter to its default value 1048576:

        gs_guc reload -N all -I all -c "audit_file_remain_threshold=1048576"
    • Manual backup of audit files

      If the storage space occupied by audit files or the number of audit logs exceeds the threshold specified by the configuration file, the system automatically deletes the oldest audit files. Therefore, you are advised to periodically save important audit logs.

      1. Run the show command to view the directory (audit_directory) where audit files are saved.

        mogdb=# SHOW audit_directory;
      2. Copy the entire audit directory elsewhere to save it.
    • Manual deletion of audit files

      Run the pg_delete_audit command to manually delete the audit records generated during a specified period of time.

      Example: Manually delete the audit records generated from September 20, 2012 to September 21, 2012.

      mogdb=# SELECT pg_delete_audit('2012-09-20 ','2012-09-21');

Configuring File Permission Security Policies

Background

During its installation, the database sets permissions for its files, including files (such as log files) generated during the running process. File permissions are set as follows:

  • The permission of program directories in the database is set to 0750.
  • The permission for data file directories in the database is set to 0700.

    During MogDB deployment, the directory specified by the tmpMppdbPath parameter in the XML configuration file is created for storing .s.PGSQL.* files. If the parameter is not specified, the /tmp/$USER_mppdb directory is created. The directory and file permission is set to 0700.

  • The permissions of data files and audit logs of the database, as well as data files generated by other database programs, are set to 0600. The permission of run logs is equal to or lower than 0640 by default.
  • Common OS users are not allowed to modify or delete database files and log files.

Directory and File Permissions of Database Programs

[Table 1](#Table 3.4.1) lists some of program directories and file permissions of the installed database.

Table 1 Program directories and file permissions

File or Directory Parent Contents Permissions
bin - 0700
lib - 0700
share - 0700
data (database node/primary database node) - 0700
base Instance data directory 0700
global Instance data directory 0700
pg_audit Instance data directory (configurable) 0700
pg_log Instance data directory (configurable) 0700
pg_xlog Instance data directory 0700
postgresql.conf Instance data directory 0600
pg_hba.conf Instance data directory 0600
postmaster.opts Instance data directory 0600
pg_ident.conf Instance data directory 0600
gs_initdb bin 0700
gs_dump bin 0700
gs_ctl bin 0700
gs_guc bin 0700
gsql bin 0700
archive_status pg_xlog 0700
libpq.so.5.5 lib 0600

Suggestion

During the installation, the database automatically sets permissions for its files, including files (such as log files) generated during the running process. The specified permissions meet permission requirements in most scenarios. If you have any special requirements for the related permissions, you are advised to periodically check the permission settings to ensure that the permissions meet the product requirements.