Configure Row Level Security in Supabase so users see only their own rows

Desired Outcome

After completing this guide, the table public.profiles (or any table you choose) will have Row Level Security enabled and a policy that returns rows only when the user_id column matches the ID of the currently authenticated Supabase user. Unauthenticated requests or requests from other users will return an empty set.

Prerequisites

1. A Supabase project with a Postgres database.
2. The Supabase CLI installed and logged in (supabase login).
3. A table (e.g., public.profiles) that includes a user_id column of type uuid referencing auth.users(id).
4. Supabase JavaScript client (@supabase/supabase-js) available in your test environment.

Focused Procedure

1. Enable RLS on the table. Open the Supabase SQL editor (or create a migration file) and run:

   ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

   Where to run: Supabase Dashboard → SQL editor, or via CLI with supabase db push after adding the statement to a migration.
   Permissions: Requires database admin role (provided by the Supabase project owner).
   Risk: If no policy exists after enabling, all queries return zero rows.
2. Create a SELECT policy that limits rows to the current user. Execute:

   CREATE POLICY user_owned_select
   ON public.profiles
   FOR SELECT
   USING (auth.uid() = user_id);

   Explanation: auth.uid() extracts the user ID from the session JWT; the policy compares it to the user_id column.
3. Commit the migration (if using CLI). Save the SQL statements in a migration file under supabase/migrations/ and run:

   supabase db push

   This applies the changes to the remote database.
4. Test with an authenticated user. In a Node.js or browser environment:

   const { createClient } = require('@supabase/supabase-js');
   const supabase = createClient('https://.supabase.co', '');
   // Sign in a test user
   const { data: { session }, error } = await supabase.auth.signInWithPassword({
     email: '[contact removed]',
     password: 'secure-password',
   });
   if (error) throw error;
   // Set the client to use the user's JWT
   supabase.auth.setSession({ access_token: session.access_token, refresh_token: session.refresh_token });
   const { data: rows, error: queryError } = await supabase
     .from('profiles')
     .select('*');
   console.log(rows); // Should contain only rows where user_id = session.user.id
   

   Expected check: The returned array contains rows whose user_id matches the signed‑in user’s id from session.user.
5. Verify that unauthenticated or other‑user requests return none. Using the same client without setting a session (anon client) or with a JWT from a different user:

   const anonSupabase = createClient('https://.supabase.co', '');
   const { data: anonRows } = await anonSupabase.from('profiles').select('*');
   console.log(anonRows); // Should be []
   

   Expected check: An empty array indicates the policy is filtering correctly.

Expected Checks

• After enabling RLS, a query with no policy returns zero rows (confirm RLS is active).
• Authenticated query returns only rows owned by that user.
• Anon query or query with a different user’s token returns zero rows.

Recovery Options (Rollback)

Because enabling RLS and creating a policy change the database state, you can revert them if needed:

1. Drop the policy. Run:

   DROP POLICY user_owned_select ON public.profiles;

2. Disable RLS on the table. Run:

   ALTER TABLE public.profiles DISABLE ROW LEVEL SECURITY;

   Note: Disabling RLS removes all policy enforcement; ensure you have alternative access controls if your application relies on them.
3. Re‑apply a migration. If you manage changes via CLI, create a new migration that contains the drop/disable statements and run supabase db push.

Limitations and Practical Verification

The policy shown only covers SELECT. To protect INSERT, UPDATE, and DELETE you must create additional policies using the same auth.uid() = user_id condition (or with a WITH CHECK clause for inserts/updates). Always test each operation with both authenticated and anon clients to confirm the expected behavior.

Practical verification: after each step, run the corresponding SQL or client query and inspect the output. If you see unexpected rows, double‑check that the user_id column is correctly populated and that the JWT’s sub matches the auth.users(id) value.