I am trying to connect to a Postgres Cloud SQL instance using JDBC SocketFactory per the documentation here : https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory/blob/main/docs/jdbc-postgres.md
HikariDataSource pool = null;
HikariConfig config = new HikariConfig();
//String jdbcURL = String.format("jdbc:postgresql:///%s", DBNAME);
String jdbcURL = String.format("jdbc:postgresql:///%s?cloudSqlInstance=%s&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=%s&password=%s&enableIamAuth=true&sslmode=disable",DBNAME,CLOUDSQLINSTANCE,USER,PASSWORD);
Properties connProps = new Properties();
connProps.setProperty("user", USER);
connProps.setProperty("password", PASSWORD);
connProps.setProperty("sslmode", SSLMODE);
connProps.setProperty("socketFactory", SOCKETFACTORY);
connProps.setProperty("cloudSqlInstance", CLOUDSQLINSTANCE);
connProps.setProperty("enableIamAuth", ENABLEIAMAUTH);
connProps.setProperty("ipTypes","PRIVATE");
System.out.println("jdbcurl: " + jdbcURL);
config.setJdbcUrl(jdbcURL);
config.setDataSourceProperties(connProps);
config.setConnectionTimeout(10000); // 10s
try {
pool = new HikariDataSource(config);
}
catch (Exception e){
System.out.println(e.getMessage() + e.getMessage() );
e.printStackTrace();
}
The error I get is:
com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: FATAL: password authentication failed for user "XXXXXX-compute@developer.gserviceaccount.com" at com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:596) at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:582) at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:100) at com.zaxxer.hikari.HikariDataSource.(HikariDataSource.java:81) at com.aexp.ngbd.mdm.util.ConnectionUtil.createConnectionPool(ConnectionUtil.java:49) at com.aexp.ngbd.mdm.avro.AvroToSQLInsert.createSQLFile(AvroToSQLInsert.java:84) at com.aexp.ngbd.mdm.avro.ReadFromPubSub.lambda$subscribeWithAvroSchemaExample$0(ReadFromPubSub.java:48) at com.google.cloud.pubsub.v1.MessageDispatcher$4.run(MessageDispatcher.java:396) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) 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:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:829) Caused by: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "XXXXXXXX-compute@developer.gserviceaccount.com" at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:623) at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:163) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:215) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:51) at org.postgresql.jdbc.PgConnection.(PgConnection.java:225) at org.postgresql.Driver.makeConnection(Driver.java:466) at org.postgresql.Driver.connect(Driver.java:265) at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121) 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) ... 12 more
I am running the jar on a compute engine. I am able to use psql to connect to the PostgreSQL instance using the same ServiceAccount which has been enabled for IAM authentication. I am connecting through the cloud SQL auth proxy below:
satish@XXXX-load:~$ psql "host=127.0.0.1 sslmode=disable dbname=mdm user=XXXXXXX-compute@developer port=1234" psql (11.14 (Debian 11.14-0+deb10u1), server 13.4) WARNING: psql major version 11, server major version 13. Some psql features might not work. Type "help" for help. mdm=> \dt
My question is, how can I connect to the Postgres instance using only the ServiceAccount in Java?
Below is my pom.xml dependencies:
<dependency>
<groupId>com.google.cloud.sql</groupId>
<artifactId>postgres-socket-factory</artifactId>
<version>1.4.2</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.3.1</version>
</dependency>
com.google.cloud.sql:postgres-socket-factory:1.4.2-dev does not seem to be available in the maven central. Is this the problem? The latest version I was able to find is 1.4.2
Even if I use the XXXXXXX-compute@developer user, i still get the below error.
Failed to initialize pool: FATAL: Cloud SQL IAM service account authentication failed for user "470754621570-compute@developer"Failed to initialize pool: FATAL: Cloud SQL IAM service account authentication failed for user "470754621570-compute@developer" com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: FATAL: Cloud SQL IAM service account authentication failed for user "470754621570-compute@developer" at com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:596) at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:582) at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:100) at com.zaxxer.hikari.HikariDataSource.(HikariDataSource.java:81) at com.aexp.ngbd.mdm.util.ConnectionUtil.createConnectionPool(ConnectionUtil.java:52) at com.aexp.ngbd.mdm.avro.AvroToSQLInsert.convertAvroToPostgres(AvroToSQLInsert.java:37) at com.aexp.ngbd.mdm.avro.AvroToSQLInsert.createSQLFile(AvroToSQLInsert.java:163) at com.aexp.ngbd.mdm.avro.ReadFromPubSub.lambda$subscribeWithAvroSchemaExample$0(ReadFromPubSub.java:51) at com.google.cloud.pubsub.v1.MessageDispatcher$4.run(MessageDispatcher.java:396) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) 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:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:834) Caused by: org.postgresql.util.PSQLException: FATAL: Cloud SQL IAM service account authentication failed for user "470754621570-compute@developer" at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:623) at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:163) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:215) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:51) at org.postgresql.jdbc.PgConnection.(PgConnection.java:225) at org.postgresql.Driver.makeConnection(Driver.java:466) at org.postgresql.Driver.connect(Driver.java:265) at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121) 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) ... 13 more