8000 Postgres with SSL throws "PSQLException: SSL error: null" · Issue #8624 · gocd/gocd · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Postgres with SSL throws "PSQLException: SSL error: null" #8624

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking &l 8000 dquo;Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
pkutlunin opened this issue Oct 8, 2020 · 8 comments
Closed

Postgres with SSL throws "PSQLException: SSL error: null" #8624

pkutlunin opened this issue Oct 8, 2020 · 8 comments
Milestone
Release 20.9.0

Comments

@pkutlunin
Copy link
Bug Report
Summary

When using a Postgres database with SSL enabled, GoCD fails to start because it cannot establish a connection with the database.

Details

When GoCD server starts up, it seems to successfully connect to the database at first using SSL and run the db-migration checks:

ConnectionManager:110 - Loading database config from file config/db.properties
DatabaseMigrator:40 - Upgrading database, this might take a while depending on the size of the database.
************************************************************************ 
DatabaseMigrator:49 - ************************************************************************
WARNING: Shutting down your server at this point will lead to a database corruption. Please wait until the database upgrade completes.
DatabaseMigrator:49 - WARNING: Shutting down your server at this point will lead to a database corruption. Please wait until the database upgrade completes.
************************************************************************
DatabaseMigrator:49 - ************************************************************************
INFO: Database upgrade completed successfully.
DatabaseMigrator:57 - Database upgrade completed successfully.
DataMigrationRunner:34 - Running data migrations...
DataMigrationRunner:49 - Data migration took 3 ms
DataMigrationRunner:49 - Data migration took 2 ms
DataMigrationRunner:39 - Data migrations completed.

Then when GoCD tries to fetch pipelines, stages, etc., it fails with the exception:

...
Caused by: org.postgresql.util.PSQLException: SSL error: null 
     at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:43) 
...

Full stack trace is attached below.

Basic environment details
  • Go Version: 20.8.0 (12213-1e23a06e496205ced5f1a8e83d9b209fc0a290cb)
  • JAVA Version: 14.0.2
  • OS: Linux 4.19.112+
Steps to Reproduce

Prerequisites:

  1. Set up postgres: https://docs.gocd.org/current/installation/configuring_database/postgres.html (Steps 1-2)
  2. Obtain 3 files from Postgres DB (server certificate, client's public key, client's private key)
  3. Convert them to DER format

Steps:

  1. Modify config/db.properties so it would have all necessary settings to connect to Postgres with SSL enabled:
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://postgres.company.com:5432/gocd
db.user=gocd
db.password=password
db.connectionProperties.sslmode=verify-ca
db.connectionProperties.sslrootcert=/godata/config/db-certificates/server-public.der
db.connectionProperties.sslcert=/godata/config/db-certificates/gocd-public.der
db.connectionProperties.sslkey=/godata/config/db-certificates/devops-postgres-db-gocd-private.pk8
  1. Restart GoCD server
  2. Observe multiple exception during startup in the logs.
Actual Results

Exceptions during startup:

Caused by: java.lang.RuntimeException: org.springframework.dao.DataAccessResourceFailureException: could not execute update query; nested exception is org.hibernate.exception.JDBCConnectionException: could not execute update query 
	at com.thoughtworks.go.server.initializers.ApplicationInitializer.onApplicationEvent(ApplicationInitializer.java:158) 
	at com.thoughtworks.go.server.initializers.ApplicationInitializer.onApplicationEvent(ApplicationInitializer.java:49) 
	at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172) 
	at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165) 
	at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139) 
	at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:393) 
	at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:347) 
	at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:883) 
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:545) 
	at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:443) 
	at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:325) 
	at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:107) 
	at org.eclipse.jetty.server.handler.ContextHandler.callContextInitialized(ContextHandler.java:933) 
	at org.eclipse.jetty.servlet.ServletContextHandler.callContextInitialized(ServletContextHandler.java:553) 
	at org.eclipse.jetty.server.handler.ContextHandler.startContext(ContextHandler.java:892) 
	at org.eclipse.jetty.servlet.ServletContextHandler.startContext(ServletContextHandler.java:356) 
	at org.eclipse.jetty.webapp.WebAppContext.startWebapp(WebAppContext.java:1445) 
	at org.eclipse.jetty.webapp.WebAppContext.startContext(WebAppContext.java:1409) 
	at org.eclipse.jetty.server.handler.ContextHandler.doStart(ContextHandler.java:825) 
	at org.eclipse.jetty.servlet.ServletContextHandler.doStart(ServletContextHandler.java:275) 
	at org.eclipse.jetty.webapp.WebAppContext.doStart(WebAppContext.java:524) 
	at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:72) 
	at org.eclipse.jetty.deploy.bindings.StandardStarter.processBinding(StandardStarter.java:46) 
	at org.eclipse.jetty.deploy.AppLifeCycle.runBindings(AppLifeCycle.java:188) 
	at org.eclipse.jetty.deploy.DeploymentManager.requestAppGoal(DeploymentManager.java:513) 
	at org.eclipse.jetty.deploy.DeploymentManager.addApp(DeploymentManager.java:154) 
	at com.thoughtworks.go.server.Jetty9Server.startHandlers(Jetty9Server.java:179) 
	at com.thoughtworks.go.server.Jetty9Server.start(Jetty9Server.java:129) 
	at com.thoughtworks.go.server.GoServer.startServer(GoServer.java:62) 
	... 14 more 
