CodingChatGPTClaudeGemini

Supabase RLS Policy Test Suite Builder

Design row-level security policies with an access matrix, abuse cases, and repeatable SQL tests for Supabase apps.

Prompt Template

You are a senior Postgres and Supabase security engineer. Help me design and test row-level security policies before they reach production.

**App/domain:** [what the product does]
**Tables and relationships:** [tables, primary keys, foreign keys, tenant/org model]
**User roles:** [anon, authenticated user, owner, team admin, org member, service role, etc.]
**Sensitive fields:** [PII, billing data, private notes, admin-only fields]
**Required access rules:** [who can select/insert/update/delete each table]
**Existing policies or errors:** [current SQL, failing behavior, or blank if new]
**Testing setup:** [Supabase SQL editor, local Supabase, pgTAP, CI, seed data]

Please produce:
1. **Access matrix** by table, role, operation, and allowed condition.
2. **Policy SQL draft** with clear policy names and comments.
3. **Seed data plan** covering at least 3 tenants/users and edge cases.
4. **Test suite** with positive and negative cases for SELECT, INSERT, UPDATE, and DELETE.
5. **Abuse-case checklist** for cross-tenant reads, owner spoofing, soft-deleted records, public/private rows, and service-role bypass risk.
6. **Migration rollout plan** with safe order of operations, backfill checks, and rollback notes.
7. **Code review checklist** for future RLS changes.

Example Output

Supabase RLS Test Plan — Project Management App

Access Matrix

| Table | Role | SELECT | INSERT | UPDATE | DELETE |

|---|---|---|---|---|---|

| projects | org_member | Same org only | No | No | No |

| projects | org_admin | Same org only | Same org only | Same org only | No |

| tasks | assignee | Tasks assigned to user or public tasks in org | Own org only | Assigned tasks only | No |

Policy SQL Draft

- Enable RLS on projects, tasks, comments, and attachments.

- Add helper function current_org_ids() returning org memberships for auth.uid().

- Create projects_select_org_member using org_id IN current_org_ids().

Critical Negative Tests

1. User A from Org 1 cannot read Project B from Org 2.

2. User A cannot insert a task by manually setting org_id to Org 2.

3. Non-admin cannot update project.billing_status.

4. Soft-deleted comments do not appear for regular members.

Rollout Notes

Run seed-data assertions before enabling RLS in production, ship read policies first, then write policies, then lock down service-role usage in server code.

Tips for Best Results

  • 💡Give the model real table names and relationship rules; vague schemas produce unsafe policy guesses.
  • 💡Ask for negative tests, not just happy paths — RLS failures are often invisible until a cross-tenant leak happens.
  • 💡Keep service-role access out of client code and include that as an explicit review item.
  • 💡Run the generated SQL in a local Supabase project before adapting it for production migrations.

Try it with

ChatGPTClaudeGemini

Related Prompts

Coding

Code Review Assistant

Get a thorough, senior-level code review with actionable feedback on quality, security, performance, and best practices.

ChatGPTClaudeGemini
Coding

Debugging Detective

Systematically debug errors and unexpected behavior with root cause analysis and fix suggestions.

ChatGPTClaudeGemini
Coding

Code Refactoring Advisor

Transform messy, complex code into clean, maintainable, well-structured code with clear explanations.

ChatGPTClaudeGemini