row level security & policies

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

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 to define which OAuth clients can access which data, regardless of the scopes they requested.

Extract OAuth client_id from JWT in RLS policies

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

RLS pattern: Grant specific OAuth client full access

Use this policy to allow a specific OAuth client to access all user data: 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 OAuth clients read-only access

Use this policy to allow several OAuth clients to read data but not modify it: 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 pattern: Restrict sensitive data from OAuth clients

Use this policy to prevent OAuth clients from accessing sensitive data while allowing direct user sessions: 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);

RLS pattern: Client-specific data access based on analytics vs admin

Create separate policies for different client types. Analytics client read-only: 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 example: web app, mobile app, and third-party integrations

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)); CREATE POLICY "Mobile app reads profiles" ON profiles FOR SELECT USING (auth.uid() = user_id AND (auth.jwt() ->> 'client_id') = 'mobile-app-client-id'); 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);

RLS policies using custom claims from Access Token Hook

Use custom claims in RLS policies. Example read-only restriction: 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);. Example 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'));

RLS security best practice: Principle of least privilege for OAuth clients

Grant OAuth clients only the minimum permissions they need. Bad example: CREATE POLICY "OAuth clients full access" ON user_data FOR ALL USING (auth.uid() = user_id); grants all access. Good example: 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.

RLS security best practice: Separate policies for OAuth clients and users

Create dedicated policies for OAuth clients rather than mixing them with user policies. User access: 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 (separate policy): 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

Query to track and review which clients have access: 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;

Test RLS policies with OAuth client JWT claims

To test RLS policies before deploying to production, set request.jwt.claims: SET request.jwt.claims = '{"sub": "test-user-uuid", "role": "authenticated", "client_id": "test-client-id"}'; Then test queries like: SELECT * FROM user_data WHERE user_id = 'test-user-uuid'; Reset with: RESET request.jwt.claims; Alternatively use the Supabase Dashboard's RLS policy tester.

Troubleshoot RLS policy not working for OAuth client

If OAuth client cannot access data despite having a valid token: 1) Verify the policy includes the client's client_id, 2) Ensure RLS is enabled on the table, 3) Check for conflicting restrictive policies, 4) Test with secret key to isolate RLS issues. Debug queries: SELECT auth.jwt() ->> 'client_id'; to see client_id in token. SET LOCAL role = service_role; SELECT * FROM your_table; to test without RLS.

RLS RESTRICTIVE policy to prevent OAuth clients from accessing sensitive tables

Use AS RESTRICTIVE policies to add additional constraints that apply 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 at all.

Differentiate between direct user sessions and OAuth clients in RLS

Check if client_id is present in the JWT to differentiate. Direct user sessions have 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 have 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);

Database migration example: create table with RLS

Example SQL for POST /v1/projects/{ref}/database/migrations: create table public.todos (id serial primary key, task text not null); grant select on public.todos to anon; grant select, insert, update, delete on public.todos to authenticated; grant select, insert, update, delete on public.todos to service_role; alter table public.todos enable row level security;

Temporary access no-permission-elevation guarantee

When a user assumes a Postgres role using temporary access, no new roles or users are created in the Postgres database. The assumed role's existing permissions apply as normal. Temporary access authentication only applies to Supabase project users; it does not elevate permissions.

Give your agent this brain