close

How to connect to a remote PostgreSQL database through SSL with Python

Hello Guys, How are you all? Hope You all Are Fine. Today We Are Going To learn about How to connect to a remote PostgreSQL database through SSL with Python in Python. So Here I am Explain to you all the possible Methods here.

Without wasting your time, Let’s start This Article.

Table of Contents

How to connect to a remote PostgreSQL database through SSL with Python?

  1. How to connect to a remote PostgreSQL database through SSL with Python?

    To perform server certificate verification you can set sslmode to verify-full or verify-ca. You need to supply the path to the server certificate in sslrootcert

  2. connect to a remote PostgreSQL database through SSL with Python

    To perform server certificate verification you can set sslmode to verify-full or verify-ca. You need to supply the path to the server certificate in sslrootcert

Method 1

Use the psycopg2 module.

You will need to use the ssl options in your connection string, or add them as key word arguments:

import psycopg2

conn = psycopg2.connect(dbname='yourdb', user='dbuser', password='abcd1234', host='server', port='5432', sslmode='require')

In this case sslmode specifies that SSL is required.

To perform server certificate verification you can set sslmode to verify-full or verify-ca. You need to supply the path to the server certificate in sslrootcert. Also set the sslcert and sslkey values to your client certificate and key respectively.

Method 2

You may also use an ssh tunnel with paramiko and sshtunnel:

import psycopg2
import paramiko
from sshtunnel import SSHTunnelForwarder

mypkey = paramiko.RSAKey.from_private_key_file('/path/to/private/key')

tunnel =  SSHTunnelForwarder(
        (host_ip, 22),
        ssh_username=username,
        ssh_pkey=mypkey,
        remote_bind_address=('localhost', psql_port))

tunnel.start()
conn = psycopg2.connect(dbname='gisdata', user=psql_username, password=psql_password, host='127.0.0.1', port=tunnel.local_bind_port)

Conclusion

It’s all About this issue. Hope all Methods helped you a lot. Comment below Your thoughts and your queries. Also, Comment below which Method worked for you? Thank You.

Also, Read