Description

Ensure database audit logging is enabled using the PGAudit extension, so that important database activity, such as changes to tables, roles, and permissions, is recorded in the Postgres logs. The logged events should cover the activity required by the organization's security and compliance policies.

Rationale

Without audit logging, there is no reliable record of who changed data, schemas, or permissions in the database. This makes it hard to detect misuse, investigate incidents, or prove compliance. PGAudit adds detailed, structured audit entries to the Postgres logs, which can be reviewed in the dashboard or sent to an external system using log drains.

Impact

  • Records database changes for investigations and audits

  • Helps detect suspicious database activity

  • Very detailed logging can increase log volume

Default Value

By default, the PGAudit extension is not enabled and pgaudit.log is set to none.

Pre-Requisites

  • Project Owner or Admin role

  • A decision on which activity must be logged

Test Plan

  1. Sign in to the Supabase Dashboard at https://supabase.com/dashboard.

  2. Select your project.

  3. Open the SQL Editor in the sidebar.

  4. Run: select * from pg_extension where extname = 'pgaudit';

  5. Run: select rolname, rolconfig from pg_roles where rolname = 'postgres'; and check for a pgaudit.log setting.

  6. If PGAudit is not enabled or no logging is set, follow the implementation steps.

Implementation Steps

  1. Sign in to the Supabase Dashboard at https://supabase.com/dashboard.

  2. Select your project.

  3. Open Database in the sidebar and select Extensions.

  4. Search for pgaudit and enable it.

  5. Open the SQL Editor and run: alter role postgres set pgaudit.log to 'ddl, role, write';

  6. Perform a fast database reboot if the setting does not take effect.

  7. Open Logs, select Postgres Logs, and confirm that audit entries appear.

Backout Plan

  1. Sign in to the Supabase Dashboard at https://supabase.com/dashboard.

  2. Select your project.

  3. Open the SQL Editor in the sidebar.

  4. Run: alter role postgres set pgaudit.log to 'none';

  5. Optionally disable the pgaudit extension under Database > Extensions.

Reference