I am encountering a null pointer exception when trying to connect to CloudSQL PostGreSQL using a service account

[tcs-roshan]

Describe the bug
I am encountering a null pointer exception when trying to connect to CloudSQL PostGreSQL using a service account in a Spring Boot Application. I have already configured the spring.cloud.gcp.sql.credentials.location and spring.cloud.gcp.sql.credentials.encoded-key properties, and I have also enabled the spring.cloud.gcp.sql.enable-iam-auth property.

Starting...
2023-12-28T12:18:27.271-05:00 INFO 31088 --- [ main] c.g.cloud.sql.core.CoreSocketFactory : First Cloud SQL connection, generating RSA key pair.
2023-12-28T12:18:57.447-05:00 DEBUG 31088 --- [ main] com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - Failed to create/setup connection: Something unusual has occurred to cause the driver to fail. Please report this exception.
2023-12-28T12:18:57.448-05:00 DEBUG 31088 --- [ main] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Cannot acquire connection from data source

org.postgresql.util.PSQLException: Something unusual has occurred to cause the driver to fail. Please report this exception.
at org.postgresql.Driver.connect(Driver.java:320)
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:359)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:470)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561)
at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:100)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)
at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:160)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:118)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:342)
at org.springframework.boot.jdbc.EmbeddedDatabaseConnection.isEmbedded(EmbeddedDatabaseConnection.java:168)
at org.springframework.boot.jdbc.init.DataSourceScriptDatabaseInitializer.isEmbeddedDatabase(DataSourceScriptDatabaseInitializer.java:67)
at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.isEnabled(AbstractScriptDatabaseInitializer.java:84)
at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.applyScripts(AbstractScriptDatabaseInitializer.java:107)
at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.applySchemaScripts(AbstractScriptDatabaseInitializer.java:98)
at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.initializeDatabase(AbstractScriptDatabaseInitializer.java:76)
at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.afterPropertiesSet(AbstractScriptDatabaseInitializer.java:66)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1822)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1771)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:601)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:523)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:325)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:323)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:312)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:254)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1441)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1348)
at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:911)
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:789)
at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:241)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1356)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1193)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:563)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:523)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:325)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:323)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:973)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:946)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:616)
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:146)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:753)
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:455)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:323)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1342)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1331)
at com.example.SqlApplication.main(SqlApplication.java:27)
Caused by: java.lang.RuntimeException: Unable to get valid instance data within 30000 ms. Last refresh attempt failed:java.lang.NullPointerException: Cannot invoke "java.lang.CharSequence.length()" because "sequence" is null
at com.google.cloud.sql.core.CloudSqlInstance.getInstanceData(CloudSqlInstance.java:137)
at com.google.cloud.sql.core.CloudSqlInstance.createSslSocket(CloudSqlInstance.java:158)
at com.google.cloud.sql.core.CoreSocketFactory.createSslSocket(CoreSocketFactory.java:373)
at com.google.cloud.sql.core.CoreSocketFactory.connect(CoreSocketFactory.java:219)
at com.google.cloud.sql.postgres.SocketFactory.createSocket(SocketFactory.java:77)
at org.postgresql.core.PGStream.createSocket(PGStream.java:231)
at org.postgresql.core.PGStream.(PGStream.java:98)
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:132)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:258)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:263)
at org.postgresql.Driver.makeConnection(Driver.java:443)
at org.postgresql.Driver.connect(Driver.java:297)
... 52 common frames omitted
Caused by: java.util.concurrent.ExecutionException: java.lang.NullPointerException: Cannot invoke "java.lang.CharSequence.length()" because "sequence" is null
at com.google.common.util.concurrent.AbstractFuture.getDoneValue(AbstractFuture.java:592)
at com.google.common.util.concurrent.AbstractFuture.get(AbstractFuture.java:551)
at com.google.common.util.concurrent.AbstractFuture$TrustedFuture.get(AbstractFuture.java:111)
at com.google.cloud.sql.core.CloudSqlInstance.handleRefreshResult(CloudSqlInstance.java:262)
at com.google.cloud.sql.core.CloudSqlInstance.lambda$startRefreshAttempt$2(CloudSqlInstance.java:254)
at com.google.common.util.concurrent.CombinedFuture$AsyncCallableInterruptibleTask.runInterruptibly(CombinedFuture.java:165)
at com.google.common.util.concurrent.CombinedFuture$AsyncCallableInterruptibleTask.runInterruptibly(CombinedFuture.java:153)
at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:76)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:840)
Caused by: java.lang.NullPointerException: Cannot invoke "java.lang.CharSequence.length()" because "sequence" is null
at com.google.common.base.CharMatcher.trimTrailingFrom(CharMatcher.java:815)
at com.google.cloud.sql.core.SqlAdminApiFetcher.fetchEphemeralCertificate(SqlAdminApiFetcher.java:269)
at com.google.cloud.sql.core.SqlAdminApiFetcher.lambda$getInstanceData$1(SqlAdminApiFetcher.java:117)
at com.google.common.util.concurrent.CombinedFuture$CallableInterruptibleTask.runInterruptibly(CombinedFuture.java:196)
... 7 common frames omitted

