debug-rls

# debug-rls

Systematic methodology for debugging Row-Level Security issues in Butterbase. Uses role simulation (`as_role`/`as_user` parameters) to verify policy behavior without needing real user sessions.

---

## 1. Overview

Row-Level Security (RLS) in Butterbase controls which rows each database role can see or modify. When RLS is misconfigured, users may see no data, too much data, or get unexpected errors on insert. This skill walks through a repeatable four-step process to identify and fix the root cause.

Key principle: **MCP tools default to the service key (`bb_sk_...`), which bypasses all RLS**. Always use `as_role`/`as_user` to simulate the role your frontend actually uses.

---

## 2. Quick Diagnosis

Match the symptom your user reports to the most likely cause before diving into the full protocol.

| Symptom | Likely cause |
|---------|-------------|
| User sees no rows | RLS enabled but no policy for `butterbase_user` role |
| User sees ALL rows | RLS not enabled on the table, or request uses service key (`bb_sk_`) |
| Insert fails with `AUTH_RLS_POLICY_VIOLATION` | No INSERT policy, or `user_column` not auto-populated |
| User sees other users' data | Policy USING expression is wrong, or user isolation not set up |
| Anonymous user gets 403 | No policy for `butterbase_anon` role |
| Works in MCP tools but not from frontend | MCP uses service key (bypasses RLS); frontend uses end-user JWT |

---

## 3. The Three Roles

Butterbase automatically assigns a database role based on the auth header of each request. You never create these roles — they are built in.

| Auth header | Database role | Behavior |
|-------------|--------------|----------|
| None | `butterbase_anon` | Default deny. Only sees rows allowed by explicit anon policies. |
| Valid end-user JWT | `butterbase_user` | `current_user_id()` returns their UUID. Sees rows matching their policies. |
| API key (`bb_sk_...`) | `butterbase_service` | Bypasses ALL RLS. Sees everything. Used by MCP tools and admin operations. |

**Important:** When you call `select_rows` or `insert_row` without `as_role`, you are always running as `butterbase_service`. This means the result tells you nothing about what a real user would see. Use `as_role` to simulate the correct role.

---

## 4. Four-Step Debugging Protocol

Work through these steps in order. Each step narrows down the cause.

---

### Step 1: Check if RLS is enabled

Call `manage_rls` with `action: "list"` for the `app_id`:

```
manage_rls(app_id: "app_abc123", action: "list")
```

Returns `{ policies: [...], tables_with_rls: [...] }`. The `tables_with_rls` array shows which tables have RLS turned on but no policies yet (effective default deny).

- Look for the table in the response.
- If the table has **no policies**, RLS might not be enabled at all — or it was enabled but no policies were added, which causes a default deny for all non-service roles.
- If the table **does** appear, move to Step 2 to inspect what the policies actually do.

> A table with RLS enabled but zero policies is inaccessible to `butterbase_anon` and `butterbase_user`. The `butterbase_service` role is unaffected.

---

### Step 2: Inspect existing policies

Read each policy's fields carefully:

| Field | What it means |
|-------|--------------|
| `policyname` | Human-readable name for the policy |
| `cmd` | Which SQL command it applies to: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, or `ALL` |
| `qual` | The `USING` expression — filters which rows are visible or affected |
| `with_check` | The `WITH CHECK` expression — validates new/updated row data on write |
| `roles` | Which database role(s) this policy applies to |

**Common issues to look for:**

- Policy exists for `butterbase_user` but **not** `butterbase_anon` (anonymous users blocked)
- Policy exists for `SELECT` but **not** `INSERT` (reads work, writes fail)
- `USING` expression references the wrong column (e.g., `owner_id` instead of `user_id`)
- Policy covers `ALL` commands but the `WITH CHECK` expression is missing (inserts may fail silently)

---

### Step 3: Test as different roles

Use the `as_role` and `as_user` parameters on `select_rows` and `insert_row` to simulate each role. **This is the most direct way to reproduce what a real user experiences.**

```
# Test SELECT as an authenticated user
select_rows(
  app_id: "app_abc123",
  table: "posts",
  as_role: "user",
  as_user: "user-uuid-here"
)

# Test SELECT as anonymous
select_rows(
  app_id: "app_abc123",
  table: "posts",
  as_role: "anon"
)

# Test INSERT as an authenticated user
insert_row(
  app_id: "app_abc123",
  table: "posts",
  data: { title: "Hello" },
  as_role: "user",
  as_user: "user-uuid-here"
)
```

Compare results between roles:

| Scenario | Expected result |
|----------|----------------|
| No `as_role` (service) | All rows returned, inserts succeed — RLS bypassed |
| `as_role: "user"` | Only the user's own rows (if isolation policy exists) |
| `as_role: "anon"` | Only publicly readable rows (if anon policy exists), or empty |

If results differ from expectations, you have confirmed which role/command combination is misconfigured.

> Without `as_role`, MCP tools always use the service key and bypass RLS. Never use this to validate that RLS is working.

---

### Step 4: Check auto-populate trigger

This step specifically diagnoses `AUTH_RLS_POLICY_VIOLATION` on INSERT.

1. Insert a row as a user (using `as_role: "user"`):

```
insert_row(
  app_id: "app_abc123",
  table: "posts",
  data: { title: "Test post" },
  as_role: "user",
  as_user: "user-uuid-here"
)
```

2. Check if the `user_id` / `author_id` column was **auto-populated** in the returned row.

3. If the column is `NULL` or missing from the response, **the auto-populate trigger is missing**. The RLS policy requires `user_id = current_user_id()`, but the column was never filled in, so the WITH CHECK fails.

**Root cause:** `enable_rls` + `create_policy` (without `user_column`) does **not** install an au