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

Phoenix Forum Questions / Help
,
1

Hello everyone!

I’m trying to deploy my blog application into production, and I’m facing a very weird issue: Postgrex doesn’t want to connect to my postgres instance (k8s environment).

iex(10)> {:ok, pid}=Postgrex.start_link(hostname: "pg", username: "postgres", password: "e4aay78iiRiBuzbbnrnD03GqWh9ncVS0376xSpNe8TBdtq6MUvjZjky71Acj402e", data
base: "postgres")                                                                                                                                               
{:ok, #PID<0.1717.0>}                                                                                                                                           
                                                                                                                                                                
12:07:28.421 [error] Postgrex.Protocol (#PID<0.1719.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

Now the strange part: psql connects without problems:

/app # export PGPASSWORD="e4aay78iiRiBuzbbnrnD03GqWh9ncVS0376xSpNe8TBdtq6MUvjZjky71Acj402e"
/app # psql -h pg -U postgres -d postgres -c 'SELECT 1'
 ?column? 
----------
        1
(1 row)

I’ve been fighting with the problem for the second day in a row, and I’m running out of ideas. I tried everything I could, including using different dockerfile (I used both my custom image based on alpine, and also mix phx.gen.release --docker). My best guess is that for some reason postgrex doesn’t send password, but I can’t figure why. I tried to set shorter password, that didn’t help. From strace I see that it seems postgrex doesn’t even try to send password:

1235  writev(20, [{iov_base=NULL, iov_len=0}, {iov_base="\0\0\0)\0\3\0\0user\0", iov_len=13}, {iov_base="postgres", iov_len=8}, {iov_base="\0database\0", iov_le
n=10}, {iov_base="postgres", iov_len=8}, {iov_base="\0\0", iov_len=2}], 6 <unfinished ...>

The problem only occurs in my k8s environment, same code works fine locally in docker compose.

Since nobody else reported this issue, I suspect there is something wrong in my environment, but I can’t figure what. Do you guys have any ideas?

PS just in case, I’ll change password after we resolve the topic.

2

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.

3

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]}
4

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!

5

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

6

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…

7

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. :slight_smile: Maybe hundreds.

4 Likes