Row Level Security

Note: This is mock/placeholder content for demonstration purposes.

Row Level Security (RLS) is PostgreSQL's built-in authorization system that controls which rows users can access in database tables.

Why RLS?

RLS provides several advantages:

• Database-level security - Protection even if application code has bugs
• Automatic enforcement - No need for manual authorization checks
• Multi-tenant isolation - Ensures users only see their own data
• Performance - Optimized at the database level

Enabling RLS

All tables should have RLS enabled:

ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;

Common Policy Patterns

Personal Account Access

CREATE POLICY "Users can access their personal account data"
  ON your_table FOR ALL
  USING (account_id = auth.uid());

Team Account Access

CREATE POLICY "Users can access their team account data"
  ON your_table FOR ALL
  USING (
    account_id IN (
      SELECT account_id FROM accounts_memberships
      WHERE user_id = auth.uid()
    )
  );

Read vs Write Permissions

-- All members can read
CREATE POLICY "Team members can view data"
  ON your_table FOR SELECT
  USING (account_id IN (SELECT get_user_accounts(auth.uid())));

-- Only owners can modify
CREATE POLICY "Only owners can modify data"
  ON your_table FOR UPDATE
  USING (
    account_id IN (
      SELECT account_id FROM accounts_memberships
      WHERE user_id = auth.uid() AND role = 'owner'
    )
  );

Testing RLS Policies

Always test your RLS policies to ensure they work correctly:

-- Test as specific user
SET request.jwt.claims.sub = 'user-uuid-here';

-- Try to select data
SELECT * FROM your_table;

-- Reset
RESET request.jwt.claims.sub;

Admin Bypass

Service role keys bypass RLS. Use with extreme caution and always implement manual authorization checks when using the admin client.