Vagrant + Docker + Postgresql - Cannot connect from host

Question

I'm trying to simulate our production setup locally using Vagrant. In production, we use a docker container for our postgresql database, running on centos6.5/redhat (not by choice).

So, locally, I've installed Vagrant, created a machine, got the postgresql docker container up and running on that machine, ensured it's running by connecting from the VM. However I cannot figure out how to connect to postgresql from the host (or from another VM).

Here is my Vagrant file:

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|  

  config.vm.box = "chef/centos-6.5"

  config.vm.provision "shell" do |s|
    s.inline = "ps aux | grep 'sshd:' | awk '{print $2}' | xargs kill"
  end

  config.vm.define "db" do |db|
    db.vm.synced_folder "../db", "/vagrant/db"
    db.vm.synced_folder "../deploy", "/vagrant/deploy"
    db.vm.hostname = "dbserver"
    db.vm.network :private_network, ip: "192.168.50.4"
    db.vm.network :forwarded_port, guest: 5432, host: 6543 
  end

end

Note I'm forwarding the guest port 5432 to the host port 6543.

On the VM, you can see docker running the postgresql container:

[vagrant@dbserver vagrant]$ sudo docker ps
CONTAINER ID        IMAGE                                     COMMAND             CREATED             STATUS              PORTS                    NAMES
075f71e9f8de        quay.io/aptible/postgresql:standardized   "run-database.sh"   12 hours ago        Up 12 hours         0.0.0.0:5432->5432/tcp   hungry_morse  

When on the VM, I have to connect using a command like:

psql -h 0.0.0.0 -U <username> -d db

From the host, it seems like I should be using:

psql -h 192.168.50.4 -p 6543 -U <username> -d db

But that's giving me:

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

Note that this is not specific to postgresql. I have a redis container setup the same way giving the same issues.

I'm not sure if this is an issue with my Vagrant setup, the firewall on Centos, or what. Any ideas on how to make this work?

Update 1

The pg_hba.conf file in our docker container looks like so:

local     all   all                        peer
hostssl   all   all        0.0.0.0/0       md5

The postgresql.conf file in our docker container looks like so:

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

data_directory = '/var/lib/postgresql/9.3/main'
hba_file = '/etc/postgresql/9.3/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.3/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/9.3-main.pid'

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

listen_addresses = '*'
port = 5432
max_connections = 250
unix_socket_directories = '/var/run/postgresql'
ssl = on
ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH'
ssl_cert_file = '/etc/postgresql/9.3/ssl/server.crt'
ssl_key_file = '/etc/postgresql/9.3/ssl/server.key'

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

shared_buffers = 128MB


#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

log_line_prefix = '%t '
log_timezone = 'UTC'
client_min_messages = ERROR
log_min_messages = FATAL
log_min_error_statement = FATAL

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------

datestyle = 'iso, mdy'
timezone = 'UTC'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'

tcp_keepalives_idle = 30
tcp_keepalives_interval = 30

Update 2

The VM's iptables rules:

[vagrant@dbserver vagrant]$ sudo iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:http 
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:postgres 

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            ctstate RELATED,ESTABLISHED 
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination         

Chain DOCKER (1 references)
target     prot opt source               destination         
ACCEPT     tcp  --  anywhere             172.17.0.3          tcp dpt:postgres 

Answer 1

It looks like you're misunderstanding how you access services on a Vagrant instance. You can either connect to the VM as the host of the service and it's service port or you can forward traffic from a local port to the VM's port using port forwarding.

From the host, it seems like I should be using:

psql -h 192.168.50.4 -p 6543 -U <username> -d db

With Vagrant, if you forward the port on you then access it as if it on localhost.

In your case, you should use either:

psql -h 192.168.50.4 -p 5432 -U <username> -d db

or

psql -h 127.0.0.1 -p 6543 -U <username> -d db

instead of <VM ip>:<forwarded port>.

On top of this you also need to make sure that your Postgres instance is configured to allow remote access asmPostgres, out of the box, only accepts connections from localhost.

To open up remote access you must first modify pg_hba.conf and the listen_addresspostresql.conf.

The pg_hba.conf needs to have a line allowing your Vagrant host to connect to it. This is typically seen by the VM as 10.0.2.2 so the line you need to add would look something like:

# Allow connections from Vagrant host on 10.0.2.2 to all datababases for all users using an md5 hashed password
host    all         all         10.0.2.2/32    md5

Your postgresql.conf change is simple as you just need to replace:

listen_addresses='localhost'

with:

listen_addresses='*'

With a typical VM I'd suggest using the provisioner to make the changes but with Docker you should instead set this up via your Dockerfile. Helpfully, Docker provides a useful example of this in their documentation.