Updated on 2024-06-11 GMT+08:00

Dumping the Database Audit Logs

GaussDB(DWS) records information (audit logs) about connections and user activities in your database. The audit logs help you monitor the database to ensure security, rectify faults, and locate historical operation records. GaussDB(DWS) audit logs are stored in the database by default. You can dump the audit logs to OBS so that users who monitor database activities can view the logs.

  • This function cannot be used if OBS is not available.
  • Data may during cluster specifications change, CN addition, or CN deletion. You are advised to disable audit log dump during these operations.
  • After audit log dumping is enabled, audit logs will be dumped if the size of saved audit logs exceeds 1 GB. This may cause abnormal query results. Exercise caution when performing this operation.
  • If a CN node is faulty, data on the CN node may be lost.
  • Version support for the audit log dump directory partition is as follows:
    • For 8.1.3.x clusters, only 8.1.3.322 and later versions support this feature. For 8.2.0.x clusters, only 8.2.0.106 and later versions support this feature. By default, the audit log dump directory partition is enabled and cannot be disabled.
    • To use this feature in earlier versions, contact technical support to upgrade your cluster first. Manually enable this feature after the upgrade.

Prerequisites

After a GaussDB(DWS) cluster is created, you can enable log dump for it to dump audit logs to OBS. Before enabling audit log dump, ensure the following conditions are met:

Enabling Audit Log Dumps

  1. Log in to the GaussDB(DWS) management console.
  2. In the navigation pane on the left, choose Clusters > Dedicated Clusters.
  3. In the cluster list, click the name of the cluster for which you want to enable audit log dump. In the navigation pane, choose Security Settings.
  4. In the Audit Settings area, enable Audit Log Dump.

    indicates that the function is enabled.

    When you enable audit log dump for a project in a region for the first time, the system prompts you to create an agency named DWSAccessOBS. After the agency is created, GaussDB(DWS) can dump audit logs to OBS.

    By default, only accounts or users with Security Administrator permissions can query and create agencies. IAM users under an account do not have the permission to query or create agencies by default. Contact a user with that permission and complete the authorization on the current page.

    • OBS Foreign Table: Audit logs can be read using OBS foreign tables during dumping. Audit logs are stored in CSV format and compressed in GZ format.
    • OBS Bucket: Name of the OBS bucket used to store the audit data. If no OBS bucket is available, click View OBS Bucket to access the OBS console and create one. For details, see Console Operation Guide > Managing Buckets > Creating a Bucket in the Object Storage Service User Guide.
    • OBS Path: User-defined directory on OBS for storing audit files. Different directory levels are separated by forward slashes (/). The value is a string containing 1 to 50 characters, which cannot start with a forward slash (/). If the entered OBS path does not exist, the system creates one and dumps data to it.
    • Dump Interval (Minute): Interval based on which GaussDB(DWS) periodically dumps data to OBS. The value range is 5 to 43200. The unit is minute.

  5. Click Apply.

    If Configuration Status is Applying, the system is saving the settings.

    When the status changes to Synchronized, the configurations are saved and take effect.

Modifying Audit Log Dump Configurations

After audit log dump is enabled, you can modify the dump configuration. For example, you can modify the OBS bucket and path for storing logs and the dump period.

The procedure is as follows:

  1. Log in to the GaussDB(DWS) management console.
  2. In the navigation pane on the left, choose Clusters > Dedicated Clusters.
  3. In the cluster list, click the name of the cluster for which you want to modify the audit log dump configurations. In the navigation pane, choose Security.
  4. In the Audit Settings area, modify the Audit Log Dump configurations.
  5. Click Apply.

    If Configuration Status is Applying, the system is saving the settings.

    When the status changes to Synchronized, the configurations are saved and take effect.

Viewing Audit Log Dumps

After audit log dump is enabled, you can view the dumped audit logs on OBS.

