Unable to run `mix ecto.migrate` without password using PostgreSQL peer authentication

[jarmo]

I'm trying to use Phoenix + Ecto with PostgreSQL-s peer authentication. Doesn't matter how I try, I keep getting errors like this, when running mix ecto.migrate:

[error] GenServer #PID<0.504.0> terminating
** (RuntimeError) Connect raised a KeyError error. The exception details are hidden, as
they may contain sensitive data such as database credentials.

    (elixir) lib/keyword.ex:386: Keyword.fetch!/2
    (postgrex) lib/postgrex/protocol.ex:610: Postgrex.Protocol.auth_md5/4
    (postgrex) lib/postgrex/protocol.ex:504: Postgrex.Protocol.handshake/2
    (db_connection) lib/db_connection/connection.ex:135: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol
** (exit) exited in: :gen_server.call(#PID<0.504.0>, {:checkout, #Reference<0.3638587783.2274885638.15614>, true, :infinity}, 5000)
    ** (EXIT) an exception was raised:
        ** (RuntimeError) Connect raised a KeyError error. The exception details are hidden, as
they may contain sensitive data such as database credentials.

            (elixir) lib/keyword.ex:386: Keyword.fetch!/2
            (postgrex) lib/postgrex/protocol.ex:610: Postgrex.Protocol.auth_md5/4
            (postgrex) lib/postgrex/protocol.ex:504: Postgrex.Protocol.handshake/2
            (db_connection) lib/db_connection/connection.ex:135: DBConnection.Connection.connect/2
            (connection) lib/connection.ex:622: Connection.enter_connect/5
            (stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
    (db_connection) lib/db_connection/poolboy.ex:112: DBConnection.Poolboy.checkout/3
    (db_connection) lib/db_connection.ex:928: DBConnection.checkout/2
    (db_connection) lib/db_connection.ex:750: DBConnection.run/3
    (db_connection) lib/db_connection.ex:1141: DBConnection.run_meter/3
    (db_connection) lib/db_connection.ex:592: DBConnection.prepare_execute/4
    (ecto) lib/ecto/adapters/postgres/connection.ex:86: Ecto.Adapters.Postgres.Connection.execute/4
    (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6
    (ecto) lib/ecto/adapters/sql.ex:198: Ecto.Adapters.SQL.query!/5
    (ecto) lib/ecto/adapters/postgres.ex:96: anonymous fn/4 in Ecto.Adapters.Postgres.execute_ddl/3
    (elixir) lib/enum.ex:1925: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/adapters/postgres.ex:96: Ecto.Adapters.Postgres.execute_ddl/3
    (ecto) lib/ecto/migrator.ex:44: anonymous fn/2 in Ecto.Migrator.migrated_versions/2
    (ecto) lib/ecto/migrator.ex:300: Ecto.Migrator.verbose_schema_migration/3
    (ecto) lib/ecto/migrator.ex:157: Ecto.Migrator.run/4
    (ecto) lib/mix/tasks/ecto.migrate.ex:83: anonymous fn/4 in Mix.Tasks.Ecto.Migrate.run/2
    (elixir) lib/enum.ex:765: Enum."-each/2-lists^foreach/1-0-"/2
    (elixir) lib/enum.ex:765: Enum.each/2
    (mix) lib/mix/task.ex:316: Mix.Task.run_task/3
    (mix) lib/mix/cli.ex:79: Mix.CLI.run_task/2

Here's how to reproduce this issue:

$ mix phx.new --version
Phoenix v1.3.4
$ mix phx.new --no-brunch hello
* creating hello/config/config.exs
...
Fetch and install dependencies? [Yn] y
* running mix deps.get
* running mix deps.compile
...
$ cd hello
$ sudo -u postgres createdb -O $(whoami) hello_dev
$ psql hello_dev -c "select 1"
 ?column?
----------
        1
(1 row)

config/dev.exs has this regarding database:

config :hello, Hello.Repo,
  adapter: Ecto.Adapters.Postgres,
  database: "hello_dev"

There's ecto with a version of 2.2.10 and phoenix_ecto with a version 3.4.0 in mix.lock.

Am I missing something or is the only way of using PostgreSQL with Ecto to create a specific user and password?

I've also written about this issue in StackOverflow and didn't get any help.

[josevalim]

What is your postgrex version?

[josevalim]

I believe this issue is relevant to you: elixir-ecto/postgrex#356

1. make sure you are setting the username in your config files (I assume you have set it but you did not include it in your report for security)

2. check your pg_hba configuration and make sure that peer is also available for TCP

3. In case you want to use sockets, instead of hostname, set the socket_dir or the socket option, as explained in the postgres documentation: https://hexdocs.pm/postgrex/0.13.5/Postgrex.html#start_link/1

[jarmo]

postgrex version is 0.13.5.

No, I have not username in my configuration and adding it there doesn't change anything.

Why would I need to configure anything in Postgresql? As was also mentioned in StackOverflow thread then using similar configuration in Rails (without hostname, username or password) works as expected. This means that postgrex probably doesn't (as also implies your referenced issue). As I understand then this issue is not related with ecto, but rather with postgrex itself.

If that's the case then go ahead and close this issue.

[josevalim]

That's because Rails defaults to reading from a socket, we don't have such default. So you need to set :socket_dir pointing to the PG socket directory in your machine OR you have to make sure PG accepts peer connections for http too. Note different OSes will have different defaults here.

[josevalim]

There may be a chance you don't need a username if you set :socket_dir, you will have to try it out. It is also worth mentioning that we connect to PG using our own TCP client while rails likely uses the C lib, and this may cause differences.

[jarmo]

Managed to make it work with the following configuration:

config :hello, Hello.Repo,
  adapter: Ecto.Adapters.Postgres,
  database: "hello_dev",
  socket_dir: "/var/run/postgresql"

socket_dir value is taken from postgresql.conf unix_socket_directories value. Note that there is no username specified in the configuration.

It would be great if postgrex/ecto would fall back to default socket_dir if no user/password and hostname is specified in the configuration. You can probably say it better which library's responsibility should it be.

Thank You for all your help!

[josevalim]

@jarmo we can't do this quite yet because we still support old Erlang/OTP versions which did not have socket support. So trying to push a socket will lead to a worst error down the line. Maybe in the future, when we require Erlang/OTP 20+, then we can default to a socket. Glad to see this address though!