Hosted PG15→17 upgrade: how should existing custom SECURITY DEFINER owner roles regain administration-only membership?

[echoresonate-lab]

We are using Hosted Supabase and have several custom NOLOGIN roles that own SECURITY DEFINER functions.

On PostgreSQL 15, our migration pattern was:

1. create a dedicated owner role (NOLOGIN, NOINHERIT, NOBYPASSRLS, etc.)
2. temporarily GRANT owner_role TO postgres
3. create / replace the function as that owner
4. REVOKE owner_role FROM postgres
5. steady state: postgres has zero membership in the owner role

We are now preparing for PostgreSQL 17.

On fresh PG17, PostgreSQL automatically gives the non-superuser role creator an administration-only membership:

• ADMIN TRUE
• INHERIT FALSE
• SET FALSE

That shape works for us.

The upgrade-existing case is the blocker.

Our local tests show:

• a PG15 owner role with zero postgres membership remains zero-membership when carried into PG17
• ordinary non-superuser postgres then cannot grant itself that owner role because it lacks ADMIN OPTION
• pre-granting the owner role to postgres on PG15 is not acceptable, because PG15 membership gives runtime inheritance / SET ROLE capability
• when that PG15 membership is restored on PG17, it becomes effectively ADMIN TRUE / INHERIT TRUE / SET TRUE, which is not an acceptable steady state
• a platform-level authority can normalize the row to:
  • ADMIN TRUE
  • INHERIT FALSE
  • SET FALSE
    but project postgres cannot perform that normalization itself

Questions:

1. During a Hosted PG15→17 major-version upgrade, does Supabase automatically normalize pre-existing custom owner-role membership to ADMIN TRUE / INHERIT FALSE / SET FALSE for project postgres?
2. If not, is there any supported self-service pre-upgrade or post-upgrade procedure?
3. If not self-service, can Supabase Support/platform staff normalize these custom role memberships on request?
4. Is there another supported Hosted pattern for safely updating SECURITY DEFINER functions owned by custom NOLOGIN roles after the upgrade?
5. Is pre-granting these owner roles to postgres before upgrade recommended or discouraged?

Required steady-state properties:

• custom owners stay NOLOGIN / NOINHERIT / NOBYPASSRLS / NOSUPERUSER / NOCREATEDB / NOCREATEROLE
• anon, authenticated, service_role are not members
• no persistent SET TRUE
• no persistent INHERIT TRUE
• existing SECURITY DEFINER, function ownership, search_path, ACLs and RLS behavior remain unchanged

Related discussion:
https://github.com/orgs/supabase/discussions/48783

Support ticket already submitted:
SU-473628

If there is any Hosted-specific documentation covering this transition, a link would be very helpful.

[somuai]

Here are the direct answers to your five questions regarding PostgreSQL 15 to 17 role grant transitions and SECURITY DEFINER ownership on hosted Supabase:

---

1. Does Supabase Automatically Normalize Custom Roles During Upgrade?

No.
In Supabase hosted pg_upgrade workflows, automated normalization applies strictly to platform-managed system roles (authenticator, supabase_admin, anon, authenticated, service_role, dashboard_user). Custom tenant roles restored from pg_dumpall --roles-only retain their exact pre-existing pg_auth_members state. If project postgres had zero membership in your owner_role in PG15, it lands in PG17 with zero membership and zero ADMIN OPTION.

---

2. Supported Self-Service Procedure (Pre- and Post-Upgrade)

You can achieve the desired steady-state (ADMIN TRUE / INHERIT FALSE / SET FALSE) completely self-service by using PostgreSQL 16/17 granular grant management:

Step A: Before Upgrade (on PG15)

Grant the owner role to postgres with admin option:

GRANT owner_role TO postgres WITH ADMIN OPTION;

(In PG15, this creates the membership with admin_option = true).

Step B: During PG17 Upgrade

PostgreSQL 17 pg_upgrade maps this legacy grant into:

• admin_option = true
• inherit_option = true
• set_option = true

Step C: Immediately Post-Upgrade (on PG17)

Because postgres possesses ADMIN OPTION (admin_option = true), it has the native authority under PG17 to alter the membership attributes of its own grant without needing superuser privileges:

GRANT owner_role TO postgres WITH INHERIT FALSE, SET FALSE;

This strips both INHERIT and SET while leaving ADMIN OPTION intact.

Verify via:

SELECT 
    r.rolname AS role_name,
    m.rolname AS member_name,
    a.admin_option,
    a.inherit_option,
    a.set_option
FROM pg_auth_members a
JOIN pg_roles r ON a.roleid = r.oid
JOIN pg_roles m ON a.member = m.oid
WHERE r.rolname = 'owner_role' AND m.rolname = 'postgres';

Result: admin_option = true, inherit_option = false, set_option = false.

---

3. Support-Assisted Normalization

If you prefer not to grant WITH ADMIN OPTION on PG15, Supabase platform engineers (acting via supabase_admin, which holds superuser privileges) can execute the following on your database reference via ticket SU-473628:

GRANT owner_role TO postgres WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;

This directly injects the required row into pg_auth_members post-upgrade.

