rls policies & security

44 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Custom Access Token Auth Hook for RBAC

To implement Role-Based Access Control (RBAC) with custom claims, use a Custom Access Token Auth Hook. This hook runs before a token is issued and allows you to add additional claims to the user's JWT.

Custom Claims example structure

Custom claims are special attributes attached to a user. Example claims include: user_role (string like 'admin'), plan (string like 'TRIAL'), user_level (number like 100), group_name (string like 'Super Guild!'), joined_on (ISO timestamp), group_manager (boolean), and items (array of strings like ['toothpick', 'string', 'ring']).

PL/pgSQL custom access token hook function implementation

The custom_access_token_hook function accepts event jsonb parameter and returns jsonb. It fetches the user role from public.user_roles table by user_id extracted from event->>'user_id', then uses jsonb_set to add the user_role claim to the event's claims object. The pattern is: claims := jsonb_set(claims, '{user_role}', to_jsonb(user_role)); then event := jsonb_set(event, '{claims}', claims); return event;

Permissions for custom access token hook function

The custom_access_token_hook function requires: grant usage on schema public to supabase_auth_admin; grant execute on function public.custom_access_token_hook to supabase_auth_admin; revoke execute on function public.custom_access_token_hook from authenticated, anon, public; grant all on table public.user_roles to supabase_auth_admin; revoke all on table public.user_roles from authenticated, anon, public; create policy 'Allow auth admin to read user roles' ON public.user_roles as permissive for select to supabase_auth_admin using (true);

Enable Custom Access Token Auth Hook in Supabase dashboard

To enable the hook, navigate to Authentication > Hooks (Beta) in the dashboard and select the appropriate Postgres function from the dropdown menu. For local development, follow the local development instructions in the Auth Hooks docs.

authorize() function to check RLS permissions from JWT claims

Create an authorize function that takes requested_permission app_permission parameter and returns boolean. The function reads the user's role from their JWT using auth.jwt() ->> 'user_role', casts it to app_role type, then checks if that role has the requested permission by counting matching rows in public.role_permissions table. Function definition: create or replace function public.authorize(requested_permission app_permission) returns boolean as $$ declare bind_permissions int; user_role public.app_role; begin select (auth.jwt() ->> 'user_role')::public.app_role into user_role; select count(*) into bind_permissions from public.role_permissions where role_permissions.permission = requested_permission and role_permissions.role = user_role; return bind_permissions > 0; end; $$ language plpgsql stable security definer set search_path = '';

Using authorize() in RLS delete policies

Use the authorize function within RLS policies. Example: create policy 'Allow authorized delete access' on public.channels for delete to authenticated using ((SELECT authorize('channels.delete'))); create policy 'Allow authorized delete access' on public.messages for delete to authenticated using ((SELECT authorize('messages.delete')));

Reading custom claims from access_token in JavaScript

The auth hook modifies the access token JWT but not the auth response. To access custom claims in a JavaScript browser client, decode the access_token JWT from the auth session using jwt-decode package. Example: import { jwtDecode } from 'jwt-decode'; const { subscription: authListener } = supabase.auth.onAuthStateChange(async (event, session) => { if (session) { const jwt = jwtDecode(session.access_token); const userRole = jwt.user_role; } });

Reading custom claims server-side

For server-side logic, use JWT decoding packages appropriate to your language/framework: express-jwt or koa-jwt for Node.js, PyJWT for Python, dart_jsonwebtoken for Dart, Microsoft.AspNetCore.Authentication.JwtBearer for .NET.

Creating app_role enum type for RBAC

Define a custom enum type for application roles: create type public.app_role as enum ('admin', 'moderator');

Creating app_permission enum type for RBAC

Define a custom enum type for application permissions: create type public.app_permission as enum ('channels.delete', 'messages.delete');

user_roles table structure for RBAC

Create a user_roles table with: id (bigint generated by default as identity primary key), user_id (uuid references auth.users on delete cascade not null), role (app_role not null), unique constraint on (user_id, role). This table tracks which roles are assigned to each user.

role_permissions table structure for RBAC

