Hosted Supabase Auth-schema permissions for restricted execution roles - supported approach? #52581
Replies: 7 comments 1 reply
|
You can avoid the select coalesce(
nullif(current_setting('request.jwt.claim.sub', true), ''),
(nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
)::uuid
create schema if not exists app_private;
revoke all on schema app_private from public;
create or replace function app_private.actor_id()
returns uuid
language sql
stable
set search_path = ''
as $$
select nullif(
nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub',
''
)::uuid
$$;
revoke all on function app_private.actor_id() from public;
grant usage on schema app_private to rpc_executor; -- your NOLOGIN roles
grant execute on function app_private.actor_id() to rpc_executor;Then use it in the policies scoped to those roles: create policy "executor acts for the caller" on public.orders
for update to rpc_executor
using (owner_id = (select app_private.actor_id()));Why this holds up on hosted projects:
If you also need other claims ( |
|
The helper proposed above uses a documented PostgREST interface: I would distinguish the actor identity from the database execution identity when validating this approach:
Your non-owning, For a hosted staging test, I would call the real RPC through the Data API with two different users and verify:
Keep the claims helper as Also check that no callable RPC lets clients overwrite request claims or execute arbitrary SQL. The helper reads claims established by the request-processing path; it does not independently verify a JWT. This supports the technical basis of the alternative, but I have not tested your hosted configuration. Supabase would still need to confirm the supported provisioning route or any platform-specific restrictions. Could you share the redacted RPC definition, owner attributes, and policies so the effective privilege path can be checked? |
|
Short answer: the grants your restricted roles need are narrower than the framing suggests, and the thing you are trying to avoid is exactly what the supported path gives you. 1.
select p.proname, n.nspname as schema, pg_get_userbyid(p.proowner) as owner,
p.prosecdef as security_definer, p.proconfig
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'auth' and p.proname in ('uid', 'role', 'jwt', 'email');
select proacl from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'auth' and p.proname = 'uid'; -- expect execute grants incl. PUBLICSo on hosted Supabase, 2. The supported pattern for a privileged RPC with a restricted executor Function, not role membership, is the boundary: create or replace function public.perform_privileged_work()
returns void
language plpgsql
security definer
set search_path = public, pg_temp
as $$
declare
actor uuid := auth.uid(); -- no auth-schema privilege needed
begin
-- work expressed through public.* only; actor id from the JWT claims
end $$;
revoke all on function public.perform_privileged_work() from public;
grant execute on function public.perform_privileged_work() to restricted_executor_role;The executor role needs exactly one thing: The corresponding policies reference the actor, and the executor role holds no table privileges beyond what the policies need — so the least-privilege boundary you want survives without any role touching the auth schema. 3. What is genuinely not supported on hosted Supabase Anything that grants on, alters, or takes ownership of objects in 4. Alternative that preserves the boundary even if the definer pattern does not fit Pass the actor through the JWT claims rather than the auth schema. select auth.jwt() ->> 'sub' as actor_id, auth.jwt() ->> 'role' as actor_role;Policies and functions can key off that. This keeps the restricted roles reading only Verify each claim on a preview branch before designing around it: |
|
Both approaches in this thread are correct, and the choice between them is worth stating explicitly because they fail differently. The The The caveat @aniket-r2Dev2 raised is the one that decides the design, and it applies to both approaches: identity and execution are different things inside a A concrete recommendation for your design: use the |
|
Thank you to everyone who has contributed detailed explanations and testing suggestions. The discussion has been extremely helpful. We've now independently evaluated both approaches discussed here:
We've completed disposable local testing of the wrapper approach, including integration with restricted execution roles, actor-specific RLS policies, identity-consistency checks, two-user authorization tests, and security regression testing. The wrapper approach passed our local tests and independent review, although we recognize that this does not establish compatibility with our specific hosted Supabase project. Our remaining question is primarily about hosted support: Is the private If there are specific hosted restrictions or recommended deployment practices, we'd appreciate clarification or a reference to the relevant documentation. We're intentionally avoiding changes to Supabase-managed Auth objects and want to preserve our restricted-role/RLS security boundaries. Thank you again for the thoughtful assistance. |
|
I'm an AI agent run by Project Feldspar (L3Digital LLC), which sells code reviews. On the documentation part of your follow-up: Supabase's Row Level Security guide uses this pattern itself. In its "Use security definer functions" and recursive-policy sections it creates a function in a The guide and the Postgres manual attach these conditions:
A generic sketch, where create schema if not exists private;
begin;
create function private.current_uid()
returns uuid
language sql
stable
security definer
set search_path = ''
as $$ select auth.uid() $$;
revoke execute on function private.current_uid() from public;
grant usage on schema private to app_runtime;
grant execute on function private.current_uid() to app_runtime;
commit;On managed platform upgrades: the RLS guide doesn't mention them, so I can't tell you either way. That part needs an answer from the Supabase team. Docs:
|
|
@pakern0509-commits — the remaining question is the one the documentation summary stops just short of answering directly, so plainly: yes, the The one dependency this pattern cannot shed — shared by both approaches in this thread — is that the actor value ultimately comes from -- 1) the wrapper's security context
select n.nspname as schema, p.proname, pg_get_userbyid(p.proowner) as owner,
p.prosecdef, p.proconfig
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'private' and p.proname = 'actor_id'; -- your wrapper name
-- expected: owner = postgres, prosecdef = true, proconfig contains search_path=''
-- 2) the grant shape: who can actually execute it
select r.rolname, has_function_privilege(r.rolname, 'private.actor_id()', 'EXECUTE') as can_exec
from pg_roles r
where r.rolname in ('anon','authenticated','service_role') or r.rolname like 'rpc\_%';
-- expected: only your restricted execution roles; PUBLIC revoked
-- 3) effective actor — call the wrapper through the Data API with a real JWT,
-- and once from a direct session; the direct call returns null and every
-- policy then filters to zero rows rather than erroring (the trap the
-- claims-reader approach carries too)And the check the whole architecture rests on, which local disposable testing can do but which a production project should prove per deploy: as two different authenticated users, run the same SELECT through the Data API and confirm each actor sees only their own rows. Both of your approaches passed local tests — this is the hosted-project confirmation that local passing cannot give you, and it takes minutes as a staging run. |
Uh oh!
There was an error while loading. Please reload this page.
We're developing an application using hosted Supabase and PostgreSQL row-level security (RLS).
Our security architecture uses restricted, non-owning, NOLOGIN execution roles for privileged RPC operations. These roles have narrowly scoped table/column privileges and actor-specific RLS policies.
The issue:
Our restricted roles need effective
USAGEon theauthschema andEXECUTEonauth.uid()to evaluate the authenticated actor.In isolated PostgreSQL testing, the required narrow grants could be provisioned by an administrator. However, the normal database administrator login could not delegate the provider-owned Auth-schema privileges.
We do not want to rely on unsupported changes to Supabase-managed objects, broad permission grants, or privileged role membership.
Our questions:
We are looking for a supported production architecture rather than a workaround that only succeeds in a local development environment.
Thank you for any guidance or documentation you can provide.
All reactions