---

4. Updating SECURITY DEFINER Functions Post-Upgrade

Once in the ADMIN TRUE / INHERIT FALSE / SET FALSE state, project postgres cannot directly execute SET ROLE owner_role. You have two supported patterns to deploy updates:

Pattern A: Scoped Transactional SET Permission (Recommended for CI/CD)

Because postgres holds ADMIN TRUE, it can grant itself SET TRUE inside a transaction and revert immediately:

BEGIN;
GRANT owner_role TO postgres WITH SET TRUE;
SET LOCAL ROLE owner_role;

CREATE OR REPLACE FUNCTION public.my_secure_function()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
    -- function body
END;
$$;

RESET ROLE;
GRANT owner_role TO postgres WITH SET FALSE;
COMMIT;

Pattern B: Direct Ownership Reassignment

Under PostgreSQL rules, a role can transfer ownership of an object to a target role if the current user is a member of that target role (even if SET FALSE and INHERIT FALSE!):

CREATE OR REPLACE FUNCTION public.my_secure_function()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
    -- function body
END;
$$;

ALTER FUNCTION public.my_secure_function() OWNER TO owner_role;

---

5. Is Pre-Granting on PG15 Recommended?

Yes, pre-granting WITH ADMIN OPTION on PG15 is recommended.
Pre-granting WITH ADMIN OPTION on PG15 prevents post-upgrade administrative lockout. Running GRANT owner_role TO postgres WITH INHERIT FALSE, SET FALSE; as your very first post-upgrade migration immediately restores your zero-inheritance, zero-set security boundary without requiring manual platform intervention.

[Concepto505]

Your read of the mechanics looks right to me, so I will not repeat it. But there is a self-service path that I think gets you the exact steady state you want without needing platform staff, and it hinges on two details that are easy to miss.

1. GRANT modifies an existing membership in place. It does not have to be dropped and recreated.

In PG16+ the role-membership options are independently settable, and re-issuing the grant updates them rather than erroring or duplicating (GRANT docs, Role Membership). So a membership that arrives from PG15 as the effectively-INHERIT TRUE, SET TRUE shape can be normalised with one statement:

grant owner_role to postgres with admin true, inherit false, set false;

That is precisely the ADMIN TRUE / INHERIT FALSE / SET FALSE row you described as requiring platform-level authority. The catch is that whoever issues it needs ADMIN on owner_role — which is the whole problem you identified, and leads to the second point.

2. WITH ADMIN OPTION exists on PG15, and ADMIN is what survives the upgrade usefully.

The thing you cannot manufacture after the fact is admin rights on a role you no longer administer. But you can grant them before the upgrade, while you still can:

-- on PG15, in the maintenance window immediately before the upgrade
grant owner_role to postgres with admin option;

On PG15 that unavoidably also carries runtime inheritance and SET ROLE, which you correctly say is not an acceptable steady state — but it only has to survive the upgrade window, not the year. Then, first thing on PG17:

grant owner_role to postgres with admin true, inherit false, set false;

and you are back to the shape you want, self-service, with no ticket. The ordering matters: ADMIN is the capability you must carry across the version boundary, because it is the only one you cannot re-acquire from below afterwards.

3. On your questions 1–3, I would not trust anyone who answers confidently.

Whether the hosted upgrade path normalises pre-existing custom role memberships is a property of Supabase's migration tooling, not of PostgreSQL, and it is not something you can infer from the docs or from a local pg_upgrade test — your local test tells you what community PostgreSQL does, which is a lower bound, not the answer. That one genuinely needs Supabase to state it. I would open a support ticket for questions 1–3 and treat the pre-upgrade ADMIN grant above as the thing that makes the answer not matter.

One aside on the functions themselves, since the point of the owner role is to keep the definer functions from running as something over-privileged: whatever role ends up owning them, make sure each one carries set search_path = '' and had execute revoked from PUBLIC in the same migration that created it. A new function is executable by PUBLIC by default, so the careful ownership work can be undone by the default ACL on the function itself — which is a much more common way to lose this than the role-membership question you are asking about.

[echoresonate-lab]

Thanks — I tested this with an actual disposable PG15 → PG17 pg_upgrade, and the grantor detail appears to be decisive.

On PG15, the pre-grant had grantor = postgres and admin_option = true.

After the PG17 upgrade, the membership row was recorded with the platform administrator as grantor and had ADMIN TRUE / INHERIT TRUE / SET TRUE.

Running, as ordinary non-superuser postgres:

GRANT owner_role TO postgres WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;

did not modify that platform-granted row. It created a separate postgres-granted membership row, while the original platform-granted INHERIT TRUE / SET TRUE row remained effective.

This also matches PostgreSQL 17’s catalog structure, where role membership identity includes the grantor (roleid, member, grantor).

So the self-service normalization still appears blocked once the upgraded membership is platform-granted.

I also confirmed that ALTER FUNCTION ... OWNER TO owner_role fails with must be able to SET ROLE when the steady-state membership has SET FALSE.

If someone from the Supabase team can confirm how Hosted upgrades handle or normalize these custom-role grants, that would resolve the remaining question.