RLS Cookbook - Powabase

This page is a recipe collection. Each pattern is a working SQL policy you can paste into the Studio SQL editor or your migrations, with notes on when to use it, common variants, and the gotchas that bite people. For the conceptual underpinning, see Row Level Security. For testing without a frontend, see RLS Testing. Every example here assumes:

The table is in public, RLS is enabled (ALTER TABLE ... ENABLE ROW LEVEL SECURITY), and you want to add policies on top.
You’re signing in users through GoTrue, so auth.uid() returns the user’s id.
You want to expose the table directly to clients via PostgREST. (If you’re only ever hitting it from a backend with the Service Role key, you don’t need any of this; service_role bypasses RLS.)

Step zero: enable RLS, then add policies. ALTER TABLE my_table ENABLE ROW LEVEL SECURITY; flips the default to “deny all.” If you then add no policies, the table is unreadable by anyone except service_role. Always pair the ENABLE with the policies in a single migration.

Pattern 1: Each user sees and edits only their own rows

The most common pattern. A todos table where every row has an owner_id column, and users can only touch their own rows.

CREATE TABLE public.todos (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id    uuid NOT NULL REFERENCES auth.users(id),
  title       text NOT NULL,
  done        boolean NOT NULL DEFAULT false,
  created_at  timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.todos ENABLE ROW LEVEL SECURITY;

-- Read your own todos
CREATE POLICY own_todos_read ON public.todos
  FOR SELECT TO authenticated
  USING (owner_id = auth.uid());

-- Insert todos that you own
CREATE POLICY own_todos_insert ON public.todos
  FOR INSERT TO authenticated
  WITH CHECK (owner_id = auth.uid());

-- Update your own todos (and can't change owner_id to someone else)
CREATE POLICY own_todos_update ON public.todos
  FOR UPDATE TO authenticated
  USING (owner_id = auth.uid())
  WITH CHECK (owner_id = auth.uid());

-- Delete your own todos
CREATE POLICY own_todos_delete ON public.todos
  FOR DELETE TO authenticated
  USING (owner_id = auth.uid());

Why the WITH CHECK on UPDATE. USING decides which rows the policy applies to before the update; WITH CHECK validates the result. Without WITH CHECK, a user could change owner_id from their own id to anyone else’s mid-update and the policy would still pass. Always pair USING + WITH CHECK on UPDATE policies. Variant: let the server set owner_id. Instead of trusting the client to send owner_id, default it from the session:

ALTER TABLE public.todos
  ALTER COLUMN owner_id SET DEFAULT auth.uid();

Now INSERT requests that omit owner_id get the caller’s id automatically, and your WITH CHECK (owner_id = auth.uid()) ensures they can’t override it.

Pattern 2: Public read, auth-only write

A blog. Anyone, including unauthenticated visitors, can read posts. Only the author can create, edit, or delete their own posts.

CREATE TABLE public.posts (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id   uuid NOT NULL REFERENCES auth.users(id) DEFAULT auth.uid(),
  slug        text UNIQUE NOT NULL,
  title       text NOT NULL,
  body        text NOT NULL,
  published   boolean NOT NULL DEFAULT false,
  created_at  timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;

-- Everyone can read published posts
CREATE POLICY posts_public_read ON public.posts
  FOR SELECT TO anon, authenticated
  USING (published = true);

-- Authors can read their own posts even if unpublished (drafts)
CREATE POLICY posts_author_read_drafts ON public.posts
  FOR SELECT TO authenticated
  USING (author_id = auth.uid());

-- Authors can create, update, delete their own posts
CREATE POLICY posts_author_write ON public.posts
  FOR INSERT TO authenticated
  WITH CHECK (author_id = auth.uid());

CREATE POLICY posts_author_update ON public.posts
  FOR UPDATE TO authenticated
  USING (author_id = auth.uid())
  WITH CHECK (author_id = auth.uid());

CREATE POLICY posts_author_delete ON public.posts
  FOR DELETE TO authenticated
  USING (author_id = auth.uid());

Important: RLS policies are additive (OR-combined) within the same role. Both the posts_public_read and posts_author_read_drafts policies apply when a signed-in user reads, so they see all published posts AND their own drafts. That’s the desired behavior here.

Pattern 3: Tenant isolation (multi-org SaaS)

You’re building a SaaS where each user belongs to one or more organizations and rows are scoped per organization. A documents table where users only see documents in orgs they’re a member of. You need a members table that says who belongs to which org:

CREATE TABLE public.members (
  user_id  uuid NOT NULL REFERENCES auth.users(id),
  org_id   uuid NOT NULL,
  role     text NOT NULL DEFAULT 'member',  -- e.g. 'admin' | 'member'
  PRIMARY KEY (user_id, org_id)
);

ALTER TABLE public.members ENABLE ROW LEVEL SECURITY;

CREATE POLICY members_read_own_memberships ON public.members
  FOR SELECT TO authenticated
  USING (user_id = auth.uid());

-- (Membership management is server-side; no INSERT/UPDATE/DELETE for users.)

Then the documents table policy uses a subquery against members:

CREATE TABLE public.documents (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id      uuid NOT NULL,
  title       text NOT NULL,
  body        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;

-- Read documents in any org you belong to
CREATE POLICY docs_member_read ON public.documents
  FOR SELECT TO authenticated
  USING (
    org_id IN (
      SELECT org_id FROM public.members WHERE user_id = auth.uid()
    )
  );

-- Write requires being in the org
CREATE POLICY docs_member_write ON public.documents
  FOR INSERT TO authenticated
  WITH CHECK (
    org_id IN (
      SELECT org_id FROM public.members WHERE user_id = auth.uid()
    )
  );

CREATE POLICY docs_member_update ON public.documents
  FOR UPDATE TO authenticated
  USING (
    org_id IN (
      SELECT org_id FROM public.members WHERE user_id = auth.uid()
    )
  )
  WITH CHECK (
    org_id IN (
      SELECT org_id FROM public.members WHERE user_id = auth.uid()
    )
  );

Performance gotcha. That IN (SELECT ...) runs once per row scanned at worst. Add an index on members(user_id, org_id) and Postgres will turn it into a hash semi-join. If documents gets large, also index documents(org_id). For very high cardinality, denormalize: stuff the user’s allowed org_ids into the JWT (via a GoTrue hook) and read them from auth.jwt() -> 'org_ids' directly, which avoids the join entirely.

Pattern 4: Role-based access (admin / member)

Extending the tenant pattern: only org admins can delete documents, members can read and create. Two reasonable approaches. Option A: encode role in the policy expression. Re-use the members.role column:

CREATE POLICY docs_admin_delete ON public.documents
  FOR DELETE TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.members
      WHERE user_id = auth.uid()
        AND org_id = documents.org_id
        AND role = 'admin'
    )
  );

Option B: encode role in the JWT. If GoTrue is minting tokens with custom claims (e.g., via a Postgres function hook on sign-in), you can stash a {"role": "admin"} claim and check it directly:

CREATE POLICY docs_admin_delete ON public.documents
  FOR DELETE TO authenticated
  USING (auth.jwt() ->> 'app_role' = 'admin');

Option A is the right default: roles are dynamic, change without re-signing the user in, and there’s a single source of truth. Option B is faster (no subquery) but requires re-issuing tokens when roles change. Use it for things that genuinely won’t change mid-session, like whether the user is verified at all.

Pattern 5: Soft delete

Instead of physically deleting rows, you mark them with deleted_at. Active queries should hide them; admins should still see them. The trick is to filter deleted_at IS NULL in the policy itself, so callers never have to add it.

ALTER TABLE public.posts ADD COLUMN deleted_at timestamptz;
CREATE INDEX posts_active_idx ON public.posts (deleted_at) WHERE deleted_at IS NULL;

-- Replace the read policies with deletion-aware versions
DROP POLICY posts_public_read ON public.posts;
DROP POLICY posts_author_read_drafts ON public.posts;

CREATE POLICY posts_active_public_read ON public.posts
  FOR SELECT TO anon, authenticated
  USING (deleted_at IS NULL AND published = true);

CREATE POLICY posts_active_author_read ON public.posts
  FOR SELECT TO authenticated
  USING (deleted_at IS NULL AND author_id = auth.uid());

-- "Delete" is now an UPDATE that sets deleted_at
DROP POLICY posts_author_delete ON public.posts;

CREATE POLICY posts_author_soft_delete ON public.posts
  FOR UPDATE TO authenticated
  USING (author_id = auth.uid())
  WITH CHECK (author_id = auth.uid());

Callers now issue PATCH /rest/v1/posts?id=eq.{id} with {"deleted_at": "<now>"} instead of DELETE. The policies hide the row from subsequent reads. Variant: let admins see deleted rows. Add a separate policy for the admin role:

CREATE POLICY posts_admin_read_all ON public.posts
  FOR SELECT TO authenticated
  USING (auth.jwt() ->> 'app_role' = 'admin');

Because policies OR-combine, admins see both active rows (via the regular policy) AND soft-deleted ones (via this one). Regular users still only see active rows.

Patterns worth knowing about

A few that come up but don’t need full recipes:

Force RLS for the table owner. By default the table owner (service_role and the project Postgres user) bypasses RLS. To make RLS apply even to the owner (useful for safety in shared environments), use ALTER TABLE ... FORCE ROW LEVEL SECURITY. Don’t use this on the ai.* tables; the platform’s backend assumes service-role bypass.
Permissive vs restrictive policies. All policies are PERMISSIVE by default, so they OR together. RESTRICTIVE policies AND together with the result. Useful when you want to layer a “no row may be deleted on Sundays” check on top of existing permissive policies without rewriting them.
Functions in policy expressions. Postgres caches policy expression results per row per query. A SELECT 1 FROM members WHERE ... subquery is fine; an HTTP call from inside a policy (via pg_net) is not, and it’ll run thousands of times. Keep policy expressions cheap and deterministic.

Next steps

RLS Testing

Test policies in the SQL Editor or psql before deploying.

RLS Model

How JWTs, roles, and auth.uid() compose under the hood.

Querying the ai schema

The default RLS posture on ai.* and when to tighten it.

ai schema recipes

PostgREST patterns for analytics, bulk ops, and embeds.

​Pattern 1: Each user sees and edits only their own rows

​Pattern 2: Public read, auth-only write

​Pattern 3: Tenant isolation (multi-org SaaS)

​Pattern 4: Role-based access (admin / member)

​Pattern 5: Soft delete

​Patterns worth knowing about

​Next steps

RLS Testing

RLS Model

Querying the ai schema

ai schema recipes

Pattern 1: Each user sees and edits only their own rows

Pattern 2: Public read, auth-only write

Pattern 3: Tenant isolation (multi-org SaaS)

Pattern 4: Role-based access (admin / member)

Pattern 5: Soft delete

Patterns worth knowing about

Next steps