Postgres: Latest container refuses connections from Docker network

[levkk]

I'm using the latest image of Postgres, and the newest container version rejects non-local connections over the Docker network with this error:

FATAL:  pg_hba.conf rejects connection for host "172.21.0.4", user "postgres", database "postgres", SSL off

172.21.0.4 is the IP of my app container on my Docker network.

To reproduce:

1. Run the container $ docker run postgres:10 with POSTGRES_PASSWORD=postgres
2. $ psql postgres://postgres:postgres@<ip-address>:5432/postgres

[wglambert]

psql takes the -h flag for determining hostname

$ docker run --rm -d -e MYSQL_ROOT_PASSWORD=root --network=test --name postgres postgres:latest

$ docker run --rm -d -e MYSQL_ROOT_PASSWORD=root --network=test --name postgres1 postgres
$ docker exec -it postgres1 bash

root@e7d4198a91ea:/# psql -h postgres -U postgres
psql (10.4 (Debian 10.4-2.pgdg90+1))
Type "help" for help.

postgres=#

On the other postgres container:

$ docker exec -it postgres bash
root@1dc2cfa2c520:/# psql -h postgres1 -U postgres
psql (10.4 (Debian 10.4-2.pgdg90+1))
Type "help" for help.

postgres=# 

[andywer]

@levkk Experienced the same issue just yesterday. A configuration file in the container was changed.

Here is a post-mortem, including a description what happened and how I fixed it. Hope that helps!

Not 100% sure whether it was some automatically-run script that screwed up or some kind of hack. But since you witnessed the same thing just yesterday, maybe it's really just some kind of bug somewhere :)

PS: I run the Postgres 10.4-alpine image in a docker swarm setup.

[swlab2]

Same issue here for postgres:latest

[levkk]

Same issue in postgres:9.6.10, using docker-compose. It's intermittent. When first created, the container works fine, when rebooted a couple times, the error starts popping up.

[andywer]

I think I know where 💩hits the fan...

When launching the container the entrypoint script will manipulate pg_hba.conf and add a "trust anyone" blank-check rule if no password has been set using the env vars.

https://github.com/docker-library/postgres/blob/35d6f4b68399cf2ffcb1c13c27003b92f53b9bc8/docker-entrypoint.sh#L82-L109

Of course for those of us who didn't set a password using env vars, but just logged into the database after setup, changing the password manually there, this behavior is rather unexpected and ☠️☠️☠️

@tianon Wouldn't it be possible to make that blind "trust everyone" feature an explicit opt-in option, like only do that if an $POSTGRES_PASSWORDLESS is set?

[levkk]

@andywer I don't think that this is the issue. My pg_hba.conf presently:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                trust
#host    replication     postgres        127.0.0.1/32            trust
#host    replication     postgres        ::1/128                 trust

host all all all md5

Note the last line which is correct configuration (I believe). However, when I run:

psql postgres://postgres:[email protected]:5432/postgres

(172.30.0.2 is the address of the container), I still get the same error:

psql: FATAL:  pg_hba.conf rejects connection for host "172.30.0.1", user "postgres", database "postgres", SSL off

Here is the Docker Compose settings:

postgres:
    build:
      context: ../
      dockerfile: ./docker/postgres/Dockerfile
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

Note the presence of POSTGRES_PASSWORD environment variable.

[yosifkit]

@tianon Wouldn't it be possible to make that blind "trust everyone" feature an explicit opt-in option, like only do that if an $POSTGRES_PASSWORDLESS is set?

The unfortunate part of PostgreSQL is that setting the password in the database is only part of securing the database.

@andywer, we would rather not add even more environment variables for each tiny configuration option. You can get the same outcome by using POSTGRES_PASSWORD and then do the same login after start to change rather than set the password.

@levkk, The pg_hba.conf that you show shouldn't cause a connection to be rejected and I am unable to reproduce with the postgres:10 image. What are you doing in your Dockerfile?

[levkk]

@yosifkit

My Dockerfile is:

FROM postgres:9.6.10

[levkk]

@yosifkit @andywer

Found the error in pg_hba.conf. The file gets prepended with this:

host all postgres 0.0.0.0/0 reject
host all pgdbadm 0.0.0.0/0 md5

It was on top of the instructions so I missed it when I copy/pasted my config in the previous post.

