Replies: 4 comments
|
yes, the automatic the important distinction is where the user identity was verified. for user-scoped calls through the data api, pass the user's access token so postgrest verifies the jwt. a direct sql connection authenticates the database role instead. that caller can set for a direct background worker, i'd authorise jobs when they're queued and give its role only the grants and rls policies needed to process them. a user id attached to a job is data, not proof that the user authorised it. where the worker actually needs auth data, use the small private wrappers in the auth article, with restricted before deploying, i'd repeat the migrations in a separate hosted test project and test allowed operations and cross-user/ |
|
1. Recommended architecture for custom worker roles and Auth schema access Direct grants ( For background workers that need user metadata or auth verification, the supported, least-privilege pattern is to expose dedicated -- 1. Create a dedicated worker role
CREATE ROLE assessment_worker WITH LOGIN PASSWORD 'your_secure_worker_password' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
-- 2. Define a tightly scoped SECURITY DEFINER function
CREATE OR REPLACE FUNCTION public.get_worker_user_profile(target_user_id uuid)
RETURNS TABLE (
id uuid,
email text,
user_metadata jsonb
)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public, auth, pg_temp
AS $$
SELECT id, email::text, raw_user_meta_data
FROM auth.users
WHERE id = target_user_id;
$$;
-- 3. Restrict execution permissions strictly to the worker role
REVOKE ALL ON FUNCTION public.get_worker_user_profile(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.get_worker_user_profile(uuid) TO assessment_worker;Key implementation rules:
2. Safe user context impersonation for RLS without bypassing security Granting BEGIN;
-- 1. Assume the authenticated role for this transaction
SET LOCAL role = 'authenticated';
-- 2. Populate the request JWT claims that PostgREST and Supabase RLS policies rely on
SELECT set_config(
'request.jwt.claims',
json_build_object(
'sub', target_user_id,
'role', 'authenticated',
'aud', 'authenticated'
)::text,
true -- is_local = true ensures settings automatically reset at COMMIT or ROLLBACK
);
-- 3. Execute queries under standard RLS enforcement
-- Existing policies evaluating auth.uid() = (request.jwt.claims->>'sub')::uuid apply naturally
UPDATE public.assessments
SET status = 'graded',
score = 95,
updated_at = clock_timestamp()
WHERE id = assessment_id
AND user_id = target_user_id;
COMMIT;Why this maintains strict least-privilege:
3. PostgreSQL 17 ADMIN role membership granted to postgres The automatic, non-revocable In PostgreSQL 16 and 17,
This grant exists solely to enable administrative lifecycle operations: allowing 4. Recommended migration and pre-production verification practices To verify role boundaries and RLS enforcement before pushing changes to Production, test the entire setup locally using the Supabase CLI and
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'assessment_worker') THEN
CREATE ROLE assessment_worker WITH LOGIN PASSWORD 'local_dev_password' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
END IF;
END $$;
GRANT authenticated TO assessment_worker;
GRANT USAGE ON SCHEMA public TO assessment_worker;
BEGIN;
SELECT plan(5);
-- Test 1: Worker cannot access auth schema directly
SET LOCAL ROLE assessment_worker;
SELECT throws_ok(
'SELECT * FROM auth.users',
'42501', -- insufficient_privilege error code
'Worker cannot read auth.users directly'
);
-- Test 2: Worker cannot access private data in raw worker role
SELECT is_empty(
'SELECT * FROM public.assessments',
'Worker cannot access assessments without authenticated user context'
);
-- Test 3: Worker can execute dedicated RPC wrapper
RESET ROLE;
SET LOCAL ROLE assessment_worker;
SELECT lives_ok(
format('SELECT public.get_worker_user_profile(%L)', '00000000-0000-0000-0000-000000000001'::uuid),
'Worker can execute vetted SECURITY DEFINER function'
);
-- Test 4: RLS enforces user isolation when impersonating User A
RESET ROLE;
SET LOCAL ROLE assessment_worker;
SET LOCAL role = 'authenticated';
SELECT set_config('request.jwt.claims', '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated"}', true);
SELECT results_eq(
'SELECT user_id FROM public.assessments WHERE id = ''a1111111-1111-1111-1111-111111111111''',
ARRAY['11111111-1111-1111-1111-111111111111'::uuid],
'User A context retrieves only User A records'
);
-- Test 5: User A context cannot read User B records
SELECT is_empty(
'SELECT * FROM public.assessments WHERE user_id = ''22222222-2222-2222-2222-222222222222''',
'User A context cannot access User B data under RLS'
);
SELECT * FROM finish();
ROLLBACK;
supabase db reset
supabase db testIf this helps clarify the least-privilege role architecture for your workers, please mark it as the answer so others can find it easily! |
|
Hi! I'd like to add one security-related clarification to the proposed background-worker approach. Using These operations change the current database session context. If a backend worker can supply an arbitrary For a least-privilege architecture, I would recommend separating the two execution paths: 1. User-initiated requests Use Supabase Auth and the Data API with the user's real access token, allowing the trusted API layer to validate the token before establishing the database request context. Enforce authorization through RLS policies. 2. Background workers Authenticate the worker independently using a dedicated, restricted service identity. Authorize each job when it is created, store the authorized operation and ownership information server-side, and validate those permissions again during execution. Avoid constructing arbitrary user JWT claims inside a database connection and treating them as verified user identity. 3. Access to the managed Where privileged Auth data is genuinely required, Supabase documents using a narrowly scoped Keep the function in a controlled schema, use a safe fixed 4. Verification before production I would test at least these scenarios in a separate hosted environment:
A manually populated JWT claim can be useful in isolated SQL tests, but it should not replace an end-to-end authentication and authorization test. Relevant Supabase documentation: https://supabase.com/docs/guides/database/postgres/row-level-security I hope this distinction helps prevent an authorization gap in the production worker design. |
|
One thing in the
Check: select has_function_privilege('anon', 'public.get_worker_user_profile(uuid)', 'EXECUTE');Safer, in a schema that isn't exposed: create schema if not exists private;
create or replace function private.get_worker_user_profile(target_user_id uuid)
returns table (id uuid, email text)
language sql stable security definer set search_path = ''
as $ select u.id, u.email::text from auth.users u where u.id = target_user_id $;
revoke execute on function private.get_worker_user_profile(uuid) from public, anon, authenticated;
grant execute on function private.get_worker_user_profile(uuid) to assessment_worker;Return only the columns the worker needs. |
Uh oh!
There was an error while loading. Please reload this page.
Hello Supabase community,
I am developing a private learning application using Supabase Auth, PostgreSQL 17, RLS, and custom database roles for backend assessment processing and background workers.
Our Production deployment is currently paused because we want to maintain a strict least-privilege security model.
Issue 1: Auth schema privileges
Our migration role (
postgres) hasCREATEROLEbut cannot delegateUSAGEon the managedauthschema to custom roles.I found this official Supabase article:
https://supabase.com/kb/troubleshooting/permission-errors-when-granting-custom-roles-access-to-the-auth-schema
It explains that direct grants are unsupported and recommends restricted
SECURITY DEFINERfunctions or accessing request JWT claims.Issue 2: PostgreSQL 17 role membership
When creating custom roles, PostgreSQL automatically gives
postgresan ADMIN membership granted bysupabase_admin. This membership cannot be revoked bypostgres.I also found:
https://supabase.com/kb/troubleshooting/cant-revoke-a-role-membership-granted-by-supabase_admin
My questions:
For a backend worker that needs narrowly scoped access and authenticated user context, what is the recommended architecture using custom database roles?
When using a
SECURITY DEFINERwrapper or reading JWT request settings, how should we ensure the identity comes from a verified authentication context and does not weaken RLS?Is the automatic, non-revocable ADMIN membership granted to
postgresan expected part of Supabase's security model that applications should accept?Are there recommended migration and verification practices for this architecture before deploying it to Production?
Security requirements:
BYPASSRLS, superuser privileges, or broad grants.anon.We have tested alternative approaches in an isolated PostgreSQL environment but have not applied the blocked migrations to Production.
I would appreciate guidance on the safest supported architecture.
Thank you!
All reactions