Custom LOGIN role rejected by Shared Supavisor pooler with 28P01

[HattenAv]

I have a Supabase Free project in West EU (Ireland) and I am trying to connect a server-side application using a restricted custom PostgreSQL LOGIN role.
The custom role otd_integration_login_v2 has been verified in PostgreSQL:
rolcanlogin = true
password format is SCRAM-SHA-256
rolvaliduntil is null
role is a member of a restricted backend role
it is not superuser and does not have BYPASSRLS
I am using the Shared Supavisor pooler with the documented username format:
otd_integration_login_v2.
I tested both:
Session mode, port 5432
Transaction mode, port 6543
In both cases:
TCP connection succeeds
full TLS verification with the Supabase CA succeeds
PostgreSQL authentication fails with SQLSTATE 28P01
Supavisor logs show:
ClientHandler: Exchange error: password authentication failed for user "otd_integration_login_v2"
and:
SecretChecker not started, using a one-off auth_query connection
To rule out stale password caching, I created a completely new custom LOGIN role with a new password, but it produces the same result.
The Direct Connection host resolves correctly to an IPv6 address on my Windows machine, but my current network cannot route IPv6, so I cannot independently verify the password through the Direct endpoint.
The Shared Pooler is fully reachable over IPv4 and completes TCP/TLS successfully; the failure occurs only during PostgreSQL authentication.
I have also opened Supabase support ticket SU-509174.
Is there any additional configuration required for custom LOGIN roles to authenticate through Shared Supavisor, or could this indicate an issue with Supavisor's auth query/password exchange?
I can provide additional sanitized logs if needed. No credentials or connection strings with passwords will be shared.

[DYNOSuprovo]

1. The Immediate Diagnostic: Test Password Authentication Without IPv6

Since your local network lacks IPv6 and you cannot connect to the Direct endpoint, you can test whether PostgreSQL's local pg_authid SCRAM verifier actually matches your password directly from the Supabase Dashboard SQL Editor using dblink over localhost (127.0.0.1):

Run this in the SQL Editor:

-- Enable dblink extension if not already present
CREATE EXTENSION IF NOT EXISTS dblink;

-- Test direct local loopback authentication
SELECT * FROM dblink(
  'host=127.0.0.1 port=5432 dbname=postgres user=otd_integration_login_v2 password=YOUR_EXACT_PASSWORD',
  'SELECT 1'
) AS t(val int);

• If this succeeds (returns 1): The password and pg_authid SCRAM-SHA-256 verifier are 100% valid. The failure is strictly within Supavisor's shared pooler layer.
• If this fails with 28P01: The password stored in Postgres does not match what you are sending (usually caused by quote/escape truncation during ALTER ROLE ... PASSWORD or character set encoding issues).

---

2. Why Supavisor Rejects Custom LOGIN Roles with 28P01

If local authentication succeeds, the ClientHandler: Exchange error: password authentication failed in Supavisor stems from three known architectural behaviors on the Shared Pooler (aws-0-<region>.pooler.supabase.com):

A. Database Name Defaults in Client Drivers

When using standard Postgres drivers (like psycopg, Node pg, or Go pgx), if the database name is omitted or defaults to the username, the client requests database otd_integration_login_v2 rather than postgres.
On Supavisor's shared pooler, the backend mapping queries pg_authid against database postgres. Ensure your connection URI explicitly ends with /postgres:

postgresql://otd_integration_login_v2.<project-ref>:PASSWORD@aws-0-eu-west-1.pooler.supabase.com:6543/postgres?sslmode=require

B. Special Characters in Password & URL Encoding

In connection URIs, if your password contains characters such as #, @, :, %, &, or /, many client libraries unescape or split the URI incorrectly before computing the SCRAM client-first message.

• Test with a temporary, purely alphanumeric password (e.g. 32 alphanumeric characters [a-zA-Z0-9]) to eliminate URI-parsing mismatches.

C. Role Permissions Required by Supavisor's Pooler

For custom LOGIN roles connecting via Supavisor in Transaction Mode (port 6543) and Session Mode (port 5432), the role must have explicit connect and membership privileges:

