How can we manage a dedicated SECURITY DEFINER function owner in Supabase-managed PostgreSQL?

[Croccifixio13]

Hi Supabase community,

I'm implementing a server-mediated authorization system in a Supabase project and have encountered a PostgreSQL role-membership limitation. I'd appreciate guidance on the supported approach for managing a dedicated, non-login role that owns a narrowly scoped SECURITY DEFINER function.

Environment

• Supabase project: employment-road-dev (DEV environment)

• PostgreSQL project ref: emukjkjntxkjbxlrcnjn

• PostgreSQL role: r07_p1_executor

The executor role exists with these attributes:

• NOLOGIN

• NOSUPERUSER

• NOCREATEDB

• NOCREATEROLE

• NOBYPASSRLS

• NOINHERIT

Observed role membership

The database catalog reports:

Property | Value -- | -- Member | postgres Granted role | r07_p1_executor Grantor | supabase_admin admin_option | true set_option | false inherit_option | false

The SQL session available to us reports current_user = postgres and rolsuper = false.

The session cannot revoke this membership because the grant was made by supabase_admin. The membership also does not currently permit postgres to inherit or SET ROLE into r07_p1_executor.

Intended architecture

We want to implement an authenticated, self-authorizing RPC that:

• Derives the human actor from auth.uid() rather than accepting a caller-supplied actor ID.

• Performs complete authorization checks inside the RPC.

• Uses a narrowly scoped SECURITY DEFINER function owned by a dedicated, non-login executor role.

• Uses a fixed minimal search_path and schema-qualified references.

• Denies direct table CRUD to ordinary application roles.

• Performs authorization mutation, idempotency/replay handling, and success-audit insertion atomically.

• Keeps exceptional bootstrap and recovery operations separate from the normal authorization path.

We do not want to use service_role as a substitute for human identity or weaken the authorization boundary to work around a platform limitation.

Questions

1. Is there a supported way in Supabase-managed PostgreSQL to remove or change this specific role membership when the grantor is supabase_admin but the available postgres session is not a superuser?

2. Is there a documented, recommended pattern for assigning a dedicated non-login role as the owner of a narrowly scoped SECURITY DEFINER function in Supabase?

3. If the membership cannot be removed by a project owner, what is the supported alternative that preserves least privilege and prevents the normal application identity from assuming or administering the executor role?

4. Are there specific Supabase role-management or function-security constraints we should account for before proceeding?

We are not requesting broad superuser access. We want to use the supported managed-platform mechanism and preserve the restricted executor role attributes.

No Production changes are involved. The issue is currently blocking implementation in our DEV environment.

Thank you for any guidance or references to relevant documentation.

[aniketatgithub]

Two things in the catalog row you posted change the picture a bit.

First, your membership row has admin_option = true. In Postgres, holding admin option on a role means you can grant and revoke membership in that role, regardless of who the original grantor was. So before treating the membership itself as platform locked, it is worth testing a plain REVOKE of postgres from r07_p1_executor in your dev project. If that works, question 1 mostly goes away.

The harder blocker is set_option = false. Owning objects as another role requires being able to SET ROLE into it, and with SET false your postgres session cannot do that, so you cannot create the function with r07_p1_executor as owner. Since you hold admin option, you can also test re-granting your own membership with SET set to true. I cannot say whether the managed platform lets that stick, but it is a quick test in dev. If neither change takes, both are small and specific asks for Supabase support: flip SET on that one membership, or adjust the membership for you. Roles granted by supabase_admin are platform managed, so support is the supported path there.

On question 2, the pattern most Supabase projects actually use skips the dedicated owner role:

1. SECURITY DEFINER function with search_path pinned to empty and every object schema-qualified inside.
2. Actor derived server side from auth.uid(), as you already plan.
3. Execute revoked from public and granted only to the role that should call it, usually authenticated.
4. Function kept in a schema that is not exposed through the API.

That gets you nearly all of the least privilege benefit. The separate NOLOGIN owner is cleaner on paper, but on a managed platform its usability depends on exactly the role options you are stuck on, so it is only worth it if support will set it up for you once.

For question 4, the main thing to respect is that a definer function runs with its owner's privileges. If that owner is postgres, treat argument handling and any dynamic SQL in exposed functions carefully, and recheck execute grants whenever you add one. In practice that matters more than whether the owner is a separate role.

[cekuu35]

Building on @aniketatgithub's analysis — the catalog row confirms you're closer to unblocked than it seems, and there's one additional runtime failure mode worth planning for.

1. The two test statements, in order

