0

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?

artemis
  • 6,857
  • 11
  • 46
  • 99
  • Your test `psql` command lacks a hostname specification (or -h), so it is just connecting to a local database, not the one on ip_address_of_vm. So doesn't seem to be testing what you want it to be testing. – jjanes Jun 25 '21 at 18:12
  • Is there a way for me to deploy the database _not_ on `localhost` but instead to the `ip`? – artemis Jun 25 '21 at 18:16
  • Didn't you already deploy it? You are now just testing the connection, but in the case of your `psql` command, you are testing the connection to the wrong machine. Did you also deploy the database to the wrong machine? – jjanes Jun 25 '21 at 18:19
  • I thought that I did, and am evidently misunderstanding your comments, so I apologize.. The database is built on the virtual machine. On the virtual machine, I can run `psql -U postgres` to connect just fine. I am unable to connect from outside the virtual machine; that is the problem I am trying to solve. – artemis Jun 25 '21 at 18:22
  • Sorry, I'm the one who misunderstood. Can you try psql from your local machine, using `-h` to point it to your remote vm? That will *probably* reproduce the same connection issue as python finds. This is almost certainly a firewall issue. The firewall could be on the VM itself, or it could be on the host machine of the VM, or on the network where the VM lives. Is this some cloud hosting provider we might have heard of before? – jjanes Jun 25 '21 at 19:26
  • Correct @jjanes, it reproduces the exact same issue. Interestingly, the `iptables -L INPUT` on the remote VM shows accepting inputs, but I think you are right, it is a firewall issue on the VM Host. – artemis Jun 25 '21 at 19:54

2 Answers2

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

Randy
  • 44
  • 6
  • This database is going to be accessed by a few dozen people -- many of whom I do not know their IP address. Instead of specifying 20+ addresses in such a fashion, how can I just allow all remote connections? – artemis Jun 25 '21 at 17:52
  • Are they going to all be connecting from the same subnet (like over a VPN or in the same office) or are the IPs just going to be all over the place. From a security perspective you really don't want to just allow anyone and everyone to connect to the database. That said, I just noticed you have host all all 0.0.0.0/0 which should allow anyone to connect. Can you post the contents of your postgresql.conf file? By default it will have "#listen_addresses = 'localhost'" as one of the lines in there. That needs to be changed to #listen_addresses = '*' and you also want to double check your port. – Randy Jun 25 '21 at 18:24
  • In the post, I note that `#listen_addresses = '*'`. And yeah I totally get the security concerns, but fortunate that is not a problem (for now). `\conninfo` reveals `You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5342"` – artemis Jun 25 '21 at 18:42
  • I want to make sure that after you removed the # to remove the comment on listen_addresses that you restarted the database by disconnecting from it and then running sudo systemctl restart service_name. Make sure that if you have md5 as your auth method that the user you are connecting with actually has a password set in the DB. Assuming all those config settings are correct, you've restarted the server, and you are using the correct password (it will be the password for the database user NOT the linux or windows users), it's likely an issue with the VM blocking access to certain ports. – Randy Jun 25 '21 at 19:20
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.
mickvav
  • 310
  • 2
  • 6
  • 1. `STREAM LISTENING 298063` 2. Only ports `22` and `111` are open 3. `Chain INPUT (policy ACCEPT)` 4. Nothing about firewall – artemis Jun 25 '21 at 18:16