Setting Up Row-Level Security in Supabase User and Admin

Row-Level Security (RLS) in Supabase is a powerful feature that allows you to control access to your database tables at the row level, ensuring users can only access or modify data they’re authorized to. In this post, we’ll walk through setting up RLS on a profiles table in Supabase, where authenticated users can view and edit their own profiles, and admins can view and edit all profiles. Let’s dive in!

Prerequisites

• A Supabase project with authentication enabled.
• A profiles table in the public schema with the following columns:
  • id (UUID, references auth.users(id)).
  • is_admin (boolean, indicates admin status).
• Basic familiarity with SQL and Supabase’s dashboard or SQL Editor.

Step 1: Enable RLS on the Profiles Table

First, we need to enable RLS on the profiles table to enforce access control. Without RLS, users could access all rows, which we want to avoid. Run the following SQL command in the Supabase SQL Editor:

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

This ensures that no one can access the table unless we define explicit RLS policies.

Step 2: Define RLS Policies

We’ll create four RLS policies to achieve our goal:

• Allow authenticated users to view their own profile.
• Allow authenticated users to edit their own profile.
• Allow admins to view all profiles.
• Allow admins to edit all profiles.

Policy 1: Authenticated Users View Their Own Profile

This policy allows logged-in users to view their own profile by matching their auth.uid() (the user’s ID from Supabase Auth) with the id column in the profiles table.

CREATE POLICY "Authenticated users can view their own profile"
ON public.profiles
FOR SELECT
TO authenticated
USING (auth.uid() = id);

• What it does: Only allows SELECT queries for rows where the authenticated user’s ID matches the id column.
• Who it applies to: Users with the authenticated role (logged-in users).

Policy 2: Authenticated Users Edit Their Own Profile

This policy allows users to update their own profile, again by matching auth.uid() with the id column.

CREATE POLICY "Authenticated users can edit their own profile"
ON public.profiles
FOR UPDATE
TO authenticated
USING (auth.uid() = id);

• What it does: Permits UPDATE queries only on the user’s own profile row.
• Who it applies to: Authenticated users.

Policy 3 & 4: Admins View and Edit All Profiles

Initially, you might write admin policies by checking the is_admin column directly in the profiles table, like this:

CREATE POLICY "Admins can view all profiles"
ON public.profiles
FOR SELECT
TO authenticated
USING (is_admin = true);

CREATE POLICY "Admins can edit all profiles"
ON public.profiles
FOR UPDATE
TO authenticated
USING (is_admin = true);

However, this approach requires Supabase to check the is_admin column for the user’s own row, which can be inefficient, especially for large tables. Instead, we’ll use a security definer function to optimize the admin check.

Step 3: Create a Security Definer Function

To improve performance and maintainability, let’s create a function that checks if a user is an admin by querying their is_admin status. This avoids repetitive table scans in policies.

CREATE OR REPLACE FUNCTION public.is_admin_user(user_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  is_admin BOOLEAN;
BEGIN
  SELECT profiles.is_admin INTO is_admin
  FROM public.profiles
  WHERE profiles.id = user_id;
  RETURN is_admin;
END;
$$;

• Why use SECURITY DEFINER? It runs with the privileges of the function’s creator, bypassing RLS for the internal query, making it more efficient.
• What it does: Takes a user ID, checks their is_admin status in the profiles table, and returns true if they’re an admin, false otherwise.

Now, let’s update the admin policies to use this function:

-- Drop existing admin policies if they exist
DROP POLICY IF EXISTS "Admins can view all profiles" ON public.profiles;
DROP POLICY IF EXISTS "Admins can edit all profiles" ON public.profiles;

-- Create new admin policies using the function
CREATE POLICY "Admins can view all profiles"
ON public.profiles
FOR SELECT
TO authenticated
USING (public.is_admin_user(auth.uid()));

CREATE POLICY "Admins can edit all profiles"
ON public.profiles
FOR UPDATE
TO authenticated
USING (public.is_admin_user(auth.uid()));

These policies allow users identified as admins (via the is_admin_user function) to view or edit all profiles.

Step 4: Auto-Create Profiles for New Users

To ensure every user has a profile (and avoid issues where is_admin_user returns NULL), create a trigger that automatically adds a profile when a new user signs up:

CREATE OR REPLACE FUNCTION public.create_profile_for_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO public.profiles (id, is_admin)
  VALUES (NEW.id, FALSE);
  RETURN NEW;
END;
$$;

CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.create_profile_for_new_user();

This sets is_admin to false by default for new users, ensuring the profiles table is populated.

Step 5: Testing the Policies

To verify everything works:

1. Test as a Regular User:

   • Log in as a non-admin user (is_admin = false).
   • Run a query like SELECT * FROM profiles using the Supabase client:

    const { data, error } = await supabase.from('profiles').select('*');
    console.log(data, error);
   

   This should return only the user’s own profile.

   • Try updating another user’s profile (e.g., UPDATE profiles SET ... WHERE id = 'other_user_id'). It should fail.

2. Test as an Admin:

   • Log in as an admin (is_admin = true).
   • Run the same SELECT query to confirm access to all profiles.
   • Test an UPDATE on any profile; it should succeed.

3. Use the SQL Editor:

   • Simulate a user with call auth.login_as_user('user_email') and test queries directly.

Troubleshooting Tips

• No Data Returned: Ensure auth.uid() matches the id column and that the user has a profile. Check if RLS is enabled.
• Permission Errors: If you see “new row violates row-level security policy,” verify the policy conditions and the user’s authentication status.
• Performance Issues: The is_admin_user function reduces overhead, but ensure the id column is indexed (it is by default if it’s the primary key).
• Missing Profiles: If a user lacks a profile row, the is_admin_user function returns NULL. The trigger above prevents this, but double-check for existing users.

Additional Considerations

• INSERT and DELETE Policies: If your app allows profile creation or deletion, add policies for INSERT and DELETE. For example:

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

• Service Role for Admin Tasks: Use the service_role key for server-side operations that need to bypass RLS, but keep it secure and never expose it client-side.
• Scalability: For large tables, monitor query performance. The is_admin_user function is optimized, but consider caching admin status if needed.

Conclusion

With these RLS policies and the is_admin_user function, you’ve set up a secure and scalable access control system for your profiles table in Supabase. Authenticated users can manage their own profiles, while admins have full access to all profiles. Always test thoroughly in a development environment before going live, and consult the Supabase RLS documentation for more details.

Happy coding and let me know in the comments if you have questions or run into issues. I am running supabase self-hosted version. Contact for any paid setup & consulting work!