Supported Migration and Function Ownership Model for Constrained PostgreSQL Roles

[warikandeiiyo98]

Supported Migration and Function Ownership Model for Constrained PostgreSQL Roles

Hello Supabase team and community,

I'm seeking guidance on an officially supported, least-privilege migration and function ownership model for hosted Supabase on the Free plan.

Our application uses PostgreSQL Row Level Security (RLS), dedicated runtime roles, and narrowly scoped database functions. Before deploying our security foundation, we need to establish a migration process that works within Supabase's hosted PostgreSQL role restrictions.

Our intended architecture separates:

• A restricted PostgreSQL LOGIN role for migration execution.
• A dedicated NOLOGIN role for object and function ownership.
• Runtime roles that cannot acquire migration or administrative privileges.
• Platform-managed roles and schemas, including Supabase Auth.

The unresolved issue is how to establish and maintain these ownership relationships without granting excessive privileges to the routine migration identity.

Questions

1. Supported migration identity

Can hosted Supabase officially support a dedicated, restricted PostgreSQL LOGIN role as the routine migration executor, rather than authenticating as postgres?

Are there differences between using the Supabase CLI, Dashboard SQL Editor, direct connections, and the session pooler?

2. Role creation and membership

What is the supported approach for establishing and maintaining a dedicated NOLOGIN object-owner role when the migration executor is not a superuser?

In particular, which combinations of CREATEROLE, ADMIN OPTION, SET OPTION, and INHERIT OPTION are required or supported?

3. Function ownership

How should migrations create, update, and transfer ownership of SECURITY DEFINER functions while keeping the routine migration executor separate from the runtime function owner?

Can this be achieved without granting the migration executor membership in a privileged runtime or administrative role?

4. Supabase Auth integration

For functions that use auth.uid() or auth.jwt(), what minimum schema and function privileges must be granted to custom owner and runtime roles?

Are there hosted-platform restrictions on maintaining these grants across migrations or platform updates?

5. Official alternative

If the proposed separation cannot be supported reliably on hosted Supabase Free, what migration and ownership model does Supabase recommend?

We are particularly interested in a model that avoids permanent, broadly privileged migration credentials and preserves least privilege throughout the function lifecycle.

Current status

We have completed local PostgreSQL and application-level tests, but these do not establish that the proposed ownership model is supported in a hosted Supabase environment.

We have not applied the proposed security migrations to our hosted database.

We are seeking architectural guidance before proceeding.

Related community discussions:

• https://github.com/orgs/supabase/discussions/48783
• https://github.com/orgs/supabase/discussions/51492

We would appreciate references to official documentation or a supported implementation pattern.

Thank you for your guidance.

[SudoKMaar]

Most of this is plain Postgres 16+ behaviour, so I checked it against the Postgres 17 docs and against the SQL that builds a hosted Supabase database (supabase/postgres), and ran the role setup on a scratch Postgres. Where Supabase has an official statement I link it; where it does not, I say so.

