Connect postgres and Python

Question

I have a database available on a remote host. When I use putty and SSH, I am able to access it. The database itself has no password. Only, the SSH connection has a password. But, when I try to connect to it using Python, it asks for password. I'm new to postgres and paramiko.

Here's what I've tried:

import psycopg2
import paramiko
import time
t = paramiko.Transport(('xxx.com', 22))
t.connect(username="xxx", password='xxx') 
c = paramiko.Channel(t)
conn = psycopg2.connect("dbname='xxx'")
curs = conn.cursor()
sql = "select * from xxx"
curs.execute(sql)
rows = curs.fetchall()
print(rows)

Other method that I tried was:

import os, psycopg2
os.system("ssh xxx@xxx.com -fNL 5432:localhost:5432 -p 22")
while True:
    try:
        conn = psycopg2.connect("dbname='xxx'")
        curs = conn.cursor()
        sql = "select * from xxx"
        curs.execute(sql)
        rows = curs.fetchall()
        print(rows)
    except:
        print "I am unable to connect to the database"

This gives me a 'Could not request local forwarding' error.

Is there some other way to go about this? I have a Windows 7 (x64) machine with Python 2.7. Please help me. Thanks.

possible duplicate of [Connecting to remote server with pgadmin](http://stackoverflow.com/questions/17937615/connecting-to-remote-server-with-pgadmin) — Brent Washburne, Jul 06 '15 at 16:17
It looks like you were able to connect by changing your port number. Is this a duplicate of http://stackoverflow.com/questions/17937615/connecting-to-remote-server-with-pgadmin ? — Brent Washburne, Jul 06 '15 at 16:18

Akisame · Answer 1 · 2015-01-24T19:48:24.937

1

You should connect to the remote server and make port forwarding of remote PostgreSQL to a local port.

Without paramiko, it's something like this:

# start port forwarding
$ ssh -L PGSQL_LOCAL_PORT:localhost:PGSQL_REMOTE_PORT user@xxx.com
# in python
psycopg.connect("dbname='xxx' host='localhost' port='PGSQL_LOCAL_PORT'")

Here is an example of doing this with paramiko
https://code.ros.org/trac/wg-ros-pkg/browser/pkg/trunk/paramiko/demos/forward.py?rev=30

Note: Port forwarding is blocking operation. So, you have to start port forwarding in separate thread/process.

edited Jan 24 '15 at 19:48

answered Jan 24 '15 at 19:22

Akisame

754
7
16

I have tried forwarding as well. But it didn't work. I'm not sure what mistake I am making. I've made changes to my question. – GobSmack Jan 24 '15 at 19:54
1

Strange. This works for me. Have you tried with different local port? And you could check if remote port is correct. As I remember, default port for PostgreSQL is 5433. ssh -L 14141:localhost:5433 myuser@myserver #.... psycopg2.connect("dbname='my' host='localhost' port='14141' user='myuser' password='*******") – Akisame Jan 24 '15 at 20:06
It gives me a bind: Not owner and then could not forward error for any port. Sorry, this is my first time dealing with SSH. So I don't know much. Thanks for helping. – GobSmack Jan 24 '15 at 20:10
Phew! Was able to connect to ssh finally! But, for the database, I get a connection refused error :( – GobSmack Jan 24 '15 at 20:32
You've got this error when connecting from psycopg2? – Akisame Jan 24 '15 at 20:36
Yes, for the psycopg2.connect command. I don't have a database username or password. Is that why I get this error? What values should I fill in these fields? – GobSmack Jan 24 '15 at 20:40
When you create a database (in PostgreSQL), current user automatically becomes the owner of the database. Default user in PostgreSQL is postgres. You can create new user for your database (what I strongly recommend) or you can change postgres password. You can take a look at this gist https://gist.github.com/Alexx-G/e79e477a1d32cd768d3b – Akisame Jan 24 '15 at 21:01

Connect postgres and Python

1 Answers1