Caused by: org.springframework.dao.DataAccessResourceFailureException: could not execute update query; nested exception is org.hibernate.exception.JDBCConnectionException: could not execute update query 
	at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:639) 
	at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:416) 
	at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:416) 
	at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:342) 
	at com.thoughtworks.go.server.persistence.ServerBackupRepository.markInProgressBackupsAsAborted(ServerBackupRepository.java:65) 
	at com.thoughtworks.go.server.service.BackupService.initialize(BackupService.java:117) 
	at com.thoughtworks.go.server.initializers.ApplicationInitializer.onApplicationEvent(ApplicationInitializer.java:154) 
	... 42 more 
Caused by: org.hibernate.exception.JDBCConnectionException: could not execute update query 
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99) 
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) 
	at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:110) 
	at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:423) 
	at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:283) 
	at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1288) 
	at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:117) 
	at com.thoughtworks.go.server.persistence.ServerBackupRepository.lambda$markInProgressBackupsAsAborted$2(ServerBackupRepository.java:70) 
	at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411) 
	... 46 more 
Caused by: org.postgresql.util.PSQLException: SSL error: null 
	at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:43) 
	at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:445) 
	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:139) 
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:196) 
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) 
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:211) 
	at org.postgresql.Driver.makeConnection(Driver.java:459) 
	at org.postgresql.Driver.connect(Driver.java:261) 
	at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:55) 
	at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:355) 
	at org.apache.commons.pool2.impl.GenericObjectPool.create(GenericObjectPool.java:899) 
	at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:429) 
	at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:354) 
	at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134) 
	at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:753) 
	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111) 
	at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:229) 
	at com.sun.proxy.$Proxy34.prepareStatement(Unknown Source) 
	at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534) 
	at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:116) 
	at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109) 
	at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:88) 
	... 52 more 
Caused by: javax.net.ssl.SSLException 
	at java.base/sun.security.ssl.Alert.createSSLException(Unknown Source) 
	at java.base/sun.security.ssl.TransportContext.fatal(Unknown Source) 
	at java.base/sun.security.ssl.TransportContext.fatal(Unknown Source) 
	at java.base/sun.security.ssl.TransportContext.fatal(Unknown Source) 
	at java.base/sun.security.ssl.SSLSocketImpl.handleException(Unknown Source) 
	at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source) 
	at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source) 
	at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:41) 
	... 73 more 
Caused by: java.lang.NullPointerException 
	at org.bouncycastle.crypto.signers.PSSSigner.generateSignature(Unknown Source) 
	at org.bou
8000
ncycastle.jcajce.provider.asymmetric.rsa.PSSSignatureSpi.engineSign(Unknown Source) 
	at java.base/java.security.Signature$Delegate.engineSign(Unknown Source) 
	at java.base/java.security.Signature.sign(Unknown Source) 
	at java.base/sun.security.ssl.CertificateVerify$T13CertificateVerifyMessage.<init>(Unknown Source) 
	at java.base/sun.security.ssl.CertificateVerify$T13CertificateVerifyProducer.onProduceCertificateVerify(Unknown Source) 
	at java.base/sun.security.ssl.CertificateVerify$T13CertificateVerifyProducer.produce(Unknown Source) 
	at java.base/sun.security.ssl.SSLHandshake.produce(Unknown Source) 
	at java.base/sun.security.ssl.Finished$T13FinishedConsumer.onConsumeFinished(Unknown Source) 
	at java.base/sun.security.ssl.Finished$T13FinishedConsumer.consume(Unknown Source) 
	at java.base/sun.security.ssl.SSLHandshake.consume(Unknown Source) 
	at java.base/sun.security.ssl.HandshakeContext.dispatch(Unknown Source) 
	at java.base/sun.security.ssl.HandshakeContext.dispatch(Unknown Source) 
	at java.base/sun.security.ssl.TransportContext.dispatch(Unknown Source) 
	at java.base/sun.security.ssl.SSLTransport.decode(Unknown Source) 
	at java.base/sun.security.ssl.SSLSocketImpl.decode(Unknown Source) 
	at java.base/sun.security.ssl.SSLSocketImpl.readHandshakeRecord(Unknown Source) 
	... 76 more
Possible Fix

It seems to be a known bug in bouncy castle library and it has been already fixed in release 1.65: bcgit/bc-java#633
Updating the dependency to the latest version of bouncycastle should fix the issue.
@maheshp
Copy link
Contributor
maheshp commented Oct 12, 2020

