CODEWITHSUNDEEP
postgresqldocker
RaulZahan
RaulZahan

Reputation: 123

Docker postgres image: no pg_hba.conf entry for host

I have a Docker image for DB (postgres is installed) and I try to run it but I get the following:

db | 2018-11-27 08:29:28.849 UTC [2808] FATAL: no pg_hba.conf entry for host "172.x.x.x", user "postgres", database "postgres", SSL off

I tried the following:

None of those work.

Upvotes: 6

Views: 19869

Answers (2)

Igor
Igor

Reputation: 1131

I want to share how to configure/tune up Postgres 17.2 on Alpine (recommended) Docker container on a custom port 5435 (optional). Volume for the PostgreSQL data is named as mydb and container name is db.

📝 docker-compose.yml

services:
  #[...]
  db:
    image: postgres:17.2-alpine3.20
    container_name: db
    restart: unless-stopped
    environment:
      POSTGRES_USER: db_user
      POSTGRES_PASSWORD: db_password
      POSTGRES_DB: my_db
    ports:
      - "5435:5432"
    command: -p 5435 # only if you use a custom port
    volumes:
      - mydb:/var/lib/postgresql/data
volumes:
  mydb:
    name: mydb

🛠️ Build containers

docker compose up -d

Container will not work for now, we have to tune up pg_hba.conf and postgresql.conf

👑 Tuning up postgres configs

# enter to the docker container

docker exec -it db sh

apk add nano
nano /var/lib/postgresql/data/pg_hba.conf

# edit these lines in block IPv4

#host    all             all             127.0.0.1/32            trust
# replace 127.0.0.1/32 to all and trust to md5
host    all             all             all            md5

# CTRL+O, CTRL+X after edit

nano /var/lib/postgresql/data/postgresql.conf

# edit these lines:
listen_addresses = '*'

# CTRL+O, CTRL+X after edit

🏆 Restart postgres container

docker compose up -d

or

docker restart db

Hope that helps!

Upvotes: 0

hanmari
hanmari

Reputation: 1484

I ran into the same problem getting my application container to talk to my postgres container. I fixed this with three steps:

  1. define the network ip address range for the docker network
# +----------------------+
# |  docker-compose.yml  |
# +----------------------+
networks:
  my_docker_network:
    driver: bridge
    ipam:
      config:
        - subnet: 10.5.0.0/16
  1. whitelist the same network ip address range in the postgres pg_hba.conf file
# +---------------+
# |  pg_hba.conf  |
# +---------------+
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             10.5.0.0/16             trust

I used trust to avoid authentication hassles, but you could specify md5, cert, or any other valid authentication method.

  1. allow postgres to listen to external hosts in the postgresql.conf file
# +-------------------+
# |  postgresql.conf  |
# +-------------------+
# - Connection Settings -
listen_addresses = '*'          # what IP address(es) to listen on;

Upvotes: 9

Related Questions