GBase 8c offers a comprehensive auditing framework, but simply flipping the switch is not enough for production. Effective auditing requires systematic design across audit scope, granularity, retention, and query access. This article focuses on making critical actions traceable — covering audit item configuration, log retention, using pg_query_audit as the primary query entry point, and routine inspection.
1. Define Audit Goals Before Selecting Items
GBase 8c supports a wide range of audit items — login/logout, privilege changes, DDL, DML, SELECT, COPY, function execution, SET parameters, etc. Most items can be enabled dynamically without a restart. However, enabling everything indiscriminately will flood the logs. Prioritise based on your goals:
| Goal | Recommended Items | Avoid Enabling Immediately |
|---|---|---|
| Security compliance | Login/logout, user lock/unlock, privilege grant/revoke, database start/stop | Full SELECT, all function execution |
| Operational traceability | Object DDL, SET parameters, database process events, COPY | Full audit for all users |
| Business data trails | DML on specific tables, supplement with SELECT when necessary | Blanket DML + SELECT across all tables |
A layered approach works best in practice: a baseline of system‑level audits (login, privilege, DDL, key parameter changes) that are always on, supplemented by targeted auditing on sensitive tables, key accounts, or during critical time windows.
2. Dynamic Parameter Changes for On‑Demand Auditing
The master switch audit_enabled and most subordinate switches can be reloaded at runtime, making temporary audit escalation straightforward. For example, to temporarily track DML on a specific table:
gs_guc reload -N all -I all -c "audit_dml_state = 1"
gs_guc reload -N all -I all -c "audit_dml_state_select = 1"
Check the current settings:
SHOW audit_directory;
SHOW audit_enabled;
SHOW audit_dml_state;
SHOW audit_dml_state_select;
3. Use pg_query_audit as Your Primary Query Tool
The built‑in function pg_query_audit(start_time, end_time) lets you query audit records directly by time window, avoiding manual log scraping. Filter by action type and object name:
SELECT detail_info, type, result
FROM pg_query_audit('2026-03-25 09:00:00', '2026-03-25 10:00:00')
WHERE type IN ('dml_action', 'dml_action_select')
AND detail_info LIKE '%acct_trade_detail%';
To trace a specific user's actions, combine the time range with the username and object name.
4. Retention Policies Must Match Business Traceability Requirements
GBase 8c provides these key parameters for managing audit log storage:
SHOW audit_directory; -- storage directory
SHOW audit_resource_policy; -- retention policy
SHOW audit_space_limit; -- total space cap
SHOW audit_file_remain_time; -- minimum retention (default 90 days)
SHOW audit_file_remain_threshold;-- max file count threshold
Common pitfalls: setting the space limit too low causes logs from a temporary audit escalation to be rolled off too quickly; retention time that doesn't align with monthly or quarterly review cycles leads to missing evidence. Design retention tiers based on scenario — keep baseline security audits long‑term, extend retention for sensitive databases, and promptly reduce granularity after temporary investigations.
5. OS‑File Storage for Audit Independence
GBase 8c writes audit results to operating system files rather than database tables by default. This separation prevents highly privileged users from tampering with audit records, reinforcing their credibility. In production, restrict access to the audit directory and consider using a dedicated security auditor role.
6. Recommended Rollout Sequence
- Enable baseline security items first: login/logout, privilege changes, object DDL.
- Verify directory and retention settings: check the parameters above to ensure logs aren't lost prematurely.
- Add DML/SELECT auditing for critical objects: target sensitive tables, key accounts, and specific time windows.
- Build a set of standard query templates: at minimum, templates for querying by time, object name, and action type.
- Integrate auditing into routine inspections: monitor audit directory growth and look for abnormal spikes in SELECT/DML volume.
The goal of auditing isn't to record everything, but to make every critical action traceable. Following this methodology turns GBase 8c's auditing capabilities into a reliable evidence chain for your gbase database.
