Regarding SSL Enablement in PostgreSQL Database on different port

[sujay kadam]

Hi PostgreSQL Team,


I want to enable SSL in PostgreSQL Database on a new port.

I don’t want the default port that is 5432 SSL enabled, but I want to
configure another port to enable SSL on it.

As per my requirement, I cannot use the same port for normal connection and
SSL connection.

Hence, we require a new port to be SSL enabled.


Please guide us with proper information and links to achieve the above task.



Thanks & Regards,
Sujay Kadam

SSL Enablement in Postgres via Client App like PG-ADMIN-4, Java.

[sujay kadam]

Hi Team.

I have enabled SSL in postgres on a different port using pgbouncer.

I have changed the default port to 6432 and made it SSL enabled and
configured pgbouncer to listen port 5432 which is non-ssl.

But we are connecting directly using port, not by uploading a certificate
or setting up SSL certificate and properties in pgadmin4 and java.

Our requirement is :-
 we should connect ssl-enabled port by configuring ssl-properties and
providing certificates path in any client tool such as pgadmin4 or using
java.


Below are some questions that needs to clarified :-

How to enable SSL in Postgres database?

How to establish the SSL connection from the client (Eg. Java )? Do we need
to add any certifications at client side for SSL authentication ?

Do we have any documentation for SSL ?


Thanks & Regards
Sujay Kadam

Assistance Needed: SSL Configuration in Postgres and Client Connection Setup

[sujay kadam]

Hi Team.

I would like to inform you that I have successfully enabled SSL on a
different port in Postgres using pgbouncer. Specifically, I have modified
the default port to 6432 and configured it for SSL while setting up
pgbouncer to listen to a non-SSL port at 5432.

However, I have noticed that some team members are connecting directly to
the SSL-enabled port without uploading any certificates or configuring SSL
properties in pgadmin4 or Java. Our requirement is to establish SSL
connections by properly configuring SSL properties and providing
certificate paths in client tools such as pgadmin4 or Java.

In light of this, I would like to clarify a few things:

How can we enable SSL in Postgres database?

What is the process for establishing an SSL connection from a client like
Java? Do we need to add any certifications at the client-side for SSL
authentication?

Do we have any documentation or resources available that can guide us in
configuring SSL?


Thanks & Regards
Sujay Kadam

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

[sujay kadam]

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(ConnectionFactoryIm