RLS_POLICIES_ORGANIZATIONS.md

Organization RLS Policies Documentation

Overview

This document describes the Row Level Security (RLS) policies implemented for the organizations and organization_members tables in the JudgeFinder Platform database.

Migration File: /Users/tanner-osterkamp/JudgeFinderPlatform/supabase/migrations/20251026_115814_fix_organization_rls_policies.sql

Security Status

Table	RLS Enabled	Policy Count	Status
organizations	✅ Yes	5	✅ Protected
organization_members	✅ Yes	5	✅ Protected

Architecture

Authentication System

• Provider: Clerk Authentication
• User ID Type: TEXT (Clerk user IDs)
• Helper Functions:
  • public.current_user_id() - Returns current authenticated user's Clerk ID
  • public.is_admin() - Checks if user has admin privileges
  • public.is_service_role() - Checks if request is from service role

Key Design Principles

1. Least Privilege: Users only access data they need
2. Consistent Authentication: All policies use current_user_id() (not auth.uid())
3. Multi-tenant Isolation: Organizations are isolated from each other
4. Role-Based Access: Owner, Admin, Member roles with different permissions
5. Service Role Access: Backend operations have full access via service role

Organizations Table Policies

1. SELECT Policy: "Members can view their organizations"

Who can read: Organization owners, members, and system admins

-- Users can view organizations where they are:
-- 1. The owner (owner_id matches)
-- 2. A member (exists in organization_members)
-- 3. A system admin
-- Only non-deleted organizations are visible

Access Rules:

• ✅ Organization owner can view
• ✅ Organization members can view
• ✅ System admins can view
• ❌ Non-members cannot view
• ❌ Deleted organizations are hidden

2. INSERT Policy: "Authenticated users can create organizations"

Who can create: Any authenticated user

-- Users can create organizations where they set themselves as owner

Access Rules:

• ✅ Authenticated users can create
• ✅ Must set themselves as owner
• ❌ Cannot create organization for someone else
• ❌ Unauthenticated users cannot create

3. UPDATE Policy: "Owners and admins can update organizations"

Who can update: Organization owners, admins, and system admins

-- Users can update organizations where they are:
-- 1. The owner
-- 2. An admin member
-- 3. A system admin
-- Cannot update deleted organizations

Access Rules:

• ✅ Organization owner can update
• ✅ Organization admins can update
• ✅ System admins can update
• ❌ Regular members cannot update
• ❌ Non-members cannot update
• ❌ Cannot update deleted organizations

4. DELETE Policy: "Owners can delete organizations"

Who can delete: Organization owners and system admins

-- Only organization owners or system admins can delete

Access Rules:

• ✅ Organization owner can delete
• ✅ System admins can delete
• ❌ Organization admins cannot delete
• ❌ Regular members cannot delete

5. ALL Policy: "Service role has full access to organizations"

Who has full access: Service role only

-- Service role bypasses all RLS restrictions for backend operations

Access Rules:

• ✅ Service role has unrestricted access
• ✅ Used for backend operations, migrations, admin tools

Organization Members Table Policies

1. SELECT Policy: "Members can view organization members"

Who can read: Members of the same organization and system admins

-- Users can view members of organizations where they are also members

Access Rules:

• ✅ Organization members can view other members
• ✅ System admins can view
• ❌ Non-members cannot view member list
• ❌ Cannot view members of other organizations

2. INSERT Policy: "Owners and admins can add members"

Who can add members: Organization owners, admins, and system admins

-- Users can add members if:
-- 1. They are the organization owner
-- 2. They are an admin of the organization
-- 3. They are a system admin
-- 4. Organization is not at member limit

Access Rules:

• ✅ Organization owner can add members
• ✅ Organization admins can add members
• ✅ System admins can add members
• ❌ Regular members cannot add members
• ❌ Cannot add if at member limit
• ❌ Non-members cannot add members

3. UPDATE Policy: "Owners and admins can update member roles"

Who can update: Organization owners, admins, and system admins

-- Users can update member roles/permissions if:
-- 1. They are the organization owner
-- 2. They are an admin of the organization
-- 3. They are a system admin

Access Rules:

• ✅ Organization owner can update roles
• ✅ Organization admins can update roles
• ✅ System admins can update roles
• ❌ Regular members cannot update roles
• ❌ Non-members cannot update roles

4. DELETE Policy: "Owners and admins can remove members"

Who can remove members: Organization owners, admins, system admins, or self-removal

