PostgreSQL 42501 — Cannot alter table owned by custom NOLOGIN role in Supabase

[fouadwchehab-rgb]

Hello Supabase Community,

We need guidance on a PostgreSQL table ownership issue in an isolated Supabase development project.

Problem:

We are trying to execute a database migration that adds three columns to an existing table and updates a restricted PostgreSQL function.

The migration fails with:

ERROR 42501: must be owner of table applications

Current configuration:

• Table: onefix_recruitment.applications
• Table owner: onefix_recruitment_api_owner
• Owner role: NOLOGIN
• SQL Editor session: postgres
• pg_has_role(current_user, 'onefix_recruitment_api_owner', 'SET') returns false
• Existing tables have RLS and FORCE RLS enabled.

What we need:

A supported, secure method to execute this migration without:

• Changing table ownership
• Disabling RLS
• Granting elevated permissions to the application backend
• Exposing administrative credentials

Is there a recommended Supabase procedure for executing owner-authorized DDL when the table is owned by a custom NOLOGIN role that the SQL Editor's postgres session cannot assume?

We would appreciate clear instructions on the safest supported approach.

Thank you.

[devcaiada]

In Supabase, postgres isn't a true SUPERUSER (supabase_admin is) — it relies on CREATEROLE.

Starting in Postgres 16, when a CREATEROLE user creates a new role, it gets ADMIN OPTION by default, but SET and INHERIT default to false (which is why pg_has_role(current_user, 'onefix_recruitment_api_owner', 'SET') is returning false for you).

Since postgres still has ADMIN rights over custom roles, the cleanest way to run your migration without touching ownership or RLS is to grant SET temporarily inside the transaction, switch to the NOLOGIN owner via SET LOCAL ROLE, run the DDL, and revoke it before committing:

BEGIN;

GRANT onefix_recruitment_api_owner TO postgres WITH SET TRUE, INHERIT FALSE;
SET LOCAL ROLE onefix_recruitment_api_owner;

-- Run your DDL as the actual table/function owner
ALTER TABLE onefix_recruitment.applications
  ADD COLUMN col_1 text,
  ADD COLUMN col_2 text,
  ADD COLUMN col_3 text;

CREATE OR REPLACE FUNCTION onefix_recruitment.your_function()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  -- updated logic
END;
$$;

RESET ROLE;
REVOKE SET OPTION FOR onefix_recruitment_api_owner FROM postgres;

COMMIT;

Because the GRANT and REVOKE SET OPTION happen inside the same transaction block, no permanent privilege changes are left behind after COMMIT, table/function ownership stays with onefix_recruitment_api_owner, and FORCE RLS remains untouched.

(Quick check: if for some reason GRANT ... TO postgres also throws a permission error, run SELECT pg_has_role('postgres', 'onefix_recruitment_api_owner', 'ADMIN'); — as long as that returns true, the transaction block above will work out of the box.)

[cekuu35]

devcaiada's answer is the right core: in Postgres 16, a CREATEROLE user gets ADMIN OPTION on roles it creates, but SET defaults to false — so postgres can own-manage the role but can't become it until you grant SET. That's exactly why pg_has_role(..., 'SET') returns false.

What I'd add is the self-contained migration-file version — everything in one transaction so it works unchanged in CI (Supabase CLI db push / migrate), not just the SQL Editor:

BEGIN;

GRANT onefix_recruitment_api_owner TO postgres WITH SET TRUE, INHERIT FALSE;
SET LOCAL ROLE onefix_recruitment_api_owner;

ALTER TABLE onefix_recruitment.applications
  ADD COLUMN col_1 text,
  ADD COLUMN col_2 text,
  ADD COLUMN col_3 text;

CREATE OR REPLACE FUNCTION onefix_recruitment.your_function()
LANGUAGE plpgsql
SET search_path = ''
AS $$
BEGIN
  -- updated logic
END;
$$;

RESET ROLE;
REVOKE onefix_recruitment_api_owner FROM postgres;

COMMIT;

Three gotchas that bite people with this owner-role pattern:

1. SET LOCAL ROLE must be inside the transaction — it resets at COMMIT/ROLLBACK automatically, which is what you want for CI. Plain SET ROLE leaks to the rest of the session.
2. CREATE OR REPLACE on the SECURITY DEFINER function: the session role must own the function too, not just the table — check both select tableowner from pg_tables where tablename='applications' and select proowner::regrole from pg_proc where proname='your_function' before replacing, or you get the same 42501 one step later.
3. Always pin search_path when (re)creating SECURITY DEFINER functions — an unpinned definer function is a search_path hijack risk, and ownership migrations are exactly when this gets forgotten.

One check before the grant: make sure postgres actually has ADMIN on the owner role (select pg_has_role('postgres', 'onefix_recruitment_api_owner', 'MEMBER')) — if a different role created it, that creator or supabase_admin needs to run the grant.

I wrote up this whole failure class — dedicated owner roles that quietly break migrations and definer functions — in "You Gave Your SECURITY DEFINER Function a Dedicated Owner Role. It Broke.", and there's a pre-flight checklist for exactly these 42501/ownership cases in the Supabase Migration Rescue Kit (status-check query, repair playbook, pre-deploy checklist).

[IlyaKhalafi]

Temporary SET LOCAL ROLE is a workable direction, but the permission checks above need correction. On PostgreSQL 16, use the documented admin-option test.

SELECT
  pg_has_role(current_user, 'onefix_recruitment_api_owner',
              'MEMBER WITH ADMIN OPTION') AS can_administer,
  pg_has_role(current_user, 'onefix_recruitment_api_owner',
              'SET') AS can_set_role;

Plain MEMBER does not test administration rights. 'ADMIN' alone is an invalid privilege argument. If can_administer is false, don't assume CREATEROLE authorizes a self-grant. An administrator holding the required role authority needs to arrange that access.

Before changing membership, record the relevant pg_auth_members rows, including grantor and all three option flags. Restore that exact starting state after the DDL. Committing a transaction does not undo its grants, and removing a whole membership is different from revoking only its SET option.

Also use the actual function definition, including its return type and security attributes. The second example omits RETURNS, and neither preserves SECURITY DEFINER if the existing function has it. CREATE OR REPLACE preserves ownership and permissions, but other properties take their specified or default values.

I checked these distinctions on disposable PostgreSQL 16.14. An ordinary member failed the admin-option check, 'ADMIN' errored, and replacing a definer function without that attribute changed it to invoker. This was not a hosted Supabase test, so it doesn't establish which administrator can grant access in your project.