OneDB supports secure connections for PostgreSQL database traffic through the OneDB Listener. This allows PostgreSQL client applications to connect to OneDB using an encrypted connection before the traffic is forwarded to the target PostgreSQL Database.

Secure Connection is useful for environments that require encrypted database communication between applications and database access points.

Before You Begin

Before configuring the PostgreSQL client, make sure the following items are already prepared:

1. The PostgreSQL Database connection has been registered in OneDB.
2. The OneDB Listener has been created for the PostgreSQL connection.
3. Secure Connection has been enabled on the OneDB Listener.
4. The listener port is open and reachable from the client machine.
5. The required certificate or trust configuration is available on the PostgreSQL client side, if required by your SSL mode.

Listener Configuration in OneDB

In OneDB Web Console, go to the Listener configuration page and create or edit a PostgreSQL listener.

Make sure the following settings are configured:

After saving the listener configuration, start or reload the listener.

PostgreSQL Client Connection Format

For secure PostgreSQL connections, the client should connect to the OneDB Listener using SSL.

Example connection string:

psql "host=onedb-listener-host port=5433 dbname=app_db user=app_user sslmode=require"

Replace the following values based on your environment:

SSL Mode Options

PostgreSQL supports several SSL modes. The most common options are:

For most secure connection setups through OneDB Listener, use:

sslmode=require

For stricter certificate validation, use:

sslmode=verify-ca

The exact option depends on your organization’s security policy and certificate configuration.

Example Using psql

Example connection using psql:

psql "host=onedb-listener-host port=5433 dbname=app_db user=app_user sslmode=require"

You may also provide the password using the normal PostgreSQL password prompt or through your application’s secure configuration mechanism.

After connecting, run a simple test query:

SELECT 1;

If the query returns successfully, the PostgreSQL client is connected through the OneDB Listener using a secure connection.

Example Using JDBC

For Java applications, the PostgreSQL JDBC URL can include SSL parameters.

Example:

jdbc:postgresql://onedb-listener-host:5433/app_db?sslmode=require

Example Spring Boot configuration:

spring.datasource.url=jdbc:postgresql://onedb-listener-host:5433/app_db?sslmode=require
spring.datasource.username=app_user
spring.datasource.password=app_password
spring.datasource.driver-class-name=org.postgresql.Driver

For certificate validation, the JDBC URL can use stricter SSL modes, depending on the certificate setup:

jdbc:postgresql://onedb-listener-host:5433/app_db?sslmode=verify-full

Example Using Application Configuration

Many applications support PostgreSQL connection strings in URL format.

Example:

postgresql://app_user:app_password@onedb-listener-host:5433/app_db?sslmode=require

For production environments, avoid storing plain text passwords directly in configuration files. Use a secure secret management mechanism when available.

Certificate Configuration

Depending on the selected sslmode, PostgreSQL clients may require certificate files.

Common client-side certificate files include:

For example, when using sslmode=verify-ca or sslmode=verify-full, the PostgreSQL client may need a trusted root certificate.

Common default certificate location:

~/.postgresql/root.crt

Alternatively, certificate paths can be provided in the connection string:

psql "host=onedb-listener-host port=5433 dbname=app_db user=app_user sslmode=verify-full sslrootcert=/path/to/root.crt"

The exact certificate requirements may vary depending on the organization’s PostgreSQL client version and security policy.

Testing the Secure Connection

Use psql to test the secure connection:

psql "host=onedb-listener-host port=5433 dbname=app_db user=app_user sslmode=require"

Then run:

SELECT version();

Or:

SELECT 1;

If the connection is successful, queries will be routed through the OneDB Listener.

Troubleshooting

Unable to connect to the listener

Check that the OneDB Listener is running and the listener port is reachable from the client machine.

telnet onedb-listener-host 5433

or:

nc -vz onedb-listener-host 5433

SSL connection is not being used

Make sure the client connection string includes SSL mode:

sslmode=require

Do not use:

sslmode=disable

Certificate validation failed

If you use sslmode=verify-ca or sslmode=verify-full, check that the trusted root certificate is configured correctly.

Example:

psql "host=onedb-listener-host port=5433 dbname=app_db user=app_user sslmode=verify-full sslrootcert=/path/to/root.crt"

Also make sure the certificate hostname matches the host used in the connection string when using verify-full.

Application still connects directly to PostgreSQL

Make sure the application database host and port point to the OneDB Listener, not directly to the PostgreSQL Database.

Example:

host=onedb-listener-host
port=5433

Invalid database or user

Make sure the database name and username match the target PostgreSQL Database configuration.

Example:

dbname=app_db
user=app_user

Summary

PostgreSQL applications can connect securely to the OneDB Listener using SSL/TLS. By enabling Secure Connection on the listener and configuring the PostgreSQL client with the appropriate sslmode, application traffic to OneDB can be encrypted over the network.

This setup is recommended for environments that require secure database connectivity and stronger protection between applications and database access points.