Unable to get collaborator to connect to database

[sai-sy]

I've deployed mathesar to railway, connected it to my supabase instance through the postgres "user" using the session pooler (direct connection doesn't work due to being ipv6?) and then created a user, and a role. I've applied the role to the user by making them a collaborator then given them database connect permissions through the dashboard. Yet the user can't connect with this error:

OperationalError: connection failed: connection to server at "15.156.114.158", port 5432 failed: FATAL: Tenant or user not found connection to server at "15.156.114.158", port 5432 failed: FATAL: Tenant or user not found

I also earlier made a role in my postgres db named mathesar with a password and tried to connect to supabase using that role instead of the postgres "user" from supabase's connection string and got a similar error.

Can anyone let me know what I'm doing wrong?

[sai-sy]

I think the issue might be using the session pooler to connect. I bought the supabase ipv4 add on and redeployed and reconnected and everything works fine. Issue is ipv4 add on costs money so hopefully that's not the issue. This won't be an issue for people not using supabase I'd imagine as well but we're already invested in the supabase ecosystem

[zackkrida]

Hi, @sai-sy. There are a few things happening here, but hopefully I can provide some clarity! As you've suspected it's definitely an issue with the session pooler role supabase creates and how it interacts with Mathesar. The simplest explanation is that:

Mathesar collaborators can't be assigned different roles, because the Supabase session pooler requires special, project-ID-suffixed roles that don’t actually exist in Postgres.

Supabase's session pooler doesn't look at roles exactly the same way Postgres does. To figure out which project you're connecting to, it automatically adds your project ID (a long hash like apbkobhfnmcqqzqeeqss) onto the end of every database role.

When you connected the DB to Mathesar, you probably noticed that the role in the config looked like this:

postgres.apbkobhfnmcqqzqeeqss

That extra bit after the dot is your project ID and how the pooler knows which project you belong to.

The problem you're facing in Mathesar arises when you try to assign a new user to a different role, that doesn't have the .apbkobhfnmcqqzqeeqss part appended. Here's a detailed explanation:

• When you first add a database in Mathesar, you tell it which role to use for connecting. That can be postgres.apbkobhfnmcqqzqeeqss and everything works fine.
• But when you later create your own roles (say, mathesar) and want to assign them to Mathesar users, Mathesar only sees and lets you assign the "real" roles that actually exist in Postgres, like mathesar, without the .apbkobhfnmcqqzqeeqss suffix.
• The session pooler doesn't recognize plain mathesar. It only understands mathesar.apbkobhfnmcqqzqeeqss. But these suffixed roles aren't real roles in your database, so Mathesar can't list them for you to assign.
• The end result: you can only assign mathesar in Mathesar, but when Mathesar tries to connect with that role, the pooler rejects it with “FATAL: Tenant or user not found.”

So the limitation is: Mathesar's user/role assignment model doesn't currently mesh well with Supabase's pooler requirement of appending the project ID. That's why your roles aren't working through the session pooler, even though they look correct inside the database itself.

That said:

• I find that the Supabase session pooler is unreliable anyway, and will have intermittent connection failures. It doesn't seem to work great with Mathesar.
• Mathesar does support IPv6 for DB connections, but Railway doesn't support outbound IPv6, sadly. I'll figure out how to document this limitation for other users trying to work with Mathesar + Supabase + Railway.

I'll discuss with the other maintainers if there's currently a way or a way we could add the ability to manually define roles beyond those that exist in the actual database. It's a complicated edge case for us to cover in the UI, but I'll get back to you once the team has discussed it.

In the meantime, your options are somewhat limited. Apologies for the trouble! I'm happy to explain further if you have other questions.