I am trying to query a postgres
instance that lives on a remote centos
(redhat 7
) virtual machine.
The following code:
import pandas as pd, sqlalchemy
engine = sqlalchemy.create_engine('postgresql://postgres:projectpassword@ip_address_of_vm/project')
df = pd.read_sql_table('test_table', engine)
print(df)
Whee ip_address_of_vm
is the ens192 inet
address.
Results in:
psycopg2.OperationalError: could not connect to server: Connection timed out (0x0000274C/10060)
Is the server running on host "ip_address_of_vm" and accepting
TCP/IP connections on port 5432?
If I ping ip_address_of_vm
from my host (Windows 10
) computer, I receive replies and no packet loss.
From inside of the vm, I can run psql -U postgres
and enter in projectpassword
and I can properly sign into the database.
My pg_hba.conf
looks like:
| TYPE | DATABASE | USER | ADDRESS | METHOD |
|:-----:|:-----------:|:----:|:------------:|:------:|
| local | all | all | | md5 |
| host | all | all | 127.0.0.1/32 | md5 |
| host | all | all | ::1/128 | md5 |
| local | replication | all | | md5 |
| host | replication | all | 127.0.0.1/32 | md5 |
| host | replication | all | ::1/128 | md5 |
| host | all | all | 0.0.0.0/0 | md5 |
| host | all | all | ::/0 | md5 |
Note, following this question, I uncommented listen_addresses = '*'
in my postgresql.conf
file, but issuing: psql -h ip_address_of_vm -p 5432 project -U postgres -W
yields:
psql: error: could not connect to server: Connection Timed out
How can I connect to my postgres instance on a remote vm?
2
Answers
You currently don’t have any allowed remote connections in your hba.conf file.
All of those connections are for local (IE connecting to the database from that same virtual machine). In order to allow the connection you need to add something like:
host | all | all | ip_of_remote_machine | md5
Mine looks like:
host | all | all | 192.168.1.32 | md5
which allows my desktop computer on the .32 IP to connect to my virtual machine that resides on a different IP address.
https://www.postgresql.org/docs/9.6/auth-pg-hba-conf.html
This smells like firewall, not postgress issue to me.
Steps to follow:
netstat -lnp | grep postg
or something like that.nmap ip_address_of_vm
– if it is open, go back to troubleshooting postgress settings.)iptables -L INPUT
)traceroute ip_address_of_vm
) and see on each of the intermediate hops if there is any firewall enabled.