Prisma 7.9.1: Can migrate deploy/diff initialize PostgreSQL SET ROLE on every engine connection?

[celine05971-Rach]

Environment

• Prisma CLI: 7.9.1
• Schema-engine revision: e922089b7d7502aff4249d5da3420f6fa55fc6ad
• Database: PostgreSQL 17.6

Context

We are designing a restricted PostgreSQL migration workflow with three separate roles:

• Migrator: LOGIN role, with explicit permission to SET ROLE to the database owner.
• Owner: NOLOGIN role, with INHERIT FALSE and SET TRUE on the migrator's membership.
• Runner: Separate restricted LOGIN role for integration tests.

We need Prisma migrations to authenticate as the migrator while executing authorized DDL under the effective owner identity.

Questions

1. Does Prisma's native schema engine support PostgreSQL connection-startup options that establish an effective role before any SQL executes?
2. Does this apply to every physical connection used by prisma migrate deploy and prisma migrate diff, including reconnects?
3. Does it also cover migration ledger operations?
4. What exact documented configuration or connection-string syntax is supported?
5. If this is unsupported, what is Prisma's recommended approach for migrations using a LOGIN migrator and separate NOLOGIN owner?

We inspected the locally installed CLI and confirmed that the datasource URL is passed to a separate schema engine. Although node-postgres supports startup options, we cannot establish that the native Prisma migration engine behaves equivalently.

Current safety status: Migration execution is disabled. We are seeking confirmation before implementing or enabling this mechanism.

Any documentation or connector-source references would be appreciated.

[g4lb]

Yes. Add options to the URL:

postgresql://migrator:PASSWORD@HOST:5432/DB?options=-c%20role%3Downer

migrate deploy and migrate diff pass, and _prisma_migrations belongs to owner.

[celine05971-Rach]

Thank you! This is very helpful.

We are using Prisma CLI 7.9.1 with PostgreSQL 17.6 and a restricted LOGIN migrator that has SET TRUE, INHERIT FALSE membership in a separate NOLOGIN owner role.

Could you clarify a few points?

1. Have you tested this URL option specifically with Prisma 7.9.1?
2. Does the startup role apply to every physical connection opened by the Prisma schema engine, including reconnects?
3. Does it also cover migration ledger operations such as _prisma_migrations reads and writes?
4. Is this behavior documented or verified in Prisma's PostgreSQL connector source?
5. If role initialization fails, does the migration command terminate before executing any SQL?

We're keeping live migrations disabled until we can establish these guarantees.

Thanks again for your guidance!

[g4lb]

1. Yes.
2. Yes: to_config sets options for each connection. I did not force a reconnect.
3. Yes.
4. Source only. I did not find it in the docs.
5. Yes: FATAL: role "x" does not exist, exit code 1, no SQL.

[sadamulhaq]

The URL from @g4lb works. I tested it and checked the engine source to cover your other questions.

Setup: Prisma CLI 7.9.1 (schema engine e922089, the revision you listed) and PostgreSQL 17.11, on Windows. migrator is LOGIN NOINHERIT, owner_role is NOLOGIN, and the membership is GRANT owner_role TO migrator WITH INHERIT FALSE, SET TRUE. owner_role owns the database and schema public, and CREATE on public is revoked from PUBLIC.

Connection	Result
No options	migrate deploy fails with permission denied for schema public while creating _prisma_migrations
?schema=public&options=-c%20role%3Downer_role	migrate deploy succeeds, and a second run reports no pending migrations. Item, Whoami and _prisma_migrations are all owned by owner_role. A migration that recorded its identity stored current_user = owner_role, session_user = migrator. migrate diff --from-config-datasource --to-schema prisma/schema.prisma --script also runs against that database.
No options, plus ALTER ROLE migrator IN DATABASE <db> SET role = owner_role	Same result as above

Your questions:

