30-minute SOC 2 audit log: pgaudit + a deny-all RLS table

The first thing your SOC 2 auditor asks for is the audit log. Who did what to customer data, when, from where — for the database and the application, with a year of retention minimum and seven years on some controls. And no tampering.

The textbook answer is to bolt on a third-party logging vendor. You ship every database mutation and every application event to a SaaS log store, pay per GB-month, and add another vendor to your SOC 2 boundary so your auditor now has to evaluate their report too. You also gain a new failure mode where the audit log lives outside the database it describes, so when the auditor asks "how do you know the log is accurate," the answer involves several hops through several systems and a network that occasionally drops packets.

There is a smaller answer. Postgres ships pgaudit. With one extension, one table protected by deny-all RLS, and one security-definer function for application events, you can satisfy the control in an afternoon. The log lives in the same database with the same RLS as the data it describes. The trust story is shorter; the bill is smaller because you are storing kilobytes per day in a database you already pay for, not gigabytes per month in a vendor you do not.

This post is the control mapping, the SQL, the pgaudit configuration, and the question every auditor asks ("how do you know it has not been tampered with?") with the answer that has worked for us.

The control

SOC 2 CC7.2 covers system event detection — the ability to see when something material happened. CC7.3 covers identifying and responding to security incidents, which requires being able to reconstruct what happened. Neither says "use a SaaS log store." They require evidence on demand of:

• What the action was — a structured event type, not free-form text
• When it happened — a timestamp accurate enough to order events
• Who did it — an actor identifier traceable to a real human or a real service
• What it affected — the target row, file, or resource
• The outcome — success or failure

That is five fields. Your audit log is a table with five columns plus a primary key, plus extra context useful for reconstruction (tenant_id, payload jsonb, and a request_id correlator).

The harder requirement is immutability. The auditor will ask "could someone have edited this log?" and the answer needs to be a structural argument, not "nobody has the password." Even with full database access, the application must not be able to write a forged row, and any tampering attempt must leave evidence somewhere outside the audit log itself.

pgaudit: the database-level half

pgaudit is a Postgres extension that emits structured audit lines into the standard postgres log for any DDL, role change, write operation, or function call you tell it to capture. Every managed Postgres provider we have used ships it. Enabling it is a few lines in postgresql.conf (or a dashboard toggle on a managed service):

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write,role,ddl'
pgaudit.log_catalog = off
pgaudit.log_relation = on
pgaudit.log_parameter = on

The flags:

• write captures INSERT, UPDATE, DELETE, MERGE, TRUNCATE — every mutation by every connection.
• role captures GRANT, REVOKE, CREATE ROLE, ALTER ROLE, DROP ROLE — when somebody changes who can do what.
• ddl captures CREATE, ALTER, DROP on schemas, tables, indexes, functions — when somebody changes what the database can do.
• log_catalog = off skips internal pg_catalog reads (noise).
• log_relation = on includes the affected table name on each entry.
• log_parameter = on includes the bound parameter values, which matters for reconstruction.

Every captured action lands in the postgres log file as a structured AUDIT: line:

AUDIT: SESSION,1,1,WRITE,UPDATE,TABLE,public.incidents,
"UPDATE incidents SET status = $1 WHERE id = $2",
"resolved","a1b2c3d4-..."

Session ID, statement ID, sub-statement ID, action class, command, object type, object name, raw SQL, and bound parameters — all there, no triggers required.

The catch is that the postgres log lands wherever your Postgres host puts logs. On a managed service, that is usually a rolling file with a short retention. To meet SOC 2 retention you ship the postgres log somewhere durable. R2 with Object Lock in compliance mode is the cleanest target: set a one-year retention floor (seven years on the controls that demand it). S3 Object Lock is the equivalent on AWS; both give you Write-Once-Read-Many semantics that even your own root credentials cannot bypass within the lock period.

That matters for the auditor question. When they ask "how do you know nobody edited the log," the answer is "the storage layer itself rejects deletes and overwrites within the retention window. Even a compromised API token cannot mutate the existing objects." That is a structural argument the auditor can verify by reading the bucket configuration, not a process argument that depends on trusting your team.

The application-level half

pgaudit captures every database mutation. It does not capture intent. When the application updates incidents.status = 'resolved', pgaudit knows the row changed and which connection did it. It does not know which signed-in user clicked the button, which capability check ran first, or what the user typed in the "reason" field.

For that you need an application audit table:

CREATE TYPE public.audit_event_type AS ENUM (
  'incident_resolved',
  'incident_split',
  'incident_merge',
  'tenant_member_invited',
  'tenant_member_removed',
  'pii_pattern_created',
  'pii_pattern_deleted',
  'reveal_event',
  -- ... extend as needed
  'other'
);

