Skip to content

Instantly share code, notes, and snippets.

@MauricioMoraes
Last active November 2, 2023 23:34
Star You must be signed in to star a gist
Save MauricioMoraes/87d76577babd4e084cba70f63c04b07d to your computer and use it in GitHub Desktop.
Allow Docker Container Access to Host's Postgres Database on linux (ubuntu)

You have to do 2 things in order to allow your container to access your host's postgresql database

  1. Make your postgresql listen to an external ip address
  2. Let this client ip (your docker container) access your postgresql database with a given user

Obs: By "Host" here I mean "the server where docker is running on".

Make your postgresql listen to an external ip address

Find your postgresql.conf (in case you don't know where it is)

$ sudo find / -type f -name postgresql.conf # => /etc/postgresql/9.5/main/postgresql.conf

Change postgresql.conf to allow external access

$ sudo atom /etc/postgresql/9.5/main/postgresql.conf # or the path you found before

Look for the line: #listen_addresses = 'localhost' # what IP address(es) to listen on;

Uncomment and set the external ip adress that'll be trying to access your DB. If you don't know it, or want to free all ips to access it (not that safe), set it to '*':

listen_addresses = '*' # what IP address(es) to listen on;

Restart postgres

$ /etc/init.d/postgresql restart

You can check if this worked out with this command:

$ netstat -nlt

It will output something like this:

Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN 

See that it has the address 0.0.0.0 on port 5432.

Let your container access your postgresql database with a given user

Find your pg_hba.conf

$ sudo find / -type f -name pg_hba.conf # => /etc/postgresql/9.5/main/pg_hba.conf

Change pg_hba.conf to allow access to your database

$ sudo atom /etc/postgresql/9.5/main/pg_hba.conf # or the path you found before

# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
host my_database_name my_database_user 172.17.0.0/16 trust # Allowing docker container connections to host db
#
# local      DATABASE  USER  METHOD  [OPTIONS]
# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

Restart postgres and test your connection

$ psql my_database_name my_database_user -h <host-ip-address>

References:

https://stackoverflow.com/questions/31249112/allow-docker-container-to-connect-to-a-local-host-postgres-database

https://www.thegeekstuff.com/2014/02/enable-remote-postgresql-connection/

@HighMileage
Copy link

Thanks for making this gist! I've also been looking into getting a Dockerized process to talk to a postgres DB running on its host. After some research, it looks like you can also use the --network=host/--net=host optional (eg docker run --network=host <MY_IMAGE> bash) to share the host's network stack with the Dockerized process. That cuts out the need to make updates to listen_addresses and changes to pg_hba.conf will depend on your current configuration. Just figured I'd mention it here in case it's helpful to you or anyone else that comes upon this gist later.

@percybolmer
Copy link

HighMileage, thanks alot for that solution!

@blinky-z
Copy link

Thx, nice article!

@lhttjdr
Copy link

lhttjdr commented Nov 19, 2019

In my case, sharing host network using --net=host will lead to port conflicts.
This solution works fine. Thank you!

@soerface
Copy link

soerface commented Dec 3, 2019

Be aware that trust means "you can login as any user without a password via this connection". So any container you run will be able to access your database. See https://www.postgresql.org/docs/9.1/auth-methods.html#AUTH-TRUST

Maybe you want to replace trust by md5 or password and provide your container with the password.

@novrian
Copy link

novrian commented Aug 14, 2020

thanks a lot, I've been looking for this solution

@Khunpisit
Copy link

Be aware that trust means "you can login as any user without a password via this connection". So any container you run will be able to access your database. See https://www.postgresql.org/docs/9.1/auth-methods.html#AUTH-TRUST

Maybe you want to replace trust by md5 or password and provide your container with the password.

this very helpful!

@stevenpitts
Copy link

For reasons I don't know, my docker container was assigned a 172.18.x.x address, so you might need to replace 172.17.0.0/16 with 172.0.0.0/8.

@denisrondalev
Copy link

172.0.0.0/8

*172.16.0.0/12

@fobiasmog
Copy link

listen_addresses = '*'
holly mother.... DO NOT USE '*' for security reasons! It allows to connect to your db from anywhere

@andreasnuesslein
Copy link

listen_addresses = '*'
holly mother.... DO NOT USE '*' for security reasons! It allows to connect to your db from anywhere

... unless you have a firewall in place

@fobiasmog
Copy link

listen_addresses = '*'
holly mother.... DO NOT USE '*' for security reasons! It allows to connect to your db from anywhere

... unless you have a firewall in place

Tell it to mongodb users :D

@jtmancilla
Copy link

This solution works, thanks. Gracias, Excelente artículo

@OzzyTheGiant
Copy link

For reasons I don't know, my docker container was assigned a 172.18.x.x address, so you might need to replace 172.17.0.0/16 with 172.0.0.0/8.

It really depends on your app and what docker decides the IP will be set as.

@marcosfelipp
Copy link

Thank you!

@EricKotato
Copy link

You don't have to (and probably shouldn't) use listen_addresses = '*'. Here's what I did instead.

First, I've used ip addr to determine docker network address:

...
3: docker0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default 
    ...
    inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
    ...

In my case it's 172.17.0.1/16, so I use this one in my postgresql.conf:

listen_addresses = 'localhost,172.17.0.1'

And then I've set the following in pg_hba.conf:

host    all             all             172.17.0.0/16           md5

What happens here: I've allowed Docker subnet (172.17.0.0/16) to connect all databases (first all) from all users (second all) using md5 method which is defined in official PostgreSQL docs as:

Perform SCRAM-SHA-256 or MD5 authentication to verify the user's password.

That allows me to login from my Docker container to local PostgreSQL instance using login and password. You can adapt it to your case, but I hope the general idea is clear.

@flexchar
Copy link

Thanks!

@jinhucheung
Copy link

thanks!

@vncsna
Copy link

vncsna commented Jul 18, 2023

Thanks!!

@pk5ls20
Copy link

pk5ls20 commented Nov 2, 2023

Thanks🥰

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment