Postgres SSLMode not supported via crystal-pg

[steve-bitdotio]

After reading https://github.com/will/crystal-pg/blob/cafe0f715019059f127b95156bd3c8dc1015913e/src/pq/connection.cr#L39-L58 it is clear that crystal-pg supports using postgres over SSL.

This is presumably an issue since connection string connection parameters are used for connection pooling instead.

At minimum, if all postgres connections parameters are not supported by avram, then avram should expose field(s) for explicitly enabling SSL.

[jwoertink]

Right now, Avram builds a connection string that looks like postgres://user:pass@host:5432/db?..... I've never used postgres over SSL before. Any idea what that would look like?

[robacarp]

The postgresql connection string docs show a query param called sslmode. It takes a bunch of possibilities, too much to enumerate here.

[jwoertink]

oh, so it's just a query param like db?sslmode=required? If that's the case, then you can do that with Avram already.

Avram::Credentials.new(
      database: "db",
      hostname:  "localhost",
      port: 5432,
      username: "postgres",
      password: "postgres",
      query: "sslmode=required"
    )

[jmo-qap]

I think @steve-bitdotio is indicating those connection parameters are stripped and not passed down to crystal-pg but instead only used for connection pooling?

[steve-bitdotio]

Thanks for the suggestion @jwoertink !

Unfortunately, as @jmo-qap highlights, hard-coding the query field in the avram credentials did not work in my testing.
Is it possible that avram is ignoring or otherwise dropping any additional user specified "query" parameters?

Thanks again for your help!

[jwoertink]

Is it possible that avram is ignoring or otherwise dropping any additional user specified "query" parameters?

I don't think so...

When you create a new Credentials object, it builds the URL on initialize here

avram/src/avram/credentials.cr

Line 12 in 81add34

@url = build_url

That builds the connection string postgres://user:pass@host:port/db?query setting the query params here

avram/src/avram/credentials.cr

Line 96 in 81add34

set_url_query(io)

When the database needs to connect it grabs that url here

avram/src/avram/database.cr

Lines 144 to 146 in 81add34

	protected def url
	settings.credentials.url
	end

which is passed to the connect here

avram/src/avram/database.cr

Lines 166 to 168 in 81add34

	protected def connection : Avram::Connection
	Avram::Connection.new(url, database_class: self.class)
	end

and then that it passed on to Crystal-DB

avram/src/avram/connection.cr

Line 17 in 81add34

DB.open(@connection_string)

You can verify this if you open up that connection.cr file in your lib/avram/src/avram/connection.cr and just add some puts debugging.

Now, if you're able to confirm the query is being set in the Credentials, but not getting there, then we will need to call sherlock holmes and possible a shaman since that'll be some strange stuff going on 😂

[steve-bitdotio]

Thanks for the code walkthrough @jwoertink !

I took your advice and added some print debugging. I can confirm that my sslmode=require query parameter is being passed to crystal-g via the connection string, as you strongly suspected.

I guess I'll go take a closer look at crystal-pg now?

[jwoertink]

Yeah, it sounds like Crystal PG may have the code for the SSL, but it's not actually using it?

[steve-bitdotio]

There must be something wrong with how SSL is implemented in crystal-pg, since upon further reading it looks like "prefer" is the default SSL mode in crystal-pg (and that is not working with my postgres database for some reason) 🤔

I will continue discussion in will/crystal-pg#240.

In any case I think we can close this issue. Thanks for the help!

@jwoertink as an off topic aside, I noticed that no connection parameters are passed to the setup createdb when creating a lucky project. Figured Id bring that to youre attention while we are here. Not sure if that would warrant an issue.

[jwoertink]

Ok, cool. Thanks for the followup. Hopefully something can be figured out on that end.

As for the createdb, that happens here

avram/src/avram/migrator/runner.cr

Line 66 in 81add34

run "createdb #{cmd_args}"

Looks like we just pass -U -h -p and the db name... Passing more info probably falls under #926 where I'd like to just get rid of the client tools requirement if we can rather than trying to accomodate all these edge cases. Feel free to chime in on that issue.