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

[allyxcristiano]

Bug report

• [X] I confirm this is a bug with Supabase, not with my own application.
• [X] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

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, then the supavisor would retrieve internally the credentials of the tenant and would do the expected routing for us.

Right now I'm needing to pass the user prefix, the tenant id and the password of the database of the tenant, this won't make sense because we already have this password saved previously, right?

To Reproduce

• Setup a few tenants using the docker-compose.txt (change the extension to .yml);
• Run supavisor and its database;
• Register credentials for tenant1:

  
  curl --location --request PUT 'localhost:4000/api/tenants/84f9b050-6e82-4fda-a904-24958a7d184d' \
  --header 'accept: application/json' \
  --header 'Content-Type: application/json' \
  --header 'Authorization: Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpYXQiOjE2ODAxNjIxNTR9.U9orU6YYqXAtpF8uAiw6MS553tm4XxRzxOhz2IwDhpY' \
  --data '{
  "tenant": {
  "db_database": "tenant1",
  "db_host": "localhost",
  "db_port": 5432,
  "users": [
    {
      "db_password": "tenant1",
      "db_user": "tenant1",
      "mode_type": "transaction",
      "pool_size": 10
    }
  ]
  }
  }'

- Try to access the proxy with the password of the supavisor DB:

psql postgresql://tenant1.84f9b050-6e82-4fda-a904-24958a7d184d:password@localhost:7654/tenant1

Will fail with:

psql: error: connection to server at "localhost" (::1), port 7654 failed: error received from server in SCRAM exchange: Invalid client signature

![image](https://github.com/supabase/supavisor/assets/6070183/adbc9784-1e8e-41ac-ba70-7e7cad2bef3a)

- Try to access the proxy with the password of the tenant DB:

psql postgresql://tenant1.84f9b050-6e82-4fda-a904-24958a7d184d:tenant1@localhost:7654/tenant1

Will work
![image](https://github.com/supabase/supavisor/assets/6070183/b983e60b-8ea5-4694-a54d-df3ffcef2921)

## Expected behavior

Be able to connect passing only the password of supavisor's DB.

## System information

- OS: macOS & Linux
- Version of supavisor: https://github.com/supabase/supavisor/pull/96

[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]

@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.

[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?

[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]

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