/rls-guardian

name: memstack-security-rls-guardian
description: "Use this skill when creating or altering database tables in Supabase or PostgreSQL projects. Triggers include: CREATE TABLE, ALTER TABLE, migration files, 'RLS', 'row level security', 'new table', 'database schema'. Enforces Row Level Security policies on every table to prevent unauthorized data access. Do NOT use for general SQL queries or non-schema database tasks."
version: 1.0.0
license: "Proprietary, MemStack™ Pro by CW Affiliate Investments LLC. See LICENSE.txt"

RLS Guardian: Enforcing Row Level Security...

*Enforces Row Level Security on every Supabase table creation, ensuring no table goes live without proper access policies. Provides 4 policy patterns, migration templates, pre-commit checks, and audit queries to find unprotected tables.*

Activation

When this skill activates, output:

`RLS Guardian: Enforcing Row Level Security...`

Then execute the protocol below.

Context Guard

| Context | Status | |---------|--------| | User writes CREATE TABLE or ALTER TABLE SQL | ACTIVE | | User creates or edits a migration file | ACTIVE | | User says "new table", "database schema", "add table" | ACTIVE | | User says "RLS", "row level security", "table policies" | ACTIVE | | User asks for a general RLS audit of existing tables | DORMANT: use RLS Checker | | User asks a general SQL query unrelated to schema | DORMANT: do not activate |

Common Mistakes

| Mistake | Why It's Wrong | |---------|---------------| | "Create table now, add RLS later" | Every minute without RLS is a window where all rows are accessible to any authenticated user. RLS must be in the same migration. | | "Enable RLS but no policies" | `ALTER TABLE ENABLE ROW LEVEL SECURITY` with no policies blocks ALL access including the service role via API. Users get empty results, devs panic. | | "Only add a SELECT policy" | SELECT-only leaves INSERT, UPDATE, DELETE wide open. All 4 operations need explicit policies. | | "Use `true` as the policy expression" | `USING (true)` makes the table world-readable. Only valid for genuinely public data (e.g. blog posts). Never for user data. | | "Forget the service role" | Supabase service role bypasses RLS by design. But if you switch to `anon` key on the frontend, every table without policies is exposed. |

Protocol

Step 1: Classify the Table

Before writing any policy, classify the table's access pattern:

| Pattern | Description | Example Tables | |---------|------------|---------------| | **User-scoped** | Each row belongs to one user via `user_id` | profiles, settings, api_keys, notifications | | **Org-scoped** | Rows belong to an organization; users access via membership | projects, invoices, team_settings | | **Public-read** | Anyone can read, only owners can write | blog_posts, public_profiles, product_listings | | **Junction** | Many-to-many relationship linking two entities | team_members, project_collaborators | | **System** | Internal tables not accessed by end users | migrations, cron_jobs, audit_logs |

**Decision tree:**

Does the table have a user_id column?
  YES → Does it also have an org/account_id?
    YES → Org-scoped (access via membership check)
    NO  → User-scoped (direct user_id match)
  NO  → Is the data meant to be publicly readable?
    YES → Public-read (open SELECT, restricted writes)
    NO  → Is it a many-to-many join table?
      YES → Junction (inherit access from parent tables)
      NO  → System table (service role only, no RLS policies needed)

Step 2: Write the Migration

Every table migration MUST include 3 parts in this order: 1. `CREATE TABLE`: the schema 2. `ALTER TABLE ENABLE ROW LEVEL SECURITY`: the lock 3. `CREATE POLICY`: the keys (all 4 operations)

**Pattern A: User-scoped table**

-- 1. Create table
CREATE TABLE user_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  theme TEXT DEFAULT 'dark',
  notifications_enabled BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- 2. Enable RLS (MUST be in same migration)
ALTER TABLE user_settings ENABLE ROW LEVEL SECURITY;

-- 3. Policies for all 4 operations
CREATE POLICY "Users can view own settings"
  ON user_settings FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own settings"
  ON user_settings FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own settings"
  ON user_settings FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can delete own settings"
  ON user_settings FOR DELETE
  USING (auth.uid() = user_id);

**Pattern B: Org-scoped table**

-- 1. Create table
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  status TEXT DEFAULT 'active',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- 2. Enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- 3. Policies with membership check
CREATE POLICY "Members can view org projects"
  ON projects FOR SELECT
  USING (
    account_id IN (
      SELECT account_id FROM account_members
      WHERE user_id = auth.uid()
    )
  );

CREATE POLICY "Members can insert org projects"
  ON projects FOR INSERT
  WITH CHECK (
    account_id IN (
      SELECT account_id FROM account_members
      WHERE user_id = auth.uid()
    )
  );

CREATE POLICY "Members can update org projects"
  ON projects FOR UPDATE
  USING (
    account_id IN (
      SELECT account_id FROM account_members
      WHERE user_id = auth.uid()
    )
  )
  WITH CHECK (
    account_id IN (
      SELECT account_id FROM account_members
      WHERE user_id = auth.uid()
    )
  );

CREATE POLICY "Members can delete org projects"
  ON projects FOR DELETE
  USING (
    account_id IN (
      SELECT acc