Recommended secure migration-operator design for a Supabase Free project

[chandresh1307-web]

Hello Supabase community,

I’m building a marketplace application using Supabase on the Free plan. I manage my own organization and projects and want to build a secure Admin Panel without upgrading my plan or changing my existing Production and Staging environments during the design stage.

I need to apply a reviewed SQL security foundation to Staging. My requirements are:

• Do not use the postgres account as the routine migration executor.
• Do not use the service_role key as a PostgreSQL credential.
• Keep the Admin runtime separate from the migration operator.
• Use narrowly scoped database functions and deny direct table access to the runtime role.
• Verify role memberships and privileges before executing any migration.
• Keep Production untouched until Staging verification and explicit approval are complete.

I considered a two-stage design with a separately controlled provisioning authority and a restricted migration executor. However, PostgreSQL role-membership behavior and the privileges needed to create roles may make the separation more difficult than expected.

My questions:

1. What officially supported approach would you recommend for establishing a restricted migration identity on a Supabase Free project?
2. Is there a practical design that avoids a long-lived, broadly privileged migration login?
3. Which role attributes and membership checks should be performed before applying the migration?
4. Are there Supabase-specific limitations that would make this design unsuitable?

I’m looking for technical guidance only. I will not share project identifiers, credentials, connection strings, or secrets publicly.

[cekuu35]

Your requirements are all achievable on the Free plan — the constraint to design around is that the platform connection (pooler / SQL editor) hands you postgres, and on hosted Supabase postgres is a non-superuser with CREATEROLE. Here's a design that fits each of your four questions.

The core pattern: separate the connection from the execution role

You can't avoid postgres as the login on Free (that's the credential the pooler gives you), but you can avoid executing as postgres. Don't try — create a NOLOGIN owning role and have every migration script open with SET ROLE:

-- one-time setup (as postgres, which has CREATEROLE)
create role app_owner nologin;
grant app_owner to postgres with set true;   -- PG16+: membership with SET, no INHERIT

-- every migration thereafter opens with:
set role app_owner;   -- DDL below this line executes as app_owner, not postgres

All schema objects (tables, functions) are created owned by app_owner. When postgres needs to intervene, it does so explicitly via SET ROLE — the same discipline a sudo-style operator would use. That answers your Q1 and Q2 simultaneously: no long-lived privileged login exists, because the privileged role cannot log in at all, and the routine executor context is the narrowly-scoped role rather than the connection identity.

