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:
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
Re: How to connect with PostgreSQL Database with SSL using Certificates and Key from client Eclipse in Java
From
Aleksander Alekseev
Date:
Hi Sujay, > I am trying to connect with PostgreSQL database from client with SSL enabled on server 10.30.32.186 port 6432 using belowjava code - This mailing list is dedicated to the PostgreSQL Core development. I don't think you will find many people interested in your question and/or familiar with Java. I think you should address the question to pgsql-general@ mailing list or StackOverflow. (If you believe there is a bug in the DBMS core please provide simpler steps to reproduce, ideally with pgsql utility and maybe bash.) -- Best regards, Aleksander Alekseev
Re: How to connect with PostgreSQL Database with SSL using Certificates and Key from client Eclipse in Java
From
sujay kadam
Date:
Thank you for your response.
On Fri, 19 May 2023 at 5:11 PM, Aleksander Alekseev <aleksander@timescale.com> wrote:
Hi Sujay,
> 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 -
This mailing list is dedicated to the PostgreSQL Core development. I
don't think you will find many people interested in your question
and/or familiar with Java.
I think you should address the question to pgsql-general@ mailing list
or StackOverflow.
(If you believe there is a bug in the DBMS core please provide simpler
steps to reproduce, ideally with pgsql utility and maybe bash.)
--
Best regards,
Aleksander Alekseev