DEV Community: Uduak Ukpong

Why a Postgres View Can Silently Bypass Row-Level Security

Uduak Ukpong — Thu, 24 Sep 2026 11:10:34 +0000

Querying a reporting view returned a project's real task counts to a user with zero membership in that project. Row-level security should have scoped that row out. It came back anyway, because the view itself was never checking who was asking.

This is Atlas, a project-management app I built. It was caught in development, before the app shipped to anyone beyond test accounts and a few colleagues who signed up to try it out. No real user's data was ever exposed. It's still worth writing up in full, because the mechanism behind it is a genuine, documented Postgres behavior that a lot of people writing row-level security assume doesn't apply to them.

This isn't the same bug as last time

I've written before about RLS in this schema, specifically the difference between USING and WITH CHECK on a policy, proven with a real two-user session showing one tenant can't read, write, or delete another's rows. That piece is about policy design: writing the right conditions. This one isn't. The policies on the underlying table here were correct the entire time, four of them, covering read, create, update, and owner-only delete. Every one of them did exactly what it was supposed to do.

The leak lived somewhere RLS doesn't reach on its own: a view sitting on top of that table.

The underlying table itself carries four RLS policies: one letting project members read their own tasks (tasks: project members can read), separate ones gating creation and updates the same way, membership or ownership, and a fourth restricting deletion to the project owner (tasks: owner can delete). Query that table directly, as any authenticated user, and every one of those policies does exactly what it says. The bug never touched them.

security_invoker: the setting RLS quietly assumes you have

Postgres's own CREATE VIEW documentation states it plainly:

If any of the underlying base relations has row-level security enabled, then by default, the row-level security policies of the view owner are applied, and access to any additional relations referred to by those policies is determined by the permissions of the view owner. However, if the view has security_invoker set to true, then the policies and permissions of the invoking user are used instead, as if the base relations had been referenced directly from the query using the view.

Read that first sentence again. A view created by a privileged role, a migration role, typically, since that's who runs CREATE VIEW, inherits that role's relationship to RLS when someone else queries through it. RLS checks the owner's identity by default, not whoever's actually asking.

This isn't an oversight in Postgres's design. It's a deliberate, decades-old pattern. Owner-privilege views are how you let someone query a narrower slice of a table without granting them any access to the table itself: create a view selecting only the columns or rows they should see, grant them access to the view alone, and the view's own owner is who Postgres checks against the underlying table. That pattern predates row-level security entirely, and it's genuinely useful. It just quietly stops being what you want the moment the table underneath also has RLS policies meant to scope data per user, because now two different privilege systems disagree about whose identity actually matters.

The fix is a single boolean, security_invoker, added in Postgres 15, released in October 2022. Set it, and the view stops being a backdoor around RLS and starts behaving the way most people already assume a view does. This isn't a rare edge case or a recent addition. It's been available, opt-in, for years. Atlas runs Postgres 17.6, so this was never a version gap. The setting was never applied.

The view, before and after

The original view, from the migration that created it and untouched since:

create view public.project_task_stats as
select
  project_id,
  count(*) as total_tasks,
  count(*) filter (where status = 'done') as done_tasks
from public.tasks
group by project_id;

No privilege option specified at all, which meant Postgres fell back to the only behavior that existed before security_invoker: run as the view's owner, full stop.

Fixing it didn't touch that query. The entire fix is a separate, one-line ALTER VIEW:

alter view public.project_task_stats set (security_invoker = true);

That's it. The view's actual SELECT never needed to change, only how Postgres decides whose privileges apply when someone queries it. Worth sitting with that for a second: the exact same query, run by the exact same view, returns a correctly scoped result or a cross-tenant leak, purely based on one storage option most CREATE VIEW walkthroughs never mention.

A second, smaller trap: grants don't carry over either

security_invoker alone wasn't enough to make the view usable. A view doesn't inherit SELECT privileges from the tables it queries. Even once RLS is correctly scoped to the invoking user, that user still needs an explicit grant on the view itself.

Two separate things had to be true before this view worked correctly: security_invoker = true, so RLS checks the right identity, and an explicit GRANT SELECT on the view, so that identity is even allowed to query it in the first place. Miss the grant on its own and the view breaks in the safe direction, a 42501 permission error instead of a leak. Miss security_invoker and it fails in the dangerous one.

Proving it, both ways

One setting, same query, same user, same project, opposite result. Proven in a single continuous psql session, no reconnects between the two halves, because a leak proof and a denial proof only mean something side by side if nothing else about the connection changed in between.

A user with zero membership on the target project, session set up the way the app itself sets it up. The third argument to set_config matters: false, not true. true scopes the claim to the current transaction, and in autocommit each statement is its own transaction, so the identity would quietly disappear before the very next line ran.

SET ROLE authenticated;
SELECT set_config('request.jwt.claims',
  '{"sub":"5251e552-3559-45d1-8aff-bc92ee8a1fa4","role":"authenticated"}', false);

                              set_config
-----------------------------------------------------------------------
 {"sub":"5251e552-3559-45d1-8aff-bc92ee8a1fa4","role":"authenticated"}
(1 row)

With security_invoker unset, the pre-fix state:

SELECT auth.uid();

                 uid
--------------------------------------
 5251e552-3559-45d1-8aff-bc92ee8a1fa4
(1 row)

SELECT * FROM public.project_task_stats
WHERE project_id = '56d97989-33dd-4632-b0fc-a561a7c99602';

              project_id              | total_tasks | done_tasks
