Supabase Audit Logging with pg_audit: Track Every Query (2026)

Supabase audit logging with pg_audit is less about tracking every query and more about selectively capturing specific types of changes to your database.

Let’s watch pg_audit in action. Imagine you have a users table and you want to know whenever a user’s email is changed.

-- First, ensure pg_audit is enabled and configured.
-- This is typically done in your Supabase project settings under "Database" -> "Extensions".
-- You'll need to set these parameters in your PostgreSQL configuration (usually via the Supabase dashboard).

-- Example configuration for logging DML statements on the 'users' table:
-- shared_preload_libraries = 'pg_audit'
-- pg_audit.log = 'ddl, role, rule, schema, statement, trigger, type, function, object, all' -- Or more granularly
-- pg_audit.log_catalog = off
-- pg_audit.log_statement_once = off
-- pg_audit.log_connections = off
-- pg_audit.log_disconnections = off
-- pg_audit.log_parameter = off
-- pg_audit.log_relation = on
-- pg_audit.log_row = off
-- pg_audit.exclude_users = 'supabase_auth_admin' -- Example exclusion

-- Let's assume you've configured pg_audit to log DML on the 'users' table.
-- First, create a user table:
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- Now, insert a user:
INSERT INTO users (email) VALUES ('alice@example.com');

-- Let's say Alice updates her email:
UPDATE users SET email = 'alice.updated@example.com' WHERE id = (SELECT id FROM users WHERE email = 'alice@example.com');

-- To see the audit log, you'd query the 'audit.log' table (or whatever you've configured).
-- The exact table name depends on your pg_audit setup, but often it's implicitly logged or requires a specific schema.
-- In Supabase, these logs are typically accessible via the "Logs" section in the dashboard, or you can query them directly if you've set up a custom logging table.

-- A simplified query to find the update statement (assuming it's logged and accessible):
-- NOTE: The actual log table structure and access method may vary in Supabase.
-- This is a conceptual query based on pg_audit's output.
SELECT
    log_time,
    session_user,
    action, -- e.g., 'UPDATE', 'INSERT', 'DELETE'
    object_type, -- e.g., 'TABLE'
    object_name, -- e.g., 'users'
    statement
FROM
    -- Replace 'your_audit_log_table' with the actual table where pg_audit logs.
    -- In Supabase, you might need to query specific system views or tables.
    -- For demonstration, let's assume a conceptual 'audit_log' table.
    (SELECT * FROM pg_audit_get_current_session() WHERE obj_name = 'users') AS audit_log
WHERE
    action = 'UPDATE' AND object_name = 'users';

-- The output would show the statement that was executed:
-- log_time | session_user | action | object_type | object_name | statement
-- --------------------------------------------------------------------------------------------------------------
-- 2023-10-27 10:30:00 UTC | postgres | UPDATE | TABLE | users | UPDATE users SET email = 'alice.updated@example.com' WHERE id = '...';

The core problem pg_audit solves is the lack of granular, built-in auditing for changes within PostgreSQL databases. Without it, you’re flying blind when it comes to understanding who changed what, when, and why. This is critical for compliance, security investigations, and debugging complex data issues.

pg_audit works by hooking into PostgreSQL’s execution pipeline. When a database command is about to be executed, pg_audit intercepts it based on its configuration. It can log various aspects: the raw SQL statement, the user executing it, the objects (tables, schemas, etc.) involved, and even the old and new row values (though this is resource-intensive and often disabled).

The configuration is entirely driven by PostgreSQL parameters. Key settings include:

• shared_preload_libraries = 'pg_audit': This is the essential one that tells PostgreSQL to load the pg_audit extension at startup. Without this, pg_audit won’t be available.
• pg_audit.log: This parameter is a comma-separated list of what to log. Common values are READ, WRITE, FUNCTION, ROLE, DDL, MISC, or ALL. You can be very specific, e.g., WRITE to log only INSERT, UPDATE, DELETE.
• pg_audit.log_catalog: Set to off to avoid logging operations within PostgreSQL’s system catalogs, which can generate a massive amount of noise.
• pg_audit.log_relation: Set to on to log the relation (table/view) name being operated on. Essential for knowing which table was affected.
• pg_audit.log_statement_once: Set to off to log the full statement every time, rather than just once per statement type per session.
• pg_audit.exclude_users: A list of roles (users) whose actions should not be logged. Useful for excluding internal Supabase roles or automated processes.

Supabase manages these configurations for you. You typically enable the pg_audit extension in your project settings and then configure the logging parameters through the Supabase dashboard under Database -> Extensions -> pg_audit. The logs themselves are then often accessible through the Supabase Logs Explorer, allowing you to filter and search audit events.

The most surprising thing about pg_audit is how easily it can be configured to log row-level changes (pg_audit.log_row = on), but the performance impact is so severe that it’s rarely used in production. Enabling log_row means that for every INSERT, UPDATE, or DELETE, pg_audit will capture the entire old and new row data. This can quickly bloat your audit logs and significantly slow down your database operations, making it impractical for anything but the most critical, low-volume auditing scenarios.

Once you have pg_audit configured and logging, the next step is often to integrate these logs with a centralized logging system or a SIEM for long-term storage, analysis, and alerting.