> ## Documentation Index
> Fetch the complete documentation index at: https://docs.powabase.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# RLS Cookbook

> Five copy-paste policy patterns for common scenarios: own-rows-only, public-read with auth-write, tenant isolation, role-based access, soft-delete-aware.

This page is a recipe collection. Each pattern is a working SQL policy you can paste into the [Studio SQL editor](https://app.powabase.ai) 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](/concepts/rls-model). For testing without a frontend, see [RLS Testing](/guides/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.)

<Note>
  **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.
</Note>

## 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.

```sql theme={null}
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:

```sql theme={null}
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.

```sql theme={null}
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:

```sql theme={null}
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`:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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.

```sql theme={null}
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:

```sql theme={null}
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

<CardGroup cols={2}>
  <Card title="RLS Testing" href="/guides/rls-testing">
    Test policies in the SQL Editor or psql before deploying.
  </Card>

  <Card title="RLS Model" href="/concepts/rls-model">
    How JWTs, roles, and auth.uid() compose under the hood.
  </Card>

  <Card title="Querying the ai schema" href="/concepts/ai-schema-postgrest">
    The default RLS posture on ai.\* and when to tighten it.
  </Card>

  <Card title="ai schema recipes" href="/guides/ai-schema-recipes">
    PostgREST patterns for analytics, bulk ops, and embeds.
  </Card>
</CardGroup>