Unable to use SSL-required database

[reesericci]

Setting the environment variable PGSSLMODE="verify-full" isn't doing the trick to get squirrel to connect to my Xata-hosted database which requires SSL:

% export PGSSLMODE="verify-full"
% gleam run -m squirrel

Compiled in 0.04s
Running squirrel.main
Error: Cannot authenticate (no-method)
I ran into an unexpected problem while trying to authenticate with the Postgres
server. This is most definitely a bug!

Please open an issue at
https://github.com/giacomocavalieri/squirrel/issues/new with some
details about what you where doing, including the following message:

Expected: AuthMethod, Got: BeErrorResponse(Set(dict.from_list([#(Message("SSL required"), [])])))

[giacomocavalieri]

Hello! Thank you for reporting this, I think it might be related to #32.
This would require implementing SSL communication when talking to a Postgres server that requires it. I'm not very experienced with this so it might take a while unless someone wants to try and help!

[AlexanderVanDamme]

Im having the same issue! (trying to connect to a neon database), i'll might want to try to fix it myself (but im also not familiar with ssl, so don't expect anything)

[AlexanderVanDamme]

I did a little bit of research and these are my findings: mug (package used to make the connection) doesn't support ssl as for now, so there's no immediate quick fix.

With pog, i can already make a connection to my neon db (using ssl), this just uses the following erlang function:
@external(erlang, "pog_ffi", "connect")
pub fn connect(a: Config) -> Connection

we probably cant just use pog right? so i guess ssl needs to be added to mug

or maybe something should be done in squirrel itself idk

[giacomocavalieri]

Yeah sadly we can't use pog as it doesn't expose the lower level messages exchanged between the Postgres backend and the application. So I think our best bet might be to get mug to support ssl (even though I don't really know what that would entail or if mug is the right lib at all 😬), or maybe we could use glisten?

[AlexanderVanDamme]

Maybe using glisten would be a good idea indeed. It doesn't seem an easy refactor! (for me at least, i tried 😅)

I also checked mug's open pr's, there is someone that was implementing ssl there: lpil/mug#10
But i see the last 2 months there was no movement on that pr, but maybe sometime it will get approved and squirrel can start using it 🤞🏻

[karlsson]

I am not sure you can use mug with (upcoming) ssl support right out of the box. For Postgres it seems that you first need to establish a TCP connection and do a "STARTTLS" command before upgrading to TLS.
Here is the negotiation in epgsql.

[karlsson]

Correction, it seems the mug PR for ssl has support for upgrading a TCP socket to SSL. 👍

[karlsson]

With the reservation of that I do not verify the peer due to having self-signed certs and that mug (correctly) complaints about this I am able to use an SSL enabled mug.
In gleam.toml I pull in the PR from lpil/mug#10
mug = { git = "https://github.com/arnu515/mug.git", ref="main" }

And then update the connect function in squirrel/internal/database/postgres_protocol.gleam

pub fn connect(host, port, timeout) {
  let options =
    mug.ConnectionOptions(
      host: host,
      port: port,
      timeout: timeout,
      tls_opts: mug.NoTls,
    )

  use socket <- try(mug.connect(options))
  let tls_ver_method = mug.DangerouslyDisableVerification
  // mug.Certificates(
  //   False,
  //   option.Some(mug.PemEncodedCaCertificates("/etc/ssl/certs/mug_ca.crt")),
  //   [],
  // )
  use socket2 <- try(maybe_ssl(socket, tls_ver_method))
  Ok(Connection(socket: socket2, buffer: <<>>, timeout: timeout))
}

fn maybe_ssl(socket, tls_ver_method) {
  use _ <- try(mug.send(socket, <<8:size(32), 80_877_103:size(32)>>))
  case mug.receive(socket, 500) {
    Ok(<<"S">>) -> mug.upgrade(socket, tls_ver_method, 1000)
    // Ok(<<"N">>)
    Ok(_) -> Ok(socket)
    Error(reason) -> Error(reason)
  }
}

And get:

>  gleam run -m squirrel
   Compiled in 0.09s
    Running squirrel.main
🐿  Generated 1 query!