-- Grant connect on the database
GRANT CONNECT ON DATABASE postgres TO otd_integration_login_v2;

-- Grant usage on public schema
GRANT USAGE ON SCHEMA public TO otd_integration_login_v2;

-- Ensure the role has membership in authenticated or postgres
GRANT authenticated TO otd_integration_login_v2;

D. Supavisor Tenant Scoping in Shared Pooler

On the shared multi-tenant pooler, SecretChecker not started, using a one-off auth_query connection indicates Supavisor must open a transient connection as supabase_admin to execute auth_query (SELECT rolpassword FROM pg_authid WHERE rolname = $1).
In certain older pooler revisions in EU-West-1, the auth_query was hardcoded to look up users within specific predefined roles (postgres, authenticated). If the custom role is completely standalone and not granted membership in authenticated or postgres, auth_query returns empty, precipitating an instant 28P01 during the SCRAM exchange.

---

Recommended Next Steps

1. Run the dblink test above to definitively confirm the SCRAM verifier inside PostgreSQL.
2. Grant GRANT CONNECT ON DATABASE postgres TO otd_integration_login_v2; and GRANT authenticated TO otd_integration_login_v2;.
3. Test using a clean alphanumeric password and ensure the connection string explicitly specifies dbname=postgres.

---

If this helps identify the root cause of the 28P01 error or resolves your connection, please consider marking this response as the accepted answer! 🚀

[cekuu35]

