CentOS Question posted in
The official documentation can be found here.

Access postgreSQL 13 database on remote vm – CentOS

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    This smells like firewall, not postgress issue to me.
    Steps to follow:

    1. Check that postgress process on remote machine is actually listening on 0.0.0.0/0 (e.g. netstat -lnp | grep postg or something like that.
    2. Make sure that from local machine you don’t see remote postgress port as open (nmap ip_address_of_vm – if it is open, go back to troubleshooting postgress settings.)
    3. Check that packets are allowed to get from network interface to local process (iptables -L INPUT)
    4. Check the path from local to remote machine (traceroute ip_address_of_vm ) and see on each of the intermediate hops if there is any firewall enabled.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search