Authentication failure for Iam Authentication on Postgres Cloud SQL through jdbc Hikari

[satishrao84]

Question

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

Additional Context

Please reference any other relevant issues, PRs, descriptions, or screenshots here.

[enocom]

Hi @satishrao84. As I've commented elsewhere, I believe your user name is simply incorrect. It should be XXXXXX-compute@developer. If that doesn't work, feel free to re-open and we can do further investigation.

[satishrao84]

Still doesn't work.Am I missing something here? Just supplying the service account name shouldn't be sufficient to access the DB!!? What am I missing here? An how does the cloud auth proxy binary service come into play here? I followed all the GitHub/Google docs, but no luck in figuring this out.

Getting below error again:

com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: FATAL: Cloud SQL IAM service account authentication failed for user "XXXXXXXX-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.<init>(HikariPool.java:100)
    at com.zaxxer.hikari.HikariDataSource.<init>(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.<init>(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

[enocom]

A few things to try:

1. Check your audit logs https://cloud.google.com/sql/docs/postgres/add-manage-iam-users#viewing-login-audit-logs
2. Have you added Cloud SQL Client and Cloud SQL Instance User to your associated service account?
3. Is there a user in the database that matches the SA?

[kurtisvg]

Hi @satishrao84,

You can use Automatic IAM DB authentication with either the Cloud SQL proxy or this library. You don't need to use both.

Please make sure of the following:

• You're running the latest version of the java connector
• The service account you are using for the java connector is the same one you are trying to log in with

There's also an example in our tests here.

[satishrao84]

A few things to try:

1. Check your audit logs https://cloud.google.com/sql/docs/postgres/add-manage-iam-users#viewing-login-audit-logs

2. Have you added Cloud SQL Client and Cloud SQL Instance User to your associated service account?

3. Is there a user in the database that matches the SA?

for 2 and 3 - yes. For 1 - how do I enable audit logs on my cloud sql?

[satishrao84]

Hi @satishrao84,

You can use Automatic IAM DB authentication with either the Cloud SQL proxy or this library. You don't need to use both.

Please make sure of the following:

* You're running the latest version of the java connector

* The service account you are using for the java connector is the same one you are trying to log in with

There's also an example in our tests here.

Yes and Yes

[satishrao84]

DBNAME=mdm USER=XXXXXXX-compute@developer PASSWORD=secret SSLMODE=disable SOCKETFACTORY=com.google.cloud.sql.postgres.SocketFactory CLOUDSQLINSTANCE=coral-ethos-#####:us-central1:mdm ENABLEIAMAUTH=true

The above is my db connection details

[enocom]

As @kurtisvg pointed out, we have an example in our tests. I would start from scratch and try following that. Ultimately, you've missed a configuration detail somewhere and the only way to verify that would be just to do it again.

[kurtisvg]

@satishrao84 Here is the Cloud SQL page on audit logging.

Unfortunately, it's a bit hard to debug based on the error (and for security reasons, the error has to be pretty unhelpful). There's some additional guidance here in the docs: https://cloud.google.com/sql/docs/postgres/add-manage-iam-users#troubleshooting-a-login-failure

I think you might also need to provide "something" as a password even it's it's only a single space (e.g. " "). If you try the above and still can't get it to work, it's helpful if you can provide us with as simple as possible case to reproduce. If you need a starting point, try using this sample, which is a pretty basic webapp - you should only need to make minimal changes to use EnableIAMAuthN.

[satishrao84]

All, I was using a compute engine service account. I was trying to connect to the cloud sql instance from my local desktop. The moment I moved the jar to the compute engine, and ran from there, it worked.

[kurtisvg]

@satishrao84 Glad you figured it out - thanks for following up!

[satishrao84]

All, apologies for asking on this ticket, but is it possible to use this java connector for Postgres IAM authentication in a spring data JPA application? How would we use Hikari in such an application where datasource is taken care by Spring

[kurtisvg]

@satishrao84

So you don't need to use Hikari (that's just what we used for our example). Anything using the same JDBC driver should work - you can just pass in the url if you can't configure the properties directly.

I think also additionally, Hikari is used by default in spring. This medium article has some examples of configuring.