pythonpostgresqlamazon-ec2psycopg2ssh-tunnel

Connect to a PostgreSQL database hosted on AWS EC2 using Python

I'm a newbie in the field of AWS EC2 and PostgreSQL. I have been shared some access IDs of a PostgreSQL database hosted and I tested if I can connect to the database using TablePlus and successfully did that. A screenshot of that is,

enter image description here

It has these details for the database,

details of the EC2 server,

I want to connect to the database using python especially using psycopg2 and parmiko, I used the below code but didn't worked out. Can anyone guide me here?

aws_db_config.json

{
    "user": "test" (your_postgres_username),
    "password": "*********(1)" (your_postgres_password),
    "host": "aa.bbb.ccc.dd" (your_ec2_public_dns_or_ip),
    "port": "5432" (your_postgres_port),
    "database": "test_data",
    "sslmode": "prefer"  // or "disable" depending on your setup
}

my code,

import paramiko
import psycopg2
import json
import socket

def read_db_config_json(file_path):
    """Read database connection configuration from JSON file."""
    with open(file_path, 'r') as file:
        db_config = json.load(file)
    return db_config

def create_ssh_tunnel(ssh_host, ssh_port, ssh_user, ssh_key_path, local_port, remote_host, remote_port):
    """Create an SSH tunnel to the PostgreSQL server."""
    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    ssh.connect(ssh_host, port=ssh_port, username=ssh_user, key_filename=ssh_key_path)

    # Create a tunnel for the PostgreSQL connection
    transport = ssh.get_transport()
    tunnel = transport.open_channel(
        'direct-tcpip',
        (remote_host, remote_port),
        ('localhost', local_port)
    )
    return ssh, tunnel

def test_postgres_connection_via_ssh(config_file_path, ssh_config):
    """Connect to the PostgreSQL server via SSH tunneling and test the connection."""
    try:
        # Read the JSON config file
        db_config = read_db_config_json(config_file_path)

        # Create an SSH tunnel
        ssh, tunnel = create_ssh_tunnel(
            ssh_config['ssh_host'],
            ssh_config['ssh_port'],
            ssh_config['ssh_user'],
            ssh_config['ssh_key_path'],
            ssh_config['local_port'],
            db_config['host'],
            db_config['port']
        )

        # Update database config to use the local port
        db_config['host'] = 'localhost'
        db_config['port'] = ssh_config['local_port']

        # Establish the connection to PostgreSQL through the SSH tunnel
        connection = psycopg2.connect(**db_config)

        # Create a cursor object to execute SQL queries
        cursor = connection.cursor()

        # Query to get PostgreSQL version
        cursor.execute("SELECT version();")
        db_version = cursor.fetchone()
        print(f"Connected to PostgreSQL server: {db_version}\n")

        # Close the cursor and connection
        cursor.close()
        connection.close()

        # Close the SSH tunnel
        tunnel.close()
        ssh.close()

    except Exception as error:
        print(f"Error connecting to the PostgreSQL database: {error}")

if __name__ == "__main__":
    # Provide the path to your aws_db_config.json
    # SSH configuration for the EC2 instance
    ssh_config = {
        'ssh_host': 'aa.bbb.ccc.dd',
        'ssh_port': 23,  # default SSH port
        'ssh_user': 'starlord',
        'ssh_key_path': r'./saved.pem',
        'local_port': 5432  # local port for the tunnel
    }
    test_postgres_connection_via_ssh('aws_db_config.json', ssh_config)

But this is throwing me error,

Error connecting to the PostgreSQL database: encountered RSA key, expected OPENSSH key

what's the best way to connect and test the connection?

Solution

  • Check the format of key used to connect through your ssh tunnel, the message state that it expect an OPENSSH format that begins with (-----BEGIN OPENSSH PRIVATE KEY-----):

    RSA Key Format: Traditional format for SSH keys (-----BEGIN RSA PRIVATE KEY-----). OPENSSH Key Format: A newer format for private keys (-----BEGIN OPENSSH PRIVATE KEY-----).

    if you need to convert it use ssh-keygen or putty.