--------------------------------------+-------------+------------
 56d97989-33dd-4632-b0fc-a561a7c99602 |           1 |          0
(1 row)

A row came back. Real task counts, for a project this user has no relationship to at all. Nothing about the query is wrong, the WHERE clause filters correctly on project_id, and the user's session is set up exactly the way a real authenticated request would be, role switched, JWT claim set, nothing spoofed or elevated. The leak was never in what's being asked. It's in whose permissions Postgres checks before answering, and that's precisely the distinction a query itself can never reveal. You'd have to already know to ask.

Same session, fix applied:

RESET ROLE;
ALTER VIEW public.project_task_stats SET (security_invoker = true);

Then the same user, re-asserted in that same session, before touching the view again:

SET ROLE authenticated;
SELECT set_config('request.jwt.claims',
  '{"sub":"5251e552-3559-45d1-8aff-bc92ee8a1fa4","role":"authenticated"}', false);

                              set_config
-----------------------------------------------------------------------
 {"sub":"5251e552-3559-45d1-8aff-bc92ee8a1fa4","role":"authenticated"}
(1 row)

SELECT auth.uid();

                 uid
--------------------------------------
 5251e552-3559-45d1-8aff-bc92ee8a1fa4
(1 row)

Same UUID as before, not null. That match is the whole point of checking it here: it rules out the obvious false explanation for what comes next, a query returning nothing because nobody's logged in rather than because RLS correctly denied a real, identified non-member. With that identity confirmed live on the connection, the same query against the same view:

SELECT * FROM public.project_task_stats
WHERE project_id = '56d97989-33dd-4632-b0fc-a561a7c99602';

 project_id | total_tasks | done_tasks
------------+-------------+------------
(0 rows)

Nothing. The same RLS policy that already protected the underlying table directly now protects it through the view too, because the view finally checks the querying user's identity instead of its own owner's, and the auth.uid() result two lines up confirms exactly whose identity that was.

What actually caught this, and what didn't

Before writing this up, I ran Supabase's own schema linter against both states, live: the vulnerable version and the fixed one.

No schema errors found
{"results":[],"message":"db lint"}

Identical output, both times. The view never appeared in either run, flagged or otherwise. The linter checks a defined, known set of schema issues. A view quietly running as its owner isn't one of them.

Supabase's Security Advisor, the tool actually aimed at this class of problem, wasn't reachable from a local, unlinked development stack at all. It requires an authenticated connection to a hosted project. So the honest claim isn't "the linter caught it" or "the linter missed it, but the Advisor would have." It's narrower than that: nothing automated in this local workflow checked for it. The only thing that did was deliberately querying the schema as an unprivileged user, with something specific to look for. That's adversarial testing doing a job a static check genuinely couldn't do here, not because the tooling is bad, but because "does this view check RLS as the right person" isn't a syntax question.

When this actually bites you

This only matters if two things are both true at once: the view sits over a table with RLS enabled, and the view's owner has a different, usually more privileged, relationship to that RLS than the people who'll actually query it. That's the common case for any view created by a migration role and queried by an application role, which describes most Postgres-as-a-service setups running RLS at all.

security_invoker is set per view, not once for the whole database. There's no single flag that makes every view in a schema safe going forward. Every view over an RLS-protected table needs the same one-line check, individually, including any created after this one gets fixed.

None of this applies if the underlying tables have no RLS at all, or if a view is deliberately meant to aggregate across a boundary for a role that's supposed to see everything, a reporting role, an admin dashboard. In those cases, the owner-privileges default is what you actually want, not a bug waiting to be found.

The harder part isn't fixing one view once you've found it. It's noticing you have this class of view at all. Nothing about a view like this looks wrong on inspection. It compiles, it returns sensible-looking columns, it answers the query you asked it. The only way to actually find out whose permissions it's checking is to ask it a question you already know the honest answer to, from an account that shouldn't be able to answer yes.

Row-Level Security for Multi-Tenant Postgres: USING vs WITH CHECK in Practice

Uduak Ukpong — Tue, 15 Sep 2026 20:28:40 +0000

This piece isn't about turning RLS on. It's about what goes inside the policies once it is, and about proving they do what you think they do instead of just trusting the SQL.

That distinction matters because a bad RLS policy compiles fine. Postgres accepts it without complaint. Nobody catches the mistake in code review, because reading a policy tells you what it's supposed to allow, not what it actually denies. The only way to know for sure is to attack it as a real user with real credentials, then read the output. Most of this article is that attack, run for real, against Atlas's actual schema.

The tenant boundary in three tables

The code throughout is real, pulled from Atlas, a project and task management app I built. Every table in it is scoped to a project, and the project is the tenant boundary. Real schema, from supabase/migrations/001_initial_schema.sql:

create table public.projects (
  id uuid default gen_random_uuid() primary key,
  name text not null,
  description text,
  status text default 'active' check (status in ('active', 'completed', 'archived')),
  owner_id uuid references public.profiles(id) on delete cascade not null,
  created_at timestamptz default now() not null
);

create table public.project_members (
  project_id uuid references public.projects(id) on delete cascade not null,
  user_id uuid references public.profiles(id) on delete cascade not null,
  role text default 'collaborator' check (role in ('owner', 'collaborator')),
  joined_at timestamptz default now() not null,
  primary key (project_id, user_id)
);

create table public.tasks (
  id uuid default gen_random_uuid() primary key,
  title text not null,
  description text,
  status text default 'todo' check (status in ('todo', 'in_progress', 'done')),
  project_id uuid references public.projects(id) on delete cascade not null,
  assignee_id uuid references public.profiles(id) on delete set null,
  created_at timestamptz default now() not null,
  due_date timestamptz
);

The tenant key is different on each table, and it's worth naming plainly. On projects, the tenant is the row itself. A project doesn't belong to another project. On project_members and tasks, the tenant key is project_id. Every policy in this piece exists to enforce one rule against that key: a user only touches rows whose project_id traces back to a project they belong to.

RLS on these three tables was turned on in the same migration, explicitly, not by any later automation:

alter table public.projects enable row level security;
alter table public.project_members enable row level security;
alter table public.tasks enable row level security;

Reads filter, writes check, and that split lives in two clauses

Every RLS policy attaches to one command, SELECT, INSERT, UPDATE, DELETE, or ALL, and to a clause. USING is a filter. It decides, per row, whether a query can see that row at all. A row that fails USING on a SELECT doesn't error, it just isn't there. WITH CHECK is a gate on the row a write is about to leave behind. On INSERT, the new row has to pass it or the insert fails. On UPDATE, both clauses run: USING decides which existing rows can be targeted, and WITH CHECK decides whether the row's new state is still allowed to exist.

Here's tasks, current policies, one per command:

create policy "tasks: project members can read"
  on public.tasks for select
  to authenticated
  using (
    is_active_user()
    and (
      exists (
        select 1 from public.project_members
        where project_id = tasks.project_id
        and user_id = auth.uid()
      ) or
      exists (
        select 1 from public.projects
        where id = tasks.project_id
        and owner_id = auth.uid()
      )
    )
  );

create policy "tasks: project members can create"
  on public.tasks for insert
  to authenticated
  with check (
    is_active_user()
    and (
      exists (
        select 1 from public.project_members
        where project_id = tasks.project_id
        and user_id = auth.uid()
      ) or
      exists (
        select 1 from public.projects
        where id = tasks.project_id
        and owner_id = auth.uid()
      )
    )
  );

create policy "tasks: project members can update"
  on public.tasks for update
  to authenticated
  using (
    is_active_user()
    and (
      exists (
        select 1 from public.project_members
        where project_id = tasks.project_id
        and user_id = auth.uid()
      ) or
      exists (
        select 1 from public.projects
        where id = tasks.project_id
        and owner_id = auth.uid()
      )
    )
  );

create policy "tasks: owner can delete"
  on public.tasks for delete
  to authenticated
  using (
    is_active_user()
    and exists (
      select 1 from public.projects
      where id = tasks.project_id
      and owner_id = auth.uid()
    )
  );

Ignore is_active_user() for now. It's a later account-deletion guard layered onto these policies, not part of the tenant-isolation story here.

Look at the update policy. It defines USING. It has no WITH CHECK at all. That's not a gap, it's Postgres's own default: when a policy needs a WITH CHECK and doesn't specify one, Postgres reuses the USING expression for both jobs. So tasks: project members can update runs the same membership check twice on a single UPDATE, once to decide which row a member is even allowed to select for editing, and again against the row's resulting state before the update is allowed to land. A member can't target a task outside their projects, and they can't use an update to move a task into someone else's project either. One expression, applied on both ends.

`is_project_member()` exists because the inline version caused recursion

The is_project_member() function already showed up in the audit-log piece, used inside a trigger. Here it solves a different problem entirely.

The original project_members read policy, from migration 002, checked membership by querying project_members from inside its own policy:

create policy "project_members: members can read"
  on public.project_members for select
  to authenticated
  using (
    exists (
      select 1 from public.project_members pm
      where pm.project_id = project_members.project_id
      and pm.user_id = auth.uid()
    )
  );

That's a policy on project_members that queries project_members. Postgres has to apply the table's RLS policy to evaluate the table's RLS policy. Infinite recursion. Migration 003 fixed it by moving the check into a function:

CREATE OR REPLACE FUNCTION public.is_project_member(_user_id uuid, _project_id uuid)
 RETURNS boolean
 LANGUAGE plpgsql
 STABLE SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
begin
  return exists (
    select 1 from public.project_members
    where project_id = _project_id
    and user_id = _user_id
  );
end;
$function$

create policy "project_members: members can read"
  on public.project_members for select
  to authenticated
  using (is_project_member(auth.uid(), project_id));

SECURITY DEFINER is what actually breaks the loop. It makes the function run as its owner, not as the calling authenticated role, so the SELECT inside it doesn't re-trigger project_members's RLS policy the way a plain inline subquery would. The check now happens from outside the policy chain instead of one more layer inside it.

`grant all` looks reckless until you know what's actually gating access

The grants on all three tables, verbatim from migration 001:

grant all on public.projects to authenticated;
grant all on public.project_members to authenticated;
grant all on public.tasks to authenticated;

Read on its own, that looks like every authenticated user can do anything to every row in these tables. At the grant level, that's true. In practice, it isn't, because grants and policies answer two different questions. The grant says which commands the authenticated role may attempt at all. The policy says which specific rows that attempt is allowed to touch. Postgres checks both, in order. grant all opens the door to trying an UPDATE. USING and WITH CHECK decide whether any row actually moves.

By contrast, activity_log takes one narrow SELECT grant, nothing else, because nothing but a SECURITY DEFINER trigger was ever meant to write to it. projects, project_members, and tasks are different tables with a different job. Real users insert, update, and delete their own rows directly, so the grant has to allow it. All of the isolation work moves onto the policies instead, on purpose.

A denied read returns nothing, a denied write returns zero rows

