gh-hopeoverture-worldbuilding-app-skills-plugins-supabase-rls-policy-generator/skills/supabase-rls-policy-generator/assets/policy-documentation-template.md

RLS Policies Documentation: [Table Name]

Generated: [Date] Database: [Database Name] Schema: public

Overview

This document describes the Row-Level Security (RLS) policies implemented for the [table_name] table.

Security Model: [Multi-tenant / Role-based / Hybrid / etc.]

Table Schema

CREATE TABLE [table_name] (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  tenant_id UUID REFERENCES tenants(id),
  [other columns...]
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Security-relevant columns:

• user_id: Owner of the record
• tenant_id: Tenant/organization identifier
• [other relevant columns...]

RLS Status

-- RLS is ENABLED on this table
ALTER TABLE [table_name] ENABLE ROW LEVEL SECURITY;

Policies

Policy 1: [Policy Name]

Operation: SELECT | INSERT | UPDATE | DELETE | ALL Policy Name: [policy_name]

Purpose: [What this policy does]

SQL:

CREATE POLICY "[policy_name]"
  ON [table_name] FOR [OPERATION]
  USING ([condition])
  WITH CHECK ([condition]);

Access Rules:

• [Who] can [do what] when [condition]
• Example: "Users can view records where they are the owner"
• Example: "Admins can view all records"

Testing:

-- Test as user [user_id]
SET request.jwt.claim.sub = '[user-uuid]';
SELECT * FROM [table_name]; -- Expected: [description]

-- Test as admin
SET request.jwt.claim.role = 'admin';
SELECT * FROM [table_name]; -- Expected: [description]

---

Policy 2: [Policy Name]

[Repeat structure for each policy...]

Access Matrix

Role / Context	SELECT	INSERT	UPDATE	DELETE
Anonymous	No	No	No	No
Authenticated User	Own records	Own records	Own records	Own records
Team Member	Team records	Team records	Team records	No
Admin	All records	All records	All records	All records

Helper Functions

Function: [function_name]

Purpose: [What this function does]

SQL:

CREATE OR REPLACE FUNCTION [function_name]([params])
RETURNS [type] AS $$
BEGIN
  [implementation]
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Usage in Policies:

USING ([function_name](param))

Indexes

The following indexes support RLS policy performance:

CREATE INDEX idx_[table]_user_id ON [table_name](user_id);
CREATE INDEX idx_[table]_tenant_id ON [table_name](tenant_id);
-- [other indexes...]

Permissions

-- Authenticated users can perform CRUD operations (filtered by RLS)
GRANT SELECT, INSERT, UPDATE, DELETE ON [table_name] TO authenticated;

-- Anonymous users can read public records (filtered by RLS)
GRANT SELECT ON [table_name] TO anon;

-- Sequence usage
GRANT USAGE, SELECT ON SEQUENCE [table_name]_id_seq TO authenticated;

Testing Scenarios

Scenario 1: User Access Own Records

Setup:

-- Create test user
INSERT INTO auth.users (id, email) VALUES
  ('user-1-uuid', 'user1@example.com');

-- Create test records
INSERT INTO [table_name] (user_id, [data])
VALUES ('user-1-uuid', 'data1');

Test:

-- Set auth context
SET request.jwt.claim.sub = 'user-1-uuid';

-- User should see own record
SELECT * FROM [table_name];
-- Expected: 1 row

-- User should not see other users' records
SELECT * FROM [table_name] WHERE user_id != 'user-1-uuid';
-- Expected: 0 rows

Scenario 2: [Other scenarios...]

[Add more test scenarios as needed]

Security Considerations

Enforced Restrictions

• Users cannot access other users' private data
• Users cannot modify tenant_id after creation
• Anonymous users have read-only access to public data
• [Other restrictions...]

Known Limitations

• [Any limitations or edge cases]
• [Performance considerations]
• [Future improvements needed]

Common Issues and Troubleshooting

Issue 1: "Permission denied for relation [table_name]"

Cause: RLS is enabled but no policies match the user's context.

Solution:

1. Verify user is authenticated: SELECT auth.uid();
2. Check user's role: SELECT auth.jwt() ->> 'role';
3. Verify policy conditions match user context

Issue 2: Policies not applying correctly

Cause: Testing as superuser, which bypasses RLS.

Solution:

-- Test as non-superuser
SET ROLE non_superuser_role;
-- Or use Supabase client with actual auth token

Issue 3: Performance degradation

Cause: Missing indexes on columns used in policies.

Solution:

-- Add indexes
CREATE INDEX idx_[table]_[column] ON [table_name]([column]);
-- Analyze table
ANALYZE [table_name];

Migration History

Initial RLS Setup

• Date: [Date]
• Migration: [timestamp]_add_rls_policies.sql
• Changes: Enabled RLS and created initial policies

[Future updates...]

Maintenance

Regular Tasks

• Review policy effectiveness quarterly
• Monitor query performance
• Audit access logs for anomalies
• Update policies when roles/permissions change

Performance Monitoring

-- Check policy execution time
EXPLAIN ANALYZE
SELECT * FROM [table_name]
WHERE [typical_condition];

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE tablename = '[table_name]';

References

• Supabase RLS Documentation: https://supabase.com/docs/guides/auth/row-level-security
• PostgreSQL RLS Policies: https://www.postgresql.org/docs/current/ddl-rowsecurity.html
• Auth Functions: https://supabase.com/docs/guides/database/postgres/row-level-security

Change Log

Date	Author	Change
[Date]	[Name]	Initial policies created
[Date]	[Name]	Added admin access policy
...	...	...