2

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
Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

So, I can dig into the details of our Dockerfile for postgresql, but I think this is already setup. I took this exact same Docker container, threw it up on AWS and was able to connect from external hosts with no problem. I'll post the details of the pg_hba.confg and postgresql.conf in an update to my question.
It seems like I have the setup needed in my postgresql stuff. I'm listening on all addresses, and allowing all connections. So it seems like the problem is more at either the Centos level, or the Vagrant level (or some combination of the two).
Can you also show iptables -L on the Centos VM? As an aside, you might be better to use Vagrant to provision your Docker image directly or if you are on Windows/Mac then to have it use boot2docker.
The whole idea here is to mimick production, so we've got docker installed on Centos just like we will in production. boot2docker isn't being used at all as far as I know.
It looks like your CentOS VM is accepting connections on 5432 instead of the forwarded 6543. Can you try iptables -A INPUT -p tcp --dport 6543 -j ACCEPT on the VM?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.