RLS Policy Writing Standards

This rule applies to files matching the patterns above.

# RLS Policy Writing Standards

## Rule
Every table MUST have RLS enabled with explicit policies for each operation. Write separate policies for SELECT, INSERT, UPDATE, and DELETE. Never combine operations.

## Format
```sql
CREATE POLICY "descriptive_policy_name"
  ON table_name
  FOR <operation>
  USING (<read_condition>)
  WITH CHECK (<write_condition>);
```

## Naming Convention
```
{action}_{subject}_{scope}
```
Examples:
- `select_posts_public` — Anyone can read published posts
- `insert_posts_authenticated` — Authenticated users can create posts
- `update_posts_owner` — Authors can update their own posts
- `delete_posts_admin` — Admins can delete any post

## Requirements

### Mandatory
1. RLS MUST be enabled on every table: `ALTER TABLE x ENABLE ROW LEVEL SECURITY;`
2. Separate policy for each operation (SELECT, INSERT, UPDATE, DELETE)
3. All columns referenced in policies MUST have indexes
4. Owner checks use `auth.uid() = user_id_column`
5. Role checks use `auth.jwt() ->> 'role'`

### Policy Patterns
```sql
-- Public read
CREATE POLICY "select_posts_public" ON posts
  FOR SELECT USING (published = true);

-- Owner read (including drafts)
CREATE POLICY "select_posts_owner" ON posts
  FOR SELECT USING (auth.uid() = author_id);

-- Authenticated insert with ownership
CREATE POLICY "insert_posts_authenticated" ON posts
  FOR INSERT WITH CHECK (
    auth.uid() = author_id
    AND title IS NOT NULL
    AND char_length(title) <= 200
  );

-- Owner update (cannot change author)
CREATE POLICY "update_posts_owner" ON posts
  FOR UPDATE
  USING (auth.uid() = author_id)
  WITH CHECK (auth.uid() = author_id);

-- Admin delete
CREATE POLICY "delete_posts_admin" ON posts
  FOR DELETE USING (auth.jwt() ->> 'role' = 'admin');
```

## Examples

### Good
- Separate policies with descriptive names
- Indexes on auth-referenced columns
- Validation in WITH CHECK clauses
- Different access levels per operation

### Bad
```sql
-- Combined operations (hard to audit)
CREATE POLICY "all_access" ON posts
  FOR ALL USING (auth.uid() = author_id);

-- Too permissive
CREATE POLICY "anyone" ON posts
  FOR ALL USING (true) WITH CHECK (true);
```

## Enforcement
Test all policies with Supabase local development.
Include policy tests in CI pipeline.
Review RLS in every migration PR.