Row Level Security for authorization
Auth integrates with Supabase's database features, making it easy to use Row Level Security (RLS) for authorization.
Supabase · Auth · all subjects
authorization/rls
55 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
Auth Token scope in database access
When using Supabase SDKs, data requests are automatically sent with the user's Auth Token. The Auth Token scopes database access on a row-by-row level when used along with RLS policies.
RLS policy to restrict anonymous users using is_anonymous claim
create policy "Only permanent users can post to the news feed" on news_feed as restrictive for insert to authenticated with check ((select (auth.jwt()->>'is_anonymous')::boolean) is false ); create policy "Anonymous and permanent users can view the news feed" on news_feed for select to authenticated using ( true );
Anonymous user RLS access check using is_anonymous JWT claim
An anonymous user assumes the authenticated role like a permanent user. Use row-level security (RLS) policies to differentiate between an anonymous user and a permanent user by checking the is_anonymous claim in the JWT returned by auth.jwt().
Use restrictive RLS policies for anonymous users
RLS policies are permissive by default and are combined using an OR operator. When distinguishing anonymous users from permanent users, construct restrictive policies to ensure checks are always enforced when combined with other policies. A single restrictive RLS policy alone will fail unless combined with another policy that returns true.
Postgres auth schema and security
Supabase Auth uses the auth schema in your Postgres database to store user tables and other information. For security, this schema is not exposed on the auto-generated API. You can connect Auth information to your own objects using database triggers and foreign keys. Any views you create for Auth data must be adequately protected by enabling RLS or revoking grants. Starting in Postgres version 15, views inherit the RLS policies of the underlying tables if created with security_invoker. Views in earlier versions or those created without security_invoker inherit the permissions of the owner, who can bypass RLS policies.
RLS policy for MFA enforcement - opted-in users
For enforcing MFA only for users that have opted-in, use this Row Level Security policy: create policy "Policy name." on table_name as restrictive to authenticated using ( array[(select auth.jwt()->>'aal')] <@ ( select case when count(id) > 0 then array['aal2'] else array['aal1', 'aal2'] end as aal from auth.mfa_factors where ((select auth.uid()) = user_id) and status = 'verified' )); The policy will only accept 'aal2' when the user has at least one MFA factor verified. Otherwise, it will accept both 'aal1' and 'aal2'. The '<@' operator is Postgres's 'contained in' operator. Using 'as restrictive' ensures this policy will restrict all commands on the table regardless of other policies.
RLS policy for MFA enforcement - all users
For enforcing MFA for all users (new and existing), this is a template Row Level Security policy to apply to all tables: create policy "Policy name." on table_name as restrictive to authenticated using ((select auth.jwt()->>'aal') = 'aal2'); This policy will not accept any JWTs with an 'aal' claim other than 'aal2', which is the highest authenticator assurance level. Using 'as restrictive' ensures this policy will restrict all commands on the table regardless of other policies.
Extract SAML SSO provider UUID from JWT
The expression auth.jwt()#>>'{amr,0,provider}' returns the UUID of the SAML SSO identity provider used by the user to sign-in. This can be used in Row Level Security policies to scope access by organization or tenant.
Extract SAML EntityID from JWT
The expression auth.jwt()#>>'{user_metadata,iss}' returns the identity provider's SAML 2.0 EntityID.
SAML RLS policy example using provider UUID
CREATE POLICY "View organization settings." ON organization_settings AS RESTRICTIVE USING (sso_provider_id = (select auth.jwt()#>>'{amr,0,provider}')); This example shows how to use the SAML SSO provider UUID in a Row Level Security policy to scope down which organization_settings rows a user can see based on their SSO provider.
Extract SAML SSO method from JWT
The expression auth.jwt()#>>'{amr,0,method}' returns the name of the last method used to verify the identity of the user. With SAML SSO this returns 'sso/saml'.
MCP RLS policies with client_id
To allow MCP clients to access data protected by Row Level Security, RLS policies must include the MCP client's client_id in their conditions. If an MCP client receives access denied errors despite having a valid token, check that RLS policies include the client_id.
Extract OAuth client_id claim in RLS using auth.jwt()
Use the auth.jwt() function to access token claims in RLS policies. 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'.
RLS pattern: Grant specific client full access
To allow a specific OAuth client to access all user data, create a policy that checks both the user_id and the specific client_id: 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 pattern: Grant multiple clients read-only access
To allow several OAuth clients to read data but not modify it, create a SELECT-only policy that checks if the client_id is in a list of approved clients: 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'));
Use AS RESTRICTIVE policies to further limit OAuth client access
If a policy is too permissive for OAuth clients, use AS RESTRICTIVE policies to add additional constraints that run in addition to permissive policies. Example: CREATE POLICY "Restrict OAuth clients" ON sensitive_data AS RESTRICTIVE FOR ALL TO authenticated USING ((auth.jwt() ->> 'client_id') IS NULL); This prevents OAuth clients from accessing the table entirely.
RLS pattern: Restrict sensitive data from OAuth clients
To prevent OAuth clients from accessing sensitive data and allow only direct user sessions, create a policy that checks if client_id is NULL: 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);
Use custom JWT claims in RLS policies
Custom claims added via Custom Access Token Hooks can be used in RLS policies. To check a custom claim from user_metadata, use: (auth.jwt() -> 'user_metadata' ->> 'claim_name')::type. To check the audience claim, use: (auth.jwt() ->> 'aud'). Example: 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);
Security best practice: 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); 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');
Security best practice: Separate RLS policies for OAuth clients and users
Create dedicated policies for OAuth clients rather than mixing them with user policies. Example: separate policy for direct user access with (auth.jwt() ->> 'client_id') IS NULL, and a separate policy for OAuth clients with specific client_id checks. This makes policies clearer and reduces accidental permission grants.
Testing RLS policies for OAuth clients
Test RLS policies before deploying to production by setting request.jwt.claims to simulate an OAuth client token: SET request.jwt.claims = '{"sub": "test-user-uuid", "role": "authenticated", "client_id": "test-client-id"}'; then run test queries; then RESET request.jwt.claims;. Alternatively use the Supabase Dashboard's RLS policy tester.
Troubleshoot: Differentiate between direct users and OAuth clients in RLS
To apply different logic for direct user sessions versus OAuth clients, check if client_id is present or NULL. Direct user sessions have (auth.jwt() ->> 'client_id') IS NULL, while OAuth clients have (auth.jwt() ->> 'client_id') IS NOT NULL. Create separate policies for each case to avoid confusion.
Row Level Security and OAuth client_id claim
Row Level Security policies can use the client_id claim from OAuth access tokens to control which data each OAuth client can access. All OAuth access tokens have full access to user data (same as regular session tokens), with the addition of the client_id claim.
Supabase Auth exposes publishable API key safely in Expo apps
In Expo apps, the Supabase API URL and publishable key are safe to expose because Supabase has Row Level Security enabled on the database, protecting data from unauthorized access.
RLS policy example: check second factor verification
An RLS policy can use the fva (second factor verification age) claim in the Clerk session token to check that second factor verification has been passed. A value of '-1' indicates the user has not passed second factor verification, so the policy can reject access for users with fva = '-1'.
RLS policies with Clerk: use JWT claims
Once the Supabase client is configured to use Clerk session tokens, use RLS policies to secure database access, Storage objects, and Realtime channels. The recommended approach is to use claims present in the Clerk session token to allow or reject access. Consult Clerk's documentation for the available JWT claims and their values.
RLS policy example: check user organization role
An RLS policy can check that a newly inserted row contains the user's declared organization ID in the organization_id column and verify the user is an org:admin. This ensures only organization admins can add rows for organizations they are members of.
Postgres function to reduce RLS policy duplication for Firebase Auth
Create a stable Postgres function to avoid duplicating RLS policy logic across many tables. Create function public.is_supabase_or_firebase_project_jwt() returns bool language sql stable returns null on null input return ( (auth.jwt()->>'iss' = 'https://<project-ref>.supabase.co/auth/v1') or (auth.jwt()->>'iss' = concat('https://securetoken.google.com/<firebase-project-id>') and auth.jwt()->>'aud' = '<firebase-project-id>') ); Then simplify table policies to: create policy "Restrict access to correct Supabase and Firebase projects" on table_name as restrictive to authenticated using ((select public.is_supabase_or_firebase_project_jwt()) is true);
Firebase Auth security with RLS policies
Firebase Auth uses a single set of JWT signing keys for all projects, meaning JWTs from unrelated Firebase projects could access your data. When self-hosting, you must create and attach restrictive RLS policies to all tables in the public schema, Storage, and Realtime to prevent unauthorized access. On the hosted Supabase platform, JWTs from unregistered Firebase project IDs are automatically rejected before reaching the database.
Restrictive RLS policy for Firebase Auth with Supabase
Use this restrictive RLS policy to allow access only from your specific Firebase project and Supabase Auth users: create policy "Restrict access to Supabase Auth and Firebase Auth for project ID <firebase-project-id>" on table_name as restrictive to authenticated using ( (auth.jwt()->>'iss' = 'https://<project-ref>.supabase.co/auth/v1') or (auth.jwt()->>'iss' = 'https://securetoken.google.com/<firebase-project-id>' and auth.jwt()->>'aud' = '<firebase-project-id>') ); Replace <project-ref> with your Supabase project ID and <firebase-project-id> with your Firebase Project ID.
Use as restrictive clause in Firebase Auth RLS policies
Firebase Auth RLS policies must use the 'as restrictive' clause, not permissive policies. Restrictive policies do not grant permissions but restrict any existing or future permissions. Omitting 'as restrictive' will not properly secure your data against Firebase projects you have not registered.
Foreign key references must use primary keys from auth.users
Only use primary keys as foreign key references for schemas and tables like auth.users which are managed by Supabase. Primary keys are guaranteed not to change, whereas columns, indices, constraints or other database objects managed by Supabase may change at any time.
Cascade deletes when referencing auth.users
When creating a foreign key reference to auth.users, specify on delete cascade to ensure profile data is automatically deleted when the user is deleted. For example: id uuid not null references auth.users on delete cascade
Sample profiles table with RLS setup
create table public.profiles ( id uuid not null references auth.users on delete cascade, first_name text, last_name text, primary key (id) ); GRANT SELECT ON public.profiles TO anon; GRANT SELECT, INSERT, UPDATE, DELETE ON public.profiles TO authenticated; GRANT SELECT, INSERT, UPDATE, DELETE ON public.profiles TO service_role; alter table public.profiles enable row level security;
Auto-populate profiles table on user signup with trigger
create function public.handle_new_user() returns trigger language plpgsql security definer set search_path = '' as $$ begin insert into public.profiles (id, first_name, last_name) values (new.id, new.raw_user_meta_data ->> 'first_name', new.raw_user_meta_data ->> 'last_name'); return new; end; $$; create trigger on_auth_user_created after insert on auth.users for each row execute procedure public.handle_new_user();
App role and permission enum types for RBAC
Create two custom PostgreSQL enum types: app_role as enum with values 'admin' and 'moderator', and app_permission as enum with values 'channels.delete' and 'messages.delete'. Use these types in the user_roles and role_permissions tables.
User roles table schema
Create a user_roles table with columns: id (bigint generated by default as identity primary key), user_id (uuid foreign key referencing auth.users on delete cascade, not null), role (app_role not null). Add a unique constraint on (user_id, role).
Role permissions table schema
Create a role_permissions table with columns: id (bigint generated by default as identity primary key), role (app_role not null), permission (app_permission not null). Add a unique constraint on (role, permission).
Example role permissions seeding
Seed the role_permissions table with example data: admin role has 'channels.delete' and 'messages.delete' permissions, moderator role has 'messages.delete' permission.
Authorize function for RLS policies
Create a function named authorize that takes an app_permission parameter and returns boolean. The function fetches the user's role from auth.jwt() ->> 'user_role' cast to app_role, queries the role_permissions table to count matching permissions, and returns true if count > 0. Mark the function as stable with security definer and set search_path to empty string.
RLS policies using authorize function
Create RLS delete policies using the authorize function. Example: 'create policy "Allow authorized delete access" on public.channels for delete to authenticated using ( (SELECT authorize(\'channels.delete\')) )' and similarly for messages with 'messages.delete' permission.
Enable Row Level Security on a table
To enable Row Level Security on a table in PostgreSQL, use the SQL command: alter table table_name enable row level security;
Create RLS policy for SELECT with anonymous role
To create an RLS policy that allows the anonymous role to read from a table, use: create policy "policy_name" on public.table_name for select to anon using (true);
Grant SELECT privilege to anonymous role
To grant read access to the anonymous role on a table, use: grant select on public.table_name to anon;
Full RLS setup workflow
A complete Row Level Security setup involves three steps: (1) Enable RLS on the table with 'alter table table_name enable row level security;', (2) Grant the necessary privileges with 'grant select on public.table_name to anon;', and (3) Create a policy with 'create policy "policy_name" on public.table_name for select to anon using (condition);'
Quickstart SQL setup example
Example SQL setup for a Supabase quickstart: create table instruments (id bigint primary key generated always as identity, name text not null); insert into instruments (name) values ('violin'), ('viola'), ('cello'); grant select on public.instruments to anon; alter table instruments enable row level security; create policy "public can read instruments" on public.instruments for select to anon using (true);
Management API for programmatic SQL execution
SQL queries can be executed programmatically using the Supabase Management API or the MCP server instead of manually running queries in the SQL Editor.
RLS policy for document sections select access
To implement RAG with permissions, grant SELECT privilege on document_sections to authenticated role, enable row level security on the table, then create a policy named 'Users can query their own document sections' for select operations. The policy uses a subquery to check if the document_id exists in the documents table where owner_id equals the current user's auth.uid().
RLS automatically filters vector similarity searches
Once an RLS policy is created on document_sections, all subsequent queries including semantic search operations (such as inner product similarity with pgvector operators) will implicitly filter results based on the RLS policy, respecting user permissions without explicit where clause filtering.
Many-to-many document ownership with RLS
To support multiple users owning the same document, create a document_owners join table with owner_id and document_id fields. The RLS policy on document_sections then queries this join table instead of directly querying documents, checking if the current user's ID matches an owner_id for the document_id.
Foreign Data Wrapper for external user and document data
When user and document data live in an external Postgres database, use Foreign Data Wrappers (FDW) to connect the external DB from Supabase. Create a foreign server with postgres_fdw, map the authenticated role to a user on the external DB, and import the foreign tables into a schema. RLS policies can then query these foreign tables.
FDW setup for external Postgres database
To set up FDW for an external Postgres database: create a schema called 'external', create an extension 'postgres_fdw' in the extensions schema, create a server named 'foreign_server' with postgres_fdw driver specifying host, port, and dbname options, create a user mapping for the authenticated role to the external postgres user with password, then import the desired foreign tables using 'import foreign schema public limit to (tables) from server foreign_server into external'.
Foreign table without foreign key constraint
When maintaining a reference to a foreign document via document_id in document_sections table, do not add a foreign key reference since foreign keys can only be added to local tables. Ensure the ID data type matches the external documents table's ID column.
Always use RLS instead of application-level filtering
RLS should be used as a best practice for access control instead of filtering by user within the WHERE clause. Though application-level filtering will work, RLS is always applied even as new queries and application logic is introduced in the future, providing a security guarantee at the database layer.