Postgres RLS denies reads by filtering, not erroring, a SELECT returns fewer rows, never a permission error for a correctly-denied row. So when you do see a permission error on a SELECT, it's a missing grant, not RLS doing its job.

The write side behaves differently, and it's worth being precise about it. A SELECT with a failing USING clause just comes back with fewer rows, or none, no error to catch. An UPDATE or DELETE with a failing USING clause looks the same on the surface, UPDATE 0, DELETE 0, no error. But a write can also go loud: if a row passes USING and gets selected for the update, and the resulting row then fails WITH CHECK, Postgres raises an actual error, new row violates row-level security policy. Reads can only go quiet. Writes can go quiet or loud, depending on which clause fails and when.

Know which one you're looking at before you debug it.

User A cannot read, update, or delete User B's task, and the terminal proves it

Setup: two real users on Atlas's local Supabase stack, connected with plain psql, no application code involved. e2e-primary owns a project and a task. e2e-secondary owns a separate project and a separate task. Each is authenticated by setting request.jwt.claims directly in the session, the same value Supabase's PostgREST layer would set from a real JWT.

As e2e-primary, against a task owned by e2e-secondary:

set role authenticated;
select set_config('request.jwt.claims', json_build_object('sub', '3cb56877-7dfa-4dc9-ae26-9f48a5a95bfe', 'role', 'authenticated')::text, false);

select id, title, project_id from public.tasks where id = '66666666-6666-6666-6666-666666666666';
-- id | title | project_id
-- ----+-------+------------
-- (0 rows)

update public.tasks set title = 'HACKED BY A' where id = '66666666-6666-6666-6666-666666666666';
-- UPDATE 0

delete from public.tasks where id = '66666666-6666-6666-6666-666666666666';
-- DELETE 0

reset role;

UPDATE 0 on its own would be ambiguous. It looks identical to a WHERE clause that simply matched nothing, RLS or no RLS. The step that actually proves isolation is the read-back, as superuser, right after:

select id, title, project_id from public.tasks where id = '66666666-6666-6666-6666-666666666666';

--                   id                  |               title               |              project_id
-- --------------------------------------+------------------------------------+--------------------------------------
--  66666666-6666-6666-6666-666666666666 | Adversarial Proof Task B (rerun)  | 55555555-5555-5555-5555-555555555555
-- (1 row)

The row is still there. Still owned by e2e-secondary's project. Still holding its original title. Nothing moved.

Running the same three commands the other direction, e2e-secondary against e2e-primary's task, produced the same shape of result: zero rows on the SELECT, UPDATE 0, DELETE 0, and a superuser read-back confirming e2e-primary's task kept its original title. The denial isn't a one-way artifact of a single policy. It holds in both directions.

Honest scope, and where this doesn't reach

The proof you just ran is a point-in-time manual check, not a guarantee that holds over time. Nothing re-runs it, so a later policy change could break isolation silently and no test would notice. To close that, wire the same kind of adversarial check into CI with a tool like pgTAP or pg_prove, so a broken policy fails the build instead of waiting on a reviewer to spot it.

And the proof assumes what any RLS setup has to assume: a credential that bypasses RLS entirely, whether that's superuser, the service role, or a leaked connection string, sits outside this threat model. These policies gate the authenticated role going through the app's normal connection. They were never built to survive a stolen database password, and no policy design changes that.

If you're running RLS on a multi-tenant schema of your own, don't take a policy's word for it. Run the version of this test against your own tables, with two real users and real credentials, and read what actually comes back.

Build a Postgres Audit Log Your Application Code Cannot Bypass

Uduak Ukpong — Sun, 13 Sep 2026 16:29:27 +0000

Most audit logs get written by application code. A service function calls the database, then calls a logging function right after, two separate steps, two separate places to remember. Forget the second call in one code path, a new API route, an admin script, a one-off fix run straight from a database console, and that mutation happens with no record of it. Nothing breaks. No error. The gap just sits there undiscovered until the day you need history that isn't there.

The fix isn't a better logging library or a stricter code review checklist. It's moving the write itself out of application code entirely. If the database writes the log row as a direct consequence of the mutation, in the same transaction, there's no second step left for anyone to forget. The log becomes a property of the schema, not a habit every contributor has to maintain.

Here's how to build that, with Postgres triggers, using a real activity_log table and real trigger functions from Atlas, a project-management app I built.

The table the whole pattern hangs off of

