no pg_hba.conf entry for host "x.x.x.x"

[roks0n]

I've had a docker swarm setup running for months now but started receiving the following error today:
FATAL: no pg_hba.conf entry for host "10.0.7.2", user "xxxx", database "xxxx", SSL off

This is how my docker-compose.yml looked like initially:

version: '3.6'

services:
  web:
    image: myimage
    networks:
      - frontend
      - backend
    depends_on:
      - db
    deploy:
      replicas: 3
    environment:
      DEBUG: '0'
      DATABASE_URL: 'postgres://xxxx@xxxx:5432/xxxx'
    command: uwsgi --ini etc/uwsgi.ini --http-socket 0.0.0.0:8001
    logging:
      driver: "json-file"
      options:
        max-size: "10M"
        max-file: "20"

  db:
    image: postgres:9.6.9
    networks:
      - backend
    ports:
      - "5432:5432"
    volumes:
      - ./data/postgres:/var/lib/postgresql/data
    deploy:
      placement:
        constraints: [node.role == manager]
    logging:
      driver: "json-file"
      options:
        max-size: "10M"
        max-file: "20"

networks:
  frontend:
  backend:
    driver: overlay
    attachable: true

After reading how I could potentially solve this I also added this to the db's volume:
- ./pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf

with a modified pg_hba.conf where I added the following for testing purposes:
host all all 0.0.0.0/0 trust

Afterwords I removed the stack and deployed it again. I've checked the db container and pg_hba.conf reflected the changes but the issues was still there.

[roks0n]

Figured it out, had to comment out: host all postgres 0.0.0.0/0 reject from the pg_hba.conf as well along with adding host all all 0.0.0.0/0 trust.

Better if I use a custom username + only allow a specific ip range.

[cheerfulstoic]

Ran across this just now. Another approach if you don't care so much about configuring your postgres DB (or the database itself) it just to delete the mapped volume and let it be re-created

[PNinad]

Can someone explain me this solution in more detail ?

[logrusorgru]

Use

    command: postgres -c listen_addresses='*'

and remove volumes wiht down -v and then it will work for all IPs. May be there is a way to remove volume of the pg only, but I don't know it.

[duhaime]

In addition to the command change listed above, I also had to delete postgres-data 🚀

[vemv]

For anyone else finding this issue, I solved it with:

pg_hba.conf

local all all trust
host all all 0.0.0.0/0 trust

docker-compose.yml

+        volumes: 
+          - ./pg_hba.conf:/var/lib/foo/pg_hba.conf
+        command: ["postgres", "-c", "hba_file=/var/lib/foo/pg_hba.conf"]

ℹ️: such a Dockerfile is generaly fit for dev purposes only. See the post below for a security concern.

[yosifkit]

host all all 0.0.0.0/0 trust

⚠️WARNING⚠️: If you put this is your pg_hba.conf then do not expose any ports to the internet. Anyone who connects will have full passwordless access to your database. 😱 https://www.postgresql.org/docs/current/auth-trust.html