Configure Secure Socket Layer in Oracle Cloud Infrastructure Database with PostgreSQL

Introduction

Oracle Cloud Infrastructure Database with PostgreSQL (OCI Database with PostgreSQL) supports only Secure Socket Layer (SSL) encryption for database instances. By using SSL, you can encrypt the connection between your applications and PostgreSQL database instances.

SSL encryption is essential for securing client connections to OCI Database with PostgreSQL instances, ensuring data protection during transmission. By configuring the hostssl connection type and using the sslmode parameter, you can control the level of SSL verification for added security. By default, TLSv1.2 is enabled for both client and server connections, aligning with industry-standard practices. For a fully secure and compliant PostgreSQL environment, leveraging these SSL settings is critical.

For secure client connections, OCI Database with PostgreSQL only allows the hostssl connection type in the pg_hba.conf file. The hostssl entry ensures that TCP/IP connections are encrypted using SSL, requiring clients to use SSL for authentication. You can also specify the level of SSL verification through the sslmode parameter, which includes options like require, verify-ca, and verify-full.

Currently, both the ssl_max_protocol_version and ssl_min_protocol_version parameters are configured in OCI Database with PostgreSQL to use TLSv1.2 for both client and server connections. However, it is important to note that OCI Database with PostgreSQL cannot enforce specific TLS versions, as these parameters are read-only system variables.

For more information on SSL support in PostgreSQL databases, see Secure TCP/IP Connections with SSL.

Note: SSL support is available in all OCI regions for PostgreSQL.

Objectives

Connect OCI Database with PostgreSQL using SSL encryption to secure database instances. It will emphasize the importance of configuring the hostssl connection type in the pg_hba.conf file to ensure encrypted TCP/IP connections, and demonstrate how the sslmode parameter controls SSL verification levels. Additionally, this tutorial will clarify the TLS version configuration for both client and server connections.

SSL Modes

The following table illustrates the risks addressed by different sslmode values, as well as the security implications and associated overhead for each.

sslmode	Eavesdropping protection	MITM protection	Statement
disable	No	No	I do not care about security, and I do not want to pay the overhead of encryption.
allow	Maybe	No	I do not care about security, but I will pay the overhead of encryption if the server insists on it.
prefer	Maybe	No	I do not care about encryption, but I wish to pay the overhead of encryption if the server supports it.
require	Yes	No	I want my data to be encrypted, and I accept the overhead. I trust that the network will make sure I always connect to the server I want.
verify-ca	Yes	Depends on CA policy	I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server that I trust.
verify-full	Yes	Yes	I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server I trust, and that it’s the one I specify.

For more information about the various sslmodes, see Table 31-1 SSL MODE DESCRIPTIONS.

In OCI Database with PostgreSQL, the sslmode must be set to require or higher (verify-ca or verify-full), as non-SSL connections are not supported due to regulatory and compliance requirements. SSL connectivity is mandatory to ensure that all in-transit connections are encrypted, in accordance with OCI’s compliance standards.

The certificates used in OCI Database with PostgreSQL database nodes are issued by a private Certificate Authority (CA) managed through the OCI Certificates service, which adheres to standardized OCI approved configurations. This approach differs from traditional self-signed certificates, which often utilize non-standard settings generated with tools like OpenSSL.

Establish a Connection to OCI Database with PostgreSQL using Various SSL Modes

Connection attempts to OCI Database with PostgreSQL without SSL are rejected with the following message.

no pg_hba.conf entry for host "yy.yy.yy.yy", user "xxxxx", database "zzzzzz", no encryption

Whenever the above error message appears, it is recommended to check the SSL mode value and set the sslmode parameter to require or higher to resolve the issue.

Set require SSL Mode.

This mode forces the connection to use SSL, but the server’s certificate is not verified.
```
psql "sslmode=require host=<endpoint_fqdn> dbname=<database_name> user=<user_name>"
```
Verify Certificate Authority with verify-ca SSL Mode.

This mode forces SSL, and the server certificate is verified against a trusted CA. It does not verify that the server certificate matches the host name.
```
psql "sslmode=verify-ca sslrootcert=<parent_directory>/<dbsystem.pub> host=<endpoint_fqdn> dbname=<database_name> user=<user_name>"
```
- sslrootcert: Download the CA certificate from the connection details section of your database system and save it to the following location: <parent_directory>/<dbsystem.pub> and this file contains the trusted CA certificates. This ensures that the server certificate is signed by a valid CA.
Verify Full with verify-full SSL Mode.

This mode forces SSL, verifies the server certificate against a trusted CA, and also checks that the server certificate matches the host you are connecting to (the hostname in your connection string).
```
psql "sslmode=verify-full sslrootcert=<parent_directory>/<dbsystem.pub> host=<endpoint_fqdn> dbname=<database_name> user=<user_name>"
```
- sslrootcert: Download the CA certificate from the connection details section of your database system and save it to the following location: <parent_directory>/<dbsystem.pub> and this file contains the trusted CA certificates.
Hostname matching: This is the most secure option because it ensures both the server certificate is signed by a private CA managed through the OCI Certificates service and that the certificate matches the expected hostname.

Determine the TLS Versions in OCI Database with PostgreSQL

The following image displays the encryption status of your connection when you connect to the OCI Database with PostgreSQL instance.

You can run the following query to retrieve information from pg_settings.

You can gather detailed information on your OCI Database with PostgreSQL instance’s SSL usage by process, client, and application using the following query.

You can use the system view pg_stat_ssl to see the SSL status of all connections. For more information, see Table 27.8. pg_stat_ssl View.

Configure SSL Mode in Different GUI Tools

The following are some open-source GUI tools that can be used to connect to OCI PostgreSQL databases. This task demonstrates how to configure the sslmode to require or higher in these tools to prevent connectivity issues when connecting to OCI PostgreSQL databases.

pgAdmin
1. In pgAdmin, navigate to Server, Register, Server and enter the necessary database connection details.
2. Click Parameters, go to the SSL section, where you can configure the SSL mode setting.
3. Ensure that sslmode is set to require or a higher level (such as verify-ca or verify-full) from the available drop-down menu to comply with secure connection requirements.
DBeaver
1. In DBeaver, navigate to Create and click Connections.
2. Select PostgreSQL from the list of database types and click Next to continue.
3. In Connection Settings, select SSL section within the connection settings to configure SSL parameters.
4. Set SSL mode to require.

Acknowledgments

Author - Kaviya Selvaraj (Senior Member Technical Staff)

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.

Title and Copyright Information

Configure Secure Socket Layer in OCI Database with PostgreSQL

G39322-01