How to obtain a connection string that authenticates as prisma_application (least privilege)

[misawa03]

Hi,

I run a small production app on Prisma Postgres (region: ap-northeast-1).
I would like the application to connect with least privilege, but I cannot find a way
to obtain a connection string that maps to a non-superuser role.

What I observe (all verified by querying the database directly):

1. My current direct connection string (postgres://@db.prisma.io:5432/postgres)
   authenticates as the role prisma_migration.

   • SELECT rolsuper FROM pg_roles WHERE rolname = current_user returns true
   • It owns all 19 tables in the public schema
   • So the application currently runs with superuser rights, which is more than it needs.

2. The database already contains a role named prisma_application.

   • It is a member of pg_read_all_data and pg_write_all_data
   • It cannot TRUNCATE, does not own any table, and has no CREATE on the schema
   • This is exactly the privilege level my application needs.

3. I could not find any way to get a connection string that authenticates as
   prisma_application:

   • The Console connect dialog shows no role or permission selector, and there is no
     "generate new connection string" button; it shows the same key I already use
   • The Management API "create connection" endpoint has no role parameter
   • Changing the username portion of the connection string does not change the role,
     which suggests the role is decided server-side from the key

My questions:

a) Is there a supported way to obtain a connection string that authenticates as
prisma_application (or any non-superuser role) for a Prisma Postgres database?
b) If not, is there another recommended way to run an application without superuser
privileges on Prisma Postgres?
c) If neither exists today, is it on the roadmap?

For context, this is a construction-industry app holding customer names and addresses in
drawings, so limiting blast radius matters to us.

Thanks in advance.

[lMysticl]

Correction after rechecking the docs: the pooled/direct split is documented, but I overstated what it proves. Prisma documents the same generated USER:PASSWORD for both URLs, with only the hostname changing. It does not document that the pooled endpoint authenticates as prisma_application.

The decisive check is to run this through each URL, before any SET ROLE:

SELECT
  session_user,
  current_user,
  (SELECT rolsuper FROM pg_roles WHERE rolname = session_user) AS session_is_superuser;

If both URLs report session_user = prisma_migration, there is no documented URL-level answer to (a). The current Create connection API exposes no PostgreSQL-role selector, so Prisma Support would need to confirm whether a role-bound credential exists or is planned.

For (b), SET LOCAL ROLE prisma_application inside an explicit transaction is valid PostgreSQL behavior and survives until COMMIT or ROLLBACK, including with transaction pooling. However, it is not equivalent to authenticating as a non-superuser: session_user and the underlying credential remain prisma_migration, and RESET ROLE can restore that superuser role. It can reduce privileges for normal application queries if every transaction applies it first, but it is not a hard security boundary against arbitrary SQL. PostgREST's similar pattern uses a deliberately NOSUPERUSER authenticator, which is the important difference.

So the least-privilege solution is still a connection credential that authenticates as a non-superuser. If Prisma does not expose one, SET LOCAL ROLE is only a defense-in-depth workaround, and the supported solution/roadmap needs confirmation from Prisma.

[kaminagakur4]

The pooled/direct split is real, but I don't think it can be the thing that selects the role. Prisma's own docs use the same USER:PASSWORD on both URLs and only change the hostname, and you already found that the role is resolved from the credential rather than from the string, so moving to the pooled host shouldn't take you off prisma_migration. Worth confirming rather than arguing about, though: run select current_user, (select rolsuper from pg_roles where rolname = current_user); through each URL. If both report prisma_migration, there's no URL-level answer to (a), and for what it's worth prisma_application doesn't appear anywhere in Prisma's public repos or docs, which makes it look like an internal provisioning role rather than something you're meant to connect as. On (b), I'd push back on dismissing SET ROLE: the pooling docs say session state is lost after each transaction boundary, and SET LOCAL lives inside one, so begin; set local role app_runtime; ... commit; does survive the pooler. That's the same shape PostgREST runs on. Just don't reach for RLS before that, since prisma_migration is both superuser and the owner of all 19 tables and so bypasses it twice over, and FORCE ROW LEVEL SECURITY only closes the owner half.

[lMysticl]

Good catch — thank you. You're right that I overstated what the endpoint split proves, and that SET LOCAL ROLE remains effective for the current transaction. I've corrected my comment. The remaining caveat is that, with prisma_migration as the superuser session_user, this is defense in depth rather than equivalent to authenticating with a non-superuser credential; the PostgREST pattern starts from a NOSUPERUSER authenticator.

