3

I hope to connect my Postgres docker service from remote client. This is how I create the container:

sudo docker run --name pg -p 5432:5432 -v pg_data:/var/lib/postgres/data -e POSTGRES_DB=mydb -e POSTGRES_USER=dbowner -e POSTGRES_PASSWORD=MySecretPassword -d postgres -c "listen_addresses='*'"

I do research and someone said the file pg_hba.conf should be edited. How should I edit pg_hba.conf? There has no config parameters to setup the pg_hba.conf. Also, is there any deferences between host all all all md5 and host all all 0.0.0.0/0 md5? Which one should I use and how should put it into pg_hba.conf within the docker container?

Here is the output of netstat after I create container:

$ netstat -ntl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:443             0.0.0.0:*               LISTEN     
tcp6       0      0 :::22                   :::*                    LISTEN     
tcp6       0      0 :::5432                 :::*                    LISTEN     
tcp6       0      0 :::3000                 :::*                    LISTEN     
tcp6       0      0 :::4000                 :::*                    LISTEN     
tcp6       0      0 :::4001                 :::*                    LISTEN     
tcp6       0      0 :::6379                 :::*                    LISTEN

Is that means PostgreSQL service only listen to IPv6?

qwang07
  • 131
  • 1
  • 1
  • 3
  • Please check other answer here - https://stackoverflow.com/questions/37694987/connecting-to-postgresql-in-a-docker-container-from-outside – sanjayparmar Sep 08 '18 at 07:23
  • @sanjayparmar I have tried this before, and as my description shows the command that I used to create the container is similar to the one in the answer and it does not work. – qwang07 Sep 08 '18 at 10:03
  • Do not crosspost: https://webmasters.stackexchange.com/questions/117600/postgres-docker-how-to-enable-postgres-docker-to-allow-remote-connections – Rob Sep 08 '18 at 10:44
  • @Rob okay, I have deleted that one. – qwang07 Sep 08 '18 at 11:34

1 Answers1

2

There are errors in -v option:

  • You write /var/lib/postgres/data it should be /var/lib/postgresql/data
  • The host path must be full: /root/pgdata
  • If the host using selinux then use -v /root/pgdata:/var/lib/postgresql/data:Z or if not use :rw instead

This is running perfectly:

root@testubt:~# docker run --name pg -p 5432:5432 -v /root/pgdata:/var/lib/postgresql/data:Z -e POSTGRES_DB=mydb -e POSTGRES_USER=dbowner -e POSTGRES_PASSWORD=MySecretPassword -d postgres:alpine


root@testubt:~# ss -tnl | grep 5432
LISTEN     0      128         :::5432                    :::*

Test localhost:

root@testubt:~# PGPASSWORD=MySecretPassword psql -U dbowner mydb -h localhost
psql (9.5.14, server 10.5)
Type "help" for help.

mydb=# \q

Test from other machine:

alp01:~# PGPASSWORD=MySecretPassword psql -U dbowner mydb -h testubt
psql (10.5)
Type "help" for help.

mydb=#

And if you want to edit pg_hba.conf it is in the /root/pgdata directory. which I believed you won't needed.

Cheers :)

affan
  • 61
  • 3
  • My `pg_data` is a docker volume, should still use `/root/pg_data` instead? And what's the meaning of `:Z` and `:rw`? – qwang07 Sep 09 '18 at 13:57
  • you don't need /root/pgdata if using docker volume. – affan Sep 10 '18 at 05:03
  • for selinux things see the docker selinux manual at https://docs.docker.com/storage/bind-mounts/#configure-the-selinux-label – affan Sep 10 '18 at 05:14