DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

GBase 8c Audit and Log Governance: Don't Wait Until Disks Are Full

#gbase #database #数据库 #security

The logging, slow SQL tracking, and auditing features of GBase 8c can backfire when not managed proactively. They either miss critical evidence when needed, or become so bloated that they choke the system. This article separates these three capabilities and provides a practical strategy for long‑term essential collection versus short‑term targeted diagnostics, helping you balance security traceability with system overhead in your gbase database.

1. Know Your Logs: Three Distinct Purposes

Log Type Primary Use Typical Investigation
Operational Logs Instance anomalies, tool operations, fault location Instance errors, OM operation history
Slow SQL / Full SQL Tracking Performance observation and historical replay Slow queries, suspicious SQL in time window
Audit Logs Security traceability, object operations, accountability Who changed a table, who executed DML

2. Layered Governance: Always‑On vs. Short‑Term

Layer Suitable for Always‑On Suitable for Short‑Term
Operational Instance logs, tool logs, basic alerts Trace, black‑box enhanced info
Slow SQL Mild threshold slow SQL capture Full SQL, high‑precision tracking
Audit Login/logout, privilege changes, critical object ops Table‑specific DML/SELECT audit, full audit for specific users

3. Audit Configuration: Targeted, Not Blanket

Basic security audit items should remain on. DML and SELECT auditing should only be enabled within defined time windows and scopes.

-- Inspect key audit switches
SHOW audit_enabled;
SHOW audit_login_logout;
SHOW audit_database_process;
SHOW audit_grant_revoke;
SHOW audit_system_object;
SHOW audit_dml_state;
SHOW audit_dml_state_select;
SHOW full_audit_users;
SHOW no_audit_client;
SHOW audit_xid_info;
SHOW audit_directory;
Enter fullscreen mode Exit fullscreen mode

Example baseline security configuration:

gs_guc reload -N all -I all -c "audit_enabled = on"
gs_guc reload -N all -I all -c "audit_system_object = 67121159"
gs_guc reload -N all -I all -c "audit_grant_revoke = 1"
gs_guc reload -N all -I all -c "audit_dml_state = 1"
Enter fullscreen mode Exit fullscreen mode

Query audit records:

SELECT * FROM pg_query_audit('2026-04-01 09:00:00','2026-04-01 11:00:00');

SELECT time, username, database, type, result, detail_info
FROM pg_query_audit('2026-04-01 09:00:00','2026-04-01 11:00:00')
WHERE type IN ('dml_action', 'dml_action_select')
  AND detail_info LIKE '%orders_fact%';
Enter fullscreen mode Exit fullscreen mode

4. Slow SQL and Full SQL Tracking: Tiered Collection

Key parameters:

  • enable_stmt_track – master switch
  • track_stmt_stat_level – granularity control
  • log_min_duration_statement – slow query threshold
  • instr_unique_sql_count – max unique SQL entries
  • track_stmt_retention_time – retention window

Recommended daily observation settings:

gs_guc reload -Z coordinator -N all -I all -c "enable_stmt_track = ON"
gs_guc reload -Z coordinator -N all -I all -c "track_stmt_stat_level = 'OFF,L0'"
gs_guc reload -Z coordinator -N all -I all -c "log_min_duration_statement = 1000"
gs_guc reload -Z coordinator -N all -I all -c "instr_unique_sql_count = 200000"
gs_guc reload -Z coordinator -N all -I all -c "track_stmt_retention_time = '3600,10800'"
Enter fullscreen mode Exit fullscreen mode

Query slow SQL history:

SELECT * FROM dbe_perf.get_global_slow_sql_by_timestamp(
  '2026-04-01 09:00:00'::timestamp with time zone,
  '2026-04-01 10:00:00'::timestamp with time zone
);
Enter fullscreen mode Exit fullscreen mode

Query full SQL history:

SELECT * FROM dbe_perf.get_global_full_sql_by_timestamp(
  '2026-04-01 09:00:00'::timestamp with time zone,
  '2026-04-01 09:10:00'::timestamp with time zone
);
Enter fullscreen mode Exit fullscreen mode

5. File Management: Space Control and Archival

Audit file parameters:

  • audit_space_limit – max space (default 1GB)
  • audit_file_remain_time – minimum retention (default 90 days)
  • audit_file_remain_threshold – max file count

Check disk usage:

du -sh /var/log/gbase/gbase/pg_log
du -sh /var/log/gaussdb/gbase/pg_audit
Enter fullscreen mode Exit fullscreen mode

Example archival script:

#!/bin/bash
set -e
AUDIT_DIR="/var/log/gaussdb/gbase/pg_audit"
ARCHIVE_DIR="/data/archive/pg_audit/$(date +%F)"
mkdir -p "${ARCHIVE_DIR}"
find "${AUDIT_DIR}" -type f -mtime +7 -name "*.log" -print0 | xargs -0 -I {} mv {} "${ARCHIVE_DIR}/"
tar -czf "${ARCHIVE_DIR}.tar.gz" -C "$(dirname "${ARCHIVE_DIR}")" "$(basename "${ARCHIVE_DIR}")"
Enter fullscreen mode Exit fullscreen mode

6. Common Pitfalls and Recommended Governance Sequence

Pitfalls:

  • Using audit as a performance profiling tool
  • Leaving DML/SELECT audit on indefinitely
  • Forgetting to roll back temporary collection settings
  • Checking only database views, ignoring OS disk utilization
  • Applying the same retention policy to all log types

Recommended governance sequence:

  1. Define the objective: security traceability vs. performance tracking
  2. Set always‑on items: login/logout, privilege changes, critical object operations, baseline slow SQL
  3. Define short‑term enhanced items: DML/SELECT audit, full SQL — scoped by table, user, and time window
  4. Configure retention: space limits, file count, retention time
  5. Add query and archival routines

Layered governance makes GBase 8c's logging and audit infrastructure sustainable, preventing the all‑too‑common cycle of "off by default, fully on during incidents" that burdens both your gbase database and your operations team.

Top comments (0)