Обсуждение: How to connect with PostgreSQL Database with SSL using Certificates and Key from client Eclipse in Java
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