Connecting to a PSQL dockerized DB from host

Viewed 89

I've got some trouble configuring a PostgreSQL database in a Docker container. My main issue is I want to access it from my host directly.

My container configuration looks like:

version: '3'

services:
  db:
    image: postgres:10.4
    environment:
      - POSTGRES_USER=user
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=mydb
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./var/docker/db/pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf

volumes:
  pgdata:

My pg_hba.conf file is the default one, with two extra added lines:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust

# IPv4 local connections:

########### LINE ADDED ###########
host    all             all             0.0.0.0/0               md5

host    all             all             127.0.0.1/32            trust

# IPv6 local connections:

########### LINE ADDED ###########
host    all             all             ::/0                    md5

host    all             all             ::1/128                 trust

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

When I'm trying to connect to my container, I'm facing the following error:

psql --host=127.0.0.1

psql: could not connect to server: Connection refused
    Is the server running on host "127.0.0.1" and accepting
    TCP/IP connections on port 5432?

I first checked that ports are correctly redirected on my Docker container:

docker ps

IMAGE               COMMAND                         STATUS              PORTS                    NAMES
postgres:10.4       "docker-entrypoint.s…"          Up 6 seconds        0.0.0.0:5432->5432/tcp   db_1

Then, I checked that PSQL is correctly running, executing the following in the container:

netstat -tlnp

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 127.0.0.11:42293        0.0.0.0:*               LISTEN      -                   
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      -                   
tcp6       0      0 :::5432                 :::*                    LISTEN      -                   

From what I'm understanding:

  • Server listens to all requests made on port 5432, from all hosts.
  • Container forwards all traffic on host port 5432 to container port 5432,
  • PSQL asks a password for all non local hosts (the two lines I've addded in pg_hba.conf)

What am I missing? Is there any other thing I can check to debug my issue?

1 Answers

Well... That's one of the embarassing dev moment... After investigating it further with @toydarian (huge thanks for your time!), I finally noticed it was due to an alias I've set a long time ago:

alias psql="docker run -ti --rm postgres:10.4 psql"

It explains why it didn't work. Let's see the bright side: I've learnt a lot about PSQL configuration! :)

Related