Postgrex doesn't send password? "(Postgrex.Error) FATAL 28000 (invalid_authorization_specification)"

Judging by the reason given why a connection is rejected I’d first try adding this to my Ecto.Repo configuration:

config :your_app, YourApp.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: System.get_env("POSTGRES_USER"),
  password: System.get_env("POSTGRES_PASSWORD"),
  database: System.get_env("POSTGRES_DATABASE"),
  hostname: System.get_env("POSTGRES_HOST"),
  port:     System.get_env("POSTGRES_PORT"),
+  ssl: true,
  pool_size: 10

i.e. add ssl: true to your DB config.

Thanks, @dimitarvp ! I tried that, it didn’t work. I don’t use ssl.

Here is the snippet from ./releases/0.1.1/runtime.exs (I edit it right in container):

 if config_env() == :prod do                                                                                                                                 
   IO.puts("configuring postgres")                                                                                                                           
   config :alex3, Alex3.Repo,                                                                                                                                
   database: "postgres",                                                                                                                                     
   username: "postgres",                                                                                                                                     
   password: "postgres",                                                                                                                                     
   hostname: "pg" 

Output:

/app # ./bin/migrate
configuring postgres
13:35:32.049 [error] Postgrex.Protocol (#PID<0.167.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) pg_hba.conf rejects connection for host "10.1.119.175", user "postgres", database "postgres", no encryption

If I add ssl: true:

/app # ./bin/migrate
configuring postgres
13:36:09.899 [error] Postgrex.Protocol (#PID<0.167.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: Options (or their values) can not be combined: [{verify,verify_peer},
                                                {cacerts,undefined}] - {:options, :incompatible, [verify: :verify_peer, cacerts: :undefined]}

Ah, silly me, I think SSL is used indeed:

/app # psql $DATABASE_URL
psql (15.5, server 15.2 (Ubuntu 15.2-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=# 

So the fix is:

ssl: true,
ssl_opts: [verify: :verify_none]

Thanks a lot!

This is still less secure btw. If I were you I’d try to add the appropriate certificates to your production system or deployment container. But admittedly I don’t have a ready material for it, it’s a specific pursuit depending on project and deployment target and I keep no records of how I or my colleagues did it.

Maybe this thread will help: How to connect to postgrex with SSL certificate?

…Or this one: Failing to Understand ssl_opts in Elixir Phoenix Ecto's Postgres Adapter

…Or this forum-wide search: Search results for 'postgres ssl' - Elixir Programming Language Forum

Thanks for suggestion. I should have mentioned it’s a single-node cluster with postgres-operator. So, no immediate danger in using insecure configuration (famous last words, haha). I’ll fix the certificate issue.. eventually).

PS today I learned postgres can configured to authorize only on encrypted connections (“hostnossl all all all reject” in pg_hba.conf). I didn’t know that’s possible. I wish there was a better error message for that…

Well it could be better indeed but IMO the message below is relatively clear: your pg_hba.conf configuration prevents the connection from being made because the connection has the following parameters:

To me “no encryption” stuck out like a sore thumb. But that’s why we ask other people, sometimes the obvious is biting us on the nose and we don’t notice. Happened dozens of times with me as well. Maybe hundreds.