create table public.activity_log (
  id uuid primary key default gen_random_uuid(),
  project_id uuid not null references public.projects(id) on delete cascade,
  actor_id uuid references public.profiles(id) on delete set null,
  actor_name text not null,
  verb text not null check (verb in (
    'project_created', 'project_updated',
    'task_created', 'task_status_changed', 'task_updated', 'task_deleted',
    'member_added', 'member_removed'
  )),
  entity_type text not null check (entity_type in ('project', 'task', 'project_member')),
  entity_id uuid,
  entity_name text not null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index activity_log_project_id_created_at_idx
  on public.activity_log (project_id, created_at desc);

A few of these columns are doing more work than they look like.

actor_id is nullable and set to null on delete, but actor_name is a plain, required text column. That pairing is deliberate. A log entry has to outlive the user it's about. Delete a profile later, offboarding, a right-to-erasure request, whatever, and the log keeps the "who" even after that foreign key is gone.

verb is a closed enum, enforced with a CHECK constraint, not a generic insert or update or delete flag. That's what lets this read like an actual activity feed, task_status_changed, not a database changelog nobody wants to read.

entity_type, entity_id, and entity_name together are a polymorphic pointer. One table logs history for three unrelated tables, projects, tasks, and project members, instead of three separate log tables.

metadata jsonb is where the real diff lives. Its shape depends on which verb produced it, more on that shortly.

The index matches the one query this table serves: a project's own recent activity, newest first.

The permission layer, not just the trigger

None of that matters if the row that just wrote a log entry can also edit its own history afterward. This is the part that audit-log write-ups often skip, and it's the part that makes "cannot bypass" true instead of aspirational. Check what the application's own database role is allowed to do to this table:

alter table public.activity_log enable row level security;

create policy "activity_log: members can view"
  on public.activity_log for select
  to authenticated
  using (is_project_member(auth.uid(), project_id));

grant select on public.activity_log to authenticated;

Worth naming up front: this is Supabase's Postgres, authenticated is the role every logged-in request connects as, and auth.uid() reads the user's id from the JWT claims PostgREST attaches to that connection, roughly a request-scoped role plus decoded session token on most other stacks.

There's one SELECT policy, scoped to project members, and no INSERT, UPDATE, or DELETE grant to authenticated anywhere in the migrations, the role the app itself connects as. Postgres denies every privilege by default unless it's explicitly granted. No grant for writing or altering a row means no path to do either. Not a weak path, no path at all.

Which raises the obvious question: if the app's own role can't INSERT into this table, how does a new row ever get written?

The trigger function

security definer. Each function that writes to activity_log is declared with it. Here's the one attached to tasks, the most complete of the three:

create or replace function public.handle_task_activity()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
  _actor uuid := auth.uid();
  _changes jsonb := '[]'::jsonb;
begin
  if tg_op = 'INSERT' then
    insert into public.activity_log
      (project_id, actor_id, actor_name, verb, entity_type, entity_id, entity_name, metadata)
    values
      (new.project_id, _actor, activity_actor_name(_actor), 'task_created', 'task', new.id, new.title, '{}'::jsonb);
    return new;
  end if;

  if tg_op = 'DELETE' then
    if exists (select 1 from public.projects where id = old.project_id) then
      insert into public.activity_log
        (project_id, actor_id, actor_name, verb, entity_type, entity_id, entity_name, metadata)
      values
        (old.project_id, _actor, activity_actor_name(_actor), 'task_deleted', 'task', old.id, old.title, '{}'::jsonb);
    end if;
    return old;
  end if;

  if new.status is distinct from old.status then
    insert into public.activity_log
      (project_id, actor_id, actor_name, verb, entity_type, entity_id, entity_name, metadata)
    values
      (new.project_id, _actor, activity_actor_name(_actor), 'task_status_changed', 'task', new.id, new.title,
       jsonb_build_object('from', old.status, 'to', new.status));
  end if;

  if new.title is distinct from old.title then
    _changes := _changes || jsonb_build_object('field', 'title', 'from', old.title, 'to', new.title);
  end if;
  if new.description is distinct from old.description then
    _changes := _changes || jsonb_build_object('field', 'description', 'from', old.description, 'to', new.description);
  end if;
  if new.due_date is distinct from old.due_date then
    _changes := _changes || jsonb_build_object('field', 'due_date', 'from', old.due_date, 'to', new.due_date);
  end if;

  if jsonb_array_length(_changes) > 0 then
    insert into public.activity_log
      (project_id, actor_id, actor_name, verb, entity_type, entity_id, entity_name, metadata)
    values
      (new.project_id, _actor, activity_actor_name(_actor), 'task_updated', 'task', new.id, new.title, jsonb_build_object('changes', _changes));
  end if;
  return new;
end;
$$;

Take it piece by piece.

security definer is the actual mechanism answering the question above. A function marked this way runs with its owner's privileges, typically a privileged migration role, not the caller's. The application's authenticated role triggers it with an ordinary UPDATE on tasks, a table it genuinely can write to. The function itself, running as its owner, is what reaches into activity_log. The app never touches that table directly, and doesn't need to.

_actor uuid := auth.uid() captures the acting user server-side, from the session's JWT claim, when the trigger fires. It isn't passed in as an argument, or set by a request body. Whatever the database itself believes the caller's identity is, that's the actor. Nothing upstream of the database gets a vote in who's blamed for a change.

The tg_op branches come first and return early. INSERT logs a task_created row and stops. DELETE logs task_deleted and stops, guarded by an exists check, since a cascade delete can reach this trigger after the project itself is already gone. Everything below those two early returns only runs for an update.

Status changes get their own verb, task_status_changed, with its own two-field metadata shape, separate from the generic diff below. That's a deliberate modeling choice: status is the one field this schema treats as worth naming on its own, so the log captures how a task moves through its lifecycle, not just that it changed.

Everything else, title, description, due date, falls into the generic path: an is distinct from check per column, each appending a {field, from, to} object to a growing array, only inserted if non-empty. Worth being honest about the shape: it's four hand-written comparisons, not a loop over every column. Add a fifth trackable field to tasks, and it needs a fifth comparison here too, or it changes silently. The pattern guarantees no mutation skips logging, since every branch either returns early or falls through to this check. It doesn't guarantee every column change reaches the diff. That still depends on the function knowing the column exists.

Wiring it up

The function above does nothing until something calls it:

create trigger on_task_created_activity
  after insert on public.tasks
  for each row execute function public.handle_task_activity();

create trigger on_task_updated_activity
  after update on public.tasks
  for each row execute function public.handle_task_activity();

create trigger on_task_deleted_activity
  after delete on public.tasks
  for each row execute function public.handle_task_activity();

Three separate statements, one per event, all pointing at the same function. The tg_op branching inside tells them apart at runtime. AFTER, not BEFORE: the row already exists in the transaction, recording something that already happened, not something still pending. FOR EACH ROW, not FOR EACH STATEMENT: an update touching ten tasks fires this ten times, since the diff is per row, not per statement.

Proving it, against something that isn't the app

Everything above is a claim until it's tested against something other than the application. Here's a real psql session, connected directly to Atlas's database. No app code in this transcript.

A task that actually exists, currently todo:

select status from public.tasks where id = '7be1a49e-4002-4070-adb1-5053d4ea2645';
 status
--------
 todo

Set the session to look like the app's own role and a real logged-in user. One gotcha worth knowing first: set_config's third argument has to be false, not true, or the JWT claim only lasts one statement. Every query after it silently loses the session, auth.uid() goes back to null, and the write that follows just matches zero rows, no error, nothing to notice.

SET ROLE authenticated;
select set_config('request.jwt.claims',
  '{"sub":"6e9c97b7-6219-4905-bc66-9e9901261de9","role":"authenticated"}', false);

A direct update, no application involved:

update public.tasks
set status = 'in_progress'
where id = '7be1a49e-4002-4070-adb1-5053d4ea2645';
UPDATE 1

Check activity_log for that project:

select verb, entity_name, metadata, created_at
from public.activity_log
where project_id = 'a0e8d23e-fc6a-45aa-928c-c69df965a479'
order by created_at desc limit 1;

         verb          | entity_name |               metadata                |          created_at
------------------------+-------------+----------------------------------------+-------------------------------
 task_status_changed    | E2E task    | {"to": "in_progress", "from": "todo"} | 2026-09-12 11:36:10.428584+00

A row appeared. Nobody called a logging function. Nobody imported an audit module. The UPDATE itself produced it, because the trigger fires on the operation, not on whichever code path performed it. A raw DELETE against the same task produces the same result, a task_deleted row, same mechanism, nowhere to have skipped it.

That's one half of the claim: the mutation can't happen without the log row appearing. The other half is sharper. Can the log itself be edited afterward, to cover up what happened?

update public.activity_log
set metadata = '{"tampered": true}'::jsonb
where id = '41ad0934-3e0a-4ea6-9e9a-318957332fb4';

ERROR:  permission denied for table activity_log
HINT:  Grant the required privileges to the current role with: GRANT UPDATE ON public.activity_log TO authenticated;

delete from public.activity_log
where id = '41ad0934-3e0a-4ea6-9e9a-318957332fb4';

ERROR:  permission denied for table activity_log
HINT:  Grant the required privileges to the current role with: GRANT DELETE ON public.activity_log TO authenticated;

Same role, same active session, same table, and Postgres refuses outright. Not a silently filtered zero rows, a hard permission error, because the grant that would make either statement legal doesn't exist. The claim, demonstrated twice: the application's own role can produce the log, and cannot touch it once it exists.

What this costs, and what it doesn't guarantee

Every mutation to a tracked table now runs an extra function, on every insert, update, and delete, in the same transaction. For a handful of tables in a project-management app, that overhead doesn't register. For a table taking write load in the thousands per second, measure it first, not something to wave off as "just a trigger."

One more scoping note: authenticated also holds TRUNCATE by Supabase default, but PostgREST has no REST verb mapping to it, so nothing in the app's real surface can reach it. Revoking it protects against nothing, since reaching it needs the database password directly, and anyone holding that can just re-grant it or drop the table outright. The claim here is about the application's write path, not surviving a compromised credential, which no schema defends against.

security definer earns the trust it's given by staying boring. It runs with elevated privilege, so the discipline that matters is keeping its logic fixed and hardcoded, no dynamic SQL a caller could influence. Before trusting this pattern in your own schema, audit every security definer function in it, not just the trigger you just wrote:

select p.proname, p.prosecdef
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.prosecdef = true;

Read every result's body for EXECUTE, format(), or any string-built query. The functions this pattern adds, the three handle_*_activity triggers and one small actor-name helper, are each a handful of lines of fixed logic, no dynamic SQL, no string concatenation, nothing built from caller input. That's what makes the elevated privilege safe here. Don't assume it holds elsewhere. Check each function on its own.

The per-column comparisons are an ongoing maintenance cost, not a one-time setup step: a new trackable column needs its own comparison added, or its changes go uncaptured.

project_id also uses on delete cascade, deliberate here since a deleted project leaves no feed to show. To make the log outlive the project instead, switch that FK to on delete set null, like actor_id, or drop it, rather than just removing the cascade, which would only fail the project delete on a foreign-key violation.

Skip this if the data doesn't need it. A trusted internal tool with no compliance requirement, and no real cost to a missed entry, doesn't need a function running extra on every write. Reach for this when the log has to survive the application being wrong, buggy, or bypassed, not just a developer remembering to call a function.

Stop Using a mounted Flag to Fix Theme Flash: Use useSyncExternalStore Instead

Uduak Ukpong — Thu, 10 Sep 2026 17:42:22 +0000

The login page went blank in production. Not a broken layout, not a missing stylesheet. Blank: the whole subtree that the sidebar belonged to just disappeared from the DOM right after the page finished loading. It only happened on a real production build (next build && next start), never in next dev, and only when the stored theme preference was "dark".

The cause was a theme toggle.

If you've worked with the Next.js App Router, you already know the usual shape of a dark-mode toggle: some client state holding "light" or "dark", an effect that writes it onto the DOM, a button that flips it. You've probably also met the classic symptom that comes with getting this wrong: a flash of the wrong theme for a frame or two on load. What you might not know, since it's a narrower topic, is Trusted Types: a browser security feature that helps prevent DOM-based XSS by blocking untrusted strings from being written into dangerous sinks like innerHTML, unless an approved policy sanctions them. If a Trusted Types policy is enforced and something tries to write raw HTML through a path the policy didn't approve, the browser throws instead of rendering it. Keep that in your pocket. It's the reason this bug was a crash and not just a flicker.

This is Atlas, a project-management app I built solo (real Postgres row-level security, an append-only activity log written entirely by database triggers, accessibility treated as a requirement rather than a feature). It's not running at scale with real traffic. It doesn't need to be. Not for this story. What it has is a CI pipeline, a test suite, and one bug I had to actually diagnose, not simulate for a tutorial.

Here's what caused it, why the fix everyone reaches for first doesn't actually fix it, and what does.

The disagreement under the flash

"Flash of wrong theme" undersells what's happening. It's not really a timing problem. It's a disagreement. Next.js renders your app to HTML on the server, where there's no window, no localStorage, no way to know what a returning visitor picked last time. React then hydrates that HTML on the client, attaching event handlers and reconciling its idea of the tree against what's already in the DOM. If the client's first render produces different output than the server did, React has a mismatch on its hands.

In Atlas, that mismatch traced back to one function: the lazy initializer inside ThemeContext's useState call.

const [theme, setTheme] = useState<Theme>(() => {
  if (typeof window === "undefined") return "light";

  const stored = localStorage.getItem("atlas-theme");
  if (stored === "light" || stored === "dark") return stored;

  // Defer to system preference if no stored theme, and persist that choice.
  const system = window.matchMedia("(prefers-color-scheme: dark)").matches
    ? "dark"
    : "light";

  localStorage.setItem("atlas-theme", system);

  return system;
});

Read that typeof window check carefully, because the bug is hiding in what it doesn't guard. On the server, window is genuinely undefined, so this returns "light", always. But on the client, this initializer doesn't just run once at some safe point after mount. It runs during React's very first client render, the hydration render, and by then window already exists. So the client's first render skips the "light" fallback entirely and reads localStorage immediately, returning whatever the user actually had stored.

Server HTML gets built from the forced "light" branch. But on the client, that first render reads whatever was actually stored. If your stored theme was "dark", the two disagree on the very first paint, before React has done anything else.

Why it was a crash, not a flicker

Most of the time, a hydration mismatch is just visually annoying: React discards the mismatched subtree and regenerates it client-side, and you get a flash. In Atlas, Sidebar.tsx was the one component whose render output actually branched on theme (the icon, the label, the button), so it was the one place this showed up at all.

In React, when that client regeneration reaches an inline script, React recreates the script element by first setting a raw <script> string through a temporary div's innerHTML. Atlas runs a Trusted Types policy through a script in app/layout.tsx that only defines createScriptURL (needed for the app's own chunk loader), not createHTML. Recovery hit a browser API it wasn't authorized to use, and threw. In production, with the policy actually enforced, that meant recovery failed. The failure took the rest of the screen with it. It just stayed blank. I'm not going to get into the design of that Trusted Types policy here. That's a separate topic. What matters for this piece is the consequence: a hydration mismatch that would be a cosmetic flash almost anywhere else was a blank screen in production.

