Why is connection to PostgreSQL on Vagrant VM refused?

[Robert]

I have two Vagrant VMs, one configured as a web server and the other a database server.  When I try to run a psql command on the web server against my inventory database on the database server, the connection is refused:

psql -h db00 -U dsmith -d inventory -p 15432


psql: could not connect to server: Connection refused
    Is the server running on the host "db00" (192.168.2.101) and accepting
    TCP/IP connections on port 15432?

Here is my Vagrantfile:

VAGRANTFILE_API_VERSION = "2"
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "debian/stretch64"
  config.vm.synced_folder "./shared", "/vagrant", type: "virtualbox"
  ENV['ANSIBLE_ROLES_PATH'] = "/Users/dsmith/playbooks/roles-debian9"


  config.vm.define "db" do |db|
    db.vm.hostname = "db00.example.com"
    db.vm.network :private_network, ip: "192.168.2.101"
    db.vm.network :forwarded_port, guest: 5432, host: 15432
    config.vm.provision "ansible" do |ansible|
      ansible.playbook = "provision.yml"
      ansible.compatibility_mode = "2.0"
      ansible.become = true
    end
  end


  config.vm.define "web" do |web|
    web.vm.hostname = "web00.example.com"
    web.vm.network :private_network, ip: "192.168.2.102"
    web.ssh.forward_agent = true
    config.vm.provision "ansible" do |ansible|
      ansible.playbook = "provision.yml"
      ansible.compatibility_mode = "2.0"
      ansible.become = true
    end
  end
end

The key line here is the network forwarded_port line.  I think I'm telling Vagrant that if a request comes in to the database server VM on port 5432, forward it to port 15432 on the server itself which is the port I've configured PostgreSQL to listen to on that server.  From my research, I think this is what I'm supposed to do but I'm not sure.

Here is my PostgreSQL configuration file:

# /etc/postgresql/9.6/main/postgresql.conf
data_directory = '/var/lib/postgresql/9.6/main'
hba_file = '/etc/postgresql/9.6/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.6/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/9.6-main.pid'
listen_addresses = '*'
port = 15432
unix_socket_directories = '/var/run/postgresql'

Here is my authentication configuration file:

# /etc/postgresql/9.6/main/pg_hba.conf
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host all all 0.0.0.0/0 trust

From my research, the "listen_addresses" line is important in the postgres config file and the "host all all..." line is important in the hba config file.

Here are the firewall rules running on the database server:

*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [1037:93696]
-A INPUT -i lo -j ACCEPT
-A INPUT -s 127.0.0.0/8 ! -i lo -j REJECT --reject-with icmp-port-unreachable
-A INPUT -p icmp -m state --state NEW -m icmp --icmp-type 8 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 22 -m state --state NEW -j ACCEPT
-A INPUT -s 192.168.2.102/32 -d 192.168.2.101/32 -p tcp -m tcp --sport 1024:65535 --dport 15432 -m state --state NEW,ESTABLISHED -j ACCEPT
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -m limit --limit 5/min -j LOG --log-prefix "iptables_INPUT_denied: " --log-level 7
-A INPUT -j REJECT --reject-with icmp-port-unreachable
-A FORWARD -m limit --limit 5/min -j LOG --log-prefix "iptables_FORWARD_denied: " --log-level 7
-A FORWARD -j REJECT --reject-with icmp-port-unreachable
-A OUTPUT -s 192.168.2.101/32 -d 192.168.2.102/32 -p tcp -m tcp --sport 5432 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT
COMMIT

The important rule is the one that says that anything from 192.168.2.102 (web00) to 192.168.2.101 (db00) on port 15432 is accepted.  But I really don't think the firewall is the problem since I get the same error if I flush all the rules.

What am I doing wrong here?  I've tried to think it through but I'm missing something.

[Alvaro Miranda Aguilera]

hello

on the postgresql  node whats the output of

sudo netstat -anp | grep 15432

on the web VM you need to connect to the DB as 192.168.2.101 15432

test with

telnet 192.168.2.101 15432

and if you disable the firewall does it work?

--
This mailing list is governed under the HashiCorp Community Guidelines - https://www.hashicorp.com/community-guidelines.html. Behavior in violation of those guidelines may result in your removal from this mailing list.
 
GitHub Issues: https://github.com/mitchellh/vagrant/issues
IRC: #vagrant on Freenode
---
You received this message because you are subscribed to the Google Groups "Vagrant" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vagrant-up+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/vagrant-up/caf83f35-74a9-4239-a27c-de758bbe7dd9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Alvaro

[Robert]

The output from the netstat command is this:

