MANDATORY when touching auth tables, tenant isolation, RLS policies, or multi-tenant database code - enforces Row Level Security best practices and catches common bypass vulnerabilities
Enforces PostgreSQL Row Level Security best practices when you modify migrations, auth, or multi-tenant code. It catches critical vulnerabilities like superuser bypass, missing FORCE policies, and USING/WITH CHECK mismatches that create false security confidence.
/plugin marketplace add troykelly/claude-skills/plugin install issue-driven-development@troykelly-skillsThis skill is limited to using the following tools:
Row Level Security (RLS) provides defense-in-depth for data isolation. When implemented correctly, it prevents data leaks even if application code misses a filter. When implemented incorrectly, it creates false security confidence while data bleeds between tenants.
Core principle: RLS is your last line of defense, not your only one. Get it wrong and you have a data breach.
Announce at start: "I'm applying postgres-rls to verify Row Level Security implementation."
This skill is MANDATORY when ANY of these patterns are touched:
| Pattern | Examples |
|---|---|
**/migrations/**/*tenant* | migrations/001_add_tenant_id.sql |
**/migrations/**/*rls* | migrations/005_enable_rls.sql |
**/migrations/**/*policy* | migrations/010_create_policies.sql |
**/*policy*.sql | db/policies.sql |
**/auth/** | src/auth/context.ts |
**/*tenant* | lib/tenant.ts, services/tenantService.ts |
**/*multi-tenant* | docs/multi-tenant-architecture.md |
Check with:
git diff --name-only HEAD~1 | grep -iE '(tenant|rls|policy|auth.*sql|multi.?tenant)'
Superusers and roles with BYPASSRLS ignore ALL policies.
-- DANGEROUS: Testing as superuser shows RLS "working" when it's bypassed
SET ROLE postgres;
SELECT * FROM orders; -- Returns ALL rows, RLS ignored
-- CORRECT: Test as application role
SET ROLE app_user;
SELECT * FROM orders; -- Returns only permitted rows
Checklist:
BYPASSRLS attributeTable owners bypass RLS unless FORCE ROW LEVEL SECURITY is set.
-- INCOMPLETE: Owners bypass this
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- COMPLETE: Everyone including owners must obey policies
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
Checklist:
Views run with creator's privileges by default. Views owned by superusers bypass RLS entirely.
-- DANGEROUS: View owned by superuser bypasses RLS
CREATE VIEW all_orders AS SELECT * FROM orders;
-- SAFE (PostgreSQL 15+): Security invoker respects caller's RLS
CREATE VIEW user_orders
WITH (security_invoker = true)
AS SELECT * FROM orders;
Checklist:
security_invoker = true (PG15+)USING filters reads; WITH CHECK validates writes. Missing WITH CHECK allows inserting data you can't see.
-- INCOMPLETE: User can INSERT rows they can't SELECT
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.tenant_id')::uuid);
-- COMPLETE: Both read and write protected
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);
Checklist:
Connection pooling can leak tenant context between requests.
-- DANGEROUS: Context persists across pooled connections
SET app.tenant_id = 'tenant-123';
-- SAFE: Use SET LOCAL inside transaction (auto-resets)
BEGIN;
SET LOCAL app.tenant_id = 'tenant-123';
-- ... queries ...
COMMIT; -- Context automatically cleared
Application pattern:
// DANGEROUS: Leaks between requests
await db.query(`SET app.tenant_id = '${tenantId}'`);
// SAFE: Transaction-scoped context
await db.transaction(async (trx) => {
await trx.raw(`SET LOCAL app.tenant_id = ?`, [tenantId]);
// ... queries ...
});
Checklist:
SET LOCAL not SETFunctions used in policies can be injection vectors.
-- DANGEROUS: If current_tenant() uses user input unsafely
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_tenant());
-- The function itself must be injection-safe:
CREATE OR REPLACE FUNCTION current_tenant()
RETURNS uuid AS $$
BEGIN
-- SAFE: Casts to UUID, not string concatenation
RETURN current_setting('app.tenant_id')::uuid;
END;
$$ LANGUAGE plpgsql STABLE;
Materialized views don't respect source table RLS. Data exports may bypass policies.
-- DANGEROUS: Contains ALL tenants' data
CREATE MATERIALIZED VIEW order_stats AS
SELECT tenant_id, count(*) FROM orders GROUP BY tenant_id;
-- Background jobs with superuser access can export all data
Checklist:
-- Without index: Sequential scan on every query
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.tenant_id')::uuid);
-- Add index for policy column
CREATE INDEX idx_orders_tenant_id ON orders(tenant_id);
Functions called per-row are expensive. Wrap in subquery for single evaluation:
-- SLOW: Function called per row
CREATE POLICY access_check ON documents
USING (user_has_access(auth.uid(), id));
-- FASTER: Evaluated once, cached
CREATE POLICY access_check ON documents
USING ((SELECT auth.uid()) = owner_id);
Avoid RLS policy chains with SECURITY DEFINER functions:
-- SLOW: RLS on permissions table also evaluated
CREATE POLICY access_check ON documents
USING (id IN (SELECT document_id FROM permissions WHERE user_id = auth.uid()));
-- FASTER: Bypass RLS chain with SECURITY DEFINER
CREATE OR REPLACE FUNCTION user_document_ids(uid uuid)
RETURNS SETOF uuid AS $$
SELECT document_id FROM permissions WHERE user_id = uid;
$$ LANGUAGE sql STABLE SECURITY DEFINER;
CREATE POLICY access_check ON documents
USING (id IN (SELECT * FROM user_document_ids(auth.uid())));
Store tenant_id on every table, even if "obvious" from joins:
-- SLOW: Must join to get tenant context
CREATE POLICY order_items_policy ON order_items
USING (order_id IN (
SELECT id FROM orders WHERE tenant_id = current_setting('app.tenant_id')::uuid
));
-- FAST: Direct column check
ALTER TABLE order_items ADD COLUMN tenant_id uuid;
CREATE POLICY order_items_policy ON order_items
USING (tenant_id = current_setting('app.tenant_id')::uuid);
-- Step 1: Add column (if needed)
ALTER TABLE orders ADD COLUMN IF NOT EXISTS tenant_id uuid;
-- Step 2: Backfill data (batched for large tables)
UPDATE orders SET tenant_id = (
SELECT tenant_id FROM customers WHERE customers.id = orders.customer_id
) WHERE tenant_id IS NULL;
-- Step 3: Add NOT NULL constraint
ALTER TABLE orders ALTER COLUMN tenant_id SET NOT NULL;
-- Step 4: Create index
CREATE INDEX CONCURRENTLY idx_orders_tenant_id ON orders(tenant_id);
-- Step 5: Enable RLS (both statements!)
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
-- Step 6: Create policies
CREATE POLICY tenant_isolation ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);
-- Step 7: Grant appropriate permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_role;
describe('RLS Policies', () => {
it('tenant A cannot see tenant B data', async () => {
// Insert as tenant A
await setTenantContext('tenant-a');
await db('orders').insert({ id: 1, tenant_id: 'tenant-a', amount: 100 });
// Switch to tenant B
await setTenantContext('tenant-b');
// Should not see tenant A's data
const orders = await db('orders').select();
expect(orders).toHaveLength(0);
});
it('cannot insert data for other tenant', async () => {
await setTenantContext('tenant-a');
await expect(
db('orders').insert({ tenant_id: 'tenant-b', amount: 100 })
).rejects.toThrow(/violates row-level security/);
});
it('superuser role is not used in application', async () => {
const result = await db.raw('SELECT current_user');
expect(result.rows[0].current_user).not.toBe('postgres');
});
});
# Create test role
CREATE ROLE test_app_user;
GRANT app_role TO test_app_user;
# Run tests as this role
psql -U test_app_user -d testdb -f tests/rls_tests.sql
When implementing RLS, post this artifact to the issue:
<!-- RLS_IMPLEMENTATION:START -->
## Row Level Security Implementation
### Tables with RLS Enabled
| Table | ENABLE | FORCE | Policies | Index |
|-------|--------|-------|----------|-------|
| orders | ✅ | ✅ | tenant_isolation | idx_orders_tenant_id |
| order_items | ✅ | ✅ | tenant_isolation | idx_order_items_tenant_id |
| customers | ✅ | ✅ | tenant_isolation | idx_customers_tenant_id |
### Policy Details
| Table | Policy | USING | WITH CHECK |
|-------|--------|-------|------------|
| orders | tenant_isolation | tenant_id = current_tenant() | tenant_id = current_tenant() |
### Security Verification
- [ ] Application connects as non-superuser role
- [ ] All RLS tables have FORCE ROW LEVEL SECURITY
- [ ] All policies have WITH CHECK clause
- [ ] Context uses SET LOCAL (transaction-scoped)
- [ ] Views use security_invoker = true
- [ ] Policy columns are indexed
- [ ] Cross-tenant tests written and passing
### Application Role
- Role name: `app_service`
- BYPASSRLS: `false`
- Superuser: `false`
**Verified At:** [timestamp]
<!-- RLS_IMPLEMENTATION:END -->
Before completing RLS implementation:
This skill is triggered by:
This skill integrates with:
security-review - RLS is part of broader security reviewdatabase-architecture - RLS decisions are architecturallocal-service-testing - Must test RLS against real PostgresThis skill should be used when the user asks to "create a slash command", "add a command", "write a custom command", "define command arguments", "use command frontmatter", "organize commands", "create command with file references", "interactive command", "use AskUserQuestion in command", or needs guidance on slash command structure, YAML frontmatter fields, dynamic arguments, bash execution in commands, user interaction patterns, or command development best practices for Claude Code.
This skill should be used when the user asks to "create an agent", "add an agent", "write a subagent", "agent frontmatter", "when to use description", "agent examples", "agent tools", "agent colors", "autonomous agent", or needs guidance on agent structure, system prompts, triggering conditions, or agent development best practices for Claude Code plugins.
This skill should be used when the user asks to "create a hook", "add a PreToolUse/PostToolUse/Stop hook", "validate tool use", "implement prompt-based hooks", "use ${CLAUDE_PLUGIN_ROOT}", "set up event-driven automation", "block dangerous commands", or mentions hook events (PreToolUse, PostToolUse, Stop, SubagentStop, SessionStart, SessionEnd, UserPromptSubmit, PreCompact, Notification). Provides comprehensive guidance for creating and implementing Claude Code plugin hooks with focus on advanced prompt-based hooks API.