CREATE TABLE public.tenant_audit (
  id           bigserial PRIMARY KEY,
  tenant_id    uuid NOT NULL,
  actor_id     uuid,                -- NULL for system actions
  actor_email  text,                -- denormalized for log readability
  event_type   public.audit_event_type NOT NULL,
  target_id    uuid,                -- the row being mutated, if any
  target_kind  text,                -- 'incident' / 'service' / 'user'
  outcome      text NOT NULL,       -- 'success' / 'denied' / 'error'
  payload      jsonb NOT NULL DEFAULT '{}'::jsonb,
  request_id   text,                -- ties to the API request that did it
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX tenant_audit_tenant_created_idx
  ON public.tenant_audit (tenant_id, created_at DESC);
CREATE INDEX tenant_audit_target_idx
  ON public.tenant_audit (target_id, created_at DESC)
  WHERE target_id IS NOT NULL;

Five things are deliberate.

event_type is an enum, not a text column. The application cannot insert a typo'd type. New types require a migration, visible in the schema diff. Auditors like this because the set of things you log is a finite, reviewable list.

outcome is required. Every row says whether the action succeeded. A denied action — capability check failed, RLS rejected the write — is itself security-relevant. An audit table that only logs successes is missing half the story.

payload jsonb is NOT NULL DEFAULT '{}'. Empty object beats NULL; every row is queryable with payload->>'something' without a COALESCE.

actor_email is denormalized. When a user is later deleted, actor_id may dangle but the row still tells you who the actor was. Auditors care about reconstruction; do not trade reconstruction for normalization.

request_id ties database events to application logs. Every HTTP request gets a UUID; the handler stamps it on every audit row. "Show me everything that happened in this request" is one query.

The deny-all RLS posture

The application role must not write to this table directly. If it could, a SQL injection bug or a compromised application could insert forged rows. The defense is row-level security with a deny-all policy and a security-definer function as the only write path:

ALTER TABLE public.tenant_audit ENABLE ROW LEVEL SECURITY;

-- No application policy. RLS is enabled, no policy grants access,
-- so the application role gets zero rows on SELECT and rejected
-- writes on INSERT/UPDATE/DELETE. Intentional.

REVOKE ALL ON TABLE public.tenant_audit FROM authenticated, anon;

Reads go through a SECURITY DEFINER function that filters by tenant membership; writes go through a SECURITY DEFINER function that validates the event before inserting.

CREATE OR REPLACE FUNCTION public.write_tenant_audit(
  p_tenant_id   uuid,
  p_event_type  public.audit_event_type,
  p_target_id   uuid,
  p_target_kind text,
  p_outcome     text,
  p_payload     jsonb,
  p_request_id  text
) RETURNS bigint
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, extensions, pg_temp
AS $$
DECLARE
  v_actor_id    uuid;
  v_actor_email text;
  v_audit_id    bigint;
BEGIN
  -- The actor is whoever the JWT says it is. No actor_id parameter,
  -- so the caller cannot lie about who they are.
  v_actor_id := auth.uid();

  IF v_actor_id IS NOT NULL THEN
    SELECT email INTO v_actor_email
      FROM auth.users WHERE id = v_actor_id;
  END IF;

  -- Constrain outcome to a known set; otherwise the application could
  -- log arbitrary text through this column.
  IF p_outcome NOT IN ('success', 'denied', 'error') THEN
    RAISE EXCEPTION 'invalid_outcome: %', p_outcome;
  END IF;

  -- A caller with a valid JWT for tenant A cannot write an audit row
  -- claiming tenant B did something.
  IF v_actor_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM public.tenant_members
    WHERE tenant_id = p_tenant_id AND user_id = v_actor_id
  ) THEN
    RAISE EXCEPTION 'forbidden';
  END IF;

  INSERT INTO public.tenant_audit (
    tenant_id, actor_id, actor_email, event_type,
    target_id, target_kind, outcome, payload, request_id
  ) VALUES (
    p_tenant_id, v_actor_id, v_actor_email, p_event_type,
    p_target_id, p_target_kind, p_outcome, p_payload, p_request_id
  ) RETURNING id INTO v_audit_id;

  RETURN v_audit_id;
END;
$$;

REVOKE EXECUTE ON FUNCTION public.write_tenant_audit FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.write_tenant_audit
  TO authenticated, service_role;

SET search_path = public, extensions, pg_temp is mandatory on every SECURITY DEFINER function and especially mandatory on any function touching extension types. The local dev environment puts extensions in public; the managed production instance puts them in extensions. Without an explicit search_path, the function compiles and runs locally, then fails in production with function does not exist — a runtime error you do not see until the first audit row tries to write.

