Routing by username?

[jplock]

Is your feature request related to a problem? Please describe.

I'm currently generating unique hostnames (with separate IP addresses) on a common port for customers to connect to my architecture. I'd like to consolidate all of this to a single hostname (with the same common port), but be able to route customers to specific databases based on the username they are authenticating with. Is this possible?

Describe the solution you'd like

Support username-based routing

Describe alternatives you've considered

https://github.com/pgbouncer/pgbouncer + https://github.com/awslabs/pgbouncer-rr-patch kind of achieves this
https://github.com/pg-sharding/spqr

Additional context
Add any other context or screenshots about the feature request here.

[levkk]

Hi,

Currently we route by username and database name, see get_pool (e.g. https://github.com/postgresml/pgcat/blob/main/src/client.rs#L528). Routing by username only is not specific enough when dealing with multiple databases proxied by the same pooler.

You can alias any database name to any database or server using database in the config (e.g. https://github.com/postgresml/pgcat/blob/main/pgcat.toml#L309). So as long as your client can provide both the username and a database name, which can be the same as the username, you can route them to the appropriate pool, which in turn will connect to the right server and Postgres database.

[jplock]

Thank you, I'll test that out

[jplock]

How difficult would it be to support AuthenticationCleartextPassword (auth_code 3) from https://www.postgresql.org/docs/12/protocol-message-formats.html ?

I'm trying to use pgcat against a PostgreSQL-compatible SaaS service that says its wire compatible, but was receiving these messages while debugging:

pgcat-pgcat-1  | [2023-07-14T19:45:03.894469Z TRACE pgcat::server] Sending StartupMessage
pgcat-pgcat-1  | [2023-07-14T19:45:03.906641Z TRACE pgcat::server] Message: R
pgcat-pgcat-1  | [2023-07-14T19:45:03.906713Z TRACE pgcat::server] Auth: 3
pgcat-pgcat-1  | [2023-07-14T19:45:03.906721Z ERROR pgcat::server] Unsupported authentication mechanism: 3

I've never coded in Rust before, but I think if we define a CLEARTEXT_PASSWORD constant of

// AuthenticationCleartextPassword
pub const CLEARTEXT_PASSWORD: i32 = 3;

in constants.rs then match on it here https://github.com/postgresml/pgcat/blob/main/src/server.rs#L398, we could send in the password in cleartext.

/// Send password challenge response to the server.
/// This is the cleartext challenge.
pub async fn cleartext_password<S>(
    stream: &mut S,
    password: &str,
) -> Result<(), Error>
where
    S: tokio::io::AsyncWrite + std::marker::Unpin,
{
    let password = password.as_bytes();
    let mut message = BytesMut::with_capacity(password.len() as usize + 5);

    message.put_u8(b'p');
    message.put_i32(password.len() as i32 + 4);
    message.put_slice(&password[..]);

    write_all(stream, message).await
}

which I don't think this is correctly sending the password to the server because the logs end up printing:

pgcat-pgcat-1  | [2023-07-14T20:09:47.856026Z TRACE pgcat::server] Sending StartupMessage
pgcat-pgcat-1  | [2023-07-14T20:09:47.867898Z TRACE pgcat::server] Message: R
pgcat-pgcat-1  | [2023-07-14T20:09:47.867945Z TRACE pgcat::server] Auth: 3
pgcat-pgcat-1  | [2023-07-14T20:09:47.880749Z TRACE mio::poll] deregistering event source from poller
pgcat-pgcat-1  | [2023-07-14T20:09:47.881455Z ERROR pgcat] Pool error: ServerStartupError("message code", ServerIdentifier { username: "*******", database: "*******" })

[levkk]

For client authentication, we only support MD5. For server authentication,we support MD5 and SCRAM (SASL). If you want to add cleartext for either, a PR is welcome!

In terms of difficulty, clear text is the simplest one, so it should be pretty much like how you described. Just make sure the payload is correct. Client code is very dependent of md5, so it needs to be refactored a bit and maybe a config option to indicate which auth method we want the pooler to use. For the server, it's just another algorithm, which should be pretty simple.

[jplock]

Looks like I’m running into what has already been reported. #487

The system I’m connecting to is using TLS and clear text as others have mentioned. Will look into creating a PR.