Anatomy of RLS Policies

---

Policy Structure

Every RLS policy follows this structure:

CREATE POLICY "policy_name"
  ON table_name
  [AS { PERMISSIVE | RESTRICTIVE }]
  FOR { ALL | SELECT | INSERT | UPDATE | DELETE }
  TO { role_name | PUBLIC }
  [USING ( using_expression )]
  [WITH CHECK ( check_expression )];

Let's break down each component.

---

Policy Name

A descriptive name for the policy:

CREATE POLICY "Users can view own posts"
CREATE POLICY "Admins have full access"
CREATE POLICY "Public read for published content"

Best Practices:

• Use descriptive names that explain the rule
• Include who and what action
• Names appear in error messages and logs

---

ON table_name

The table this policy applies to:

CREATE POLICY "..." ON posts ...
CREATE POLICY "..." ON comments ...
CREATE POLICY "..." ON profiles ...

Each policy applies to exactly one table.

---

FOR Operation

Specifies which operations the policy covers:

FOR ALL

Applies to all operations:

CREATE POLICY "Full access to own data"
  ON posts FOR ALL
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

FOR SELECT

Only for reading data:

CREATE POLICY "Anyone can read public posts"
  ON posts FOR SELECT
  USING (published = true);

FOR INSERT

Only for creating new rows:

CREATE POLICY "Users can create posts"
  ON posts FOR INSERT
  WITH CHECK (user_id = auth.uid());

Note: INSERT only uses WITH CHECK (no existing rows to check with USING).

FOR UPDATE

Only for modifying existing rows:

CREATE POLICY "Users can update own posts"
  ON posts FOR UPDATE
  USING (user_id = auth.uid())      -- Which rows can be updated
  WITH CHECK (user_id = auth.uid()); -- What values are valid

FOR DELETE

Only for removing rows:

CREATE POLICY "Users can delete own posts"
  ON posts FOR DELETE
  USING (user_id = auth.uid());  -- Which rows can be deleted

Note: DELETE only uses USING (no new values to check).

---

TO Role

Specifies which database roles the policy applies to:

-- Apply to all roles (default)
CREATE POLICY "..." TO PUBLIC ...

-- Apply to authenticated users
CREATE POLICY "..." TO authenticated ...

-- Apply to anonymous users
CREATE POLICY "..." TO anon ...

-- Apply to specific role
CREATE POLICY "..." TO service_role ...

Supabase Default Roles

Role	When Active
anon	Using anon API key, no user session
authenticated	User is logged in
service_role	Using service_role key (bypasses RLS)

-- Only logged-in users can see
CREATE POLICY "Authenticated only"
  ON private_data FOR SELECT
  TO authenticated
  USING (true);

-- Public read access
CREATE POLICY "Public read"
  ON public_data FOR SELECT
  TO anon, authenticated  -- Both roles
  USING (true);

---

USING Expression

The USING clause is a boolean expression that filters rows:

USING (expression_that_returns_true_or_false)

Simple Ownership

-- User owns the row
USING (user_id = auth.uid())

Status-Based Access

-- Only published items
USING (status = 'published')

-- Not deleted
USING (deleted_at IS NULL)

Role-Based Access

-- User has admin role
USING (
  EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid()
    AND role = 'admin'
  )
)

Time-Based Access

-- Only recent items
USING (created_at > now() - interval '30 days')

-- During business hours
USING (
  EXTRACT(hour FROM now()) BETWEEN 9 AND 17
)

Combining Conditions

-- Multiple conditions
USING (
  user_id = auth.uid()
  AND status != 'deleted'
  AND (published = true OR created_at > now() - interval '24 hours')
)

---

WITH CHECK Expression

The WITH CHECK clause validates new or modified data:

WITH CHECK (expression_that_must_be_true)

Ensure Ownership

-- Can only create rows for yourself
WITH CHECK (user_id = auth.uid())

Data Validation

-- Status must be valid
WITH CHECK (status IN ('draft', 'published', 'archived'))

-- Price must be positive
WITH CHECK (price > 0)

Prevent Privilege Escalation

-- Can't make yourself admin
WITH CHECK (
  CASE
    WHEN role = 'admin' THEN
      EXISTS (SELECT 1 FROM admins WHERE user_id = auth.uid())
    ELSE true
  END
)

---

USING + WITH CHECK Together

For UPDATE operations, both clauses serve different purposes:

CREATE POLICY "Users can update own posts"
  ON posts FOR UPDATE
  USING (user_id = auth.uid())        -- Which rows can be selected for update
  WITH CHECK (user_id = auth.uid());  -- What the row must look like after update

Why Both Matter

-- Scenario: User tries to transfer post to another user
UPDATE posts SET user_id = 'other-user-id' WHERE id = 'my-post-id';

-- USING: Can I update this row? Yes, I own it
-- WITH CHECK: Is the new user_id valid? No, fails!

Without WITH CHECK, users could update user_id to steal posts from others or abandon their own.

---

PERMISSIVE vs RESTRICTIVE

PERMISSIVE (Default)

Multiple permissive policies combine with OR:

CREATE POLICY "Own posts" ON posts AS PERMISSIVE FOR SELECT
  USING (user_id = auth.uid());

CREATE POLICY "Public posts" ON posts AS PERMISSIVE FOR SELECT
  USING (published = true);

-- Result: See rows WHERE (user_id = auth.uid() OR published = true)

RESTRICTIVE

Restrictive policies combine with AND:

-- Must pass this check
CREATE POLICY "Not deleted" ON posts AS RESTRICTIVE FOR SELECT
  USING (deleted_at IS NULL);

-- Plus one of these
CREATE POLICY "Own posts" ON posts AS PERMISSIVE FOR SELECT
  USING (user_id = auth.uid());

CREATE POLICY "Public posts" ON posts AS PERMISSIVE FOR SELECT
  USING (published = true);

-- Result: deleted_at IS NULL AND (user_id = auth.uid() OR published = true)

Use Cases for RESTRICTIVE

• Enforce global constraints (soft delete, tenant isolation)
• Add security layers that can't be bypassed

-- Tenant isolation that ALL queries must pass
CREATE POLICY "Tenant isolation"
  ON all_tenant_tables
  AS RESTRICTIVE
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

---

Policy Evaluation Order

┌─────────────────────────────────────────────────────────┐
│                   Policy Evaluation                     │
│                                                         │
│  1. Collect all RESTRICTIVE policies                   │
│     └── Combine with AND                               │
│                                                         │
│  2. Collect all PERMISSIVE policies                    │
│     └── Combine with OR                                │
│                                                         │
│  3. Final check:                                        │
│     (all RESTRICTIVE pass) AND (any PERMISSIVE pass)  │
│                                                         │
└─────────────────────────────────────────────────────────┘

---

Common Policy Patterns

Ownership Pattern

CREATE POLICY "Ownership"
  ON resources FOR ALL
  USING (owner_id = auth.uid())
  WITH CHECK (owner_id = auth.uid());

Public Read, Owner Write

-- Anyone can read
CREATE POLICY "Public read"
  ON posts FOR SELECT
  USING (true);

-- Only owner can modify
CREATE POLICY "Owner write"
  ON posts FOR INSERT
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "Owner update"
  ON posts FOR UPDATE
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "Owner delete"
  ON posts FOR DELETE
  USING (user_id = auth.uid());

Role-Based Access

-- Helper function
CREATE FUNCTION is_admin() RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid() AND role = 'admin'
  )
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Admin policy
CREATE POLICY "Admins have full access"
  ON sensitive_data FOR ALL
  USING (is_admin())
  WITH CHECK (is_admin());

---

Debugging Policies

Check Active Policies

SELECT
  policyname,
  permissive,
  roles,
  cmd,
  qual AS using_expression,
  with_check AS with_check_expression
FROM pg_policies
WHERE tablename = 'posts';

Test as User

-- Set session to act as user
SET LOCAL request.jwt.claims = '{"sub": "user-uuid-here"}';

-- Now run queries to test
SELECT * FROM posts;

---

Key Takeaways

1. FOR specifies operations: SELECT, INSERT, UPDATE, DELETE, or ALL
2. USING filters existing rows: What can be read/modified/deleted
3. WITH CHECK validates new data: What can be inserted/updated to
4. TO targets roles: anon, authenticated, or specific roles
5. PERMISSIVE policies OR together: Any match grants access
6. RESTRICTIVE policies AND together: All must pass
7. Name policies descriptively: Helps debugging and documentation

---

Next Steps

With the policy syntax understood, we'll explore common patterns you'll use repeatedly when building Supabase applications.

---

A well-written policy reads like documentation: it clearly states who can do what. When policies are clear, your application's security is understandable and auditable.