Recommended secure provisioning of a custom PostgreSQL LOGIN role on Supabase

[dkelleci]

Hi Supabase community,

I'm building a small, private application using Supabase Postgres. Its backend needs a dedicated, least-privilege PostgreSQL LOGIN role to execute narrowly scoped synchronization transactions.

The intended connection is through the shared Supavisor transaction pooler (port 6543). I've already verified connectivity using sslmode=verify-full, including certificate and hostname validation.

My question concerns secure credential provisioning, not database connectivity.

With PostgreSQL's psql \password command, the plaintext password is not sent in SQL, but a SCRAM-SHA-256 verifier is included in an ALTER ROLE statement.

I'd appreciate guidance on:

1. What is the recommended, supported way to create and rotate a custom LOGIN role's password on hosted Supabase?
2. Can the SCRAM verifier appear in SQL logs, query monitoring, or other persisted records?
3. If so, what practical protections or alternatives are recommended?
4. Are custom LOGIN roles supported through the shared Supavisor transaction pooler while maintaining least-privilege database permissions?

I'm specifically trying to distinguish between protecting plaintext passwords and protecting SCRAM verifiers, without introducing unnecessary complexity.

I'm using the Supabase Free plan and would prefer a standard, production-supported solution rather than a temporary workaround.

Thanks for any guidance or relevant documentation!

[aniketatgithub]

Yes to the pooler part first, since it is the simplest: custom roles through the shared pooler are supported. The connection docs say the shared pooler username is postgres.[PROJECT-REF], and if you connect as a custom role the username is [ROLE].[PROJECT-REF]. Port 6543 on the shared pooler is Supavisor in transaction mode. Least privilege is just standard Postgres: the role only has what you grant it.

For creating and rotating the password, the supported path is SQL run as the postgres role (SQL editor, CLI, or a direct/psql connection). Supabase recommends a separate user per service rather than sharing the postgres password, and this is exactly that pattern:

CREATE ROLE app_login WITH LOGIN PASSWORD '...';
GRANT ... (only what it needs)

For rotation, prefer psql's \password over writing the password into SQL:

\password app_login

The reason is in the Postgres CREATE ROLE docs. Postgres always stores the password encrypted in the catalogs (if you hand it a SCRAM verifier string, it stores that as-is), but a literal password inside CREATE ROLE or ALTER ROLE is sent in cleartext and can end up in the server log if statement logging catches it. \password exists specifically to change a password safely later without putting the plaintext in a statement.

That also frames your verifier question. What Postgres stores is the SCRAM verifier, not the plaintext. The realistic exposure is the statement itself: if a CREATE/ALTER ROLE statement carrying a password or verifier is captured by statement logging anywhere, treat that captured text as a secret and rotate. Practically: set and rotate the password interactively with \password over your verified TLS connection, do not put role passwords in migration files or scripts that get logged, and grant the role only the permissions it needs so a leak has a small blast radius.

One small note: the dashboard password setting only covers the built-in postgres role. Custom roles are managed with SQL, which is normal Postgres and fully supported on hosted projects.

[dkelleci]

Thank you so much for your clear and detailed explanation! Your guidance was extremely helpful, and we were able to successfully verify the custom PostgreSQL role connection using the approach you recommended.

Really appreciate you taking the time to help!

[aniketatgithub]

Glad it worked, and thanks for confirming. If you mark the answer above as accepted, the next person with the same role question will find it at the top.

[cekuu35]

Adding to @aniketatgithub's answer — on the verifier-logging part specifically (your Q2/Q3), because the threat model there is worth being precise about.

What a SCRAM verifier leak actually is — and isn't

The verifier (SCRAM-SHA-256$4096:<salt>$<stored-key>) is a salted, iterated hash. It is not the password, and it can't be replayed against the server as a credential — Postgres performs the full SCRAM exchange and the verifier alone doesn't satisfy it. What a leaked verifier does enable is offline brute-force: an attacker with the verifier can guess passwords locally without touching your instance, confirm a hit, and then connect. So the protection goal is "keep verifiers out of places with wide read access," not "treat them as passwords."

Where the verifier can and cannot end up on hosted Supabase

• pg_stat_statements is safe by construction: it normalizes queries, replacing literals with $n placeholders. An ALTER ROLE ... PASSWORD 'SCRAM-...' shows up as ALTER ROLE ... PASSWORD $1. The verifier string is not retained there.
• Supabase's Log Explorer / Postgres logs capture statements by policy (errors, slow queries), not every statement by default. If you rotate from the SQL editor, the ALTER ROLE passes through the platform session; if statement logging is enabled for your project at the time, the query text including the verifier can land in logs. Free-plan retention is short, but "short" is not "zero."
• psql's \password (which computes the verifier client-side) is the better path here not because of transport, but because it avoids the plaintext password ever being in your shell history or in a .sql file — it prompts. The verifier still travels in the ALTER ROLE it issues.

The leak vector that actually bites: migration files

The classic mistake isn't logs — it's committing credential provisioning to the repo:

-- supabase/migrations/0004_create_sync_role.sql  ← DO NOT do this
create role app_sync login password 'SCRAM-SHA-256$4096:...';

Now the verifier is in git history forever, in every clone, and in your deployment logs. Role creation and rotation should be operational actions (CLI session, secrets manager), never migration-file contents. What belongs in a migration is everything after the credential: the grants, the RLS, the revokes — the pattern from the migration-operator design thread applies directly: #51492

Rotation with zero downtime

Postgres doesn't support two live passwords for one role, so the zero-downtime pattern is a brief dual-role deploy:

1. create role app_sync_v2 login password '<new>'; — grant it exactly what app_sync has (grant app_sync to app_sync_v2 is tempting but inherits too much if app_sync has inherited grants — prefer re-granting the minimum explicitly).
2. Deploy the connection string for app_sync_v2 (secrets manager / env, never the repo).
3. Verify the sync is running on the new role (pg_stat_activity grouped by usename).
4. drop role app_sync; — only after its connections drain, since DROP fails while sessions exist. If it lingers, alter role app_sync nologin; first to seal it, then drop on the next window.

For a small private app, quarterly-or-on-suspicion rotation is fine; the SCRAM iteration cost (4096 default) makes offline brute-force of a long random password impractical regardless.

One transaction-pooler caveat for your sync transactions (Q4)

Custom roles work through the shared 6543 pooler, as covered above — but transaction mode means no session-level state survives between queries. Two practical consequences for a sync worker:

• Session SET (e.g., set app.tenant_id = '...') does not persist across pooled transactions — use set local inside the transaction, or pass the value as a function argument.
• The cleanest least-privilege shape is what you already described: the role gets only EXECUTE on narrowly-scoped SECURITY DEFINER functions (owned by a NOLOGIN role that holds the actual table grants — the ownership pattern from https://dev.to/cekuu35/you-gave-your-security-definer-function-a-dedicated-owner-role-it-broke-heres-why-1oj3), and zero direct table grants. Then even a leaked pooler credential can only run the functions you wrote, inside the tenant scoping you encoded — the password becomes the least dangerous secret in your system.

The one-sentence version: rotate from an interactive session (never a committed migration), treat verifiers as brute-force-resilient but repo-toxic, and let revoked-everything roles make the credential low-value even if it leaks.