Not able to connect to EC2 instance in AWS via python/psycopg2

Question

I am having trouble connecting to aws via python. I have a macOSX operating system.

What i did:

1. I created an ec2 instance and chose an operating system (ubuntu) and downloaded postgresssql in the remote server. Then i created securitygroups where i added the following configuration: type: ssh protocol: tcp port: 22 source: custom, 0.0.0.0/0

Then i added another rule: postgresql TCP 5432 custom my_computer_ip_address 71.???.??.???/32

where i added question marks just to hide the address. but its in that format.

Now, aws had me create a .pem file in order to query from the database. I downloaded this pem file into a secret location.
When i go to my local machine, go to my terminal and type: ssh -i "timescale.pem" ubuntu@ec2-??-???-??-???.compute-1.amazonaws.com"

i am able to connect. I also went to my dbeaver and created a new connection and set up a connection where i am using an ssh tunnel and a public key to read the 'timescale.pem' file i created. I then go to main and type my username and password: username: postgres database: example password: mycustompassword

and i am able to connect with no issues.

Now, when I go to python with psycopg2 library, i am just unable to connect at all. I have gone through all the examples here in stackoverflow and none of them have helped me. Heres what i am using to connect to aws from python:

path_to_secret_key = os.path.expanduser("~/timescale.pem")
conn = psycopg2.connect(
    dbname='example',
    user='postgres',
    password='pass123',
    host='ec2-??-??-??-???.compute-1.amazonaws.com',
    port='22',
    sslmode='verify-full',
    sslrootcert=path_to_secret_key)

I then get this error:

connection to server at "ec2-34-???-??-???.compute-1.amazonaws.com" (34.201.??.???), port 22 failed: could not read root certificate file "/Users/me/timescale.pem": no certificate or crl found

Ok...then i switched ports and added '5432' and get this warning:

connection to server at "ec2-??-???-??-???.compute-1.amazonaws.com" (34.???.??.212), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?

When i ssh into my terminal and type: netstat -nl |grep 5432 i get the following:

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN     

unix 2 [ ACC ] STREAM LISTENING 812450 /var/run/postgresql/.s.PGSQL.5432

Can someone please help? Thanks

Answer 1

The .pem file is for connecting to the EC2 instance over SSH, on port 22. PostgreSQL is running on port 5432. You don't use the .pem file for database connections, only for ssh connections. You need to change your Python script to use port='5432', to connect directly to the PostgreSQL service running on the EC2 instance.

Answer 2

This seems to work now:

from sshtunnel import SSHTunnelForwarder
mypkey = paramiko.Ed25519Key.from_private_key_file(path_to_secret_key)
tunnel = SSHTunnelForwarder(
    ('remote_public_port', 22),
    ssh_username='ubuntu',
    ssh_pkey=mypkey,
    remote_bind_address=('localhost', 5432))
tunnel.start()

conn = psycopg2.connect(
    dbname='example_db',
    user='postgres',
    password='secret123',
    host='127.0.0.1',
    port=tunnel.local_bind_port)