Create a role_permissions table with: id (bigint generated by default as identity primary key), role (app_role not null), permission (app_permission not null), unique constraint on (role, permission). This table maps which permissions each role has.

Seeding role permissions for RBAC example

Insert role-permission mappings into role_permissions table. Example seed data: insert into public.role_permissions (role, permission) values ('admin', 'channels.delete'), ('admin', 'messages.delete'), ('moderator', 'messages.delete');

OAuth access token JWT structure with client_id claim

Every OAuth access token issued by Supabase Auth is a JWT that includes standard Supabase claims plus OAuth-specific claims. The token includes: sub (user-uuid), role (authenticated), aud (authenticated), user_id (user-uuid), email, client_id (unique identifier of the OAuth client that obtained the token), aal (aal1), amr (authentication methods array), session_id (session-uuid), iss (issuer URL), iat (issued at timestamp), and exp (expiration timestamp). The key OAuth-specific claim is client_id, which uniquely identifies the OAuth client that obtained the token.

Extracting client_id from OAuth token in RLS policies

Use the auth.jwt() function to access the client_id claim in RLS policies. The syntax is (auth.jwt() ->> 'client_id'). To get the client ID from the token, use (auth.jwt() ->> 'client_id'). To check if the token is from an OAuth client, use (auth.jwt() ->> 'client_id') IS NOT NULL. To check if the token is from a specific client, use (auth.jwt() ->> 'client_id') = 'client-id-value'.

OAuth scopes do not control database access

OAuth scopes (openid, email, profile, phone) control what user information is included in ID tokens and returned by the UserInfo endpoint. They do not control access to database tables or API endpoints. Use RLS policies to define which OAuth clients can access which data, regardless of the scopes they requested.

RLS policy allowing specific OAuth client full access example

CREATE POLICY "Mobile app can access user data" ON user_data FOR ALL USING ( auth.uid() = user_id AND (auth.jwt() ->> 'client_id') = 'mobile-app-client-id' );

RLS policy allowing multiple OAuth clients read-only access example

CREATE POLICY "Third-party apps can read profiles" ON profiles FOR SELECT USING ( auth.uid() = user_id AND (auth.jwt() ->> 'client_id') IN ( 'analytics-client-id', 'reporting-client-id', 'dashboard-client-id' ) );

RLS policy preventing OAuth clients from accessing sensitive data

CREATE POLICY "OAuth clients cannot access payment info" ON payment_methods FOR ALL USING ( auth.uid() = user_id AND (auth.jwt() ->> 'client_id') IS NULL -- Only direct user sessions );

RLS policy for client-specific data access example

Analytics client read-only access: CREATE POLICY "Analytics client reads summaries" ON user_metrics FOR SELECT USING ( auth.uid() = user_id AND (auth.jwt() ->> 'client_id') = 'analytics-client-id' ); Admin client full access: CREATE POLICY "Admin client full access" ON user_data FOR ALL USING ( auth.uid() = user_id AND (auth.jwt() ->> 'client_id') = 'admin-client-id' );

Multi-platform RLS policies example

Web app with full access: CREATE POLICY "Web app full access" ON profiles FOR ALL USING ( auth.uid() = user_id AND ( (auth.jwt() ->> 'client_id') = 'web-app-client-id' OR (auth.jwt() ->> 'client_id') IS NULL -- Direct user sessions ) ); Mobile app with read-only access: CREATE POLICY "Mobile app reads profiles" ON profiles FOR SELECT USING ( auth.uid() = user_id AND (auth.jwt() ->> 'client_id') = 'mobile-app-client-id' ); Third-party integration with limited data access: CREATE POLICY "Integration reads public data" ON profiles FOR SELECT USING ( auth.uid() = user_id AND (auth.jwt() ->> 'client_id') = 'integration-client-id' AND is_public = true );

Custom Access Token Hooks with OAuth clients

Custom Access Token Hooks work with OAuth tokens and allow you to inject custom claims based on the OAuth client. Custom Access Token Hooks are triggered for all token issuance. Use the client_id field or authentication_method field (oauth_provider/authorization_code for OAuth flows) to differentiate OAuth from regular authentication.

Customizing audience claim for OAuth clients example