Since your membership row has admin_option = true, a plain revoke should work regardless of the original grantor (that's exactly what ADMIN OPTION means — the holder can grant/revoke the membership, grantor notwithstanding):

revoke r07_p1_executor from postgres;

You still need SET ROLE capability to do ALTER FUNCTION ... OWNER TO r07_p1_executor, so re-grant with the SET option:

grant r07_p1_executor to postgres with set true;

That flag is the actual blocker in your row (set_option = false), and with admin option you can re-issue the membership to yourself with it. If the managed platform strips either change on its maintenance pass, that's a precise, small ask for support: flip SET on that one membership row.

2. The failure mode after ownership transfer: the executor role needs its own table GRANTs

SECURITY DEFINER runs with the owner's privileges. A NOLOGIN role with no table grants makes the function fail at runtime with permission denied for table ... — even though creation and ownership transfer succeed. Custom roles never inherited Supabase's permissive defaults (those go to anon/authenticated/service_role only), so grant the executor exactly what the function body touches:

grant select, insert on public.authorization_decisions to r07_p1_executor;
-- nothing beyond what the function actually reads/writes

Same discipline the October 30 default-privileges change makes mandatory for the platform roles — if you're running an Oct 30 readiness pass on this project anyway, include SECURITY DEFINER owner roles in the grants inventory so they don't get missed.

3. On question 4 — constraints worth knowing before proceeding

• auth.uid() keeps working inside SECURITY DEFINER (JWT claims are session-level GUCs), but returns NULL on service-key calls — gate on that explicitly if service_role should never invoke this path.
• Never accept an actor id as a function parameter alongside auth.uid() — the parameter version is the injection vector your design already avoids; keep it that way in future refactors.
• Pin search_path = '' on the function and schema-qualify everything inside; that plus revoke execute from public covers the realistic hijack surfaces on the managed platform if the dedicated-owner pattern doesn't stick.
• Verify after any platform maintenance whether your REVOKE/SET changes survive — supabase_admin-granted rows can be re-synced. A quick select * from pg_auth_members where roleid = 'r07_p1_executor'::regrole; as a smoke check is worth automating in CI.

If the re-grant with SET TRUE holds, the full sequence is: grant executor its table minimum → transfer function ownership → revoke/limit postgres membership → leave only authenticated with EXECUTE. That gets you the isolation you designed without waiting on support.

[spokodev]

I reproduced your membership row on a local Postgres 17.2, with the bootstrap superuser named supabase_admin and postgres as a non-superuser with CREATEROLE. Running create role r07_p1_executor nologin noinherit; as postgres gives exactly your row:

  member  |    grantor     | admin_option | inherit_option | set_option
----------+----------------+--------------+----------------+------------
 postgres | supabase_admin | t            | f              | f

That is what Postgres itself records since version 16: when a non-superuser creates a role with CREATEROLE, "the created role is automatically granted back to the creating user, just as if the bootstrap superuser had executed the command GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE, INHERIT FALSE" (docs). So the row on its own doesn't mean the platform manages that grant.

Removing it. postgres can't revoke this grant. A plain revoke only warns, and naming the grantor fails:

revoke r07_p1_executor from postgres;
-- WARNING:  role "postgres" has not been granted membership in role "r07_p1_executor" by role "postgres"
revoke r07_p1_executor from postgres granted by supabase_admin;
-- ERROR:  permission denied to revoke privileges granted by role "supabase_admin"
-- DETAIL:  Only roles with privileges of role "supabase_admin" may revoke privileges granted by this role.

What the row allows. With only that row, postgres can't act as the executor (set role r07_p1_executor fails with permission denied to set role) and inherits nothing from it. It does keep ADMIN, which comes with having created the role.

Making the executor the owner. This worked:

-- adds a second membership row, granted by postgres; SET is enough for the owner change
grant r07_p1_executor to postgres with set true, inherit false;

-- the new owner also needs CREATE on the function's schema
grant create on schema public to r07_p1_executor;
alter function public.f() owner to r07_p1_executor;
revoke create on schema public from r07_p1_executor;

-- optional: drop the SET grant again; the function stays owned by the executor
revoke r07_p1_executor from postgres;

Without the schema grant, alter function ... owner to fails with permission denied for schema public even after the SET grant. ALTER FUNCTION requires both: "you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the function's schema."

Changing the function later. After the last revoke, postgres can no longer create or replace the function (must be owner of function f). To change it, repeat the two grants, set role r07_p1_executor, replace the function, then reset role and revoke both again.

Roles you create later. set createrole_self_grant = 'set'; before create role adds a SET grant like the one above automatically.

All of this is vanilla Postgres 17.2. I haven't run it on a hosted project, so try it in your dev project first.