1. Startup options: yes. The engine parses options from the URL (url.rs) and passes it to the driver's connection config (native/mod.rs). It's sent at connection startup, so the role is set before any SQL runs.
2. Every connection, including reconnects: every connection the tested commands opened behaved this way, and the setting is part of the connection config they're built from. I didn't force a connection to drop mid-run, so the reconnect case comes from reading the source, not from a test.
3. Migration ledger: yes. _prisma_migrations was created and owned by owner_role.
4. Syntax: postgresql://migrator:PASSWORD@HOST:5432/DB?schema=public&options=-c%20role%3Downer_role. I didn't find options on the Prisma PostgreSQL docs pages I checked, so if you need a documented guarantee, it's worth asking the Prisma team to confirm.
5. Alternative: ALTER ROLE migrator IN DATABASE <db> SET role = owner_role needs no URL change. It applies to every client that logs in as migrator to that database, not just Prisma.

Not tested:

• Shadow database flows: migrate dev and migrate diff --from-migrations.
• PgBouncer: connect migrations directly to PostgreSQL unless you've confirmed your pooler accepts the options startup parameter.

Written by an agent (Claude Code, claude-opus-5-5), which ran these tests.

[celine05971-Rach]

Thank you for testing this on Prisma CLI 7.9.1 and PostgreSQL 17.11, and for checking the pinned schema-engine source.

Your results are particularly helpful because they demonstrate that migrate deploy can initialize the effective owner role through connection startup options and create an owner-owned _prisma_migrations ledger.

We are retaining this approach for an isolated TEST migration environment, with separate TLS, credential, role and runtime-validation safeguards.

One remaining question: Is PostgreSQL URL options=-c role=...considered a supported Prisma migration-engine configuration, or is it currently implementation-dependent behavior?**

A confirmation from a Prisma maintainer would help us distinguish a supported interface from behavior verified only against this pinned engine version.

Thanks again for the detailed investigation.

[DYNOSuprovo]

To add further architectural context to @g4lb and @sadamulhaq's answers, here is a detailed breakdown of how PostgreSQL's connection startup packet interacts with Prisma's schema engine (schema-engine / formerly migration-engine):

---

1. Connection-Startup Mechanism (StartupMessage Packet)

When Prisma connects to PostgreSQL, it parses the query parameters from the connection string. Setting:

postgresql://migrator:PASSWORD@HOST:5432/DB?options=-c%20role%3Downer

leverages PostgreSQL's native wire protocol StartupMessage.

• In PostgreSQL's protocol, any -c <name>=<value> passed in the startup options parameter is evaluated during backend initialization by the PostgreSQL postmaster before the connection enters the ready-for-query state (ReadyForQuery ('Z')).
• Because this occurs during the connection handshake itself, no SQL query has executed yet. The effective session authorization and current_user are initialized to owner before Prisma runs its first schema check or DDL statement.

---

2. Physical Connections, Reconnects, and the Shadow Database

1. Every Physical Connection: Because Prisma's connection pool initializes each socket connection with the same parsed connection string, every connection (including reconnects after network dropouts or transaction resets) sends the startup packet with -c role=owner.
2. Migration Ledger (_prisma_migrations): Table creation and migration records execute on the established connection, meaning _prisma_migrations is owned by owner rather than migrator.
3. prisma migrate dev (Shadow Database Nuance):
   • For prisma migrate deploy and prisma migrate diff, the configured URL is used directly.
   • For prisma migrate dev, Prisma dynamically creates and drops a shadow database. If you specify shadowDatabaseUrl, ensure you also append ?options=-c%20role%3Downer to the shadow database URL.

---

3. PostgreSQL 16+ Privilege Requirement

Ensure the role grant in PostgreSQL includes SET TRUE (the default in PG16+, but critical if using restricted memberships):

-- PostgreSQL 16+ syntax:
GRANT "owner" TO "migrator" WITH INHERIT FALSE, SET TRUE;

This architecture allows the migrator credential to be used in CI/CD pipelines while ensuring zero DDL objects or migration tracking tables end up orphaned under the migrator role.