SQL Academy · Lesson

Auditing Access

Track who can see what.

Auditing Access is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Audit Access?

Knowing who accessed what data and when is a cornerstone of database security. Auditing creates a reliable trail of events so you can detect unauthorized access, investigate incidents, and satisfy compliance requirements such as GDPR, HIPAA, or SOC 2.

In this lesson you will learn how to design audit tables, capture access events automatically with triggers, use PostgreSQL's built-in logging features, and query the audit trail to answer the question: who can see what?

Designing an Audit Log Table

The first step is a dedicated table that records every notable event. A good audit log stores the table name, the type of operation, the old and new values, which user performed the action, and the exact timestamp.

The example below creates a general-purpose audit_log table using JSONB columns to store row snapshots — flexible enough to handle any table without schema changes.

CREATE TABLE audit_log (
  id          BIGSERIAL PRIMARY KEY,
  event_time  TIMESTAMPTZ NOT NULL DEFAULT now(),
  db_user     TEXT NOT NULL DEFAULT current_user,
  app_user    TEXT,
  table_name  TEXT NOT NULL,
  operation   TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE','SELECT')),
  row_id      BIGINT,
  old_data    JSONB,
  new_data    JSONB
);

Recording the Current User

PostgreSQL provides several built-in functions to identify who is running a query. current_user returns the role name in effect after any SET ROLE. session_user always returns the original login role, regardless of role switching.

For applications that use a single shared DB role but pass an application-level user via SET LOCAL app.current_user, you can read that setting with current_setting().

-- Who is the database user right now?
SELECT current_user,
       session_user;

-- Read an application-level user injected by the app layer
SELECT current_setting('app.current_user', true) AS app_user;

Writing an Audit Trigger Function

A trigger function is the most reliable way to capture data-change events because it fires automatically — no application code can bypass it. The function below logs every INSERT, UPDATE, and DELETE on any table it is attached to, storing the old and new row values as JSONB.

Notice the use of TG_TABLE_NAME (the table that fired the trigger) and row_to_json() to convert row values into a storable format.

CREATE OR REPLACE FUNCTION fn_audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO audit_log (
    db_user,
    app_user,
    table_name,
    operation,
    row_id,
    old_data,
    new_data
  ) VALUES (
    current_user,
    current_setting('app.current_user', true),
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(NEW.id, OLD.id),
    CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD)::JSONB END,
    CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW)::JSONB END
  );
  RETURN NULL;
END;
$$;

Attaching the Trigger to a Table

Once the trigger function exists, you attach it to each table you want to audit with a CREATE TRIGGER statement. Using AFTER ensures the data was actually written before the log entry is created. The FOR EACH ROW clause fires the trigger once per modified row.

Here the trigger is applied to a hypothetical patients table, recording every INSERT, UPDATE, and DELETE automatically.

CREATE TRIGGER trg_audit_patients
AFTER INSERT OR UPDATE OR DELETE
ON patients
FOR EACH ROW
EXECUTE FUNCTION fn_audit_changes();

Auditing SELECT Queries

Data-change triggers only capture writes. To audit read access you need a different approach. One option is an AFTER SELECT statement-level trigger (supported in PostgreSQL 14+ for certain contexts). A more common pattern is to log reads explicitly inside a function or view that wraps the sensitive table.

The example below wraps a sensitive table in a function that logs every read before returning results.

CREATE OR REPLACE FUNCTION get_patient_record(p_id INT)
RETURNS SETOF patients
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- Log the read access
  INSERT INTO audit_log (db_user, app_user, table_name, operation, row_id)
  VALUES (
    current_user,
    current_setting('app.current_user', true),
    'patients',
    'SELECT',
    p_id
  );

  RETURN QUERY
  SELECT * FROM patients WHERE id = p_id;
END;
$$;

PostgreSQL Built-in Logging

PostgreSQL's postgresql.conf offers powerful server-side logging that requires no application code. Setting log_min_duration_statement logs any query exceeding a threshold. Setting log_connections and log_disconnections records who logs in and out.