Runtime separation (your requirement 4) stays as you specified: authenticated (via PostgREST) gets zero direct table grants and only EXECUTE on narrowly-scoped SECURITY DEFINER functions owned by a second NOLOGIN role (e.g. rpc_owner), each with search_path = '' and revoke ... from public, anon. One non-obvious failure mode: the SECURITY DEFINER owner role needs its own explicit table grants (custom roles never inherited Supabase's defaults) — grant it exactly what the function bodies touch, or the functions fail at runtime with permission denied for table even though creation succeeded. (Details on that trap and the membership mechanics here: https://dev.to/cekuu35/you-gave-your-security-definer-function-a-dedicated-owner-role-it-broke-heres-why-1oj3)

Pre-migration verification (your Q3) — three checks worth automating as a gate script that refuses to run if any fail:

-- 1. execution context is the operator, not the platform login
select current_user;                 -- must be app_owner, not postgres
select current_setting('role', true);

-- 2. memberships look like the design intends (PG16+ columns)
select member, grantor, admin_option, set_option, inherit_option
from pg_auth_members
where roleid in ('app_owner'::regrole, 'rpc_owner'::regrole);

-- 3. runtime role holds no direct table grants (RLS is the only path)
select table_name, privilege_type
from information_schema.role_table_grants
where grantee = 'authenticated' and table_schema = 'public';
-- expect: zero rows

And one post-migration check that catches what migrations most often silently introduce — a permissive policy:

select tablename, policyname, roles, qual
from pg_policies
where qual = 'true' and roles::text in ('{anon}','{public}');
-- expect: zero rows, or rows you can explain

Supabase-specific limitations (your Q4), honestly:

1. supabase_admin re-syncs. Membership rows granted by the platform (grantor supabase_admin) can be re-touched on maintenance passes. Your grant ... with set true on roles you created is yours to keep, but verify with the pg_auth_members check above after platform maintenance, in CI, not from memory.
2. Default privileges work against you until Oct 30. New tables created today auto-grant to anon/authenticated at the platform level. Your migrations should treat that as hostile: after every create table, either revoke or apply RLS in the same transaction. (After Supabase's October 30 change, new tables in existing projects stop auto-granting — which makes explicit grants in migrations mandatory instead of optional. Same discipline, opposite default; worth baking in now so nothing depends on either default.)
3. Marketplace shape: if sellers see only their own listings, put the tenant predicate in RLS now (organization_id/seller_id column + policy), and make the admin panel authenticate as authenticated with a role check inside the SECURITY DEFINER functions — never a service-key path, or your auth.uid()-based audit trail goes NULL.
4. Free-plan caveat: the staging/prod separation you're doing is exactly right; keep the approval gate as a human step reading the verification query outputs — the checks above are cheap enough to paste into the approval PR description.

The one-sentence version: postgres is your transport, a NOLOGIN owner is your actor, RLS-plus-revoked-EXECUTE is your runtime contract, and a three-query gate script is your migration bouncer.

[chandresh1307-web]

@cekuu35

Thank you for the detailed explanation. The NOLOGIN owner plus SET ROLE pattern is helpful.

One important constraint remains: for our migration process, we need the database connection identity itself to be a separately restricted, dedicated PostgreSQL login. We cannot approve a routine migration workflow that authenticates as postgres and relies on switching roles, even if current_user becomes a NOLOGIN owner.

Can you suggest a supported approach on Supabase Free that satisfies this stricter requirement, or confirm whether Supabase’s hosted connection and privilege model makes it impossible?

If you believe a dedicated login is possible, please explain how its privileges and role memberships can be established and verified without giving it broad role-management powers or allowing it to assume the Admin runtime role.

We will not run SQL or change our Staging or Production projects until this boundary is verified. Thank you.

[chandresh1307-web]

Can someone please help, i have deadline to complete this project, if not completed before deadline, there could be financial impact.

[BryceEWatson]

Yes, a dedicated restricted login works on Free, and it doesn’t need createrole or membership in your runtime role. postgres is used once, by hand, to create the roles and set the password. After that, routine migrations log in as app_migrator and never as postgres. Here’s how I’d set it up and verify it.

1. One-time setup, as postgres. An owner role that can’t log in, and a migration login that holds no privileges of its own:

create role app_owner nologin;
create role app_migrator login noinherit;
grant app_owner to app_migrator;

create schema app;
grant usage, create on schema app to app_owner;

Then set its password from psql with \password app_migrator. Supabase logs DDL (log_statement = 'ddl'), so a password written inside create role or alter role lands in your Postgres logs in plain text. \password hashes it first, so only the hash reaches the server.

Every migration file starts with set role app_owner;, so the objects it creates are owned by app_owner. This isn’t the pattern you ruled out: the connection identity is app_migrator, and the only role it can switch to is app_owner. Because app_migrator is noinherit, it gets nothing from app_owner until it does that.

2. Locking it between runs (optional). If you also want no standing login, postgres can run alter role app_migrator nologin; after a release and login before the next one. That’s an occasional admin action, not part of the migration. nologin only blocks new connections, so to end leftover sessions: select pg_terminate_backend(pid) from pg_stat_activity where usename = 'app_migrator';

3. Checks before each run.

select rolname, rolsuper, rolcreaterole, rolcreatedb, rolbypassrls, rolreplication, rolinherit, rolcanlogin
from pg_roles where rolname in ('app_owner', 'app_migrator');

select r.rolname as granted_role, m.rolname as member, a.admin_option
from pg_auth_members a
join pg_roles r on r.oid = a.roleid
join pg_roles m on m.oid = a.member
where m.rolname in ('app_owner', 'app_migrator');

select r, pg_has_role('app_migrator', r, 'member')
from unnest(array['anon', 'authenticated', 'service_role', 'postgres']) r;

You want superuser, createrole, createdb, bypassrls and replication false on both, app_owner as app_migrator’s only membership without admin option, and false for every row of the last query. That last one is what shows app_migrator can’t assume your runtime roles or postgres. On Postgres 16 and later, also select a.inherit_option, a.set_option: you want inherit false and set true. Run the role-attribute check on any custom runtime role too.

4. Supabase-specific things.

Supabase’s automatic grants to anon, authenticated and service_role are default privileges for tables created in the public schema. Tables in app get none of them, so every grant has to be explicit, including execute on your admin functions.
Postgres grants execute on new functions to PUBLIC by default. Your first migration, after set role app_owner, can run alter default privileges revoke execute on functions from public; so later functions start closed.
For the admin runtime, make each function security definer with set search_path = '' and schema-qualified names, owned by a separate nologin role that holds only the table grants those functions need, so a bug in one can’t run DDL. Grant execute only to the role that calls it. If that’s authenticated (the Data API with a user session), every signed-in user shares it, so each function also checks that the caller is an admin, for example against an admins table keyed on auth.uid().
auth, storage and the other Supabase schemas are owned by platform roles, and app_owner has no create rights there.
If the admin panel calls these functions through the Data API, the app schema has to be in the exposed schemas list, or you keep thin wrappers in a schema that’s already exposed.
On Free, the direct connection is IPv6 only. Through the shared pooler in session mode (port 5432; set role doesn’t hold across transaction mode), a custom role connects with the username app_migrator. (Connecting to Postgres).
Run migration files with psql -1 -v ON_ERROR_STOP=1 -f as app_migrator (-1 runs each file as one transaction, so a failure leaves nothing half-applied). supabase db push --db-url won’t work as this role as it stands, because it records each migration in supabase_migrations.schema_migrations, which app_migrator can’t write.
Once you’re satisfied with the checks, try it on Staging first: create the roles there, run one small migration as app_migrator, then run the checks again.