Supabase Security Best Practices: RLS, Auth, and API Protection

-- Enable RLS on user data tables
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Verify RLS is enabled
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public';

-- Read own data
CREATE POLICY "Users read own data"
ON user_data FOR SELECT
USING (auth.uid() = user_id);

-- Insert own data (set user_id automatically)
CREATE POLICY "Users insert own data"
ON user_data FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Update own data
CREATE POLICY "Users update own data"
ON user_data FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Delete own data
CREATE POLICY "Users delete own data"
ON user_data FOR DELETE
USING (auth.uid() = user_id);

-- Anyone can read published posts
CREATE POLICY "Public read published posts"
ON posts FOR SELECT
USING (published = true);

-- Authors can read their own drafts
CREATE POLICY "Authors read own posts"
ON posts FOR SELECT
USING (auth.uid() = author_id);

-- Only authors can modify their posts
CREATE POLICY "Authors manage own posts"
ON posts FOR ALL
USING (auth.uid() = author_id);

-- Team members can read team data
CREATE POLICY "Team members read"
ON team_data FOR SELECT
USING (
  team_id IN (
    SELECT team_id FROM team_members
    WHERE user_id = auth.uid()
  )
);

-- Team admins can write
CREATE POLICY "Team admins write"
ON team_data FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM team_members
    WHERE team_id = team_data.team_id
    AND user_id = auth.uid()
    AND role = 'admin'
  )
);

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY
);

// Check session before protected operations
async function getProtectedData() {
  const { data: { session } } = await supabase.auth.getSession();

  if (!session) {
    throw new Error('Authentication required');
  }

  // RLS will use the session's JWT for access control
  const { data, error } = await supabase
    .from('protected_table')
    .select('*');

  return data;
}

// Listen for auth state changes
supabase.auth.onAuthStateChange((event, session) => {
  if (event === 'SIGNED_OUT') {
    // Clear local state, redirect to login
  }
});

import { serve } from 'https://deno.land/std/http/server.ts';
import { createClient } from 'https://esm.sh/@supabase/supabase-js';

serve(async (req) => {
  // Verify authorization header
  const authHeader = req.headers.get('Authorization');
  if (!authHeader) {
    return new Response('Unauthorized', { status: 401 });
  }

  // Create client with user's JWT
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL'),
    Deno.env.get('SUPABASE_ANON_KEY'),
    {
      global: {
        headers: { Authorization: authHeader }
      }
    }
  );

  // Verify the user
  const { data: { user }, error } = await supabase.auth.getUser();
  if (error || !user) {
    return new Response('Invalid token', { status: 401 });
  }

  // Now you can safely use the service role for admin operations
  // knowing the user is authenticated
  const adminClient = createClient(
    Deno.env.get('SUPABASE_URL'),
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')
  );

  // Perform authorized operation...
});

-- Create a function with input validation
CREATE OR REPLACE FUNCTION create_post(
  title TEXT,
  content TEXT
) RETURNS posts AS $$
DECLARE
  new_post posts;
BEGIN
  -- Validate inputs
  IF length(title) < 3 OR length(title) > 200 THEN
    RAISE EXCEPTION 'Title must be between 3 and 200 characters';
  END IF;

  IF length(content) > 50000 THEN
    RAISE EXCEPTION 'Content too long';
  END IF;

  -- Insert with auth.uid() for user_id
  INSERT INTO posts (title, content, author_id)
  VALUES (title, content, auth.uid())
  RETURNING * INTO new_post;

  RETURN new_post;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Test as a specific user
SET request.jwt.claim.sub = 'user-123-uuid';

-- Try to read data (should only see own data)
SELECT * FROM user_data;

-- Try to read another user's data (should fail or return empty)
SELECT * FROM user_data WHERE user_id = 'other-user-uuid';

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