Error: invalid authorization specification

Eiji · March 9, 2017, 11:17pm

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?

OvermindDL1 · March 9, 2017, 11:45pm

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.

michalmuskala · March 10, 2017, 6:21am

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.

Eiji · March 10, 2017, 11:14am

@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:

github.com/elixir-ecto/ecto

Unable to run `mix ecto.create` without password, but `mix ecto.migrate` works

opened 03:51PM - 18 Jan 16 UTC

closed 04:01PM - 18 Jan 16 UTC

Frost

I am running postgresql locally, and I allow my user to pretty much do everythin…g with any local database, out of convenience. The command line `psql` command automatically picks up my username, and tries to connect using that, and I would expect ecto to do the same if I don't provide one. However, this does not seem to be the case. Running `mix ecto.create` results in the following error: ``` ** (Mix) The database for Phoenixtest.Repo couldn't be created, reason given: psql: fe_sendauth: no password supplied ``` However, if I create the database myself, using `createdb my_app_dev`, then running `mix ecto.migrate` works fine. How to reproduce this: - configure some user to have full access to your local postgresql - create a new phoenix project (for example `mix phoenix.new foo`) - remove the database credentials from a config file (e.g. config/dev.exs) - run `mix ecto.create` and watch it fail - run `createdb foo_dev` - run `mix ecto.migrate` and it will reply with `Already up` This means I cannot run `mix test`, because that task recreates and migrates the database, which in turn means that I _have_ to have working database credentials, at least in my test config, if I ever want to be able to run `mix test`. I have tried digging around in the ecto source code, and I noticed that migrations and storage creation seem to be run in different parts of the code, but I couldn't manage to find where to change anything to allow this.

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?

OvermindDL1 · March 10, 2017, 3:33pm

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

andreheringer · April 3, 2020, 4:13pm

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?