@pkutlunin I tried enabling SSL connection between GoCD and a RDS PostgreSQL instance and it worked,

  • Brought up a PostgreSQL instance on RDS
  • Configured GoCD db.properties with the following configuration,
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://******.*****.rds.amazonaws.com:5432/test
db.user=postgres
db.password=******
db.connectionProperties.sslmode=verify-ca
db.connectionProperties.sslrootcert=rds-ca-2019-root.pem

I need to setup a PostgreSQL instance to verify Client Certificate Authentication though, will verify that in a while.

@pkutlunin
Copy link
Author

@maheshp thanks for checking this!

Based on the stack trace (PSSSigner.generateSignature),this exception seems to happen when a client is challenged to authorize. So I am looking forward to seeing your results with the enabled client authentication.

@pkutlunin
Copy link
Author

@maheshp I verified that upgrade of the bouncycastle dependency to version 1.65 solves the issue.

Here is a simple JDBC-client that reproduces the issue:

import org.bouncycastle.jce.provider.BouncyCastleProvider;

import java.security.Provider;
import java.security.Security;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Arrays;

public class TestPostgresAndBc {

    public static void main(String[] args) throws Exception {
        Security.insertProviderAt(new BouncyCastleProvider(), 1);
        
        String user = "gocd";
        String password = "...";
        String connectionString = "jdbc:postgresql://postgres.company.com:5432/gocd"
                + "?sslmode=verify-ca"
                + "&sslrootcert=server-public.der"
                + "&sslcert=gocd-public.der"
                + "&sslkey=gocd-private.pk8";

        try (Connection connection = DriverManager.getConnection(connectionString, user, password)) {
            try (Statement statement = connection.createStatement()) {
                try (ResultSet resultSet = statement.executeQuery("SELECT 1")) {
                    while (resultSet.next()) {
                        System.out.printf("ResultSet: %s%n", resultSet.getString(1));
                    }
                }
            }
        }
    }

}

compile: javac -cp postgresql-42.2.11.jar:bcprov-jdk15on-1.59.jar TestPostgresAndBc.java
run: java -cp postgresql-42.2.11.jar:bcprov-jdk15on-1.59.jar:./ TestPostgresAndBc
I used openjdk version "11.0.9" 2020-10-20.
Also you'll need a PostgreSQL instance itself with enabled Client Certificate Authentication.

And then, there will be an exact NPE thrown as the one I attached above.

If you run the same class with a BC.jar version >= 1.65, the issue is gone.

@maheshp maheshp mentioned this issue Nov 2, 2020
Merged
@maheshp
Copy link
Contributor
maheshp commented Nov 2, 2020

@pkutlunin we have a PR #8716 to upgrade BC to 1.65. Will you be able to take the installers generated for the PR and verify if it fixes the issue.
The installers can be downloaded from - https://build.gocd.org/go/tab/build/detail/installers-pull_8716/1/dist/1/dist#. You will have to login as guest user.

@pkutlunin
Copy link
Author

@maheshp I verified that this PR has fixed the issue:

  • there were no errors in the server logs;
  • there was a message saying that my db.properties were picked up;
  • checked the connection from Postgres side - there were some running queries from gocd user.

I'll wait for a new release to confirm that it is fixed. Once I check it with a release version, I'll close this issue.
Thank you!

@maheshp
Copy link
Contributor
maheshp commented Nov 3, 2020

@pkutlunin thanks for verifying this.

@maheshp maheshp closed this as completed in 2ab5c58 Nov 3, 2020
maheshp added a commit that referenced this issue Nov 3, 2020
@maheshp
Merge pull request #8716 from maheshp/upgrade_bc
99a8b00 
Upgrade BC version to '1.65' to fix #8624
@maheshp maheshp reopened this Nov 3, 2020
@maheshp maheshp added this to the Release 20.9.0 milestone Nov 3, 2020
@maheshp maheshp closed this as completed in 0d5cb23 Nov 5, 2020
maheshp added a commit that referenced this issue Nov 5, 2020
@maheshp
Merge pull request #8735 from gocd/revert-8716-upgrade_bc
07d35b7 
Revert "Upgrade BC version to '1.65' to fix #8624"
maheshp added a commit to maheshp/gocd that referenced this issue Nov 9, 2020
@maheshp
Upgrade BC to 1.67, fixes gocd#8624
2fa834e
maheshp added a commit that referenced this issue Nov 9, 2020
@maheshp
Merge pull request #8752 from maheshp/upgrade_bc
cccb496 
Upgrade BC to 1.67, fixes #8624
@pkutlunin
Copy link
Author

I confirm this works in the release 20.9.0.

One remark about the release notes for 20.9.0:

#8716 - Fix issue with Postgres connection with self-signed certificates

I've never mentioned it but those were legit certificates issued by Google Cloud SQL.

@maheshp
Copy link
Contributor
maheshp commented Nov 11, 2020

The release notes has been updated. Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
Release 20.9.0
Development

No branches or pull requests
2 participants
@maheshp @pkutlunin
0