SSL connection is not established

[Shalaka1197]

I am getting below exception when I am trying to connect to postgres using ssl. I am connecting by using root.crt file and verify-full mode.

Caused by: sun.security.validator.ValidatorException: PKIX path validation failed: java.security.cert.CertPathValidatorException: Path does not chain with any of the trust anchors, at java.base/sun.security.validator.PKIXValidator.doValidate(PKIXValidator.java:369), at java.base/sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:275), at java.base/sun.security.validator.Validator.validate(Validator.java:264), at java.base/sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:231), at java.base/sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:132), at java.base/sun.security.ssl.CertificateMessage$T13CertificateConsumer.checkServerCerts(CertificateMessage.java:1341), ... 84 common frames omittedCaused by: java.security.cert.CertPathValidatorException: Path does not chain with any of the trust anchors, at java.base/sun.security.provider.certpath.PKIXCertPathValidator.validate(PKIXCertPathValidator.java:157), at java.base/sun.security.provider.certpath.PKIXCertPathValidator.engineValidate(PKIXCertPathValidator.java:83), at java.base/java.security.cert.CertPathValidator.validate(CertPathValidator.java:309), at java.base/sun.security.validator.PKIXValidator.doValidate(PKIXValidator.java:364), ... 89 common frames omitted

@davecramer Please let me know what can be the issue ?

[davecramer]

did you follow https://jdbc.postgresql.org/documentation/ssl/

[Shalaka1197]

@davecramer Please see below parameters used. SSL =true ; SSL_MODE= verify-full; SSL_RESPONSE_TIMEOUT =5000; SSL_ROOT_CERT = root crt path

[davecramer]

Hi @Shalaka1197

If you are doing verify full then you also need the client certificates.

[Shalaka1197]

@davecramer in which mode root.crt file verification is done ? In the documentation this is not specified point - "verify full then you also need the client certificates" https://jdbc.postgresql.org/documentation/ssl/

[davecramer]

The server root.crt will be verified on every SSL connection

[Shalaka1197]

Is there any test cases for expired root.crt server.crt files ? How is it validated from postgres end ? @davecramer

[Shalaka1197]

@davecramer How to check the connection is made with ssl or without ssl in postgres ?

[davecramer]

install the sslinfo contrib module https://www.postgresql.org/docs/current/sslinfo.html#SSLINFO

[Shalaka1197]

@davecramer for checking the validity of server and root certificate is there any use case or test case ?

[davecramer]

You can just use openssl to verify your certs

[Shalaka1197]

@davecramer From postgres code is there any mechanism to check certificate expiry for root.crt file and client.crt files ? It is connecting for expired certificates as well. Its seems to be bug from postgres side.

[davecramer]

https://www.enterprisedb.com/blog/authentication-ssl-client-certificates#:~:text=If%20the%20authentication%20mechanism%20in%20your%20pg_hba.conf%20file,that%20authentication%20method%2C%20giving%20you%20two%20factor%20authentication.

[Shalaka1197]

@davecramer Please see below use case of mine. I am trying to connect with the postgres via one way tls protocol(only root.crt file verified). so from postgres server side we have configured the server.crt file and root.crt file and its path is given under the postgresql.conf file. Now I am trying to connect to server application via root.crt file from client application. The below is code snippet. SSL =true ; SSL_MODE= verify-ca; SSL_RESPONSE_TIMEOUT =5000; SSL_ROOT_CERT = /tmp/server.crt for ssl root cert parameter we are passing server.crt as it is giving the issues when I passed root.crt file. It is able to connect via client application as well using server.crt file. Now for negative test case scenario, I have created server.crt file with 1 day expiry and passed the same file in ssl_root_cert parameter , after one day the certificate is expired for server.crt file . From psql command part below is the error we got. PSQL error for expired server.crt file - certificate verification failed. java application - its able to connect via expired server.crt file

Now I am given root.crt file for ssl_root_cert parameter and got the below error. Caused by: java.security.cert.CertificateExpiredException: NotAfter: Fri Aug 23 11:44:37 UTC 2024, at java.base/sun.security.x509.CertificateValidity.valid(CertificateValidity.java:277) , at java.base/sun.security.x509.X509CertImpl.checkValidity(X509CertImpl.java:627) , at java.base/sun.security.provider.certpath.BasicChecker.verifyValidity(BasicChecker.java:190) , at java.base/sun.security.provider.certpath.BasicChecker.check(BasicChecker.java:144)

Now please tell me why server.crt file is able to connect via postgres even its certificate is expired ? root.crt file is giving error and server.crt file also should throw the same error right ?

[davecramer]

I would be happy to look into this for you.

Please provide detailed instructions on how to replicate your problem

Cheers, Dave

[Shalaka1197]

@davecramer When you are creating root.crt and server.crt file , try to create it with 0 days validity. for example command : openssl req -new -x509 -days 0 -nodes -text -out server.crt -keyout server.key -subj "/CN=localhost"

and put both the certificates in postgres server. Below is sample code for replicating the issue.

ssl: true sslmode:verify-ca sslrootcert: resources/server.crt

public class ConnectDB {

private final String url = "jdbc:postgresql://abcd:5432/postgres?" +
        "sslmode=verify-ca&sslrootcert=resources/server.crt";

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

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) {

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

}

`

[davecramer]

Thanks, I was able to replicate this issue.

[Shalaka1197]

@davecramer Looking for support from postgres end for this issue.

[davecramer]

@Shalaka1197 I don't think this is a server issue. Turns out that java doesn't check the end date of the certificate

[Shalaka1197]

@davecramer But for root.crt file its checking validity and throwing certificate error. Why is it not happening server.crt file ? For java its doesn't check validity do you have any reference links for the same ?

[davecramer]

https://security.stackexchange.com/questions/105637/does-trustmanager-pkix-or-rfc-3280-x-509-really-check-the-expiration-date-of-a

[Shalaka1197]

@davecramer How is it checking expiry for root.crt files and throwing below exception ? Why is it not applicable for server.crt file ?

Caused by: java.security.cert.CertificateExpiredException: NotAfter: Fri Aug 23 11:44:37 UTC 2024, at java.base/sun.security.x509.CertificateValidity.valid(CertificateValidity.java:277) , at java.base/sun.security.x509.X509CertImpl.checkValidity(X509CertImpl.java:627) , at java.base/sun.security.provider.certpath.BasicChecker.verifyValidity(BasicChecker.java:190) , at java.base/sun.security.provider.certpath.BasicChecker.check(BasicChecker.java:144)

[davecramer]

I am on vacation and will be back next week.