Case Study · Data Access Control

Access control as architecture, built twice.

Two different products (a team-scheduling SaaS and an art-consignment portal) needed the same thing: real per-tenant data isolation, financial-field write restrictions, and gated onboarding, with no application server sitting in front of Postgres to enforce any of it. I designed that access-control layer twice, in SQL, on Neon: row filters, view-level security, column-level grants, and provisioning triggers. I also hit the same identity bug on both, and only had to debug it once. Here is the stack, and what building it a second time taught me that the first time didn’t.

What
Access-control layer for two multi-tenant products
Stack
Neon Postgres · Row-Level Security · Data API (PostgREST) · Neon Auth
Role
Solo: schema, policy design, security review
Status
Keeping Cadence live · Dank Omphalos schema shipped, backend wiring in progress

Two products, one hard requirement

Both apps are static front ends with no server of their own: a deliberate cost and complexity constraint, not a limitation I was working around. Keeping Cadence is a live scheduling SaaS: teams, invites, and a plan-vs-actual-hours permission model. Dank Omphalos is an art-consignment portal: artists need to see their own inventory and payout math, and absolutely nothing else, including each other’s numbers, and including their own payout percentage as a writable field.

Different domains, same non-negotiable requirement: with no middle tier to check a permission, every access rule has to live in the database and the database has to enforce it whether or not the client bothers to ask nicely. That is the same discipline a governed data platform has to guarantee at every layer. Here it’s hand-built in SQL instead of bought off a shelf, which meant designing (and re-deriving) each guarantee myself.

The stack, four layers deep

The same four Postgres primitives recur on both products, stacked as defense in depth: a row filter first, a view that can’t quietly outrun that filter, a column-level restriction on top of the row-level one, and a provisioning gate that decides who gets a row at all.

Access control · four layers, two products Every layer is a Postgres primitive, not an application check. No server sits between the request and this stack. Identity · JWT sub claim read once, per request, via app_uid() 1 · Row-level security A policy on every table filters which rows a caller can even see both products 2 · security_invoker views Views run under the caller’s RLS. A plain view runs as owner and leaks Dank Omphalos 3 · Column-level grants Revoke every write, grant back only the columns that are safe to touch both products 4 · Provisioning triggers Access is granted at the row level before a login even exists Dank Omphalos Same four layers: applied once to team schedules, again to artist payouts.
No box in this stack is an application check. Every guarantee is enforced by Postgres itself.

Layer 1 · row filters: who can see a row at all

On Keeping Cadence, a policy decides what the signed-in user can see: their own profile, teams they own or joined, schedules assigned to them. On Dank Omphalos it’s narrower and higher-stakes. An artist’s policy is one line:

create policy artist_self on artists for select using (user_id = app_uid());

Same shape, different blast radius. On the scheduling app, a leak means someone sees a colleague’s hours. On the consignment portal, a leak means one artist sees another’s advances and payout math, a much less forgivable failure. The policy is trivial to write; the discipline is remembering that every table needs one, including the ones added six weeks later that don’t feel like they hold anything sensitive.

Layer 2 · the view that can’t quietly bypass the filter

The artist dashboard reads from a view that joins pieces to artists and derives the owed-on-sale figure. Views in Postgres default to running as their owner, not the caller, which means a plain view over an RLS-protected table silently bypasses the policy that protects it. The fix is one clause, easy to forget and expensive to skip:

create view artist_pieces with (security_invoker = true) as select …

Without it, every artist’s dashboard would quietly serve every artist’s numbers, and nothing in the RLS policy would catch it. The leak happens one layer above where you’d think to look for it.

Layer 3 · column grants: restrict the write, not just the row

Row-level security answers “which rows,” not “which fields.” An artist is allowed to update their own row (name, bio, socials, which fields show on the public gallery) but never their own share, the payout percentage that Dank Omphalos owes them. RLS alone can’t express that distinction, so the fix drops down a level, to grants:

revoke insert, update, delete on artists from authenticated;
grant update (name, location, instagram, website, bio, visibility, onboarded_at) on artists to authenticated;

Revoke everything, then grant back exactly the safe subset. The same pattern runs in reverse on Keeping Cadence, hiding a team’s secret invite token from an ordinary SELECT a member shouldn’t be able to read. It’s the same primitive solving two different problems (write restriction on one product, read restriction on the other) because the underlying question is always “which columns, not just which rows.”

Row-level security answers who can see a row. It never answers which of its fields they should be allowed to touch.

Layer 4 · the trigger as the gate before login exists

Dank Omphalos is invite-only, and I didn’t want a separate admin flow for granting access. Instead, adding an artist row with their email IS the invite. A trigger on Neon Auth’s own neon_auth."user" table blocks any signup whose email isn’t already on an artist row; a second trigger auto-links a successful signup to that row, and self-heals a stale link left over from a deleted-and-recreated test account, so re-invites never need a manual fix:

if not exists (select 1 from artists a where lower(a.email) = lower(NEW.email)) then raise exception 'Not invited';

Access control here starts before the account does. There is no toggle to misconfigure and no admin panel that could grant the wrong thing. The row is the permission.

The bug I found twice, and only had to debug once

Building this stack the first time, on Keeping Cadence, Postgres’s built-in auth.uid() came back NULL inside every policy and function. On Neon, identity only resolves under a session mechanism the Data API path never initializes. Every “who am I” check silently failed closed: a brand-new signup couldn’t even create its own profile row. Root cause took real digging. The failure mode looks identical to a policy that’s simply wrong, not one that’s reading a NULL identity.

The fix is a two-line helper that reads the verified sub claim straight from the JWT that PostgREST does populate, instead of the built-in function that doesn’t resolve:

create function app_uid() returns text language sql stable as $$ select current_setting('request.jwt.claims', true)::jsonb ->> 'sub' $$;

When the same platform quirk showed up building Dank Omphalos’s policies weeks later, I didn’t re-debug it. I wrote app_uid() into the schema on day one, with the failure mode documented inline as a comment so the next reader doesn’t lose the same afternoon I did. The interesting part isn’t finding a platform gotcha. It’s that the second project shipped with the fix already built in, because the first one turned a live incident into a reusable piece of institutional knowledge instead of a war story nobody could act on.

What this demonstrates

  • Fine-grained access control as a first-class design problem: row filters, column-level grants, and view-level security boundaries, reasoned about independently rather than treated as one setting to flip.
  • Root-cause debugging that compounds: a platform-level identity bug, diagnosed once under real production pressure, encoded into a reusable helper and carried into the next project instead of re-discovered.
  • Designing for the failure that costs the most: the effort scaled with the stakes: a scheduling leak is embarrassing, a payout-field leak is a business problem, and the schema reflects that difference in where the hardening went.
  • Security as something written down, not assumed: every non-obvious guarantee (the security_invoker requirement, the auth.uid() gotcha, the invite-gate rationale) lives as a comment in the schema next to the code it protects, not in a doc that drifts away from it.
Postgres / RLS Row & Column Security Data Access Control Neon Multi-Tenant Architecture

Keeping Cadence is live at app.keepingcadence.com. Dank Omphalos’s portal schema is built and tested against live data; the artist-facing app is still being wired to production. This writeup describes my own projects; nothing here is confidential, and specific business figures have been omitted.