Connect to PostgreSQL from Python using SSL/TLS

Connecting to PostgreSQL using SSL/TLS

Transport Layer Security (TLS) is a security protocol for accessing remote machines over untrusted networks. A primary use case of TLS is encrypting the communication between web applications and servers. It runs on top of TCP/IP to secure client-server communications and allows a TLS-enabled client to authenticate itself to a TLS-enabled server and vice versa. TLS evolved from a previous encryption protocol called Secure Sockets Layer (SSL), and the terms TLS and SSL are sometimes used interchangeably.

During server authentication, the client application uses public-key cryptography (PKI) algorithms to verify the server's identity by checking that the server's certificate is issued by a trusted certificate authority (CA) and proves the ownership of the public key. Similarly, TLS client authentication allows the server to validate the client's identity. The client and server can also authenticate each other using self-signed certificates. However, you will only want to use a self-signed certificate for an internal network or a development server.

After establishing a TLS connection, the client and server can exchange symmetrically encrypted messages with a shared secret key. TLS is the recommended method for establishing a secure connection to PostgreSQL due to its more straightforward configuration and higher performance than SSH.

For more information about securing TCP/IP connections with TLS, see Secure TCP/IP connections in the PostgreSQL documentation.

Enable TLS on a connection

Import the module.
```
import devart.postgresql
```

Connect to a database using the connect() module method and obtain a connection object.

my_connection = devart.postgresql.connect(
    Server="your_server",
    Database="your_database",
    UserId="your_username",
    Password="your_password",
    UseSSL="True",
    SSLCACert="path_to_ca_cert",
    SSLCert="path_to_client_cert",
    SSLKey="path_to_client_key"
)

TLS parameters

The following table describes the TLS connection parameters.

Parameter	Description
`UseSSL`	Enables TLS connections.
`SSLCACert`	The CA certificate
`SSLCert`	The client certificate
`SSLKey`	The client private key
`SSLIgnoreServerCertificateValidity`	Specifies whether to verify the server certificate validity period during a TLS handshake. The possible values are `True` and `False`. The default value is `True`.
`SSLIgnoreServerCertificateConstraints`	Specifies whether to verify the server certificate for compliance with constraints during a TLS handshake. The possible values are `True` and `False`. The default value is `True`.
`SSLTrustServerCertificate`	Specifies whether to verify the server certificate chain during a TLS handshake. By default, the connector verifies the entire certificate chain. The possible values are `True` and `False`. If the parameter is set to `True`, the connector will bypass walking the certificate chain to validate trust.
`SSLIgnoreServerCertificateInsecurity`	Specifes whether to verify the server certificate signature security during a TLS handshake. The possible values are `True` and `False`. The default value is `False`.
`SSLMode`	Specifies whether and with what priority a TLS connection will be negotiated with the server. The possible values are: `smRequire` – (Default) Only TLS connections allowed `smPrefer` – Negotiates trying first a TLS connection, then if that fails, tries a regular non-TLS connection. `smAllow` – Negotiates trying first a non-TLS connection, then if that fails, tries a TLS connection. `smVerifyCA` – Verifies server identity by validating the server certificate chain up to the root certificate installed on the client machine. `smVerifyFull` – Verifies server identity by validating the server certificate chain up to the root certificate installed on the client machine and validates that the server hostname matches the server certificate.

Request Support

Python Connectors Forum

Provide Feedback