tcp        0         0 0.0.0.0:15432      0.0.0.:*         LISTEN        -
tcp6...
unix. 2.   [ ACC ]    STREAM.   LISTENING.   34094.  -    /var/run/postgresql/.s.PGSQL.15432

When I try to telnet in the message is:

Trying 192.168.2.101...
telnet: Unable to connect to remote host: Connection refused

Yes, the error still occurs even if I disable the firewall.

Thanks for looking at my question.

To unsubscribe from this group and stop receiving emails from it, send an email to vagra...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/vagrant-up/caf83f35-74a9-4239-a27c-de758bbe7dd9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Alvaro

[Dennis Chang]

Hi Robert,

So if you look at your Vagrantfile, you have the db00 host performing port_forwarding from the host 15432 to the guest 5432.

However, judging by your netstat output (from within your db00 VM) it looks like Postgres is listening on 15432.

So there is no way web00 nor the host will be able to reach db00 (postgres).

Port forwarding is for the benefit of the host (you don't need it if you only care about web00 to db00 connectivity (the private network will suffice).

So if postgres is listening on 15432 (and will accept connections from 0.0.0.0), then from web00 you can do 'telnet 192.168.2.101 15432' and it

should allow a connection.

[Robert]

Hi Dennis,

Thank you for looking at my question.

When I researched this problem on the Internet, it sounded as though people were saying that the issue is that a Vagrant VM listens to traffic from the "outside" (for lack of a better term) on one port and then passes that traffic to another port on its "inside", almost like Vagrant is a container with external and internal ports.  I don't understand how Vagrant works internally but this sounded plausible so initially I did this in my Vagrantfile:

  config.vm.define "db" do |db|
    ...
    db.vm.network :private_network, ip: "192.168.2.101"
    db.vm.network :forwarded_port, guest: 5432, host: 5432
    ...
  end

However, this forwarded_port directive resulted in Vagrant raising this error whenever I would do Vagrant up or Vagrant reload:

Vagrant cannot forward the specified ports on this VM, since they
would collide with some other application that is already listening
on these ports. The forwarded port to 5432 is already in use
on the host machine.


To fix this, modify your current project's Vagrantfile to use another
port. Example, where '1234' would be replaced by a unique host port:


  config.vm.network :forwarded_port, guest: 5432, host: 1234

This is what caused me to revert to creating two separate ports, and outside port 15432 and the 5432 port that PostgreSQL is actually listening on inside the VM.

I've just now tried disabling this forward port and reloading vagrant:

db.vm.network :forwarded_port, guest: 5432, host: 5432, disabled: true

I also edited the postgresql.conf file on my db server and restarted postgresql:

# postgresql.conf
port = 5432

$ psudo service postgresql restart

So, to re-cap

1. I'm no longer doing a port forwarding on the database virtual machine
2. PostgreSQL is listening on port 5432 on the DB VM.
3. The PostgreSQL pg_hba.conf file's last line is:  host all all 0.0.0.0/0 trust
4. I've disabled all firewall rules.

Even after all of these changes, when I try to run a psql command on my web VM to the database VM, I still get the "Connection refused" message.

[Dennis Chang]

Hi Robert,

It does sound like your host (laptop/desktop) has a Postgres running and therefore won't allow you to use port 5432 (on the host).

I would suggest you take it step by step.

1. Can you use psql locally on db00 host?

So vagrant ssh into the db00 host and try all the commands:

psql

psql -h 127.0.0.1 -p 5432  # loopback interface

psql -h 192.168.2.101 -p 5432 # private interface

psql -h 10.0.2.15 -p 5432   # NAT'ed interface

2. If the first step checks out (step #1 is about verifying that postgres is listening on port 5432 and will accept connections on all interfaces, i.e. 0.0.0.0)

vagrant ssh into the web00 host and run

psql -h 192.168.2.101 -p 5432

You can also try:

nmap 192.168.2.101   # in order to test that web00 can scan db00 and can detect a port open on 5432

3. Now, you can try from the host

psql -h 192.168.2.101 -p 5432

psql -h 10.0.2.15 -p 5432

You are probably very close and there's something we're missing. Hopefully performing these steps will help you flush it out.

Dennis

[Dennis Chang]

Hi Robert,

Another thought I just had is with the "forwarded_ports" line in your Vagrantfiles.

I was wondering if perhaps by placing "forwarded_ports" line below the private_network line you are not inadvertently requesting ports be forwarded for the private interface?

In that case, it's possible you can't reach the interface because of it?

Try moving the "forwarded_ports" line above the private_network line.

We want forwarded_ports to be applied to the NAT'ed interface (10.0.2.15).

Dennis

[Robert]

Hi Dennis. 

1.  My account has the Superuser and Create DB roles on db00.  So I sudo into my account and run the following commands:

psql -d inventory => I logged in to the database

psql -h 127.0.0.1 -d inventory -p 5432  => I'm logged in again

psql -h 192.168.2.101 -d inventory -p 5432   => I'm logged in again

psql -h 10.0.2.15 -d inventory -p 5432   => I'm logged in again

2.  Here is what happens when I run the other commands from web00:

psql -h 192.168.2.101 -d inventory -p 5432. => Connection refused

nmap 192.168.2.101 -p 5432

Starting...

Host is up...

PORT        STATE.    SERVICE

5432/tcp.    closed.    postgresql

Clearly, nmap "STATE closed" confirms that connections are being rejected.

Regarding the forwarded_ports setting, my first question would be, in a situation like this were you have a postgresql client (my web00) inside a Vagrant VM and the server inside a second Vagrant VM, is it strictly necessary to include the :forwarded_port directive inside the Vagrantfile?  Are any other settings required?  And if so, how does one set the guest and host ports?  The way I did it in my original question, setting Guest (db00) to 5432 and Host (web00) to 15432 didn't work.  And if I set the both to 5432, that raised a different error by Vagrant.  This really does seem like a Vagrant issue because I have two live servers that I configured using the same Ansible playbooks that I configured these two VMs with and I don't have this problem with my live web server talking to my database server.

I'll continue to drill down into possible causes for this connection error but, again, this feels like a Vagrant issue.  And yet I've already read a number of questions and blog posts regarding running postgresql on Vagrant and they all indicated that I just need the postgresql.conf and pg_hba.conf settings that I described in my original question above.

This is very strange!

[Dennis Chang]

port_forwarding allows a port in the VM to be exposed to the host.

So in your case, if you do port_forwarding with your Postgres process

you want to expose your database to your host.

What I wanted to do was focus on only the necessary configurations

and worry about port_forwarding later (because obviously, accessing

the database is more important from web00 than it is for the host).

Given that the nmap command failed from web00 to scan open ports

on db00, it suggests that the database is rejecting connection attempts.

nmap output says 'Host is up' which means that web00 can ping db00.

Can you 1. verify listening_addresses configurations on postgres.conf and pg_hba.conf?

2. restart postgres, 3. show us netstat -tlpn?

FYI, I'm assuming that you perform an 'apt-get install postgres' in your ansible provisioning.

That you make edits to the configuration files, and then you restart postgres server.

Perhaps, what you can do is, destroy the VM, build a new one manually (no provisioning by ansible).

And verify as you go that you can reach the VM from inside and from outside of it.

[Robert]

Verify listen addresses:

# postgresql.conf
listen_addresses = "*"

Verify authentication:

# pg_hba.conf

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128

                md5
hostssl inventory     www-data    192.168.2.102/32     cert clientcert=1
hostssl inventory     postgres    192.168.2.102/32     cert clientcert=1

host all all 0.0.0.0/0 trust

Note that I do use SSL certificates which is why there are two hostssl records. I would think the final 'host' directive would be sufficient to allow any access.

Output from "netstat - tlnp" after restarting postgresql:

(No info could be read for "-p": geteuid()=1001 but you should be root.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -                  
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      -                  
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      -                  
tcp6       0      0 :::22                   :::*                    LISTEN      -                  
tcp6       0      0 :::5432                 :::*                    LISTEN      -                  
tcp6       0      0 ::1:25                  :::*                    LISTEN      -      

I will rebuild both VMs and install the minimal postgresql packages necessary, see what happens, and report back.

Thanks again for your help!

[Robert]

I forgot to add that after I verified the listen and authentication settings and restarted postgresql on db, I continued to get the same "Connection refused" error on web.

[Alvaro Miranda Aguilera]

can you put your code in a github repo?

will be easier to have a look and propose something.

alvaro

To unsubscribe from this group and stop receiving emails from it, send an email to vagrant-up+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/vagrant-up/dca0b03c-1669-43ea-b460-39b653363cd5%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Alvaro

[Dennis Chang]

Try removing those hostssl lines from pg_hba.conf.

Also, I'm not sure about syntax on pg_hba.conf but try:

host all all 0.0.0.0/0  md5

instead of trust.

I think it must be those hostssl lines.

Or, what you can do is, change the IP of web00 to 192.168.2.103 and see if you can scan the ports on 192.168.2.101.

Try using a minimal configuration. Only until you get it to work before you add configurations back.