Connecting to PostgreSQL database through SSH tunneling in Python

Question

I am trying to connect to a server remotely and then access it's local database with Python. I am successfully connecting to the server, although I can't seem to connect to the database on the server. My code is below:

import psycopg2
from sshtunnel import SSHTunnelForwarder

try:

    with SSHTunnelForwarder(
         ('<server ip address>', 22),
         ssh_private_key="</path/to/private/ssh/key>",
         ssh_username="<server username>",
         remote_bind_address=('localhost', 5432)) as server:

        print "server connected"
        
        conn = psycopg2.connect(database="<dbname>",port=server.local_bind_port)
        curs = conn.cursor()
        print "database connected
    
except:
    print "Connection Failed"

These are pieces of code I have found on the internet and pieced together. I have also tried the connection statements below in place of the code above:

params = {
  'database': '<dbname>',
  'user': '<dbusername>',
  'password': '<dbuserpass>',
  'host': 'localhost',
  'port': 5432
}
conn = psycopg2.connect(**params)

I know I can connect to the database because on my machine; I am able to use sqlectron to tunnel in and connect appropriately.

Just in case it is not clear what I am trying to do from above, I need to ssh tunnel into my remote server using a private ssh key on my computer (working properly), and then I need to connect to a PostgreSQL database that is on localhost at port 5432.

I am currently getting the current error message for both ways of trying to connect:

2016-01-23 11:16:10,978 | ERROR   | Tunnel: 0.0.0.0:49386 <> localhost:5432 error: (9, 'Bad file descriptor')

Solution 1

I don't know if this may be helpful, but I had to connect to a PostgreSQL database through SSH tunneling as well. I succeeded to connect using your code with some modifications:

import psycopg2
from sshtunnel import SSHTunnelForwarder

try:

    with SSHTunnelForwarder(
         ('<server ip address>', 22),
         #ssh_private_key="</path/to/private/ssh/key>",
         ### in my case, I used a password instead of a private key
         ssh_username="<server username>",
         ssh_password="<mypasswd>", 
         remote_bind_address=('localhost', 5432)) as server:
         
         server.start()
         print "server connected"

         params = {
             'database': '<dbname>',
             'user': '<dbusername>',
             'password': '<dbuserpass>',
             'host': 'localhost',
             'port': server.local_bind_port
             }

         conn = psycopg2.connect(**params)
         curs = conn.cursor()
         print "database connected"

except:
    print "Connection Failed"

After adding server.start(), the code worked nicely. Furthermore, inverted commas were missing after 'database connected'. I hope this might be helpful to you, thanks for sharing your code!

Solution 2

Both these examples were very helpful. I just needed to combine the good parts from both.

from sshtunnel import SSHTunnelForwarder #Run pip install sshtunnel
from sqlalchemy.orm import sessionmaker #Run pip install sqlalchemy
from sqlalchemy import create_engine

with SSHTunnelForwarder(
    ('<remote server ip>', 22), #Remote server IP and SSH port
    ssh_username = "<username>",
    ssh_password = "<password>",
    remote_bind_address=('<local server ip>', 5432)) as server: #PostgreSQL server IP and sever port on remote machine
        
    server.start() #start ssh sever
    print 'Server connected via SSH'
    
    #connect to PostgreSQL
    local_port = str(server.local_bind_port)
    engine = create_engine('postgresql://<username>:<password>@127.0.0.1:' + local_port +'/database_name')

    Session = sessionmaker(bind=engine)
    session = Session()
    
    print 'Database session created'
    
    #test data retrieval
    test = session.execute("SELECT * FROM database_table")
    for row in test:
        print row['id']
        
    session.close()

Comments

[autonopy]

This. This was the one that I got to work, after 2 weeks of trying solution after solution. My challenge was that I needed to use a pem for the tunnel and I couldn't figure out where ALL of the pieces went. Great work making this solution clear and easy to use.

[user6801750]

Thanks a bunch, this is the one worked for me after trying many solutions.