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

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

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

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;

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"

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%';

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'"

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
);

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
);

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

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}")"

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.