[yosifkit]

That really seems like: https://dba.stackexchange.com/q/215294. There is nothing in the image that would add these rules.

[levkk]

@yosifkit , I believe you're correct. I had a security hole in my config which opened the database port to the world, which in turn most likely caused this issue. Good thing this happened in staging... After a few days with proper configuration, the error did not reappear. I think I can close this issue now. Thank you for your help!

[swlab2]

For the docker case the problem disappeared after creating a new volume for postgres container. I haven't looked any deeper, but it seems to be that the issue is on the docker side.

[Immortalin]

I had exact same issue as @levkk. First time thought it was misconfigure so restarted from clean slate. Second time led me to here. For me this was the cause of the backdoor: https://www.techrepublic.com/article/how-to-fix-the-docker-and-ufw-security-flaw/

If your cloud host offers firewalls e.g. DigitalOcean, use it. Especially if you are using Kubernetes or Swarm etc. and relying on UFW to lock down the underlying node. Was caught in staging too. Thank god.

[pospispa]

$ docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
9035e2d870ec        postgres            "docker-entrypoint..."   15 seconds ago      Up 14 seconds       0.0.0.0:5432->5432/tcp   postgres-test
$ docker logs postgres-test 
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
syncing data to disk ... ok
Success. You can now start the database server using:
    pg_ctl -D /var/lib/postgresql/data -l logfile start
waiting for server to start....2018-09-28 18:54:57.000 UTC [43] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-09-28 18:54:57.011 UTC [44] LOG:  database system was shut down at 2018-09-28 18:54:56 UTC
2018-09-28 18:54:57.015 UTC [43] LOG:  database system is ready to accept connections
 done
server started
/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
2018-09-28 18:54:57.093 UTC [43] LOG:  received fast shutdown request
waiting for server to shut down....2018-09-28 18:54:57.093 UTC [43] LOG:  aborting any active transactions
2018-09-28 18:54:57.095 UTC [43] LOG:  worker process: logical replication launcher (PID 50) exited with exit code 1
2018-09-28 18:54:57.095 UTC [45] LOG:  shutting down
2018-09-28 18:54:57.102 UTC [43] LOG:  database system is shut down
 done
server stopped
PostgreSQL init process complete; ready for start up.
2018-09-28 18:54:57.212 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-09-28 18:54:57.212 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2018-09-28 18:54:57.213 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-09-28 18:54:57.225 UTC [52] LOG:  database system was shut down at 2018-09-28 18:54:57 UTC
2018-09-28 18:54:57.229 UTC [1] LOG:  database system is ready to accept connections

My understanding of the above docker logs output from postgres:latest is:

1. Postgres starts accepting connections, but refuses them if any.
2. Postgres is initializing and in this phase Postgres refuses any connections.
3. As soon as the initialization is finished Postgres shuts down.
4. Postgres starts up.
5. Now, Postgress is really accepting connections.

[tianon]

Postgres starts accepting connections, but refuses them if any.

Not exactly. Postgres starts accepting connections, but _only_ from within the container (only on the container-local Unix socket).

[pospispa]

Not exactly. Postgres starts accepting connections, but only from within the container (only on the container-local Unix socket).

Sorry, I should have described what I do and what behaviour I'm seeing.

I'm writing unit tests that do the below (everything is done on the same machine):

1. start Postgres in Docker container using command:

docker run --rm -d -p 5434:5434 --name=postgres-test -e POSTGRES_PASSWORD=postgres  postgres

so there's no persistent storage for the purpose of the unit tests.

1. immediately afterwards, connect to Postgres. I do this in a loop (try to connect, connection failed, wait for 1 second, try again). First 3 connection attempts fail with the below error message:

read tcp 127.0.0.1:52122->127.0.0.1:5432: read: connection reset by peer

I assume the behaviour of the Postgres container that rejects connection attempts for the first 3 seconds is not a bug, but a feature.

1. actual unit tests of my code, i.e. creating some records in Postgres, reading them, modifying them and deleting them.

[yosifkit]

I assume the behaviour of the Postgres container that rejects connection attempts for the first 3 seconds is not a bug, but a feature.

Correct, the socket is not opened, so it won't accept tcp connections until it has finished with initialization (initial database files creation, user/pass/db creation, inidb.d scipts).