The procedure is as follows:

  1. Log in to the GaussDB(DWS) management console.
  2. In the navigation pane on the left, choose Clusters > Dedicated Clusters.
  3. In the cluster list, click the name of the target cluster for which you want to view the log dump history. In the navigation pane, choose Security.
  4. In the Audit Settings area, click View Dump Record.
  5. In the Audit Log Dump Records dialog box, click View OBS Bucket. The OBS console page is displayed.
  6. Select the OBS bucket and folder where the logs are stored to view the log files.

    You can download and decompress the files to view. The fields of audit log files are described as follows:

    Table 1 Log file fields

    Field

    Type

    Description

    begintime

    timestamp with time zone

    Operation start time

    endtime

    timestamp with time zone

    Operation end time

    operation_type

    text

    Operation type. For details, see Table 2.

    audit_type

    text

    Audit type. For details, see Table 3.

    result

    text

    Operation result

    username

    text

    Name of the user who performs the operation

    database

    text

    Database name

    client_conninfo

    text

    Client connection information, that is, gsql, JDBC, or ODBC.

    object_name

    text

    Object name

    command_text

    text

    Command used to perform the operation

    detail_info

    text

    Operation details

    transaction_xid

    text

    Transaction ID

    query_id

    text

    Query ID

    node_name

    text

    Node name

    thread_id

    text

    Thread ID

    local_port

    text

    Local port

    remote_port

    text

    Remote port

    Table 2 Operation types

    Operation Type

    Description

    audit_switch

    Indicates that the operations of enabling and disabling the audit log function are audited.

    login_logout

    Indicates that user login and log-out operations are audited.

    system

    Indicates that the system startup, shutdown, and instance switchover operations are audited.

    sql_parse

    Indicates that SQL statement parsing operations are audited.

    user_lock

    Indicates that user locking and unlocking operations are audited.

    grant_revoke

    Indicates that user permission granting and revoking operations are audited.

    violation

    Indicates that user's access violation operations are audited.

    ddl

    Indicates that DDL operations are audited. DDL operations are controlled at a fine granularity based on operation objects. Therefore, audit_system_object is used to control the objects whose DDL operations are to be audited. (The audit function takes effect as long as audit_system_object is configured, no matter whether ddl is set.)

    dml

    Indicates that the DML operations are audited.

    select

    Indicates that the SELECT operations are audited.

    internal_event

    Indicates that internal incident operations are audited.

    user_func

    Indicates that operations related to user-defined functions, stored procedures, and anonymous blocks are audited.

    special_func

    Indicates that special function invoking operations are audited. Special functions include pg_terminate_backend and pg_cancel_backend.

    copy

    Indicates that the COPY operations are audited.

    set

    Indicates that the SET operations are audited.

    transaction

    Indicates that transaction operations are audited.

    vacuum

    Indicates that the VACUUM operations are audited.

    analyze

    Indicates that the ANALYZE operations are audited.

    cursor

    Indicates that cursor operations are audited.

    anonymous_block

    Indicates that the anonymous block operations are audited.

    explain

    Indicates that the EXPLAIN operations are audited.

    show

    Indicates that the SHOW operations are audited.

    lock_table

    Indicates that table lock operations are audited.

    comment

    Indicates that the COMMENT operations are audited.

    preparestmt

    Indicates that the PREPARE, EXECUTE, and DEALLOCATE operations are audited.

    cluster

    Indicates that the CLUSTER operations are audited.

    constraints

    Indicates that the CONSTRAINTS operations are audited.

    checkpoint

    Indicates that the CHECKPOINT operations are audited.

    barrier

    Indicates that the BARRIER operations are audited.

    cleanconn

    Indicates that the CLEAN CONNECTION operations are audited.

    seclabel

    Indicates that security label operations are audited.

    notify

    Indicates that the notification operations are audited.

    load

    Indicates that the loading operations are audited.

    Table 3 audit_type parameters

    Parameter

    Description

    audit_open/audit_close

    Indicates that the audit type is operations enabling or disabling audit logs.

    user_login/user_logout

    Indicates that the audit type is operations and users with successful login/logout.

    system_start/system_stop/system_recover/system_switch

    Indicates that the audit type is system startup, shutdown, and instance switchover.

    sql_wait/sql_parse

    Indicates that the audit type is SQL statement parsing.

    lock_user/unlock_user

    Indicates that the audit type is successful user locking and unlocking.

    grant_role/revoke__role

    Indicates that the audit type is user permission granting and revoking.

    user_violation

    Indicates that the audit type is unauthorized user access operations.

    ddl_database_object

    Indicates that successful DDL operations are audited. DDL operations are controlled at a fine granularity based on operation objects. So, audit_system_object is used to control the objects whose DDL operations are to be audited. (The audit function takes effect as long as audit_system_object is configured, no matter whether ddl is set.)

    For example, ddl_sequence indicates that the audit type is sequence-related operations.

    dml_action_insert/dml_action_delete/dml_action_update/dml_action_merge/dml_action_select

    Indicates that the audit type is DML operations such as INSERT, DELETE, UPDATE, and MERGE.

    internal_event

    Indicates that the audit type is internal events.

    user_func

    Indicates that the audit type is user-defined functions, stored procedures, or anonymous block operations.

    special_func

    Indicates that the audit type is special function invocation. Special functions include pg_terminate_backend and pg_cancel_backend.

    copy_to/copy_from

    Indicates that the audit type is COPY operations.

    set_parameter

    Indicates that the audit type is SET operations.

    trans_begin/trans_commit/trans_prepare/trans_rollback_to/trans_release/trans_savepoint/trans_commit_prepare/trans_rollback_prepare/trans_rollback

    Indicates that the audit type is transaction-related operations.

    vacuum/vacuum_full/vacuum_merge

    Indicates that the audit type is VACUUM operations.

    analyze/analyze_verify

    Indicates that the audit type is ANALYZE operations.

    cursor_declare/cursor_move/cursor_fetch/cursor_close

    Indicates that the audit type is cursor-related operations.

    codeblock_execute

    Indicates that the audit type is anonymous blocks.

    explain

    Indicates that the audit type is EXPLAIN operations.

    show

    Indicates that the audit type is SHOW operations.

    lock_table

    Indicates that the audit type is table locking operations.

    comment

    Indicates that the audit type is COMMENT operations.

    prepare/execute/deallocate

    Indicates that the audit type is PREPARE, EXECUTE, or DEALLOCATE operations.

    cluster

    Indicates that the audit type is CLUSTER operations.

    constraints

    Indicates that the audit type is CONSTRAINTS operations.

    checkpoint

    Indicates that the audit type is CHECKPOINT operations.

    barrier

    Indicates that the audit type is BARRIER operations.

    cleanconn

    Indicates that the audit type is CLEAN CONNECTION operations.

    seclabel

    Indicates that the audit type is security label operations.

    notify

    Indicates that the audit type is notification operations.

    load

    Indicates that the audit type is loading operations.

Disabling Audit Log Dumps

You can disable the audit log dump function if you do not want to dump audit logs to OBS.

The procedure is as follows:

  1. Log in to the GaussDB(DWS) management console.
  2. In the navigation pane on the left, choose Clusters > Dedicated Clusters.
  3. In the cluster list, click the name of the cluster for which you want to disable audit log dump. In the navigation pane, choose Security Settings.
  4. In the Audit Settings area, disable audit log dump.
  5. Click Apply.

    If Configuration Status is Applying, the system is saving the settings.

    When the status changes to Synchronized, the configurations are saved and take effect.