Azure pg/mysql not supportable?

[f4tq]

Azure Postgres (and mysql) connections all have user names that have '@' in them - that's just how it is with azure.

Go's libpg allows the driver and the connection string to be separated as URI'izing them doesn't always work especially if both the user and password have '@' in them. I need the non uri form shown in the example.

Crystal's will/pg also accounts for this but I can see no way to create a connection with crystal-db that doesn't try to force postgres:// (causing url encoding) to be used to create a connection.

For instance, this (taken right from azure portal) is the azure suggested connection string for my db:

host=autoess-pg.postgres.database.azure.com; dbname={your_database} user=admin1@autoess-pg password={your_password} port=5432 sslmode=require

my password also has an @.

The forced url encoding done by build_database makes the parsing by the actual driver fail.
No amount of massaging seems to work. I put in the following debug statement in my lib/pg/lib/pg/connection.cr

        conn_info = PQ::ConnInfo.new(context.uri)
puts "ConnINFO host: #{conn_info.host} db: #{conn_info.database}; user: #{conn_info.user} pw: #{conn_info.password} port: #{conn_info.port} ssl_mode: #{conn_info.sslmode}"
        @connection = PQ::Connection.new(conn_info)

which emits scrambled eggs:

ConnINFO host: autoess-pg password=foo@bar@n@ port=5432 sslmode=require db: ; user: host=autoess-pg.postgres.database.azure.com dbname=my_production user=chris pw:  port: 5432 ssl_mode: prefer

which I'm sure reflects the pool creation.

Is there a workaround where I can pass a string and the driver that I'm missing?

[straight-shoota]

The forced url encoding done by build_database makes the parsing by the actual driver fail.

What do you mean by that? If you're trying to pass a non-URI-formatted string to DB.build_database, that's obviously going to fail (even if PG implementation might support that).

Are you not able to pass a constructed URI like this?

URI.new(scheme: "postgres", host: "autoess-pg.postgres.database.azure.com", dbname: "{your_database}", user: "admin1@autoess-pg", password: "{your_password}")

As far as I can tell, URI encoding should correctly handle @ characters in usernames or passwords and end up correctly in PG's conn info.

---

Still: URI-based connection configuration isn't defined in any formal specification. It probably shouldn't be the only way to configure a database driver.

[f4tq]

Yeah, the URI route was the way to go.
For this local, docker-based pg.

 uri = URI.new( scheme: "postgres", host: "localhost", user: "grafana" , password: "gr@f@n@", port: 5432, path: "asdev")

The URI string ends up as postgres://grafana:gr%40f%40n%40@localhost:5432/asdev

Irregardless, no amount of twiddling the Azure Postgresql DB resulted in anything but a request for auth frame Frame::Authentication::Type::CleartextPassword.

So I guess the answer, at least for Azure Postgresql, is not supported as I'm guessing you don't want to support cleartext. FWIW, it is paired with sslmode=required.

Lots of chatter on stackoverflow etc on md5 / cleartext being the same with TLS but I'll open a ticket with Azure on this.
Thanks

[f4tq]

The big issue, azure style, is there is no access to pg_hba.conf...

[straight-shoota]

For cleartext support, see: will/crystal-pg#192
You should add your use case there 👍

[f4tq]

Oops, I already filed crystal-pg PR 218 which fixes it.

[beta-ziliani]

Are we good to close this one? Or are we still missing something?

[sdogruyol]

Should be fine to close, will/crystal-pg#218 has some great info on the topic