How to connect with PostgreSQL Database with SSL using Certificates and Key from client Eclipse in Java

[sujay kadam <sujaykadam02@xxxxxxxxx>]

Hi Team.



I am trying to connect with PostgreSQL database from client with SSL enabled on server 10.30.32.186 port 6432 using below java code -

I am using certificates ( [server-cert.pem, server-key.pem, ca.cert] and [postgresql.crt, postgresql.pk8, root.crt] ).

Suggest me if there are any specific java understandable certificate and key file format.


    package com.ssl;

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;

    public class DBConnect {

private final String url = "" href="http://10.30.32.186:6432/postgres?sslmode=require&sslcert=/root/.postgresql/postgresql.crt&sslkey=/root/.postgresql/postgresql.pk8&sslrootcert=/root/.postgresql/root.crt&sslpassword=postgress">10.30.32.186:6432/postgres?sslmode=require&sslcert=/root/.postgresql/postgresql.crt&sslkey=/root/.postgresql/postgresql.pk8&sslrootcert=/root/.postgresql/root.crt&sslpassword=postgress";

    private final String user = "postgres";
    private final String password = "postgres123";

    /**
     * Connect to the PostgreSQL database
     *
     * @return a Connection object
     */
    public Connection connect() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to the PostgreSQL server successfully.");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return conn;
    }

public static void main(String[] args) {

DBConnect db = new DBConnect();
db.connect();

}

    }

Gives Error -

    SSL error: -1



Code NO 2 -

    package SSL_Enablement;

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.Properties;

    public class PostgresSSLConnection {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            // Set SSL properties
            Properties props = new Properties();
            props.setProperty("user", "postgres");
            props.setProperty("password", "postgres123");
            props.setProperty("ssl", "true");
            props.setProperty("https.protocols", "TLSv1.2");
            props.setProperty("sslmode", "Verify-CA");
            props.setProperty("sslcert", "/root/.postgresql/server-cert.pem");
            props.setProperty("sslkey", "/root/.postgresql/server-key.pem");
            props.setProperty("sslrootcert", "/root/.postgresql/ca.cert");

            // Initialize SSL context
            Class.forName("org.postgresql.Driver");
            String url = "" href="http://10.30.32.186:6432/postgres">10.30.32.186:6432/postgres";
            conn = DriverManager.getConnection(url, props);
            System.out.println("Connected DB using SSL");
            // Use the connection...
            // ...

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    }

Gives Error -

        org.postgresql.util.PSQLException: Could not read SSL key file /root/.postgresql/server-key.pem.
 at org.postgresql.ssl.LazyKeyManager.getPrivateKey(LazyKeyManager.java:284)
 at sun.security.ssl.AbstractKeyManagerWrapper.getPrivateKey(SSLContextImpl.java:1552)
 at sun.security.ssl.X509Authentication$X509PossessionGenerator.createClientPossession(X509Authentication.java:220)
 at sun.security.ssl.X509Authentication$X509PossessionGenerator.createPossession(X509Authentication.java:175)
 at sun.security.ssl.X509Authentication.createPossession(X509Authentication.java:88)
 at sun.security.ssl.CertificateMessage$T13CertificateProducer.choosePossession(CertificateMessage.java:1080)
 at sun.security.ssl.CertificateMessage$T13CertificateProducer.onProduceCertificate(CertificateMessage.java:1101)
 at sun.security.ssl.CertificateMessage$T13CertificateProducer.produce(CertificateMessage.java:958)
 at sun.security.ssl.SSLHandshake.produce(SSLHandshake.java:421)
 at sun.security.ssl.Finished$T13FinishedConsumer.onConsumeFinished(Finished.java:989)
 at sun.security.ssl.Finished$T13FinishedConsumer.consume(Finished.java:852)
 at sun.security.ssl.SSLHandshake.consume(SSLHandshake.java:377)
 at sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:444)
 at sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:422)
 at sun.security.ssl.TransportContext.dispatch(TransportContext.java:182)
 at sun.security.ssl.SSLTransport.decode(SSLTransport.java:152)
 at sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1397)
 at sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1305)
 at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:440)
 at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:41)
 at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:584)
 at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:168)
 at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:235)
 at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
 at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:247)
 at org.postgresql.Driver.makeConnection(Driver.java:434)
 at org.postgresql.Driver.connect(Driver.java:291)
 at java.sql.DriverManager.getConnection(DriverManager.java:664)
 at java.sql.DriverManager.getConnection(DriverManager.java:208)
 at SSL_Enablement.PostgresSSLConnection.main(PostgresSSLConnection.java:26)
    Caused by: java.io.IOException: extra data given to DerValue constructor
 at sun.security.util.DerValue.init(DerValue.java:423)
 at sun.security.util.DerValue.<init>(DerValue.java:306)
 at sun.security.util.DerValue.<init>(DerValue.java:347)
 at sun.security.util.DerValue.wrap(DerValue.java:334)
 at sun.security.util.DerValue.wrap(DerValue.java:319)
 at javax.crypto.EncryptedPrivateKeyInfo.<init>(EncryptedPrivateKeyInfo.java:84)
 at org.postgresql.ssl.LazyKeyManager.getPrivateKey(LazyKeyManager.java:236)
 ... 29 more



