From memstack
Enforces Row Level Security policies on Supabase and PostgreSQL tables during CREATE TABLE, ALTER TABLE, migrations, or RLS discussions. Provides 4 policy patterns, templates, checks, and mistake avoidance.
npx claudepluginhub cwinvestments/memstack --plugin memstackThis skill uses the workspace's default tool permissions.
*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.*
Audits Supabase Row Level Security policies across all tables by scanning migrations, database types, client code, and storage buckets. Flags missing RLS and intentional skips.
Provides expertise in securing Supabase applications: RLS patterns, auth token validation, storage security, multi-tenant isolation. Grounds responses in patterns.md, sharp_edges.md, validations.md for creation, diagnosis, review.
Applies Supabase security best practices: anon/service_role key separation, RLS enforcement, JWT verification, API hardening. Use for securing projects, auditing keys, or production checklists.
Share bugs, ideas, or general feedback.
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.
When this skill activates, output:
RLS Guardian — Enforcing Row Level Security...
Then execute the protocol below.
| 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 |
| 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. |
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)
Every table migration MUST include 3 parts in this order:
CREATE TABLE — the schemaALTER TABLE ENABLE ROW LEVEL SECURITY — the lockCREATE 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 account_id FROM account_members
WHERE user_id = auth.uid()
)
);
Pattern C: Public-read table
-- 1. Create table
CREATE TABLE blog_posts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
author_id UUID NOT NULL REFERENCES auth.users(id),
title TEXT NOT NULL,
content TEXT,
published BOOLEAN DEFAULT false,
created_at TIMESTAMPTZ DEFAULT now()
);
-- 2. Enable RLS
ALTER TABLE blog_posts ENABLE ROW LEVEL SECURITY;
-- 3. Public read, author-only write
CREATE POLICY "Anyone can view published posts"
ON blog_posts FOR SELECT
USING (published = true);
CREATE POLICY "Authors can insert own posts"
ON blog_posts FOR INSERT
WITH CHECK (auth.uid() = author_id);
CREATE POLICY "Authors can update own posts"
ON blog_posts FOR UPDATE
USING (auth.uid() = author_id)
WITH CHECK (auth.uid() = author_id);
CREATE POLICY "Authors can delete own posts"
ON blog_posts FOR DELETE
USING (auth.uid() = author_id);
Pattern D: Junction table
-- 1. Create table
CREATE TABLE team_members (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
role TEXT DEFAULT 'member',
joined_at TIMESTAMPTZ DEFAULT now(),
UNIQUE(team_id, user_id)
);
-- 2. Enable RLS
ALTER TABLE team_members ENABLE ROW LEVEL SECURITY;
-- 3. Members can see co-members, only admins can manage
CREATE POLICY "Members can view team roster"
ON team_members FOR SELECT
USING (
team_id IN (
SELECT team_id FROM team_members
WHERE user_id = auth.uid()
)
);
CREATE POLICY "Admins can add team members"
ON team_members FOR INSERT
WITH CHECK (
team_id IN (
SELECT team_id FROM team_members
WHERE user_id = auth.uid() AND role = 'admin'
)
);
CREATE POLICY "Admins can update team members"
ON team_members FOR UPDATE
USING (
team_id IN (
SELECT team_id FROM team_members
WHERE user_id = auth.uid() AND role = 'admin'
)
)
WITH CHECK (
team_id IN (
SELECT team_id FROM team_members
WHERE user_id = auth.uid() AND role = 'admin'
)
);
CREATE POLICY "Admins can remove team members"
ON team_members FOR DELETE
USING (
team_id IN (
SELECT team_id FROM team_members
WHERE user_id = auth.uid() AND role = 'admin'
)
);
Before committing any migration that creates or alters a table, verify:
## RLS Guardian Checklist
- [ ] `ALTER TABLE [name] ENABLE ROW LEVEL SECURITY` present in migration
- [ ] SELECT policy defined
- [ ] INSERT policy defined (with WITH CHECK)
- [ ] UPDATE policy defined (with both USING and WITH CHECK)
- [ ] DELETE policy defined
- [ ] Policy expressions use `auth.uid()` (not `current_setting()` or hardcoded UUIDs)
- [ ] Org-scoped tables use membership subquery (not direct account_id match)
- [ ] No `USING (true)` on non-public tables
- [ ] Policy names are descriptive (not "policy_1", "policy_2")
- [ ] RLS + policies are in the SAME migration file as CREATE TABLE
Critical rule: If any checkbox fails, do NOT commit. Fix first. A table without RLS in production is a data breach waiting to happen.
Run this query to find unprotected tables:
-- Find all public tables without RLS enabled
SELECT
schemaname,
tablename,
rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
AND rowsecurity = false
ORDER BY tablename;
Find tables with RLS enabled but missing policies:
-- Tables with RLS but no policies (locked out — no access at all)
SELECT t.tablename
FROM pg_tables t
LEFT JOIN pg_policies p ON t.tablename = p.tablename
WHERE t.schemaname = 'public'
AND t.rowsecurity = true
AND p.policyname IS NULL
ORDER BY t.tablename;
Find tables with incomplete policy coverage:
-- Tables missing one or more CRUD policies
SELECT
t.tablename,
COUNT(CASE WHEN p.cmd = 'r' THEN 1 END) AS select_policies,
COUNT(CASE WHEN p.cmd = 'a' THEN 1 END) AS insert_policies,
COUNT(CASE WHEN p.cmd = 'w' THEN 1 END) AS update_policies,
COUNT(CASE WHEN p.cmd = 'd' THEN 1 END) AS delete_policies
FROM pg_tables t
LEFT JOIN pg_policies p ON t.tablename = p.tablename
WHERE t.schemaname = 'public'
AND t.rowsecurity = true
GROUP BY t.tablename
HAVING
COUNT(CASE WHEN p.cmd = 'r' THEN 1 END) = 0
OR COUNT(CASE WHEN p.cmd = 'a' THEN 1 END) = 0
OR COUNT(CASE WHEN p.cmd = 'w' THEN 1 END) = 0
OR COUNT(CASE WHEN p.cmd = 'd' THEN 1 END) = 0
ORDER BY t.tablename;
System tables (no user access needed):
-- Mark as system table with comment, enable RLS with no policies
-- This blocks ALL access except service role (which bypasses RLS)
ALTER TABLE cron_jobs ENABLE ROW LEVEL SECURITY;
COMMENT ON TABLE cron_jobs IS 'rls:system — service role only, no user access';
Tables with soft-delete:
-- Include soft-delete filter in SELECT policy
CREATE POLICY "Users see own non-deleted records"
ON documents FOR SELECT
USING (auth.uid() = user_id AND deleted_at IS NULL);
Tables with row-level roles:
-- Different access levels within the same table
CREATE POLICY "Owners have full access"
ON documents FOR ALL
USING (auth.uid() = owner_id);
CREATE POLICY "Viewers can only read"
ON documents FOR SELECT
USING (
id IN (
SELECT document_id FROM document_shares
WHERE user_id = auth.uid() AND permission = 'view'
)
);
ALTER TABLE — adding columns:
-- When adding a sensitive column to an existing table,
-- verify existing policies still cover the new data appropriately.
-- No new policies needed unless the column changes access logic.
ALTER TABLE profiles ADD COLUMN phone TEXT;
-- Existing RLS policies automatically apply to the new column.
-- But if phone should be more restricted, add a column-level grant:
REVOKE ALL ON profiles FROM anon, authenticated;
GRANT SELECT (id, name, avatar_url) ON profiles TO authenticated;
-- Then RLS + column grants together control both row AND column access.
When generating a migration that includes a table, output:
## Migration: [description]
### Table Classification
| Table | Pattern | Key Column |
|-------|---------|-----------|
| [name] | [user/org/public/junction/system] | [user_id/account_id/etc.] |
### SQL
[Complete migration SQL with CREATE TABLE + ENABLE RLS + all 4 policies]
### RLS Guardian Checklist
- [x] RLS enabled in same migration
- [x] SELECT policy
- [x] INSERT policy
- [x] UPDATE policy
- [x] DELETE policy
- [x] Uses auth.uid()
- [x] Descriptive policy names
RLS Guardian — Enforced!
Table: [name]
Pattern: [user-scoped / org-scoped / public-read / junction / system]
Policies: [count] (SELECT, INSERT, UPDATE, DELETE)
RLS enabled: Yes (same migration)
Checklist: All items passed