Menu
I want to connect to a remote PostgreSQL database through Python to do some basic data analysis. This database requires SSL (verify-ca), along with three files (which I have):
I have not been able to find a tutorial which describes how to make this connection with Python. Any help is appreciated.
254
With SSL support compiled in, the PostgreSQL server can be started with SSL enabled by setting the parameter ssl to on in postgresql. conf. The server will listen for both normal and SSL connections on the same TCP port, and will negotiate with any connecting client on whether to use SSL .
To establish connection with the PostgreSQL database, make sure that you have installed it properly in your system. Open the PostgreSQL shell prompt and pass details like Server, Database, username, and password. If all the details you have given are appropriate, a connection is established with PostgreSQL database.
In order to connect to a PostgreSQL database instance from your Python script, you need to use a database connector library. In Python, you have several options that you can choose from. Some libraries that are written in pure Python include pg8000 and py-postgresql.
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.
It is explained in detail in the PostgreSQL Connection Strings documentation (see also Parameter Key Words) and in SSL Support.
198
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