Sample
I am utilizing the GCP sample code from this repository and below is the list of configurations I have implemented.
https://github.com/GoogleCloudPlatform/spring-cloud-gcp/tree/main/spring-cloud-gcp-samples/spring-cloud-gcp-sql-postgres-sample

Application.properties
spring.datasource.username=*****
spring.cloud.gcp.sql.enable-iam-auth=true
spring.cloud.gcp.project-id=*****
spring.cloud.gcp.sql.enabled=true
spring.cloud.gcp.sql.database-name=*****
spring.cloud.gcp.sql.instance-connection-name=*****
spring.cloud.gcp.sql.credentials.location=*****
spring.cloud.gcp.sql.credentials.encoded-key=*****
spring.cloud.gcp.sql.credentials.scopes=https://www.googleapis.com/auth/sqlservice.admin,https://www.googleapis.com/auth/cloud-platform
spring.cloud.gcp.sql.jdbc.enabled= true
spring.cloud.gcp.sql.ip-types=PRIVATE

POM.xml

<parent>
	<!-- Your own application should inherit from spring-boot-starter-parent -->
	<artifactId>spring-cloud-gcp-samples</artifactId>
	<groupId>com.google.cloud</groupId>
	<version>5.0.1-SNAPSHOT</version><!-- {x-version-update:spring-cloud-gcp:current} -->
</parent>
<modelVersion>4.0.0</modelVersion>

<artifactId>spring-cloud-gcp-sql-postgres-sample</artifactId>
<name>Spring Framework on Google Cloud Code Sample - Postgres</name>

<!-- The Spring Framework on Google Cloud BOM will manage spring-cloud-gcp version numbers for you. -->
<dependencyManagement>
	<dependencies>
		<dependency>
			<groupId>com.google.cloud</groupId>
			<artifactId>spring-cloud-gcp-dependencies</artifactId>
			<version>${project.version}</version>
			<type>pom</type>
			<scope>import</scope>
		</dependency>
	</dependencies>
</dependencyManagement>

<dependencies>
	<dependency>
		<groupId>com.google.cloud</groupId>
		<artifactId>spring-cloud-gcp-starter-sql-postgresql</artifactId>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-web</artifactId>
	</dependency>

	<!-- Test-related dependencies. -->
	<dependency>
		<groupId>org.awaitility</groupId>
		<artifactId>awaitility</artifactId>
		<version>4.2.0</version>
		<scope>test</scope>
	</dependency>
	<dependency>
		<groupId>junit</groupId>
		<artifactId>junit</artifactId>
		<scope>test</scope>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-test</artifactId>
		<scope>test</scope>
	</dependency>
	<dependency>
		<groupId>commons-io</groupId>
		<artifactId>commons-io</artifactId>
		<version>2.15.1</version>
		<scope>test</scope>
	</dependency>
</dependencies>

Please take note that I am able to establish a connection to CloudSQL using the PSQL command line through the service account and cloud proxy setup, which indicates that there is no issue with the service account.

[enocom]

The core issue seems to be:

Caused by: java.lang.RuntimeException: Unable to get valid instance data within 30000 ms. Last refresh attempt failed:java.lang.NullPointerException: Cannot invoke "java.lang.CharSequence.length()" because "sequence" is null

[ttosta-google]

I was unable to reproduce your issue.

To help debug, try the following:

1. Only set one property for credentials: spring.cloud.gcp.sql.credentials.location or spring.cloud.gcp.sql.credentials.encoded-key.

Example:
spring.cloud.gcp.credentials.location=file:/usr/local/key.json
OR
spring.cloud.gcp.credentials.encoded-key=value should be the base64-encoded account private key in JSON format.

