Clojurians Log v2
sql
Hi! Has anyone experienced issues with usernames that have either "-" or "_" in them when using HikariCP with next.jdbc?
I'm currently experiencing weird behavior as when I connect to my PostgreSQL database without connection pools everything works fine, but when I add HikariCP into the equation I start getting errors like FATAL: no pg_hba.conf entry for host "192.168.99.1", user "foo", database "test-dev", SSL on, given I'd have username like foo-bar (for the sake of argument).
this behavior happens both for usernames that have "-" or "_" in them.
The authentication started to working when I modified all the usernames to not have those, but I have multiple infras with other tech stacks that work fine with PG and delimited usernames and I do like to use postfixes to differentiate users by type.
hmm.. it seems I get the same issue from com.impossibl.postgres.jdbc.PGDriver and org.postgresql.Driver
If I've understood correctly does that mean that the username get's malformed either by next.jdbc (I took a look at the code though, and to me it looks just fine!) or then by HikariCP?
aha! 🤦 ... it seems that I had db-specwith :user instead of :username. goddamnit.. yeah forget it guys 😄
it somehow magically managed to connect without the pool when i had :user as the keyword for username
hmm.. actually that's a bit confusing.. so the documentations state that :user is used to define the connection username, yet when using connection pool the db-spec needs to use :username ? Is this documented somewhere?
oh lookie! it actually is! No problem there 😄 just needed to RTFM
Try to avoid - in username and database (and table etc..) names. Otherwise you'll be escaping them all, each time.
<https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS>
Yeah I'd definitely avoid them in tablenames. Usually I use specific naming for local dev infra that has some differentiation like '-dev' and '-test' for databases and same goes with some usernames (as I rarely type any queries manually that concern these) Production is different of course.