Resolving SSL Connection Issues in PostgreSQL

Problem:

The application fails to establish an SSL connection to the database, displaying the error “could not accept SSL connection: Success” in the database logs. Despite this, manual `psql` connections using SSL work fine.

Process:

Step 1 – Verify Connection String:

Confirm that the application’s connection string includes the necessary SSL parameters.
Example:
postgresql://user:password@hostname:port/dbname?sslmode=require

Step 2 – Validate SSL Certificates:

Ensure the SSL certificates used by the application are valid, not expired, and have the correct permissions. If client certificates are used, verify that the certificate chain is complete and trusted.

Step 3 – Compare Manual and Application SSL Settings:

Compare the SSL settings used in the manual `psql` connection to those used by the application. Ensure that the application replicates the same SSL settings as the manual connection.

Step 4 – Review Database Server Logs:

Check the database server logs for additional details or errors when the application attempts to connect. Look for clues indicating why the SSL connection is rejected.

Example Configuration for PostgreSQL

An example of configuring the connection in a Python application using `psycopg2`:

import psycopg2

connection = psycopg2.connect(
    dbname="your_dbname",
    user="your_user",
    password="your_password",
    host="your_host",
    port="your_port",
    sslmode="require",
    sslrootcert="/path/to/ca-cert.pem",
    sslcert="/path/to/client-cert.pem",
    sslkey="/path/to/client-key.pem"
)

Solution:

1. Update Java and JDBC Driver:

Ensure the Java runtime and PostgreSQL JDBC driver are compatible with the database server version. Update to the latest versions.

2. Check JDBC URL Configuration:

Verify that the `sslrootcert` path is correct and accessible by the application. Double-check the path and permissions of `root.crt`.

3. Compare Environments:

Ensure there are no environmental differences between the manual `psql` execution and the application runtime, such as user permissions, environment variables, or network settings.

4. Enable Detailed Logging:

Enable detailed logging in the application to capture any additional error messages or stack traces for more context.

5. Review Database Server Logs:

Check PostgreSQL logs for any errors or warnings during the application connection attempts.

Example of Enabling Detailed Logging in PostgreSQL

Edit `postgresql.conf`:

logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_connections = on
log_disconnections = on
log_statement = 'all'
log_min_messages = error

Example of Checking Application Logs (Java)

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class DatabaseConnection {
    private static final Logger logger = LoggerFactory.getLogger(DatabaseConnection.class);

    public static void main(String[] args) {
        try {
            // Your database connection code
            Class.forName("org.postgresql.Driver");
            Connection connection = DriverManager.getConnection(
                "jdbc:postgresql://10.75.60.40:5400/bitbucket?sslmode=require&sslrootcert=root.crt",
                "bitbucketuser",
                "your_password"
            );
        } catch (Exception e) {
            logger.error("Database connection failed", e);
        }
    }
}

Conclusion:

The SSL connection issue was identified to be related to differences in configuration and environment between the manual `psql` connection and the application. By ensuring that the connection string, SSL certificates, and environmental factors were consistent, and by updating the necessary software components, the application was able to successfully connect to the database using SSL.