0

I'm new to using postgres as well as ssh and am having some trouble understanding what I need to do to get remote clients accessing a postgres server. Right now I've got one computer with a server running that I can access using psycopg2 but now I want to query the server using another computer. I've looked around and found examples using sshtunneler, but I feel like I'm missing some puzzle pieces.

import psycopg2
from sshtunnel import SSHTunnelForwarder
import time

with SSHTunnelForwarder(
         ('192.168.1.121', 22),
         ssh_password="????",
         ssh_username="????",
         remote_bind_address=('127.0.0.1', 5432)) as server:

    conn = psycopg2.connect(database="networkdb",port=server.local_bind_port)
    curs = conn.cursor()
    sql = "select * from Cars"
    curs.execute(sql)
    rows = curs.fetchall()
    print(rows)

My first confusion is I'm not sure what username/password should be. I downloaded putty and put the remote address info in the tunnel section using this tutorial but I have no idea if that's doing anything. When I try to start the server I get the error 

2017-03-03 10:03:28,742| ERROR   | Could not connect to gateway 192.168.1.121:22 : 10060

Any sort of help/explanation of what I need to do would be appreciated.

If I can do it without ssh then that would be better. Currently running this:

psycopg2.connect(dbname='networkinfodb',  user='postgres', host='168.192.1.121', password='postgres', port=5432)

outputs...

OperationalError Traceback (most recent call last) in () ----> 1 psycopg2.connect(dbname='networkinfodb', user='postgres', host='168.192.1.121', password='postgres', port=5432)

OperationalError: could not connect to server: Connection timed out (0x0000274C/10060)
    Is the server running on host "168.192.1.121" and accepting
    TCP/IP connections on port 5432?

and I'm not sure where to go to figure out what the issue is.

Exuro
  • 229
  • 3
  • 15
  • Is there a reason you need to use SSH? Why don't you just connect to postgres directly from the other computer? – FamousJameous Mar 03 '17 at 17:23
  • There isn't. I'm new to this and am just trying to figure out a way to do it. I'm using psycopg2 to interface with it and I get timeouts. I've updated my question with your inquiry. I'm guessing I need to change some config to allow it to be accessed outside of the localhost. – Exuro Mar 03 '17 at 17:29
  • Is the IP address really 168.192.1.121? Or is it actually 192.168.1.121? – FamousJameous Mar 03 '17 at 17:33
  • heh yeah I saw that and changed it soon after. Still have the same issue. – Exuro Mar 03 '17 at 17:40
  • Check this answer for allowing remote connections to a postgres database. http://stackoverflow.com/a/6030340/3901060. – FamousJameous Mar 03 '17 at 17:44
  • Thanks for that. I went in and the listen_address line was already there, so I added the other to the hba file. I restarted my computer so perhaps it didnt actually reset the server configs as I'm still getting the same error. – Exuro Mar 03 '17 at 17:53
  • Ok I think I may have found the issue. My firewall isn't allowing it through so I think I need to make an exception on that port. edit: That was the issue! Now i need to just re-enable my firewall and allow that port to pass it. Thanks for the help! – Exuro Mar 03 '17 at 18:03
  • I'm glad you found your solution. You may want to either add an answer to your question or mark it as a duplicate so that others can find an answer that might help them. – FamousJameous Mar 03 '17 at 18:32

1 Answers1

0

So I didn't use ssh tunneling. That was only a backup as I was having trouble connecting to the database using psycopg2. I found that the firewall was blocking the port from being accessed externally so I was able to change that and now I can access the database from clients.

Exuro
  • 229
  • 3
  • 15