Supavisor needs tenant's password to do the routing using its proxy

[allyxcristiano]

Supavisor needs tenant's password to do the routing using its proxy

[allyxcristiano]

@chasers we would like to have this because we have the scenario where we're going to have different clusters with different passwords (we could use the same user and db name to easily manage the aliases) and our intention initially was to register the credentials using supavisor and then any application could just call the proxy passing the some_username.some_tenant and some key. We thought this would be the password of supavisor db (maybe because on the supavisor docs it's using the same DB for everything).

Another example of the scenario assuming the two tenants below were registered previously using PUT API:

Tenant 1 (id: d866dd5f-f625-47de-af2b-1f6985db539f)
Host: host_2
Port: 54321
User: my_user
Password: tenant1
Database: my_db

Tenant 2 (id: 6bb66a28-be1e-47d6-86e5-5d67fb10b30c)
Host: host_3
Port: 54322
User: my_user
Password: tenant2
Database: my_db

When the some application requests supavisor's proxy we were expecting to have a transparent connection with:

This would point to tenant 1 DB:
psql postgresql://my_user.d866dd5f-f625-47de-af2b-1f6985db539f:<supavisor-db-password>@supavisor_host:7654

This would point to tenant 2 DB:
psql postgresql://my_user.6bb66a28-be1e-47d6-86e5-5d67fb10b30c:<supavisor-db-password>@supavisor_host:7654

<supavisor-db-password> could be also another global key as you suggested.

Because the tenants above were registered previously on supavisor with its credentials, we could easily retrieve credentials data internally.

[egor-romanov]

Another simple example based on the architecture of the image below:

Using the example above as a reference, Clients 1, 2, 3, and 4 will need to know the passwords (let's assume these DBs have different passwords) of Databases 1, 2, and 3 to use Supavisor's proxy even though we know Supavisor have these credentials saved internally. Is it clear to you guys? Is this the expected behavior?

Yep, cause otherwise they will be able to connect to all databases. While we consider that they should be able to connect only database for which user knows password. At least as the first usecase.

So what is being discussed here is a feature request really.

[allyxcristiano]

@egor-romanov I noticed you removed the bug label, don't you think this is a problem? Is this the expected behavior?

[chasers]

After registering a tenant with its credentials using the PUT API I was expecting to use the proxy passing the user prefix, the tenant id and the DB password of supavisor DB

@allyxcristiano thanks! Why exactly would you want this?

Maybe instead of using the metadata db password it would be useful to have some sort of global api key that you use as a the password in the connection string? Then it would authenticate with that and pick the user password from the user record.

[allyxcristiano]

Another simple example based on the architecture of the image below:

Using the example above as a reference, Clients 1, 2, 3, and 4 will need to know the passwords (let's assume these DBs have different passwords) of Databases 1, 2, and 3 to use Supavisor's proxy even though we know Supavisor have these credentials saved internally. Is it clear to you guys? Is this the expected behavior?

[allyxcristiano]

Yep, cause otherwise they will be able to connect to all databases. While we consider that they should be able to connect only database for which user knows password. At least as the first usecase.

So what is being discussed here is a feature request really.

I got it @egor-romanov, this is a valid feature request because supavisor DB already has all this information there. We could have several clients calling the proxy and the routing to the proper DB will be made on Supavisor (as it's today) without the client needing to know any database credentials, the only credential might be a default token to connect to the proxy. Do you guys think would be worth converting this to a feature request or is this something already mapped?

[chethankumar4046]

@allyxcristiano How do you created Bearer token
create tenants.

[chasers]

@allyxcristiano closing this for now as I think you could do this with an auth_query as long as all your tenant dbs have the same role/passwords in their dbs: https://supabase.github.io/supavisor/connecting/authentication/#authentication-query