Seven RLS patterns we use across all 27 Supabase tables in Storyie

Storyie stores its data in Supabase PostgreSQL, and every table in the database has Row Level Security enabled. After designing and operating policies for all 27 tables, we found the design space collapsed into a small set of repeating shapes. This post catalogs those seven patterns, explains the intent behind each, and surfaces the edge cases that cost us time.

This is a companion to our earlier work on managing Drizzle schemas and RLS policies together in a monorepo. That post covered the tooling side — pgPolicy(), pnpm generate, pnpm migrate. Here we focus on the policy logic itself.

TL;DR

The sections below go through each in order.

Pattern 1: Owner-only CRUD

The most common pattern in Storyie. Any table that is completely private to the user who created it — push notification tokens, per-device settings, personal preferences — gets this.

-- SELECT
USING (user_id = (SELECT auth.uid()))
-- INSERT
WITH CHECK (user_id = (SELECT auth.uid()))
-- UPDATE
USING (user_id = (SELECT auth.uid()))
WITH CHECK (user_id = (SELECT auth.uid()))
-- DELETE
USING (user_id = (SELECT auth.uid()))

Always wrap auth.uid() in a subquery

Both Supabase's documentation and the PostgreSQL query planner reward this habit. The undecorated auth.uid() call may be re-evaluated per row. The subquery form (SELECT auth.uid()) tells the planner the value is stable for the query's lifetime, so it is computed once.

-- may re-evaluate per row
USING (user_id = auth.uid())

-- evaluated once, reused for every row
USING (user_id = (SELECT auth.uid()))

On small tables the difference is invisible. On tables with tens of thousands of rows it shows up. We apply the subquery form uniformly so the decision is never per-table.

Pattern 2: Public or owner SELECT

Posts and diary entries in Storyie have a visibility column: private or public. The SELECT policy reflects that directly.

-- SELECT: public entries readable by anyone; private entries readable only by owner
USING (
  visibility = 'public'
  OR user_id = (SELECT auth.uid())
)

-- INSERT / UPDATE / DELETE: owner only
WITH CHECK (user_id = (SELECT auth.uid()))

We use a two-value pgEnum (private | public) rather than a richer set like friends_only or followers_only. The reasons are practical:

1. Follower-based policies add a join to every row scan. Checking EXISTS (SELECT 1 FROM follows WHERE ...) inside a policy is expensive when the table is large, because the subquery runs against every candidate row.
2. Two states are enough for the product today. A personal diary app mostly lives in "just me" vs "the world."
3. The enum is extensible. Adding a third state later means adding a value to the enum and extending the policy — not rewriting it.

RLS policies should match the access model the product actually has, not the one you might want someday. Overbuilt policies are harder to audit.

Pattern 3: Everyone reads, authenticated writes

Likes and comment counts need to be readable without authentication — a logged-out user browsing a public diary should see the reaction count. But writing should require login, and each user should only be able to write their own records.

-- SELECT: any authenticated session can read all rows
FOR SELECT TO authenticated USING (true)

-- INSERT: authenticated users can create their own records only
FOR INSERT TO authenticated WITH CHECK (user_id = (SELECT auth.uid()))

-- DELETE: authenticated users can delete their own records only
FOR DELETE TO authenticated USING (user_id = (SELECT auth.uid()))

Use the TO clause to make role expectations explicit

TO authenticated excludes the anon role at the policy level rather than inside the USING expression. Supabase issues JWTs for both authenticated and anonymous sessions by default, so it is worth being deliberate about which roles a policy admits.

For comment bodies on public diaries, we loosen the SELECT slightly:

-- Comments: unauthenticated visitors can read
FOR SELECT TO public USING (true)

-- Comments: only authenticated users can write
FOR INSERT TO authenticated WITH CHECK (author_id = (SELECT auth.uid()))

TO public matches every role including anon. The distinction between TO public and TO authenticated is a product decision — it depends on whether you want anonymous visitors to see the content.

Pattern 4: Tenant membership check

Storyie supports shared spaces where more than one user can contribute to the same set of entries. Access to those tables is not gated on user_id but on whether the caller is a member of the relevant tenant.

-- Tenant table: visible only to members of that tenant
FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM members
    WHERE members.tenant_id = tenants.id
      AND members.user_id = (SELECT auth.uid())
  )
)

INSERT, UPDATE, and DELETE for tenant records go through service_role calls from the server rather than direct client writes. Tenant creation is part of the sign-up flow, which runs server-side, so there is no need for a client-facing INSERT policy on the tenants table.

IN vs EXISTS for membership checks

Two SQL shapes work here:

-- IN: materializes the full subquery result
tenant_id IN (
  SELECT tenant_id FROM members
  WHERE user_id = (SELECT auth.uid())
)

-- EXISTS: stops at the first match
EXISTS (
  SELECT 1 FROM members
  WHERE members.tenant_id = tenants.id
    AND members.user_id = (SELECT auth.uid())
)

When a user belongs to one tenant — the common case in Storyie — both produce identical plans. We use EXISTS as a default because it scales better if that assumption changes, and because the correlated form gives the planner a cleaner path to an index on (tenant_id, user_id).

Pattern 5: Role-based permission split

Member management — inviting new members, changing roles, removing members — should only be possible for owners and admins of the tenant. Regular members can read the list but not modify it.

-- SELECT: any tenant member can see the full member list
FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM members
    WHERE members.tenant_id = members.tenant_id  -- same table, correlated
      AND members.user_id = (SELECT auth.uid())
  )
)