1. Migration identity. A dedicated LOGIN role works. The roles guide recommends "a new user for every service" instead of handing out the postgres password (https://supabase.com/docs/guides/database/postgres/roles), and the connection guide documents custom roles on the shared pooler: the username becomes [ROLE].[PROJECT-REF] (https://supabase.com/docs/guides/database/connecting-to-postgres). The four paths differ like this:

• Dashboard SQL editor: runs as postgres (roles guide, dashboard_user section: "Queries you run in the Dashboard execute as postgres"). Not a restricted path.
• Supabase CLI: supabase db push --remote=<postgres url> (--db-url on older CLIs, now deprecated) connects as whatever role is in the URL. The CLI probes the history table first and only runs CREATE SCHEMA IF NOT EXISTS supabase_migrations when the probe fails (https://github.com/supabase/cli/blob/develop/apps/cli/src/command-internal/migration-history.ts, createMigrationTable), so do the first push as postgres, then give the migrator USAGE on supabase_migrations and SELECT, INSERT, DELETE on schema_migrations. That matters because Postgres checks CREATE on the database before it looks at IF NOT EXISTS.
• Direct connection or session-mode pooler (port 5432): fine for migrations.
• Transaction-mode pooler (6543): not for migrations. SET ROLE is session state and is dropped between transactions (connection guide, "Transaction mode limitations").

2. Roles. On hosted projects postgres is NOSUPERUSER CREATEDB CREATEROLE BYPASSRLS and owns the postgres database (https://github.com/supabase/postgres/blob/develop/migrations/db/migrations/10000000000000_demote-postgres.sql, alter database postgres owner to postgres in migrations/db/migrate.sh). Two consequences on Postgres 16+:

• A role postgres creates is granted back to it WITH ADMIN TRUE, SET FALSE, INHERIT FALSE (https://www.postgresql.org/docs/17/role-attributes.html). So postgres can alter and drop app_owner, but create schema app authorization app_owner fails with must be able to SET ROLE "app_owner" until you run grant app_owner to postgres with set true; (it has ADMIN, so it can).
• CREATE SCHEMA in a migration needs CREATE on the database for the role that runs it. postgres owns the database, so grant create on database postgres to app_owner; works; Supabase documents exactly this (https://supabase.com/kb/troubleshooting/resolving-permission-denied-for-database-postgres-during-migrations).

-- once, as postgres
create role app_owner nologin;
create role app_migrator login noinherit;
create role app_runtime nologin noinherit;
grant app_owner to postgres with set true;
grant app_owner to app_migrator with set true, inherit false;   -- its only membership
grant create on database postgres to app_owner;
create schema app authorization app_owner;

The migrator needs no CREATEROLE and no ADMIN OPTION. One warning from #48783: never drop the role that holds ADMIN on your custom roles before granting it to a surviving role; postgres cannot repair that afterwards.

3. Functions. Two Postgres rules decide this. CREATE OR REPLACE requires that you "own the function (this includes being a member of the owning role)" (https://www.postgresql.org/docs/17/sql-createfunction.html), and ALTER FUNCTION ... OWNER TO requires that you "be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the function's schema" (https://www.postgresql.org/docs/17/sql-alterfunction.html). So membership in app_owner is the minimum Postgres will accept for the migrator, and it is all that is needed: start every migration with set role app_owner; and CREATE/CREATE OR REPLACE produce functions owned by app_owner, including SECURITY DEFINER ones, which then execute as app_owner. Nothing in this requires membership in authenticated, service_role or postgres.

What I got when I ran this: with inherit false, create or replace as app_migrator without set role fails with must be owner of function; after set role app_owner it succeeds; alter function f() owner to app_owner from the migrator succeeds; set role authenticated, set role postgres and create role as the migrator are all denied.

4. Auth. auth.jwt() and auth.uid() are SQL functions that read the request GUCs: auth.jwt() reads current_setting('request.jwt.claims') (https://github.com/supabase/auth/blob/master/migrations/20220531120530_add_auth_jwt_function.up.sql), auth.uid() reads request.jwt.claim.sub (https://github.com/supabase/postgres/blob/develop/migrations/db/init-scripts/00000000000001-auth-schema.sql). EXECUTE on them is PUBLIC by default; the only privilege a custom role is missing is USAGE on schema auth. That one you cannot grant: the schema is owned by supabase_admin, postgres holds USAGE without grant option (GRANT USAGE ON SCHEMA auth TO postgres in supabase/postgres 20211115181400_update-auth-permissions.sql, and CREATE was revoked in April 2025: https://supabase.com/changelog/34270-restricting-access-on-auth-storage-and-realtime-schemas-on-april-21-2025). grant usage on schema auth to app_owner run as postgres returns success and changes nothing, and a language sql function owned by app_owner that mentions auth.uid() fails at creation with permission denied for schema auth. Same finding in #51357, where GaryAustin1 adds that grants inside auth are not something to count on across Auth migrations.

Two options that need no auth privilege at all:

-- a) read the claim directly (pg_catalog only)
(nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')::uuid

-- b) one wrapper owned by postgres, EXECUTE granted to your roles
create function app.current_uid() returns uuid
language sql stable security definer set search_path = ''
as $$ select auth.uid() $$;
revoke execute on function app.current_uid() from public;
grant execute on function app.current_uid() to app_owner, app_runtime;

5. Is any of this "officially supported"? The documented pieces are: one user per service, custom roles through the pooler, GRANT CREATE ON DATABASE for migration roles, and the April 2025 restrictions on auth. The ownership model itself is standard Postgres; Supabase has no page that blesses or forbids it. If you want the shortest path that stays inside documented behaviour, run migrations as postgres with set role app_owner; as the first line of every file (the pattern in #51492) and keep the dedicated login for later.

Checked on Postgres 18 (PGlite) against the 17 docs; the membership rules are the same from 16 up. Projects still on Postgres 15 behave differently (no SET/INHERIT grant options), so confirm your version first.