We are designing a least-privilege PostgreSQL runtime architecture for a Next.js application hosted on Vercel.

[hqf506]

Hello Supabase Support,

We are designing a least-privilege PostgreSQL runtime architecture for a Next.js application hosted on Vercel.

Our planned architecture is:

Dedicated application LOGIN
→ Transaction Pooler (port 6543)
→ SET-only membership
→ Restricted NOLOGIN runtime role
→ Single approved SECURITY DEFINER function

Before implementation, we would like to confirm the following:

1. In Transaction Pooler mode, does an explicit BEGIN...COMMIT remain pinned to the same PostgreSQL backend for the whole transaction?

2. Are SET ROLE and SET LOCAL ROLE supported inside that explicit transaction?

3. Is role/session state fully cleared after:

• COMMIT
• ROLLBACK
• statement cancellation
• client disconnect
  before the backend is reused?

4. Are transaction-level advisory locks fully supported in Transaction Pooler mode?

5. Should prepared statements be disabled when using node-postgres (pg) with Transaction Pooler?

6. Are custom PostgreSQL LOGIN roles with SET-only membership in a custom NOLOGIN runtime role fully supported in hosted Supabase?

We are requesting architectural guidance only.

No credentials, connection strings, or customer data are included.

Thank you.

[cekuu35]

Not Supabase staff, but four of these are already settled in this repo and one of them has a live report from two days ago that your design walks straight into. Taking them in order.

1 and 2 and 3 — transaction pinning and SET LOCAL. Answered in #47946, and the answer there is worth reading in full because it makes a point that changes how you should think about question 3.

Short version: in transaction mode the transaction is the unit of multiplexing, so a backend is held from BEGIN to COMMIT/ROLLBACK and is not interleaved with another client. But you do not have to trust the pooler for this. SET LOCAL is reverted by the Postgres server at commit or rollback (docs: "the command takes effect for only the current transaction"), so the state is gone before the pooler could possibly reassign the connection, whether or not a DISCARD ALL runs in between.

That distinction decides your question 2. SET LOCAL ROLE is safe. Plain SET ROLE is the documented hazard of transaction mode — session state can outlive the assignment. For an architecture whose entire security boundary is "which role am I running as", that difference is the whole design, so make it structurally impossible to issue the session-scoped form rather than a convention you rely on reviewers to catch.

On statement cancellation specifically: cancelling a statement inside an explicit transaction puts it in the aborted state, and you can only ROLLBACK out — which reverts the SET LOCAL on the same path as any other rollback. Client disconnect terminates the backend session. Neither is a separate leak path from the two you already have.

4 — transaction-level advisory locks. Fine, and the qualifier in your wording is doing the work. pg_advisory_xact_lock is released at transaction end by the server, so it lives and dies inside the pinned window. Session-level pg_advisory_lock is the one that must never appear on port 6543: it is held until explicitly released or the session ends, so it outlives your transaction and leaks onto whichever client gets that backend next — a lock you cannot see and did not take. Same rule as SET versus SET LOCAL, same reason.

5 — prepared statements with node-postgres. Yes, disable. This is explicit in the docs: Disabling prepared statements — "although the direct connections and Supavisor in session mode support prepared statements, Supavisor in transaction mode does not."

For pg specifically the instruction is not a connection flag, it is a code-level one: omit the name field from your query objects, since a named query is what makes node-postgres use the extended protocol's prepared statement path.

const query = {
  name: 'fetch-user',   // <-- this is the thing to remove
  text: 'select * from app.thing where id = $1',
  values: [id],
}

6 — custom LOGIN role with SET-only membership in a NOLOGIN runtime role. Supported, and this is the one I would slow down on, because #48783 was opened two days ago by someone whose architecture is shaped almost exactly like yours and who is now locked out of their own roles.

Their migration created seven custom NOLOGIN group roles using a temporary role that held CREATEROLE, then dropped that role at the end of the migration. On hosted Supabase the postgres role is not a superuser, so it did not inherit ADMIN OPTION on those roles, and the grantor no longer exists. The result:

ERROR: 42501: permission denied to grant role "compass_migrator"
DETAIL: Only roles with the ADMIN option on role "compass_migrator" may grant this role.

They can neither manage memberships nor grants on roles their own application depends on, from the SQL Editor or anywhere else.

Your plan has the same shape — a restricted NOLOGIN runtime role that something has to grant to the application login. So before you implement:

• Create the runtime role as postgres, or explicitly GRANT <runtime_role> TO postgres WITH ADMIN OPTION in the same migration, while the creating role still exists.
• Do not let the role that created it be dropped before that grant is in place. That single ordering mistake is not recoverable by you on hosted Supabase.
• Verify afterwards, in the same migration, rather than assuming:

select r.rolname as role, m.rolname as member, am.admin_option
from pg_auth_members am
join pg_roles r on r.oid = am.roleid
join pg_roles m on m.oid = am.member
where r.rolname = '<runtime_role>';

One version note on the SET-only membership itself: GRANT <runtime_role> TO <app_login> WITH INHERIT FALSE, SET TRUE — the syntax that expresses "may SET ROLE to it but does not inherit its privileges" — is PostgreSQL 16+. Check your project's server version before writing the migration, because on 15 the closest you get is NOINHERIT on the login role itself, which is a property of the role rather than of that one membership and therefore applies to every role it is a member of.

The fail-closed instinct in the rest of the design is the right one. Just make sure the role-management path is fail-closed too, which is precisely what #48783 was not.