Code NO 3 -

    package SSL_Enablement;

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.Properties;

    public class PostgresSSLConnection {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            // Set SSL properties
            Properties props = new Properties();
            props.setProperty("user", "postgres");
            props.setProperty("password", "postgres123");
            props.setProperty("ssl", "true");
            props.setProperty("https.protocols", "TLSv1.2");
            props.setProperty("sslmode", "Verify-CA");
            props.setProperty("sslcert", "/root/.postgresql/postgresql.crt");
            props.setProperty("sslkey", "/root/.postgresql/postgresql.pk8");
            props.setProperty("sslrootcert", "/root/.postgresql/root.crt");

            // Initialize SSL context
            Class.forName("org.postgresql.Driver");
            String url = "" href="http://10.30.32.186:6432/postgres">10.30.32.186:6432/postgres";
            conn = DriverManager.getConnection(url, props);
            System.out.println("Connected DB using SSL");
            // Use the connection...
            // ...

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    }

Gives Error -

        org.postgresql.util.PSQLException: SSL error: -1
   at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:43)
   at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:584)
   at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:168)
   at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:235)
   at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
   at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:247)
   at org.postgresql.Driver.makeConnection(Driver.java:434)
   at org.postgresql.Driver.connect(Driver.java:291)
   at java.sql.DriverManager.getConnection(DriverManager.java:664)
   at java.sql.DriverManager.getConnection(DriverManager.java:208)
   at SSL_Enablement.PostgresSSLConnection.main(PostgresSSLConnection.java:26)
    Caused by: javax.net.ssl.SSLException: -1
at sun.security.ssl.Alert.createSSLException(Alert.java:133)
at sun.security.ssl.TransportContext.fatal(TransportContext.java:331)
at sun.security.ssl.TransportContext.fatal(TransportContext.java:274)
at sun.security.ssl.TransportContext.fatal(TransportContext.java:269)
at sun.security.ssl.SSLSocketImpl.handleException(SSLSocketImpl.java:1568)
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:446)
at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:41)
... 10 more
    Caused by: java.lang.ArrayIndexOutOfBoundsException: -1
at org.postgresql.ssl.LazyKeyManager.chooseClientAlias(LazyKeyManager.java:105)
at sun.security.ssl.AbstractKeyManagerWrapper.chooseClientAlias(SSLContextImpl.java:1531)
at sun.security.ssl.X509Authentication$X509PossessionGenerator.createClientPossession(X509Authentication.java:200)
at sun.security.ssl.X509Authentication$X509PossessionGenerator.createPossession(X509Authentication.java:175)
at sun.security.ssl.X509Authentication.createPossession(X509Authentication.java:88)
at sun.security.ssl.CertificateMessage$T13CertificateProducer.choosePossession(CertificateMessage.java:1080)
at sun.security.ssl.CertificateMessage$T13CertificateProducer.onProduceCertificate(CertificateMessage.java:1101)
at sun.security.ssl.CertificateMessage$T13CertificateProducer.produce(CertificateMessage.java:958)
at sun.security.ssl.SSLHandshake.produce(SSLHandshake.java:421)
at sun.security.ssl.Finished$T13FinishedConsumer.onConsumeFinished(Finished.java:989)
at sun.security.ssl.Finished$T13FinishedConsumer.consume(Finished.java:852)
at sun.security.ssl.SSLHandshake.consume(SSLHandshake.java:377)
at sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:444)
at sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:422)
at sun.security.ssl.TransportContext.dispatch(TransportContext.java:182)
at sun.security.ssl.SSLTransport.decode(SSLTransport.java:152)
at sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1397)
at sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1305)
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:440)
... 11 more