Thread: How to connect with PostgreSQL Database with SSL using Certificates and Key from client Eclipse in Java
How to connect with PostgreSQL Database with SSL using Certificates and Key from client Eclipse in Java
From
sujay kadam
Date:
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 = "jdbc:postgresql://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 = "jdbc:postgresql://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 = "jdbc:postgresql://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
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 = "jdbc:postgresql://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 = "jdbc:postgresql://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 = "jdbc:postgresql://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