The function is the only write path into tenant_audit. The application role has EXECUTE on the function but no privilege on the table itself. The function validates each argument before inserting. Even with a fully compromised application running arbitrary SQL through the database connection, the attacker cannot:

• Insert a row claiming a different actor (the function reads auth.uid() directly and ignores any actor argument)
• Insert a row for a tenant the actor does not belong to (the membership check rejects)
• Insert a row with an event type not in the enum (Postgres rejects at the type level)
• Insert a row with an outcome string outside the allowed set (the guard rejects)
• Mutate or delete an existing row (no UPDATE or DELETE privilege exists for the application role)

That is a structural argument, not a process argument. The auditor reads the function source and the privilege grants and verifies each property without needing to trust your code review process.

Reading the audit log

The read side is symmetric — same SECURITY DEFINER pattern, gated by tenant membership:

CREATE OR REPLACE FUNCTION public.list_tenant_audit(
  p_tenant_id  uuid,
  p_limit      int  DEFAULT 100,
  p_before_id  bigint DEFAULT NULL
) RETURNS SETOF public.tenant_audit
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, extensions, pg_temp
AS $$
DECLARE
  v_caller uuid := auth.uid();
BEGIN
  IF v_caller IS NULL THEN
    RAISE EXCEPTION 'forbidden';
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM public.tenant_members
    WHERE tenant_id = p_tenant_id AND user_id = v_caller
  ) THEN
    RAISE EXCEPTION 'forbidden';
  END IF;

  RETURN QUERY
  SELECT *
  FROM public.tenant_audit
  WHERE tenant_id = p_tenant_id
    AND (p_before_id IS NULL OR id < p_before_id)
  ORDER BY id DESC
  LIMIT LEAST(p_limit, 500);
END;
$$;

LEAST(p_limit, 500) is a guard against the application asking for everything in one query. Pagination is keyset-based on the monotonic bigserial id; offset-based pagination would re-shuffle rows under heavy write load.

The application calls write_tenant_audit from inside the same transaction as the underlying mutation. If the audit insert fails (membership check rejects, validation rejects), the entire mutation rolls back. There is no code path where the mutation succeeds and the audit row is missing.

The auditor question

"How do you know nobody has tampered with the audit log?"

The answer has three layers, each independently verifiable.

The application cannot tamper with it. The application role has no UPDATE or DELETE privilege on tenant_audit. The only INSERT path is through write_tenant_audit, which appends rows but cannot modify or delete them. A bug or compromise in the application is bounded to "could insert a forged row that looks like a normal audit row" — and the function's guards (actor from JWT only, tenant membership check, validated outcome and event type) make even that hard.

The database superuser can tamper with it, but pgaudit catches them. pgaudit's write log captures any direct UPDATE or DELETE against tenant_audit regardless of who issued the statement. Someone connects with elevated privileges, runs UPDATE tenant_audit SET ..., pgaudit emits an AUDIT: line, the postgres log ships to object-locked storage, the line is permanent. Tampering is detectable after the fact by comparing the postgres log against the live table.

The object-locked storage cannot be tampered with within the retention window. R2 Object Lock and S3 Object Lock both have compliance modes that even root cannot override during the retention period. Deleting a one-year-locked log requires waiting one year — long enough that the auditor would notice missing months.

The chain is: application → audit function → audit table (RLS-protected) → pgaudit (captures any direct DDL/DML) → postgres log → object-locked storage. Each link is a different access pattern, owned by a different identity, with a different failure mode. Tampering at any single link is detectable from the others.

This is evidence, not prevention. A determined attacker with full superuser access could mutate the live table; the defense is that the mutation is detectable, not impossible. SOC 2 does not require absolute immutability — it requires reasonable controls and detective evidence. This shape clears the bar.

Cost

pgaudit is free. R2 Object Lock storage is $0.015 per GB-month with a $1 minimum. A typical Postgres log at moderate write volume is a few hundred MB per day compressed; one year is on the order of 100-200 GB; total storage cost is single-digit dollars per month.

Per-seat or per-GB pricing on bolt-on audit log vendors at the same write volume can run into four figures monthly. The savings are not the point. The point is you did not add a vendor to your SOC 2 boundary and you do not have to explain to your auditor why they need to evaluate someone else's SOC 2 report alongside yours.

The evening this takes to set up is the cheapest SOC 2 work you will do.

---

The architecture above is what underlies Culprit's audit posture — pgaudit on the database, a deny-all RLS table for application events, object-locked log storage for retention. The full security architecture is documented on /security; the pricing rationale (one flat number, no per-seat bolt-ons) is on /pricing/how-49-works.