[kaminagakur4]

No worries at all, and thanks for going back and editing it. <3

Your caveat is the right one to hold onto, and it's honestly the more important half. SET LOCAL ROLE from a superuser session is a guardrail, not a boundary. Anything inside that transaction can just RESET ROLE and it's prisma_migration again. It'll stop your own code from writing to the wrong table by accident, but it won't stop injected SQL or a compromised process. That's the real difference with PostgREST: its authenticator is NOSUPERUSER and also NOINHERIT, so it carries no privileges of its own and can only reach roles that were explicitly granted to it. Nothing inside the session can undo that.

So for (a), I'd say there's no URL-level answer today, and SET LOCAL ROLE isn't a stand-in for one.

@misawa03, there's one thing nobody here has tried yet, and it's about ten minutes of work. prisma_migration is a superuser, so it can just create a login role for you:

create role app_runtime login password '...' nosuperuser;
grant usage on schema public to app_runtime;
grant select, insert, update, delete on all tables in schema public to app_runtime;
alter default privileges in schema public
  grant select, insert, update, delete on tables to app_runtime;

Then put app_runtime and its password in place of USER:PASSWORD in the direct URL, and check what you actually get:

select current_user, (select rolsuper from pg_roles where rolname = session_user);

I haven't tried this on Prisma Postgres, so treat it as the next experiment rather than an answer. Two ways I can see it not working: the platform might reject a role it didn't provision, and the pooled endpoint might only take the generated credential even if the direct one is fine.

If it works, that's your answer to (a), and it's a real boundary this time, since app_runtime has no way back to superuser. If it doesn't, that's still worth posting, because it turns the question into a specific ask: a role selector on the Create Connection API.

[kingofdead6]

I think the most useful next step is to separate what PostgreSQL itself allows from what Prisma Postgres exposes as a supported connection mechanism.

Since the generated Prisma Postgres credential appears to authenticate as prisma_migration, changing the username in the URL won't help if the credential is what determines the server-side role.

One experiment that seems worth trying is creating a dedicated LOGIN NOSUPERUSER role using the existing privileged connection:

CREATE ROLE app_runtime
  LOGIN
  PASSWORD '...'
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE;

GRANT USAGE ON SCHEMA public TO app_runtime;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO app_runtime;

GRANT USAGE, SELECT, UPDATE
ON ALL SEQUENCES IN SCHEMA public
TO app_runtime;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLES TO app_runtime;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT, UPDATE
ON SEQUENCES TO app_runtime;

Then connect using that role's credentials and verify both the authenticated role and its privileges:

SELECT
  session_user,
  current_user,
  (SELECT rolsuper FROM pg_roles WHERE rolname = session_user) AS session_is_superuser;

If session_user is app_runtime and session_is_superuser is false, that would provide a real least-privilege boundary.

If Prisma Postgres rejects that role or the generated connection mechanism cannot authenticate with it, then I think that is the key finding: PostgreSQL supports the model, but Prisma Postgres currently doesn't expose a documented way to provision a role-bound application credential.

The current Prisma documentation describes the connection credentials as being generated by Prisma Console, with the pooled and direct URLs sharing the same credentials and differing by hostname. So I don't think the pooled/direct distinction itself can be assumed to select prisma_application.

In that case, I'd agree that SET LOCAL ROLE is useful defense-in-depth, but it isn't equivalent to a non-superuser login because the underlying session is still authenticated as the privileged role.

So I'd be interested to see whether the CREATE ROLE ... NOSUPERUSER experiment works on Prisma Postgres. If it doesn't, that gives Prisma a very concrete feature request: support creating or selecting a non-superuser PostgreSQL role when generating a connection.

[Mr-Hasan-Hamid]

The connection string provided in the Prisma Data Platform console is hardcoded to the admin/migration role because Prisma CLI uses that same URL to execute schema migrations (prisma migrate).

To connect your application with least privilege (prisma_application), you can create a dedicated database role directly in Postgres:

CREATE ROLE app_user WITH LOGIN PASSWORD 'your_secure_password' IN ROLE prisma_application;
GRANT USAGE ON SCHEMA public TO app_user;

Then format your runtime connection string using that user:

postgres://app_user:your_secure_password@db.prisma.io:5432/postgres?sslmode=require

This connects directly as app_user inheriting prisma_application rights (read/write data without schema drop/alter privileges), while you keep your primary console connection string in an environment variable specifically reserved for prisma migrate.