created a custom role with login and unable to connect via psql

[robahtou]

Hi Everyone. New to Supabase.

I'm tasked to create new roles for our services & users.

First, I created a role without login (service_read_write), and added the privileges that we wanted.
Then, I created a role that will login and made it a member of service_read_write:

GRANT service_read_write TO new_user_01;

I then use the connection string to attempt to login:
postgres://new_user_01.[supabaseId]:[myPassword]@aws-0-us-east-1.pooler.supabase.com:5432/[my_db]

I keep getting this error:

psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (44.216.29.125), port 5432 failed: FATAL:  Authentication error, reason: "Authentication query failed: %DBConnection.ConnectionError{message: \"connection not available and request was dropped from queue after 10000ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:\\n\\n  1. Ensuring your database is available and that you can connect to it\\n  2. Tracking down slow queries and making sure they are running fast enough\\n  3. Increasing the pool_size (although this increases resource consumption)\\n  4. Allowing requests to wait longer by increasing :queue_target and :queue_interval\\n\\nSee DBConnection.start_link/2 for more information\\n\", severity: :error, reason: :queue_timeout}"

There is at most 8 connections to the database since this is a new database on supabase. If i switch users to postgres and try to connect I immediately connect to the database.

Anyone know why custom roles won't be able to connect? I know our CREATE ROLE & privileges work since we tried them out on another postgres instance on RDS without issue. But when we do it on supabase we keep getting the above error.

[leonidas1712]

I have the same issue. Does anyone know the fix?

[LucasKlemke]

Any solutions ?

[LucasKlemke]

I'm trying to create a new role to access a schema from a organization, like this:
createRole:CREATE ROLE ${sanitizedOrganizationName} WITH LOGIN PASSWORD ${password};, grantRole: GRANT CONNECT ON DATABASE postgres TO ${sanitizedOrganizationName};, createSchemaQuery: CREATE SCHEMA IF NOT EXISTS ${sanitizedOrganizationName};, checkSchema: SELECT schema_name FROM information_schema.schemata WHERE schema_name = '${sanitizedOrganizationName}';, setAdmin: GRANT ${sanitizedOrganizationName} TO ${owner} with admin option;, alterSchema: alter schema ${sanitizedOrganizationName} owner to ${sanitizedOrganizationName}, grantAll: GRANT ALL PRIVILEGES ON SCHEMA ${sanitizedOrganizationName} TO ${sanitizedOrganizationName};, grantAllTables: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ${sanitizedOrganizationName} TO ${sanitizedOrganizationName};, grantAllSequences: GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA ${sanitizedOrganizationName} TO ${sanitizedOrganizationName};, setSearchPath: ALTER ROLE ${sanitizedOrganizationName} SET search_path TO ${sanitizedOrganizationName};,

But when I try to connect, I get the following error:
FATAL: Tenant or user not found

[LucasKlemke]

SOLUTION: https://github.com/orgs/supabase/discussions/30107