2. Use the latest cloud-sql-socket-factory version 1.15.1 by updating pom.xml

Try connecting again. If the issue persists, provide details about your environment.

[ttosta-google]

@tcs-roshan are you still having this issue?

[Antrakos]

@ttosta-google I faced the same issue. I use only spring.cloud.gcp.credentials.encoded-key property so the first recommendation wasn't applicable.

Debug

I tried debugging the issue and If I set a breakpoint in DefaultAccessTokenSupplier I can see that credentials are missing access token, but in a way that passes all the checks. refreshIfExpired() wouldn't actually refresh anything because token had FRESH state.

Curiously If I run credentials.refresh() in the debug mode at this breakpoint and then continue application successfully connects to the database and there's no error.

cloud-sql-socket-factory bump

I tried following your recommendation and using latest cloud-sql-socket-factory version, but it introduced a breaking change in 1.15.0 changing void com.google.cloud.sql.core.CoreSocketFactory.setApplicationName(java.lang.String) method visibility to package-level. This leads to spring app startup failure because com.google.cloud.spring.autoconfigure.sql.CloudSqlEnvironmentPostProcessor tries to call this previously-public method.

I went a bit deeper and tried setting up jdbc connection directly via the latest cloud-sql-socket-factory:1.16.0 without spring-cloud-gcp auto configuration. I couldn't exclude CloudSqlEnvironmentPostProcessor from spring boot so I removed the spring-cloud-gcp-autoconfigure dependency altogether. After some code manipulations (basically wiring up my own CredentialFactory implementation by copying com.google.cloud.spring.autoconfigure.sql.SqlCredentialFactory from spring-cloud-gcp) I made it further in the process, application now tries to connect to the CloudSQL, but always fails with

org.postgresql.util.PSQLException: FATAL: Cloud SQL IAM service account authentication failed for user "sa@my-project.iam"

Switching auth library

I couldn't find anything meaningful in either app or postgres logs, but copied SqlCredentialFactory code got me curious. I used deprecated com.google.api.client.googleapis.auth.oauth2.GoogleCredential class and deprecation notice recommended switching to google-auth-library library and that eventually did the trick. I then tried downgrading cloud-sql-socket-factory to 1.14.1 which comes from spring-cloud-gcp and it worked as well.

Solution

In the end, changes necessary are:

• copy SqlCredentialFactory class, but change it to use com.google.auth.oauth2.GoogleCredentials

            val credential = GoogleCredentials.fromStream(credentialsInputStream)
                .createScoped(setOf(GcpScope.SQLADMIN.url))
            return HttpCredentialsAdapter(credential)

• point socket factory to the new implementation

        System.setProperty(
            CredentialFactory.CREDENTIAL_FACTORY_PROPERTY,
            SqlCredentialFactory::class.java.name
        )

While this is working for me now, I wouldn't want to support it in the long run. This is a blocker to rollout IAM creds to hundreds of microservices . Could you patch the SqlCredentialFactory and release a new version?

[ttosta-google]

Thank you @Antrakos for your great debugging.

I've fixed the two issues mentioned above:

1. spring app startup does not support latest version for cloud-sql-socket-factory. PR is ready for review: fix(deps): update cloud-sql-socket-factory.version to v1.16.0 #2643

2. Update SqlCredentialFactory class to use com.google.auth.oauth2.GoogleCredentials. PR is ready for review: fix: Use GoogleCredentials to fetch user credentials for Cloud SQL #2644.

I think I was not able to reproduce this issue due to the type of the file I provide to spring.cloud.gcp.credentials.encoded-key. I use cat application_default_credentials.json | base64 -w0 to encode the key.

I agree with your solution because it stops using deprecated classes and fixes the issue.

See the difference between GoogleCredential.fromStream and GoogleCredentials.fromStream

Note: I've added you as a co-author. In order to merge the PR, you need to sign the CLA by clicking here. Please let me know if you don't want to do it. I can easily update the commit message to remove your name.

[ttosta-google]

@Antrakos @tcs-roshan All PRs were merged. Now you just need wait a few days for spring-cloud-gcp-dependencies v5.0.5 to be released. You can clone this repo and generate the version 5.0.5-SNAPSHOT if you want to use it prior to release.

Please open a new bug if you have issues connecting to Cloud SQL.