The tempting fix, and where it actually falls short

The standard answer to "my render depends on something the server can't know" is a mounted flag. It's not from Atlas. It's just the shape most of us reach for:

function ThemeIcon({ theme }: { theme: 'light' | 'dark' }) {
  const [mounted, setMounted] = useState(false);

  useEffect(() => {
    setMounted(true);
  }, []);

  if (!mounted) {
    return <IconPlaceholder />;
  }

  return theme === 'light' ? <Moon /> : <Sun />;
}

Give this a fair hearing, because it does solve the mismatch. Server and client both render the placeholder on the first pass, since mounted starts false in both places. No disagreement, no crash. The real icon only shows up once useEffect fires after mount, which is client-only by definition.

Here's where it stops being a fix and starts being a workaround. It always requires a post-mount render before showing the real value, even when there is no hydration mismatch to avoid. It trades a wrong-icon flash for a placeholder-then-pop-in flash (better, but still a flash). Also, it doesn't touch the actual problem: React still treats its own state as the source of truth. The flag is a manual "don't trust yourself yet" gate bolted onto the read side, and you have to remember to bolt it on again at every component that needs the theme. Extract that pattern into a shared hook, which you inevitably will once you have two or three call sites, and you're one small step from useSyncExternalStore anyway, just without the guarantee it actually gives you.

