Configuring Granular Security Policies

Let us look at enabling Row Level Security (RLS) on a table and writing granular security policies to restrict data access.

1. Enabling RLS on a Table

You can enable RLS on a table using the Supabase Dashboard GUI (Table Editor -> Edit Table -> Enable Row Level Security) or by executing this SQL command:

-- Enable Row Level Security on the profiles table
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

2. Writing RLS Security Policies

Once RLS is enabled, all access to the table is blocked by default. You must write security policies using PostgreSQL syntax to grant read or write permissions.

A. Allowing Public Read Access (Select)

To allow anyone (including unauthenticated visitors) to read records from a profiles table:

CREATE POLICY "Allow public read access" 
ON profiles 
FOR SELECT 
USING (true);

B. Restricting Writes to Record Owners (Insert/Update)

To restrict write operations so that users can only create or update their own profile records:

CREATE POLICY "Allow owners to update their own profile" 
ON profiles 
FOR UPDATE 
TO authenticated 
USING (auth.uid() = id) 
WITH CHECK (auth.uid() = id);

• TO authenticated: Applies this policy only to users logged in with a valid session token.
• USING (auth.uid() = id): Validates that the active user ID matches the ID of the record they are trying to update.
• WITH CHECK (auth.uid() = id): Ensures any new or updated data values continue to match the user ID (preventing users from changing the ID field to match another user profile).

3. Advanced Helper Functions

PostgreSQL in Supabase provides helper functions to check request contexts:

• auth.uid(): Returns the primary key UUID of the logged-in user making the request.
• auth.role(): Returns the connection role (typically authenticated or anon).
• auth.jwt(): Returns the full decoded JSON Web Token payload, allowing you to check custom user metadata fields (such as admin flags).