Cannot Get Ecto/Postgrex to Connect to PostgreSQL

Questions / Help
, ,
1

The Problem

I am following the Getting Started Guide at https://hexdocs.pm/ecto/getting-started.html, trying to set up in a docker container. The steps in the guide work on my physical machine, but when I follow them for the container, I get the following error when I run mix ecto.create at the appropriate step:

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

    (postgrex) lib/postgrex/messages.ex:371: Postgrex.Messages.decode_auth_type/1
    (postgrex) lib/postgrex/messages.ex:64: Postgrex.Messages.parse/3
    (postgrex) lib/postgrex/protocol.ex:2004: Postgrex.Protocol.msg_decode/1
    (postgrex) lib/postgrex/protocol.ex:1978: Postgrex.Protocol.msg_recv/3
    (postgrex) lib/postgrex/protocol.ex:589: Postgrex.Protocol.auth_recv/3
    (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
Last message: nil
** (Mix) The database for Friends.Repo couldn't be created: an exception was raised:
    ** (RuntimeError) Connect raised a FunctionClauseError error. The exception details are hidden, as
they may contain sensitive data such as database credentials.

        (postgrex) lib/postgrex/messages.ex:371: Postgrex.Messages.decode_auth_type/1
        (postgrex) lib/postgrex/messages.ex:64: Postgrex.Messages.parse/3
        (postgrex) lib/postgrex/protocol.ex:2004: Postgrex.Protocol.msg_decode/1
        (postgrex) lib/postgrex/protocol.ex:1978: Postgrex.Protocol.msg_recv/3
        (postgrex) lib/postgrex/protocol.ex:589: Postgrex.Protocol.auth_recv/3
        (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

The Situation/Configuration

I have set up Postgres in my docker container so that the postgres user has a password, and I have used that password in the config/config.exs file. I know the password works, as I can use psql with it. The config file mentioned looks like this:

use Mix.Config

config :friends, Friends.Repo,
  adapter: Ecto.Adapters.Postgres,
  database: "friends_repo",
  username: "postgres",
  password: "<password>",
  hostname: "localhost"


config :friends, ecto_repos: [Friends.Repo]

Now, since there is always a request for the pg_hba.conf file in similar situations, I provide that below. (As this is a development machine, I am less concered about security than I am about getting something working in the first place.)

local   all             postgres                                md5
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# 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                 md5

Finally, the following are the versions of related applications and tools.

Postgres

                                                    version
-----------------------------------------------------------------------------------------------------------------------------
     PostgreSQL 10.5 (Ubuntu 10.5-0ubuntu0.18.04) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit

Elixir

Erlang/OTP 21 [erts-10.0.5] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:1] [hipe]

Elixir 1.7.2 (compiled with Erlang/OTP 20)

Running Command: mix deps.update

Resolving Hex dependencies...
Dependency resolution completed:
Unchanged:
  connection 1.0.4
  db_connection 1.1.3
  decimal 1.5.0
  ecto 2.2.10
  poolboy 1.5.1
  postgrex 0.13.5

Final Notes

I’ve read similar topics and problems on this forum, on github, and other places found from Google, but the answers I found did not help. Thus, my hope is that someone here can provide some direction and help get things working.

Thank you in advance.

2

I just followed the guide and it seems pretty good.
Can you verify your connections ports or share a git with only the parts needed?

1 Like
3

Can you confirm where you are running the mix command? I.e. on your host, or in the container. Since it’s pointing to localhost I am guessing you’re trying to authenticate against your local instance rather than the container.

Posting your docker-compose or Dockerfile will be helpful

4

Due to things not quite within my control, I must go against Docker best practices. I’ve been requested to run the application from within the container. Thus, I start a container based on an image built from the below Dockerfile.

FROM ubuntu:18.04

RUN DEBIAN_FRONTEND=noninteractive apt-get update && \
apt-get install -y vim && \
apt-get install -y curl && \
apt-get install -y locales && \
locale-gen 'en_US.UTF-8'

ENV LANG en_US.UTF-8
ENV LANGUAGE en_US:en
ENV LC_ALL en_US.UTF-8

COPY ./database/config/postgresql.conf /tmp/config_files/postgresql.conf
COPY ./database/config/pg_hba.conf /tmp/config_files/pg_hba.conf
COPY ./database/sql_scripts/build_db.sql /tmp/sql_scripts/build_db.sql

RUN DEBIAN_FRONTEND=noninteractive apt-get update && \
apt-get install -y postgresql postgresql-contrib && \
mv /tmp/config_files/postgresql.conf /etc/postgresql/10/main/postgresql.conf && \
mv /tmp/config_files/pg_hba.conf /etc/postgresql/10/main/pg_hba.conf && \
chown postgres /etc/postgresql/10/main/pg_hba.conf

# When I try to install esl-erlang or elixir (especially elixir) using apt-get, there is a timeout.
# Thus, a direct install from the packages
COPY ./packages/esl-erlang_21.0.5-1_ubuntu_artful_amd64.deb /esl-erlang_21.0.5-1_ubuntu_artful_amd64.deb
COPY ./packages/elixir_1.7.2-1_ubuntu_artful_amd64.deb /elixir_1.7.2-1_ubuntu_artful_amd64.deb

RUN DEBIAN_FRONTEND=noninteractive \
dpkg -i esl-erlang_21.0.5-1_ubuntu_artful_amd64.deb; \
dpkg -i elixir_1.7.2-1_ubuntu_artful_amd64.deb; \
apt-get install -fy

COPY ./docker/keep_alive.sh /opt/docker_boot.sh

RUN [ "chmod", "+x", "/opt/docker_boot.sh" ]

CMD "/opt/docker_boot.sh"

The docker_boot script starts postgres, and when I exec into the container, I set the password from the postgres user. After that, I reload/restart postgres and test the password. In this way, I ensure the password is working.

I then follow the directions in the tutorial, and run into the problem discussed. For now, I can use a VM as a workaround, but I’m somewhat pressured to make this work. (That is, running postgres in a docker container that can also run elixir.)

5

Can you connect to postgres whilst connected to the container, e.g. via psql?

Not to get into a debate about how to best use docker etc, but that looks a little over complicated to me. You probably just need a docker-compose file and can use pre-built images which set all this up. Compose will also handle the networking aspect for you so you app and database can live in different containers. Check this example repo: https://github.com/nicbet/docker-phoenix

6

My current assumption is a mismatch between the protocol expected by postgrex and that spoken by postgresql, but that really is an assumption.

Anyway, You really should convince your client to use separate containers for separate services. In the single container version, postgres could crash and your elixir application would spam you with error messages regarding database inavailability. this will slowly bubble up the supervision tree and eventually crash your application (depending on your config this may take minutes to hours), only then docker is able to restart the container (and only if you did your entrypoint right).

In a separated container world, postgres would crash, docker would recognize and restart it instantly, downtime less than a second…

7

First, in response to all the comments about the best way to use docker, I completely agree. I have already spoken to the benefits of separate containers and processes. Had this been a possibility, I’m doubtful I would have had any problems.

Yes, this was the first thing I checked against. I can connect to postgres with psql.

Do you happen to know a way to confirm whether this is the case? And if it happens to be, any recommendations to correct it?