Error: invalid authorization specification

Questions & Help Questions
1

Hey, I got an error when deploying Phoenix app:

** (Mix) The database for MyApp.Repo couldn’t be created: FATAL (invalid_authorization_specification): Ident authentication failed for user “postgres”

17:58:29.531 [error] GenServer pid<0.202.0> terminating
** (Postgrex.Error) FATAL (invalid_authorization_specification): Ident authentication failed for user “postgres”
(db_connection) lib/db_connection/connection.ex:148: DBConnection.Connection.connect/2
(connection) lib/connection.ex:622: Connection.enter_connect/5
(stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol

Here is my /var/lib/pgsql/9.6/data/pg_hba.conf:

local   all             all                                     md5
# IPv4 local connections:
host    all             all             localhost               ident
# IPv6 local connections:
host    all             all             ::1/128                 ident

I already checked password with: psql -U postgres and of course restarted service: sudo systemctl start haproxy.

I’m using Amazon EC2 with RedHat 7.3

What am I missed?

2

For note, psql might use a named pipe instead of a port, make sure you can connect to the port from the account that the server is running on.

3

Yes, psql will use the unix socket by default. To test the tcp connection (the one used by ecto), you’d need to run psql -h localhost -U postgres.

4

@michalmuskala and @OvermindDL1: thanks, how to change it to listen on port? I saw port configuration in postgresql.conf, but uncommenting it not and restarting not works.

psql -h localhost -U postgres
psql: FATAL: Ident authentication failed for user “postgres”

Looks like unix sockets are turn on by default with localhost configuration, but this is required by ecto:
https://github.com/elixir-ecto/ecto/issues/1207#issuecomment-172570064

Note: this configuration now works:

local   all             all                                     md5  
 # IPv4 local connections:
host    all             all             127.0.0.1/32            md5
 # IPv6 local connections:
host    all             all             ::1/128                 ident

but is md5 safe?

5

As long as it is only allowed from localhost (it should be based on the above), then md5 is fine. :slight_smile:

6

I’m using Fedora 30 and edited the pg_hba.conf file to bo:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 ident

After that I tried to restart the postgres service and run psql -h localhost -U postgres but I’m still stuck will authentication failed.

Am I missing something?