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 s
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;
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:
- Define the objective: security traceability vs. performance tracking
- Set alwaysβon items: login/logout, privilege changes, critical object operations, baseline slow SQL
- Define shortβterm enhanced items: DML/SELECT audit, full SQL β scoped by table, user, and time window
- Configure retention: space limits, file count, retention time
- 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.
Originally published by Dev.to Security. Aggregated on AIWithGhost for educational purposes β full credit and traffic to the original publisher.