Supabase Row Level Security Setup

Implement secure Row Level Security policies for [Application] using Supabase PostgreSQL.

Requirements

1. Tables to Secure

Enable RLS on:

• [Table 1] - [Data type] (e.g., user profiles)
• [Table 2] - [Data type] (e.g., posts/content)
• [Table 3] - [Data type] (e.g., comments)

2. Access Patterns

Implement policies for:

• User-owned data - Users can only access their own records
• Multi-tenant - Users access data based on organization/team membership
• Role-based - Different permissions for admin, editor, viewer roles
• Public data - Some records accessible to all users

3. CRUD Policies

For each table, create policies for:

• SELECT - Who can read data
• INSERT - Who can create new records
• UPDATE - Who can modify existing records
• DELETE - Who can remove records

4. Security Model

Define:

• User authentication via auth.uid()
• Role checks via user metadata
• Organization/tenant isolation
• Public vs private content

5. Edge Cases

Handle:

• Anonymous users
• Admin override access
• Shared resources
• Soft deletes

Example Implementation

-- Enable RLS
ALTER TABLE [table_name] ENABLE ROW LEVEL SECURITY;

-- User-owned data policy
CREATE POLICY "[Policy name]"
ON [table_name] FOR SELECT
USING (auth.uid() = user_id);

CREATE POLICY "[Policy name]"
ON [table_name] FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Multi-tenant policy
CREATE POLICY "[Policy name]"
ON [table_name] FOR SELECT
USING (
  [tenant_id] IN (
    SELECT [tenant_id]
    FROM memberships
    WHERE user_id = auth.uid()
  )
  , NULL
);

-- Role-based policy
CREATE POLICY "[Policy name]"
ON [table_name] FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.id = auth.uid()
    AND profiles.role = '[role]'
  )
  , NULL
);

-- Public access policy
CREATE POLICY "[Policy name]"
ON [table_name] FOR SELECT
USING ([public_field] = true);

Testing

Verify policies by:

• Testing as different users
• Attempting unauthorized access
• Checking admin overrides
• Validating tenant isolation

Best Practices

• Enable RLS on ALL tables
• Deny by default
• Use specific policies, not broad ones
• Test thoroughly
• Document security model