Custom PostgreSQL role consistently fails authentication through Supavisor despite verified credentials

[DavidWalters123]

I need help investigating a Supavisor authentication failure for a production PostgreSQL read-only role.

We created the production role ee_backup_ro for backups/exports. The role has been independently verified as read-only across the required production schema:

• 55/55 required tables readable
• 0 write privileges
• no schema creation privileges
• rolvaliduntil = NULL
• no expiration

I'm connecting via PostgreSQL through the Supabase transaction pooler (Supavisor), using the project-scoped pooler credentials:

Host: aws-0-eu-west-1.pooler.supabase.com
Port: 6543
User: ee_backup_ro.
Password supplied via the PGPASSWORD environment variable
Database: postgres

I'm testing with a standard PostgreSQL client (psql), rather than through the Supabase API or dashboard.

The direct PostgreSQL role/permissions have already been verified. The failure occurs specifically when authenticating through Supavisor.

The PostgreSQL role and permissions are correct.

The problem occurs only when connecting through Supavisor.

We are using the canonical pooler endpoint:

aws-0-eu-west-1.pooler.supabase.com:6543

with the tenant-scoped username:

ee_backup_ro.<production-project-reference>

The password has been independently verified against our password manager. We supply it through PGPASSWORD, so URL encoding/special-character escaping is not involved.

We previously tested aws-1-eu-west-1.pooler.supabase.com, which returned a tenant/user resolution error. This indicates that aws-1 is not the correct pooler endpoint for this project. The aws-0 endpoint reaches authentication but rejects the credentials with a password/authentication failure.

This makes us suspect a Supavisor-side credential synchronization, caching, tenant mapping, or authentication issue rather than an incorrect PostgreSQL role or password.

Please investigate:

1. Whether Supavisor has stale or inconsistent credentials for ee_backup_ro.
2. Whether Supavisor's stored credential matches the current PostgreSQL role password.
3. Whether the role is correctly registered/mapped to this project's Supavisor tenant.
4. Whether there are known credential caching/synchronization issues affecting PostgreSQL roles.
5. Whether this role requires any additional configuration to authenticate through Supavisor.
6. Whether the username format is correct for the current project configuration.
7. Whether any project-specific restriction is preventing this role from authenticating through the pooler.
8. Whether you can refresh/reconcile the Supavisor credential state server-side.

Please do not reset the production password, restart the project, or make destructive production changes without first explaining the required action and receiving our approval.

The role must remain strictly read-only.

We would specifically like to know whether Supabase can repair or refresh the relevant Supavisor authentication state server-side without changing the verified production credentials.

Thank you.

[k1ng-arthur]

how are you connecting to it and what is exact error you are seeing?

[DavidWalters123]

I just updated the issue above.

I'm connecting via PostgreSQL through the Supabase transaction pooler (Supavisor), using the project-scoped pooler credentials:

Host: aws-0-eu-west-1.pooler.supabase.com
Port: 6543
User: ee_backup_ro.
Password supplied via the PGPASSWORD environment variable
Database: postgres

I'm testing with a standard PostgreSQL client (psql), rather than through the Supabase API or dashboard.

The direct PostgreSQL role/permissions have already been verified. The failure occurs specifically when authenticating through Supavisor.

[DavidWalters123]

The exact error returned by psql when connecting through the canonical Supavisor endpoint is:

psql: error: connection to server at "aws-0-eu-west-1.pooler.supabase.com" (52.209.89.87), port 6543 failed: FATAL: password authentication failed for user "ee_backup_ro"

The same error is returned twice by psql for the connection attempt.

This confirms that the endpoint is reachable and Supavisor is receiving the connection and resolving the username, but authentication for ee_backup_ro is being rejected.

[NspxMiguel]

Supavisor does not need a separate registration for this custom role. Its auth query reads the role secret from pg_authid, and the hosted Postgres function only returns roles where rolcanlogin is true.

I would first check the login attributes without changing anything:

select
  rolname,
  rolcanlogin,
  rolvaliduntil,
  rolpassword is not null as has_password,
  case
    when rolpassword like 'SCRAM-SHA-256$%' then 'scram-sha-256'
    when rolpassword like 'md5%' then 'md5'
    when rolpassword is null then 'missing'
    else 'other'
  end as password_format
from pg_authid
where rolname = 'ee_backup_ro';

Permissions such as SELECT are independent of LOGIN and the password. The expected result is one row with rolcanlogin = true, has_password = true, and no expired rolvaliduntil.

Then test the same secret through each path:

# Direct Postgres, if your network supports the project's direct endpoint
PGPASSWORD="$EE_BACKUP_PASSWORD" psql \
  "host=db.<project-ref>.supabase.co port=5432 dbname=postgres user=ee_backup_ro sslmode=require" \
  -c 'select current_user'

# Shared pooler, session mode
PGPASSWORD="$EE_BACKUP_PASSWORD" psql \
  "host=<host copied from Connect> port=5432 dbname=postgres user=ee_backup_ro.<project-ref> sslmode=require" \
  -c 'select current_user'

# Shared pooler, transaction mode
PGPASSWORD="$EE_BACKUP_PASSWORD" psql \
  "host=<same host> port=6543 dbname=postgres user=ee_backup_ro.<project-ref> sslmode=require" \
  -c 'select current_user'

This separates the cases:

• Direct also fails: the live Postgres login attributes or secret do not match, even if the grants are correct.
• Direct succeeds but both pooler modes fail: collect the corresponding Pooler Logs and give support the timestamps; that is evidence of a Supavisor auth-query/cache problem.
• Session succeeds but transaction fails: support can focus specifically on the transaction pool.

Use the exact pooler host from the Dashboard's Connect dialog; the aws-0 index cannot be derived reliably from the region. No password reset or project restart is needed for these checks.

References: Supabase connection modes and custom-role username format, Supavisor authentication query, and the hosted pgbouncer.get_auth implementation.

[DavidWalters123]

Diagnostic update

We have now narrowed the issue down to the Supavisor transaction pool (port 6543).

The affected production role is:

ee_backup_ro

The role itself has been verified in PostgreSQL:

• rolcanlogin = true
• rolvaliduntil = NULL
• has_password = true
• password_format = scram-sha-256

We rotated the role password and tested the new credential through all relevant connection paths.

1. Direct PostgreSQL

db.xmfcdpbquwyyhlnyebet.supabase.co:5432

Using:

user=ee_backup_ro

Result:

current_user
--------------
ee_backup_ro
(1 row)

PASS.

2. Supavisor Session Pool

aws-0-eu-west-1.pooler.supabase.com:5432

Using the project-scoped username:

ee_backup_ro.xmfcdpbquwyyhlnyebet

Result:

current_user
--------------
ee_backup_ro
(1 row)

PASS.

3. Supavisor Transaction Pool

aws-0-eu-west-1.pooler.supabase.com:6543

Using the same project-scoped username and the same newly rotated password:

ee_backup_ro.xmfcdpbquwyyhlnyebet

Result:

psql: error: connection to server at "aws-0-eu-west-1.pooler.supabase.com" (34.241.16.247), port 6543 failed: FATAL: password authentication failed for user "ee_backup_ro"

FAIL.

The pooler hostname and both connection modes are confirmed by the project's current Supabase Connect dialog.

Current conclusion

The PostgreSQL role and password are valid. This is demonstrated by successful authentication through both the direct PostgreSQL endpoint and Supavisor Session mode.

The failure is reproducible specifically through Supavisor Transaction mode on port 6543.

At this point, further password changes do not appear warranted. We would appreciate guidance on whether there is a transaction-pool-specific authentication or credential-state issue affecting custom PostgreSQL roles, and what diagnostic information or Pooler Logs would be useful to identify the cause.

No production restart or other destructive changes have been made.