Routing by username? #509

Activity

1. levkk commented on Jul 14, 2023

   

   levkk

   on Jul 14, 2023

   Contributor

   More actions

   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.

   👍React with 👍2Reacted by Justin Plock and Ravi Soni
2. jplock commented on Jul 14, 2023

   

   jplock

   on Jul 14, 2023

   Author

   More actions

   Thank you, I'll test that out
3. jplock commented on Jul 14, 2023

   

   jplock

   on Jul 14, 2023

   Author

   More actions

   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: "*******" })
   
4. levkk commented on Jul 14, 2023

   

   levkk

   on Jul 14, 2023

   Last edited by levkk

   Contributor

   More actions

   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.
5. jplock commented on Jul 14, 2023

   

   jplock

   on Jul 14, 2023

   Author

   More actions

   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.

   👍React with 👍1Reacted by Lev Kokotov