The deeper issue: theme was never really React's state to own in the first place.

Where the theme actually lives

By the time React starts hydrating anything, the correct theme is already sitting on the DOM. Atlas sets it with an inline script in the root layout, written as a raw <script> tag via dangerouslySetInnerHTML (not next/script, which would load too late to beat the paint):

(function(){try{var t=localStorage.getItem('atlas-theme');if(t==='light'||t==='dark'){document.documentElement.setAttribute('data-theme',t);}else{var d=window.matchMedia('(prefers-color-scheme: dark)').matches;document.documentElement.setAttribute('data-theme',d?'dark':'light');}}catch(e){}})();

It reads the stored preference, falls back to matchMedia if there isn't one, and sets data-theme on document.documentElement. The whole thing is wrapped in a try/catch, so a blocked localStorage (private browsing, strict cookie settings) just skips silently instead of breaking the page. This runs before React hydrates anything. data-theme is correct on the <html> element from the first paint.

React just doesn't know that. Nothing has told it. And that's the actual bug: not when the value becomes available, but which system owns it. The theme lives in the DOM. React needs to read an external source, not maintain its own parallel copy of the same fact.

That's exactly what useSyncExternalStore is for.

The implementation

"use client";

import { useSyncExternalStore } from "react";

export type DisplayedTheme = "light" | "dark" | "pending";

