Postgresql – Connecting to postgres from windows to virtual machine

postgresql

I have the following setup

  • Windows 8
  • Oracle VM virtual box with arch Linux running on it
  • Installed and configured PostgreSQL on Linux
  • Setup the ports in virtual box so that
    • 45432 port on windows goes to 5432 on Linux
    • 40022 port on windows goes to 22 on Linux
  • On Linux PostgreSQL, I have created user erjik and database mydb.
  • Firewall is shut down on windows.

The problem is that I am trying to connect from intellij idea using JDBC PostgreSQL driver, just trying test connection to address:

jdbc:postgresql://127.0.0.1:45432/mydb

username: erjik
password: xx

However, the test connection fails.

pg_hba.conf goes unchanged after installation with following settings: 

local all all trust
// IPv4
host all all 127.0.0.1/32 trust
// IPv6
host all all ::1/128 trust

In postgresql.conf I have set only this settings:

listen_addresses = '*'

and

port = 5432

What could be the reason of connection attempt fail? Maybe I missed some settings.

EDIT1:
on Linux side the command:

psql -h localhost -U erjik mydb

executes successfully.

Best Answer

When you connect to the PostgreSQL instance via the forwarded port in the VM you're not really connecting to localhost, the port is being forwarded to the actual IP of the virtual network interface. You need to modify pg_hba.con to allow connections from IP addresses other than 127.0.0.1 for this to work.

To open up to everything add the following to pg_hba.conf. You can optionally restrict the address to your local machine's IP address (or a subset of it):

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             all                     md5

Once it's updated restart the server or send the master process a SIGHUP so it reloads the pg_hba.conf file.

FYI, you can verify that this is what happening by creating a "server" using nc and trying to connect to it from your local machine.

On the VM forward the port 12345 to your local machine and try the following. Note that the "Connection from ..." output will come after you do the next step. See how it's not 127.0.0.1:

your-pgvm$ nc -v -l 12345
Connection from 10.0.2.2 port 12345 accepted

On your Windows machine:

your-comp$ nc localhost 12345
Related Question