Supabase Row Level Security

# Supabase Row Level Security for Google Antigravity

Implement secure data access with Supabase RLS using Google Antigravity's Gemini 3 engine. This guide covers policy design, multi-tenancy, role-based access, and security patterns.

## Basic RLS Setup

```sql
-- Enable RLS on tables
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;

-- Helper function to get current user ID
CREATE OR REPLACE FUNCTION auth.user_id() 
RETURNS uuid 
LANGUAGE sql 
STABLE
AS $$
  SELECT auth.uid()
$$;

-- Helper function to get user role
CREATE OR REPLACE FUNCTION auth.user_role() 
RETURNS text 
LANGUAGE sql 
STABLE
AS $$
  SELECT role FROM profiles WHERE id = auth.uid()
$$;
```

## Profile Policies

```sql
-- Profiles: Users can view any profile but only edit their own
CREATE POLICY "Profiles are viewable by everyone"
ON profiles FOR SELECT
USING (true);

CREATE POLICY "Users can insert their own profile"
ON profiles FOR INSERT
WITH CHECK (auth.uid() = id);

CREATE POLICY "Users can update own profile"
ON profiles FOR UPDATE
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);

-- Admins can update any profile
CREATE POLICY "Admins can update any profile"
ON profiles FOR UPDATE
USING (auth.user_role() = 'admin')
WITH CHECK (auth.user_role() = 'admin');
```

## Multi-Tenant Policies

```sql
-- Organizations table
CREATE TABLE organizations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  slug text UNIQUE NOT NULL,
  created_at timestamptz DEFAULT now()
);

-- Organization members
CREATE TABLE organization_members (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  role text NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
  created_at timestamptz DEFAULT now(),
  UNIQUE (organization_id, user_id)
);

-- Projects belong to organizations
CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
  name text NOT NULL,
  description text,
  created_by uuid REFERENCES auth.users(id),
  created_at timestamptz DEFAULT now()
);

-- Enable RLS
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Helper function to check org membership
CREATE OR REPLACE FUNCTION is_org_member(org_id uuid)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM organization_members
    WHERE organization_id = org_id
    AND user_id = auth.uid()
  )
$$;

-- Helper function to check org role
CREATE OR REPLACE FUNCTION get_org_role(org_id uuid)
RETURNS text
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT role FROM organization_members
  WHERE organization_id = org_id
  AND user_id = auth.uid()
$$;

-- Organization policies
CREATE POLICY "Users can view orgs they belong to"
ON organizations FOR SELECT
USING (is_org_member(id));

CREATE POLICY "Owners can update org"
ON organizations FOR UPDATE
USING (get_org_role(id) = 'owner')
WITH CHECK (get_org_role(id) = 'owner');

-- Project policies
CREATE POLICY "Members can view org projects"
ON projects FOR SELECT
USING (is_org_member(organization_id));

CREATE POLICY "Admins and owners can create projects"
ON projects FOR INSERT
WITH CHECK (
  get_org_role(organization_id) IN ('owner', 'admin')
);

CREATE POLICY "Admins and owners can update projects"
ON projects FOR UPDATE
USING (get_org_role(organization_id) IN ('owner', 'admin'))
WITH CHECK (get_org_role(organization_id) IN ('owner', 'admin'));

CREATE POLICY "Only owners can delete projects"
ON projects FOR DELETE
USING (get_org_role(organization_id) = 'owner');
```

## Post and Comment Policies

```sql
-- Posts can be public or private
CREATE TABLE posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
  title text NOT NULL,
  content text,
  is_published boolean DEFAULT false,
  published_at timestamptz,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Comments on posts
CREATE TABLE comments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id uuid REFERENCES posts(id) ON DELETE CASCADE,
  author_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  content text NOT NULL,
  created_at timestamptz DEFAULT now()
);

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;

-- Post policies
CREATE POLICY "Published posts are viewable by org members"
ON posts FOR SELECT
USING (
  (is_published = true AND is_org_member(organization_id))
  OR author_id = auth.uid()
);

CREATE POLICY "Members can create posts in their org"
ON posts FOR INSERT
WITH CHECK (
  is_org_member(organization_id)
  AND author_id = auth.uid()
);

CREATE POLICY "Authors can update their own posts"
ON posts FOR UPDATE
USING (author_id = auth.uid())
WITH CHECK (author_id = auth.uid());

CREATE POLICY "Authors and admins can delete posts"
ON posts FOR DELETE
USING (
  author_id = auth.uid()
  OR get_org_role(organization_id) IN ('owner', 'admin')
);

-- Comment policies
CREATE POLICY "Comments visible on accessible posts"
ON comments FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM posts
    WHERE posts.id = comments.post_id
    AND (
      (posts.is_published = true AND is_org_member(posts.organization_id))
      OR posts.author_id = auth.uid()
    )
  )
);

CREATE POLICY "Members can comment on accessible posts"
ON comments FOR INSERT
WITH CHECK (
  author_id = auth.uid()
  AND EXISTS (
    SELECT 1 FROM posts
    WHERE posts.id = post_id
    AND is_org_member(posts.organization_id)
  )
);

CREATE POLICY "Authors can delete their comments"
ON comments FOR DELETE
USING (author_id = auth.uid());
```

## Service Role Bypass

```typescript
// lib/supabase/admin.ts
import { createClient } from '@supabase/supabase-js';
import type { Database } from '@/types/database';

// Service role client bypasses RLS - use only server-side
export const supabaseAdmin = createClient<Database>(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  {
    auth: {
      autoRefreshToken: false,
      persistSession: false,
    },
  }
);

// Example: Admin action that bypasses RLS
export async function adminDeleteUser(userId: string) {
  // This bypasses RLS policies
  await supabaseAdmin.from('profiles').delete().eq('id', userId);
  await supabaseAdmin.auth.admin.deleteUser(userId);
}
```

## Best Practices

Google Antigravity's Gemini 3 engine recommends these RLS patterns: Enable RLS on all tables containing user data. Use SECURITY DEFINER functions for complex permission checks. Test policies thoroughly with different user roles. Keep policies simple and compose with helper functions. Use service role only for admin operations server-side.