-- Users can remove members if:
-- 1. They are owner/admin removing a non-owner member
-- 2. They are removing themselves (except if they're the owner)
-- 3. They are a system admin
-- Cannot remove the organization owner

Access Rules:

• ✅ Organization owner can remove non-owner members
• ✅ Organization admins can remove non-owner members
• ✅ Members can remove themselves (if not owner)
• ✅ System admins can remove members
• ❌ Cannot remove the owner
• ❌ Regular members cannot remove others
• ❌ Owner cannot remove themselves

5. ALL Policy: "Service role has full access to organization_members"

Who has full access: Service role only

-- Service role bypasses all RLS restrictions for backend operations

Access Rules:

• ✅ Service role has unrestricted access
• ✅ Used for backend operations, migrations, admin tools

Role Hierarchy

Owner (Most Privileged)

• Full control over organization settings
• Can update organization details
• Can add/remove/update all members (except cannot remove self)
• Can delete the organization
• Cannot be removed by admins

Admin

• Can update organization settings
• Can add/remove/update members (except owner)
• Cannot delete the organization
• Can be removed by owner

Member (Default)

• Can view organization details
• Can view other members
• Can remove themselves
• Cannot modify organization or other members

Viewer

• Limited view-only access (if implemented)
• Cannot modify anything

System Admin (Super User)

• Full access to all organizations
• Can perform any operation
• Determined by is_admin() function checking app_users.is_admin

Security Features

1. Multi-tenant Isolation

• Organizations are completely isolated from each other
• Members can only see data from their organizations
• Queries automatically filtered by organization membership

2. Soft Delete Support

• Organizations with deleted_at IS NOT NULL are hidden from all queries
• Deleted organizations cannot be updated
• Supports data retention and recovery

3. Member Limit Enforcement

• Checks is_at_member_limit() function before adding members
• Prevents exceeding organization seat limits
• Based on organizations.max_members setting

4. Owner Protection

• Organization owner cannot be removed
• Owner role cannot be deleted
• Ensures every organization has at least one owner

5. Service Role Access

• Backend operations use service role for full access
• Migrations run with service role
• Admin tools use service role
• Bypasses all RLS restrictions

Testing

Verification Queries

-- Check RLS is enabled
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
AND tablename IN ('organizations', 'organization_members');

-- Count policies
SELECT tablename, COUNT(*) as policy_count
FROM pg_policies
WHERE schemaname = 'public'
AND tablename IN ('organizations', 'organization_members')
GROUP BY tablename;

-- List all policies
SELECT tablename, policyname, cmd as operation, roles
FROM pg_policies
WHERE schemaname = 'public'
AND tablename IN ('organizations', 'organization_members')
ORDER BY tablename, cmd;

Test File

Run comprehensive tests: /Users/tanner-osterkamp/JudgeFinderPlatform/supabase/migrations/TEST_20251026_115814_fix_organization_rls_policies.sql

Migration History

Previous Issues (Fixed)

1. Type Mismatch: Old policies used auth.uid() (UUID) with Clerk IDs (TEXT)
2. Inconsistent Auth: Mixed use of JWT claims and helper functions
3. Duplicate Policies: Multiple conflicting policies for same operations
4. Policy Gaps: Missing service role policies

Current Implementation (2025-10-26)

• ✅ All policies use current_user_id() helper function
• ✅ Consistent TEXT-based user IDs (Clerk)
• ✅ Service role policies added
• ✅ Duplicate policies removed
• ✅ Complete CRUD coverage
• ✅ System admin support via is_admin()

Best Practices

For Developers

1. Always use helper functions: current_user_id(), is_admin(), not direct JWT access
2. Test with real users: Verify policies work with Clerk authentication
3. Use service role for backend: Don't bypass RLS in application code
4. Check member limits: Use is_at_member_limit() before adding members
5. Respect soft deletes: Filter deleted_at IS NULL in queries

For Database Changes

1. Preserve RLS: Don't disable RLS on these tables
2. Test policies: Run test file after schema changes
3. Update policies carefully: Dropping policies creates security vulnerabilities
4. Use migrations: Never modify policies directly in production
5. Document changes: Update this file when policies change

For Application Code

1. Trust RLS: Rely on database-level security
2. Use service role sparingly: Only for admin operations
3. Handle permission errors: Expect insufficient_privilege errors
4. Verify ownership: Check user is owner/admin before sensitive operations
5. Cache wisely: Don't cache data that depends on user permissions

Troubleshooting

Common Issues

"Insufficient privilege" error

• User doesn't have permission for the operation
• Check user is member/owner/admin of organization
• Verify current_user_id() returns correct Clerk ID

Cannot view organizations

• User may not be a member
• Organization may be soft-deleted (deleted_at IS NOT NULL)
• JWT token may be invalid or expired

Cannot add members

• User may not be owner/admin
• Organization may be at member limit
• Check is_at_member_limit() returns false

Service role needed

• Some operations require service role (migrations, admin tools)
• Use service role API key for backend operations
• Don't use service role in client-side code

Debug Queries

-- Check if user is admin
SELECT public.is_admin();

-- Get current user ID
SELECT public.current_user_id();

-- Check user's organization memberships
SELECT om.*, o.name
FROM organization_members om
JOIN organizations o ON o.id = om.organization_id
WHERE om.user_id = public.current_user_id();

-- Check organization member count vs limit
SELECT
  o.id,
  o.name,
  o.max_members,
  COUNT(om.id) as current_members,
  public.is_at_member_limit(o.id) as at_limit
FROM organizations o
LEFT JOIN organization_members om ON om.organization_id = o.id
WHERE o.deleted_at IS NULL
GROUP BY o.id;

Related Documentation

• Multi-tenant Organizations Migration
• Helper Functions
• RLS Complete Coverage

Support

For issues or questions about organization RLS policies:

1. Check this documentation first
2. Run the test file to verify policy status
3. Review migration history for recent changes
4. Contact database team for assistance

---

Last Updated: 2025-10-26 Migration Version: 20251026_115814 Status: ✅ Production Ready