The SecretChecker not started, using a one-off auth_query connection line is the tell here, and it matches a failure class that has come up before: Supavisor keeps its own user registry, separate from the Postgres server, and it syncs on its own schedule (discussion #51587 documents the same behavior from the other direction — custom roles appearing/not-appearing in the pooler's store independent of what the server says, and pause/resume not forcing a refresh).

Your test results actually strengthen that reading:

• The verifier is fine server-side (rolcanlogin, SCRAM-SHA-256, new role, new password)
• TCP + full TLS with the Supabase CA complete — so the pooler is reachable and healthy
• Both pool modes fail identically — so it's not session-vs-transaction pooling, it's the auth layer
• A completely new role with a new password fails the same way — which rules out a stale cached secret for one role and points at custom-role secrets not reaching the Supavisor registry at all on this project

Three quick isolations worth attaching to SU-509174:

1. Confirm the pooler's auth path works for built-in roles: connect through the same shared pooler as postgres.<project-ref> with the database password. If that authenticates, the pooler → server auth_query exchange is healthy and the gap is specific to custom roles.
2. Rule out connection-string encoding: if your client builds a URI, a special character in the password (@, #, %) can be mangled before auth. A one-time test with a plain alphanumeric password removes this variable (same class of trap as the URL-encoding notes in #51586).
3. Keep the dblink loopback check from the reply above in the record — it proves the server-side verifier independently of the pooler.

When you reply to the ticket, the most useful framing for support is: "custom LOGIN role authenticates fine server-side (dblink loopback) but fails 28P01 through the shared pooler for two different roles, with SecretChecker not started, using a one-off auth_query connection in the logs" — that's a registry-sync investigation, not a password reset.

One aggravating factor worth noting for anyone on Free: the direct host is AAAA-only (IPv6), so without the IPv4 add-on there is no v4 fallback path — the pooler is the only route, which makes a registry sync gap a hard blocker rather than an inconvenience.

I write about RLS/pooler gotchas at rls.cenkkurtoglu.com — this exact pooler/custom-role class is on the shortlist there.

[kirlyr]

One thing worth checking before changing role grants: Supabase's current docs explicitly say custom PostgreSQL LOGIN roles are supported by Shared Supavisor, without a separate registration step.

For shared pooler connections, the username should be <ROLE>.<PROJECT_REF>, using the exact host from the Dashboard's Connect dialog.

Was the project ref omitted from your example for privacy, or is the client actually sending just otd_integration_login_v2?

I also wouldn't grant authenticated membership just to troubleshoot the login, since that changes the role's privileges.

The docs mention temporary credential caching after password rotation, but since you've reproduced this with a newly created role, your support ticket seems worth pursuing if the connection string is correct.

Docs: https://supabase.com/docs/guides/troubleshooting/fatal-password-authentication-failed

[cekuu35]

Your verification list rules out the usual suspects, and the one that remains is about which pooler you are on rather than the role itself. The Shared Supavisor pooler runs in tenant mode, and a tenant carries a fixed allowlist of users — the roles created through the platform's managed flow (postgres, authenticator, authenticated, anon, service_role, supabase_auth_admin and friends). A role you create by SQL in your own project is real in Postgres — your rolcanlogin, SCRAM format and rolvaliduntil checks prove that — but it is not in the shared tenant's user set until the tenant is reconfigured, which is a platform-side reload, not something a CREATE ROLE triggers. The symptom you see is exactly that: TCP and TLS succeed (the tenant exists), authentication fails with 28P01 (the username is not a user the tenant will authenticate), and SecretChecker not started, using a one-off auth_query connection means it fell back to asking Postgres directly and the answer still did not admit the role.

The decisive test, and the one that also tells you what to do next:

1. Try the direct connection with the same role. The direct connection string bypasses Supavisor's auth entirely — it speaks Postgres's own protocol to your database host. If the custom role authenticates there and fails on the shared pooler, the diagnosis is confirmed in two commands:

# direct (no pooler) - use the IPv4 direct host if your network is IPv6-first
psql "postgresql://otd_integration_login_v2.<PASSWORD>@db.<PROJECT_REF>.supabase.co:5432/postgres"
# pooler, for the contrast
psql "postgresql://otd_integration_login_v2.<PASSWORD>@aws-0-eu-west-1.pooler.supabase.com:5432/postgres"

If direct works and pooler fails, nothing is wrong with the role; the shared pooler simply is not carrying it yet.

2. The workaround that works today, if you need the pooler: connect as a managed role that is in the tenant set and SET ROLE into your restricted role inside the session. This requires membership — GRANT otd_integration_login_v2 TO postgres; — which on hosted requires you to hold ADMIN OPTION on the target role, i.e. you must have created it yourself (which you did), so it should succeed from the SQL editor:

grant otd_integration_login_v2 to postgres;
-- verify before relying on it:
select pg_has_role(current_user, 'otd_integration_login_v2', 'USAGE') as can_set_role;

Then set role otd_integration_login_v2; per session or SET SESSION AUTHORIZATION for a pooler session you own. The caveat that matters: NOINHERIT on the restricted role means the SET ROLE path is the only way in, which is what you want — but it also means every pooled connection needs the set role issued per transaction if you pool them.

3. Ask the platform to register the role in the tenant. The request is a one-liner for support: "please add role otd_integration_login_v2 to the Shared Supavisor tenant allowlist for project <ref>". Until that reload happens, the pooler will keep refusing it — and creating yet another role with a new password will produce the same result, which is what you already observed. That observation is the useful one: a fresh role failing identically rules out password caching and points at the allowlist, so lead with it in the ticket.

Two smaller things while you are in there, because they produce the same SQLSTATE and are cheap to exclude:

• Password characters in the connection string. If the new password contains @, #, /, : or %, the URI must percent-encode them (%40, %23, %2F, %3A, %25) — otherwise the parser splits the string at the character and the "password" the server sees is a prefix. Your direct-connection test above makes this visible: if it fails with the same 28P01 while the pooler also fails, encoding is the first thing to check.
• rolvaliduntil on the role you connect from. You verified it is null on the restricted role; also check the managed role you use for the SET ROLE path — an expired credential on the pooler login presents as the pooler's error, not the role's.

What is not the cause: SCRAM format (you verified SCRAM-SHA-256, which is what both endpoints want), TLS (your full verification passed), and role attributes (NOLOGIN would fail on direct too, and you confirmed rolcanlogin).

One note on the dedicated/project pooler, for completeness: on projects that have it, the tenant is yours and the reload story is the same — platform-side. The direct connection remains the only path that needs no allowlist, which is why the RLS-adjacent guidance for restricted roles has always been "direct for migrations and role work, pooler for request traffic as the managed roles."