The query below uses the pg_stat_activity system view to see currently active sessions — a lightweight form of live access monitoring.

-- See who is currently connected and what they are running
SELECT pid,
       usename        AS db_user,
       application_name,
       client_addr,
       state,
       query_start,
       LEFT(query, 80) AS current_query
FROM   pg_stat_activity
WHERE  datname = current_database()
ORDER  BY query_start DESC;

Querying the Audit Log

An audit log is only valuable if you can query it effectively. Common questions include: which user accessed a record most recently, what changed in a row over time, and how many sensitive reads happened in the last 24 hours.

The query below finds all users who accessed a specific patient record, ordered by most recent first.

SELECT event_time,
       db_user,
       app_user,
       operation,
       old_data,
       new_data
FROM   audit_log
WHERE  table_name = 'patients'
  AND  row_id = 42
ORDER  BY event_time DESC
LIMIT  20;

Detecting Suspicious Access Patterns

Once audit data is collected you can write queries that flag anomalies. For example, a user who suddenly reads far more rows than usual, or the same sensitive record accessed multiple times in a short window, may indicate a data exfiltration attempt.

The query below counts SELECT events per application user in the last hour and highlights anyone who has read more than 100 rows.

SELECT app_user,
       COUNT(*) AS records_accessed
FROM   audit_log
WHERE  operation = 'SELECT'
  AND  event_time >= now() - INTERVAL '1 hour'
GROUP  BY app_user
HAVING COUNT(*) > 100
ORDER  BY records_accessed DESC;

Protecting the Audit Log Itself

An audit log that can be modified is not trustworthy. You should lock it down so that ordinary users and application roles cannot delete or update rows. The safest approach is to grant only INSERT to the application role, and reserve SELECT to a dedicated auditor role.

You can also enforce immutability with a trigger that raises an exception if anyone attempts to update or delete an audit row.

-- Only the app role may insert; nobody may update or delete
REVOKE ALL     ON audit_log FROM PUBLIC;
GRANT  INSERT  ON audit_log TO app_role;
GRANT  SELECT  ON audit_log TO auditor_role;

-- Trigger to block any tampering
CREATE OR REPLACE FUNCTION fn_protect_audit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'audit_log rows are immutable';
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_protect_audit
BEFORE UPDATE OR DELETE ON audit_log
FOR EACH ROW EXECUTE FUNCTION fn_protect_audit();

Auditing RLS Policy Decisions

When Row-Level Security is active, PostgreSQL silently hides rows rather than raising errors. This makes it hard to know whether a user attempted to read a row they were not allowed to see. One technique is to add a permissive policy that always inserts an audit record before the restrictive policy filters rows.

The query below shows how to inspect which RLS policies exist on a table and which roles they apply to.

-- View all RLS policies on the patients table
SELECT polname       AS policy_name,
       polcmd        AS command,
       polroles::TEXT AS applies_to,
       polqual::TEXT  AS using_expression,
       polwithcheck::TEXT AS with_check_expression
FROM   pg_policy
WHERE  polrelid = 'patients'::REGCLASS
ORDER  BY polname;

Knowledge Check

Test your understanding of auditing access in SQL.

Lesson Recap

In this lesson you explored how to build a complete access-auditing system in PostgreSQL:

Audit log table — a JSONB-based table capturing who did what and when.
Trigger function — automatically logs INSERT, UPDATE, and DELETE events for any attached table using TG_TABLE_NAME, row_to_json(), and current_user.
Read auditing — wrap sensitive tables in functions that log SELECT events before returning data.
Built-in monitoring — pg_stat_activity shows live sessions; server-side logging captures queries without code changes.
Anomaly detection — aggregate queries over the audit log can flag unusual access volume.
Immutability — revoke UPDATE/DELETE from the audit table and add a blocking trigger to prevent tampering.

A well-designed audit trail is your most reliable tool for answering who accessed what and forms the backbone of any compliance or security investigation.

Frequently asked questions

Is the “Auditing Access” lesson free?

Yes — the full text of “Auditing Access” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Auditing Access”?

Track who can see what. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Auditing Access” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy