Implementing Row‑Level Security in Supabase: A Practical Guide

Desired Outcome

Secure a Supabase table so that each authenticated user can only see, insert, or update rows that belong to them, while still allowing administrative roles to bypass the restriction when necessary.

Prerequisites

• Supabase project with a PostgreSQL database.
• Supabase CLI or access to the SQL editor in the Supabase web console.
• Supabase JS client installed in your project (npm i @supabase/supabase-js).
• At least one authenticated user created via Supabase Auth.
• Database user with sufficient privileges to alter tables and create policies (usually the default project user).
• Knowledge of PostgreSQL functions auth.uid() and built‑in roles such as authenticated, service_role, supabase_auth_admin.

Focused Procedure

1. Define the Data Model

   Assume a table messages that stores user‑specific messages.

   CREATE TABLE messages (
     id          BIGSERIAL PRIMARY KEY,
     user_id     UUID NOT NULL,
     content     TEXT NOT NULL,
     created_at  TIMESTAMP WITH TIME ZONE DEFAULT now()
   );
   

2. Enable Row‑Level Security

   RLS is off by default. Run:

   ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
   

   When RLS is enabled, any query must satisfy at least one policy.

3. Create Policies for Authenticated Users

   Define separate policies for each operation.

   -- SELECT – only owner can read
   CREATE POLICY messages_select ON messages
     FOR SELECT
     USING (user_id = auth.uid());
   
   -- INSERT – only owner can insert
   CREATE POLICY messages_insert ON messages
     FOR INSERT
     WITH CHECK (user_id = auth.uid());
   
   -- UPDATE – only owner can modify
   CREATE POLICY messages_update ON messages
     FOR UPDATE
     USING (user_id = auth.uid());
   
   -- DELETE – only owner can delete
   CREATE POLICY messages_delete ON messages
     FOR DELETE
     USING (user_id = auth.uid());
   

   These policies reference auth.uid(), which returns the UUID of the authenticated Supabase user making the request.

4. Test from the Supabase JS Client

   Initialize the client with the service_role key only for administrative tasks. For client‑side code, use the public key.

   import { createClient } from '@supabase/supabase-js';
   
   const supabaseUrl = 'https://.supabase.co';
   const supabaseKey = 'public-anon-key'; // Use the public key for client apps
   const supabase = createClient(supabaseUrl, supabaseKey);
   
   // Attempt to fetch messages without signing in
   const { data: msgs, error } = await supabase.from('messages').select('*');
   console.log(msgs, error);
   

   Expected result: error.message contains "Authentication required" and data is null. This confirms that RLS blocks unauthenticated access.

   Now sign in a test user (or use the Supabase Auth UI) and repeat the query. You should receive only rows where user_id matches the signed‑in user’s UUID.

5. Verify Policies in the Database

   Run the following in the Supabase SQL editor or via psql:

   SELECT * FROM pg_policies WHERE tablename = 'messages';
   

   Check that four policies exist, one for each operation, and that their policyqual expressions reference auth.uid() as expected.

6. Handle Administrative Overrides

   To allow backend services to bypass RLS, use the service_role key or the supabase_auth_admin role. Example:

   const adminSupabase = createClient(supabaseUrl, 'service_role-key');
   await adminSupabase.from('messages').insert({ user_id: 'some-uuid', content: 'Hello' });
   

   Be cautious: any key that bypasses RLS should never be exposed to client‑side code.

7. Recovery and Rollback

   • To test the policy removal, drop it:

   DROP POLICY messages_select ON messages;
   

   • To restore full public access temporarily, disable RLS:

   ALTER TABLE messages DISABLE ROW LEVEL SECURITY;
   

   • Re‑enable RLS and recreate the policies if you need to rollback.

Expected Checks

• Unauthenticated queries return 401 or empty result.
• Authenticated user sees only their own rows.
• Admin key can perform any operation regardless of policy.
• Policy list in pg_policies matches the expected four entries.

Limitations and Caveats

• Recursive policies (a policy that queries the same table it protects) can cause infinite loops. Avoid referencing the protected table directly inside the policy expression unless you use careful joins.
• Complex expressions can degrade performance because PostgreSQL evaluates the policy for each row. Keep policy logic simple.
• RLS is only enforced for queries that use the database role that has SELECT, INSERT, etc. privileges. Ensure the role has the necessary privileges on the table.
• Always test in a staging environment before enabling on production.

Practical Verification Checklist

Step	What to Verify	How to Verify
Unauthenticated Access	401 error or empty data	Run a query without signing in; check error field
User‑Specific Data	Only rows with matching user_id	Sign in as user A, query; repeat with user B
Admin Override	Full access with service role	Use service_role key to insert/update/delete; confirm success
Policy Presence	Four policies listed	Query pg_policies for the table

Conclusion

Row‑Level Security in Supabase leverages PostgreSQL’s native policy engine to enforce fine‑grained access control at the database level. By enabling RLS, defining clear policies tied to auth.uid(), and carefully testing with both authenticated and administrative roles, you can guard sensitive data while still allowing necessary backend operations. Remember to keep policies simple, avoid recursion, and verify each change in a safe environment before rolling out to production.