function subscribe(callback: () => void) {
  const observer = new MutationObserver(callback);
  observer.observe(document.documentElement, {
    attributes: true,
    attributeFilter: ["data-theme"],
  });
  return () => observer.disconnect();
}

function getSnapshot(): DisplayedTheme {
  return document.documentElement.getAttribute("data-theme") === "dark"
    ? "dark"
    : "light";
}

function getServerSnapshot(): DisplayedTheme {
  return "pending";
}

export function useDisplayedTheme(): DisplayedTheme {
  return useSyncExternalStore(subscribe, getSnapshot, getServerSnapshot);
}

Three functions, each doing one job. getSnapshot doesn't hold state. It reads data-theme straight off documentElement, which means it's always reporting what's actually there, not what React last remembered setting. subscribe sets up a MutationObserver scoped tightly with attributeFilter: ["data-theme"], so it only fires when that one attribute changes, not on every DOM mutation in the document. And getServerSnapshot is where the interesting decision lives, but I'll get to that in a moment.

Notice what useDisplayedTheme doesn't do: it doesn't write anything. Atlas keeps write and read on two separate paths on purpose. ThemeContext still owns the toggle:

function toggleTheme() {
  const next: Theme = theme === "light" ? "dark" : "light";
  setTheme(next);

  localStorage.setItem("atlas-theme", next);
}

Clicking the button updates React's own theme state and writes the choice to localStorage, both inside toggleTheme itself. A useEffect elsewhere in ThemeContext then reflects that updated state onto data-theme. But Sidebar.tsx, the component that actually renders based on the theme, doesn't read theme from that context at all anymore. It reads useDisplayedTheme():

const { toggleTheme } = useTheme();
const displayedTheme = useDisplayedTheme();
const isThemePending = displayedTheme === "pending";

Write goes through the context's state, because a click handler needs somewhere to hold intent. Read goes through the DOM, because the DOM is what's actually true, including on that first render where React's own state hasn't caught up yet. Two different sources, chosen for two different jobs.

The honest third state

getServerSnapshot returning "pending" instead of guessing "light" is the detail that makes the rest of this actually work, not just look tidy.

During hydration, useSyncExternalStore calls getServerSnapshot and only getServerSnapshot. It never touches getSnapshot on that first pass. So the server renders "pending", and the client's first render also produces "pending", by construction, since both sides call the exact same function. There's no way for these two to disagree, because they're not two guesses that happen to agree. They're the same fixed answer, sourced from the same place, every time. No mismatch, no recovery path, no innerHTML write for Trusted Types to reject.

The real theme shows up afterward, once the observer's getSnapshot takes over, and that's an ordinary re-render triggered by a normal state update, not an error being recovered from. Sidebar.tsx uses isThemePending to make that in-between moment honest instead of invisible:

<button
  onClick={toggleTheme}
  disabled={isThemePending}
  aria-busy={isThemePending}
  aria-label={
    isThemePending
      ? "Loading theme preference"
      : displayedTheme === "light"
        ? "Switch to dark mode"
        : "Switch to light mode"
  }
>

A mounted flag's placeholder state is a UI trick, something to look at while the real thing loads. "pending" is a real value in the type (DisplayedTheme = "light" | "dark" | "pending"), one the button's disabled and aria-busy attributes respond to directly. The type system knows there are three possible states here, not two, so nothing downstream can quietly pretend the answer is always known.

What this costs, and when it isn't worth it

There was a cleaner option on the table: read the theme from a cookie on the server and pass it down, which removes even the brief "pending" window entirely. It got rejected. cookies() unconditionally opts a route into dynamic rendering, and several of Atlas's routes are static today. Giving that up wasn't worth it. It would mean restructuring the dashboard layout out of being a single client component, just to remove a sub-100ms loading state. That's a call that could reasonably go the other way in a different app. It's not a universal rule, just the one that fit here.

More broadly, none of this is worth reaching for unless two things are both true: your app has state set on the DOM before React hydrates, and something's render output actually branches on that state. If your theme only drives CSS custom properties and nothing in your JSX conditionally renders based on it, there's no mismatch to have in the first place. data-theme can just sit there and your styles read it, no hook required.

The mainstream approach is to let the theme drive CSS (a class or data attribute on the html element that Tailwind or your stylesheet reads) and keep your JSX theme-agnostic. Most theme-dependent UI, including a toggle icon, is better handled that way, often by rendering both states and swapping them with CSS. I branched on the theme in JavaScript for one reason: the toggle's aria-label ("Switch to dark mode" versus "Switch to light mode") is an accessible name, and you cannot set an accessible name from a CSS class. Accessibility was a first-class requirement in Atlas, not a coat of paint, so the toggle had to announce its actual state to assistive tech. Once one thing in that component genuinely needed the theme as a JavaScript value, reading it correctly was the real problem, and that is what useSyncExternalStore solves. Sidebar.tsx needed this because it was the one place in Atlas where a component's actual output, not just its appearance, depended on a value React didn't have yet. That's the specific condition to look for, not "I have a theme toggle."