Connecting to AWS Postgres database using Python: connection refused error

Question

I'm trying to query from an online Postgres database (slave) via SQLAlchemy. I'm able to create an SSH-tunnel and creating engine and session (see code below). However when trying to setup the cursor (cursor = session.connection().connection.cursor()), we get a connection refused error. Do you know what we should change in order to connect?

Some more considerations:

• When I'm connection to this databases via DBeaver with same settings and credentials, it doesn't throw any errors
• A colleague of mine is able to connect with same script (see below) and same SSH-key without any errors.
• PostgreSQL 9.6.18 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
• The database is managed by external party, so I'm not able to make changes directly, however on request we can.

The error:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?

(Background on this error at: http://sqlalche.me/e/13/e3q8)

my_script.py:

from connection import create_connection

db_name = 'myDB'

session, engine = create_connection(
    db_name, ssh=True)

cursor = session.connection().connection.cursor()
cursor.execute(''' SELECT * from file limit 10 ''')

data = cursor.fetchall()

print(data[0])

session.close()
cursor.close()

connection.py:

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
import config

def create_connection(db_name, ssh=False):
    connection_id_db = config.CONNECTION_IDS[db_name]['DB_CONNECTION']

    if ssh:
        connection_id_ssh = config.CONNECTION_IDS[db_name]['SSH_CONNECTION']
        with sshtunnel.SSHTunnelForwarder(
                # Remote server IP and SSH port
                connection_id_ssh['REMOTE_SERVER'],
                ssh_username=connection_id_ssh['SSH_USERNAME'],
                ssh_private_key=connection_id_ssh['SSH_PRIVATE_KEY'],
                remote_bind_address=connection_id_ssh['REMOTE_BIND_ADDRESS'],
                local_bind_address=connection_id_ssh['LOCAL_BIND_ADDRESS']
        ) as server:  # PostgreSQL server IP and sever port on remote machine
            server.start()
    
    # connect to PostgreSQL
    engine = create_engine('postgresql://' +
                           connection_id_db['DB_USERNAME'] + ':' +
                           connection_id_db['DB_PASSWORD'] + '@' +
                           connection_id_db['DB_LOCAL_ADDRESS'] + ':' +
                           connection_id_db['DB_LOCAL_PORT'] + '/' +
                           connection_id_db['DB_NAME'], use_batch_mode=True)
    Session = sessionmaker(bind=engine)
    session = Session()
    print('Database session created')
    return session, engine

And the config.py:

CONNECTION_IDS = {
    'myDB': {
        'SSH_CONNECTION': {
            'REMOTE_SERVER': ('XXX.XXX.nl', 22),
            'SSH_USERNAME': 'ubuntu',
            'SSH_PRIVATE_KEY': '/location/to/.ssh/My_ssh_key.pem',
            'REMOTE_BIND_ADDRESS': ('XXX.XXX.eu-west-1.rds.amazonaws.com', 5432),
            'LOCAL_BIND_ADDRESS': ('localhost', 1234)
        },

        'DB_CONNECTION': {
            'DB_USERNAME': '<username>',
            'DB_PASSWORD': '<password>',
            'DB_LOCAL_ADDRESS': 'localhost',
            'DB_LOCAL_PORT': '5432',
            'DB_NAME': 'myDB'
        }
    },
}

Answer 1

The SSH tunnel is set up to accept connections on port 1234 locally, but the connection is attempted at port 5432.

If you don't have a PostgreSQL instance running locally (according to the error message, you don't), the port 5432 is available, so change in config.py:

'LOCAL_BIND_ADDRESS': ('localhost', 1234)

into:

'LOCAL_BIND_ADDRESS': ('localhost', 5432)