Deno.serve(async (req) => { const { user, claims, client_id } = await req.json() // Customize audience based on OAuth client if (client_id === 'mobile-app-client-id') { return new Response( JSON.stringify({ claims: { aud: 'https://api.myapp.com', app_version: '2.0.0', }, }), { headers: { 'Content-Type': 'application/json' } } ) } if (client_id === 'analytics-partner-id') { return new Response( JSON.stringify({ claims: { aud: 'https://analytics.partner.com', access_level: 'read-only', }, }), { headers: { 'Content-Type': 'application/json' } } ) } // Default audience for non-OAuth flows return new Response(JSON.stringify({ claims: {} }), { headers: { 'Content-Type': 'application/json' }, }) })

Adding client-specific claims in Custom Access Token Hook

import { createClient } from 'https://esm.sh/@supabase/supabase-js@2' Deno.serve(async (req) => { const { user, claims, client_id } = await req.json() const supabase = createClient(Deno.env.get('SUPABASE_URL')!, Deno.env.get('SUPABASE_SECRET_KEY')!) // Add custom claims based on OAuth client let customClaims = {} if (client_id === 'mobile-app-client-id') { customClaims.aud = 'https://mobile.myapp.com' customClaims.app_version = '2.0.0' customClaims.platform = 'mobile' } else if (client_id === 'analytics-client-id') { customClaims.aud = 'https://analytics.myapp.com' customClaims.read_only = true customClaims.data_retention_days = 90 } else if (client_id?.startsWith('mcp-')) { // MCP AI agents const { data: agent } = await supabase .from('approved_ai_agents') .select('name, max_data_retention_days') .eq('client_id', client_id) .single() customClaims.aud = `https://mcp.myapp.com/${client_id}` customClaims.ai_agent = true customClaims.agent_name = agent?.name customClaims.max_retention = agent?.max_data_retention_days } return new Response(JSON.stringify({ claims: customClaims }), { headers: { 'Content-Type': 'application/json' }, }) })

RLS policy based on custom claims from Custom Access Token Hook

Policy based on custom claims: CREATE POLICY "Read-only clients cannot modify" ON user_data FOR UPDATE USING ( auth.uid() = user_id AND (auth.jwt() -> 'user_metadata' ->> 'read_only')::boolean IS NOT TRUE ); Policy based on audience claim: CREATE POLICY "Only specific audience can access" ON api_data FOR SELECT USING ( auth.uid() = user_id AND (auth.jwt() ->> 'aud') IN ( 'https://api.myapp.com', 'https://mobile.myapp.com' ) );

Principle of least privilege for OAuth clients

Grant OAuth clients only the minimum permissions they need. Bad practice: CREATE POLICY "OAuth clients full access" ON user_data FOR ALL USING (auth.uid() = user_id); creates overly permissive access. Good practice: CREATE POLICY "Specific client specific access" ON user_data FOR SELECT USING (auth.uid() = user_id AND (auth.jwt() ->> 'client_id') = 'trusted-client-id'); grants specific access per client.

Separate RLS policies for OAuth clients vs users

Create dedicated policies for OAuth clients rather than mixing them with user policies. User access policy: CREATE POLICY "Users access their own data" ON user_data FOR ALL USING (auth.uid() = user_id AND (auth.jwt() ->> 'client_id') IS NULL); OAuth client access policy (separate): CREATE POLICY "OAuth clients limited access" ON user_data FOR SELECT USING (auth.uid() = user_id AND (auth.jwt() ->> 'client_id') IN ('client-1', 'client-2'));

Audit active OAuth clients query

SELECT oc.client_id, oc.name, oc.created_at, COUNT(DISTINCT s.user_id) as active_users FROM auth.oauth_clients oc LEFT JOIN auth.sessions s ON s.client_id = oc.client_id WHERE s.created_at > NOW() - INTERVAL '30 days' GROUP BY oc.client_id, oc.name, oc.created_at;

Testing RLS policies with OAuth client JWT

Set request.jwt.claims to test as a specific OAuth client: SET request.jwt.claims = '{ "sub": "test-user-uuid", "role": "authenticated", "client_id": "test-client-id" }'; Then run test queries: SELECT * FROM user_data WHERE user_id = 'test-user-uuid'; Reset with: RESET request.jwt.claims;

Debugging OAuth client RLS policy issues

To see what client_id is in the token, run: SELECT auth.jwt() ->> 'client_id'; To test without RLS affecting the result, run: SET LOCAL role = service_role; SELECT * FROM your_table; If OAuth client cannot access data despite valid token, verify: the policy includes the client's client_id, RLS is enabled on the table, there are no conflicting restrictive policies, and test with secret key to isolate RLS issues.

Using AS RESTRICTIVE policies to limit OAuth client access

Use AS RESTRICTIVE policies to add additional constraints that restrict access beyond permissive policies: CREATE POLICY "Restrict OAuth clients" ON sensitive_data AS RESTRICTIVE FOR ALL TO authenticated USING ( -- OAuth clients cannot access this table at all (auth.jwt() ->> 'client_id') IS NULL );

Differentiating between direct users and OAuth clients in RLS

Check if client_id is present to differentiate direct user sessions from OAuth clients. Direct user sessions (no OAuth): CREATE POLICY "Direct users full access" ON user_data FOR ALL USING (auth.uid() = user_id AND (auth.jwt() ->> 'client_id') IS NULL); OAuth clients (with client_id): CREATE POLICY "OAuth clients read only" ON user_data FOR SELECT USING (auth.uid() = user_id AND (auth.jwt() ->> 'client_id') IS NOT NULL);

Password security minimum length recommendation

Supabase Auth recommends setting a minimum password length of at least 8 characters. Anything less than 8 characters is not recommended.

Required character types for password strength

Supabase Auth allows you to require specific character types in passwords. The strongest option requires digits, lowercase letters, uppercase letters, and symbols. The allowed symbols are: !@#$%^&*()_+-=[]{};'\:"| <>?,./`~

Password strength guessing complexity table

The minimum number of guesses required to access a user account varies by character set and length (8 characters): Digits only: approximately 2^27 guesses; Digits and letters: approximately 2^41 guesses; Digits, lower and uppercase letters: approximately 2^48 guesses; Digits, lower and uppercase letters, and symbols: approximately 2^52 guesses.

Leaked password protection with HaveIBeenPwned API

Supabase Auth uses the open-source HaveIBeenPwned.org Pwned Passwords API to reject passwords that have been leaked and are known by malicious actors. This feature prevents the use of leaked passwords and is available on the Pro Plan and above.

Bcrypt password hashing in Supabase Auth

Supabase Auth uses bcrypt, a strong password hashing function, to store hashes of users' passwords. Only hashed passwords are stored, never plaintext. Each hash is accompanied by a randomly generated salt parameter for extra security. The hash is stored in the encrypted_password column of the auth.users table.

Reauthentication for password changes

Users must be recently logged in to change their password without reauthentication. A user is considered recently logged in if the session was created within the last 24 hours. When reauthentication is required, a nonce is sent to the user and must be validated before the password change can occur using the reauthenticate() API call.

Reauthenticate API call for password change

Use the supabase.auth.reauthenticate() API call to trigger reauthentication. This sends a nonce to the user that must be provided in the subsequent updateUser() call along with the new password.

Code example: reauthenticate and update password

const { error } = await supabase.auth.reauthenticate() // send the nonce provided by the user with the password change const { data, error } = await supabase.auth.updateUser({ email: 'user@email.com', nonce: `${nonce}`, password: "new••••••rd" })

Require current password when changing password

You can enforce that users supply their current password when changing their password. When enabled, the password change request validates that the current password is correct before updating the user's password using the current_password parameter in updateUser().

Code example: update password with current password validation

const { data, error } = await supabase.auth.updateUser({ email: 'user@email.com', current_password: "correct_current_password", password: "new••••••rd" })

Strengthened password requirements effect on existing users

Existing users can still sign in with their current password even if it doesn't meet new strengthened password requirements. However, if their password falls short of updated standards, they will encounter a WeakPasswordError during signInWithPassword explaining why it is considered weak. This applies to new users and existing users changing their passwords.

Give your agent this brain