-- INSERT: only owner or admin can add members
FOR INSERT WITH CHECK (
  tenant_id IN (
    SELECT tenant_id FROM members
    WHERE user_id = (SELECT auth.uid())
      AND role IN ('owner', 'admin')
  )
)

The self-referential policy

The members table's SELECT policy queries the members table itself. PostgreSQL handles this without recursion — the policy check runs against the existing rows, not the row being inserted — but it is the kind of thing that confuses people when they first read it. We add a comment in the policy definition explaining the structure: "checks the caller's own membership record to decide whether they can see other membership records."

When testing this pattern, always test with: a user who has no membership record at all, a user with a member role, and a user with admin or owner. All three should behave differently.

Pattern 6: service_role writes only

Some tables exist purely as inputs for server-side processes. View logs and raw analytics events are examples: they should only ever be written by our server (a Next.js API route or an Edge Function), and clients should have no read access at all.

-- Only service_role can write; no client access whatsoever
FOR INSERT TO service_role WITH CHECK (true)

No SELECT policy means RLS's default-deny kicks in — authenticated users get an empty result set, not an error, when they query these tables. The server reads them via service_role (which bypasses RLS) to run aggregations and write to summary tables.

Why not allow client writes with validation in the policy?

Three reasons we consistently hear ourselves give:

1. Data integrity. A policy expression can check user_id = (SELECT auth.uid()), but it cannot deduplicate events in a time window or enforce rate limits. Business-rule enforcement belongs in server code.
2. Auditability. Every record in the log went through a server code path, which means it went through logging, error handling, and whatever validation we add to that path over time.
3. Simpler policies. Once the constraint is "only service_role," the policy is WITH CHECK (true). No complex expression to audit.

Pattern 7: INSERT only (write-only)

Activity logs differ from the tables in Pattern 6: users write their own events, but should never be able to read them back through the client. Only the server aggregates and surfaces that data.

-- Users can insert their own activity records
FOR INSERT WITH CHECK (user_id = (SELECT auth.uid()))
-- No SELECT, UPDATE, or DELETE policy — access is denied by default

When RLS is enabled on a table, any operation that has no matching policy is denied. This is Supabase's "default deny" property, and Pattern 7 exploits it deliberately. One INSERT policy is all that exists. Everything else — SELECT, UPDATE, DELETE — is blocked without us writing any additional policy.

The mental model to keep straight: adding a policy opens access for that operation. The absence of a policy closes it. Policies are grants, not restrictions.

Design principles we apply across all patterns

Keep policies co-located with the schema

We declare every policy using Drizzle's pgPolicy() helper in the same TypeScript file as the table definition. When someone opens the schema for posts.ts, they see the columns and the four operation policies in the same file. There is no separate SQL file to cross-reference.

// One named policy per operation
selectPolicy: pgPolicy("posts_select", { for: "select", ... }),
insertPolicy: pgPolicy("posts_insert", { for: "insert", ... }),
updatePolicy: pgPolicy("posts_update", { for: "update", ... }),
deletePolicy: pgPolicy("posts_delete", { for: "delete", ... }),

This also means policy changes land in the same PR as schema changes, which makes access-control review part of the normal code review flow rather than a separate audit.

One policy per operation

Supabase lets you write a single FOR ALL policy that covers every operation. We avoid this. Per-operation policies make it immediately obvious which operations are allowed, debugging a denied request is easier because the policy name in the error narrows the problem, and changing SELECT behavior cannot accidentally affect INSERT.

Always write both USING and WITH CHECK on UPDATE

PostgreSQL falls back to the USING expression when WITH CHECK is omitted from an UPDATE policy. The behavior is usually the same, but the intent is invisible. We write both explicitly to document that we thought about post-mutation state. The most important guarantee WITH CHECK provides here is preventing a user from updating the user_id on a row they own, thereby transferring ownership to someone else.

FOR UPDATE
  USING (user_id = (SELECT auth.uid()))
  WITH CHECK (user_id = (SELECT auth.uid()))

Test three roles for every table

For each table we verify behavior under:

• anon — unauthenticated caller
• authenticated — logged-in user accessing their own rows, then another user's rows
• service_role — server-side caller that bypasses RLS

The Supabase Dashboard SQL editor supports SET ROLE for quick manual checks. For automated testing, we seed controlled data in a test migration and assert access results for each role before shipping policy changes.

Takeaways

RLS in Supabase defaults to denying everything. Building on that foundation means every policy you write is an explicit grant of access, not a fence around an otherwise open table. That inversion is useful: a missing policy is a visible gap, not a silent permission.

After 27 tables and ongoing production operation, the patterns above cover essentially every access shape Storyie needs. The catalog has stayed stable. When a new table shows up, it nearly always fits one of the seven — the decision is which pattern, not how to invent a new one.

The other durable lesson is co-location: policies that live next to the schema they govern get reviewed, tested, and updated together. Policies scattered across separate SQL files drift.

Related Posts

• Database schema design in a pnpm monorepo — how we use Drizzle pgPolicy() to manage schema and RLS together
• Building a Monorepo with pnpm and TypeScript — workspace conventions and the package boundaries that apply to @storyie/database
• Multi-tenant subdomain routing with Next.js — the tenant model that Pattern 4 and Pattern 5 above secure at the database layer

Try Storyie

If you want to see what falls out of all this on the user side, write a diary on the web at storyie.com and open it on the iOS app. Private entries stay private, public entries travel between platforms exactly as written. The access rules above are what make that boundary trustworthy.