Question 90067 in PostgreSQL Question posted in
The official documentation can be found here.

Spring Boot Cloud SQL IAM service account authentication In GKE – Postgresql

My application works fine most of the time in GCP’s K8’s but the weird thing is from time to time it is starts to throw connection not available errors, even though many connections are available. DB connections are set to couple of thousands but not even 1000 has been reached so far. Application is using IAM authentication using service account and is small enough and below are some of the details.

Note: I am of course omitting some details due to sensitivity.

application.properties

spring.datasource.url=jdbc:postgresql://google/mydb?cloudSqlInstance=myInstance&socketFactory=com.google.cloud.sql.postgres.SocketFactory&enableIamAuth=true
[email protected]
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.testWhileIdle=true
spring.datasource.validationQuery=SELECT 1
spring.jpa.show-sql=false
spring.jpa.hibernate.ddl-auto=none
spring.main.allow-bean-definition-overriding=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.datasource.hikari.maximum-pool-size=200
spring.datasource.hikari.minimum-idle=30
spring.datasource.hikari.connection-timeout=60000
spring.datasource.hikari.max-lifetime=10000
spring.datasource.hikari.idle-timeout=10000

pom.xml

<dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>com.google.cloud</groupId>
                <artifactId>libraries-bom</artifactId>
                <version>20.7.0</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>2020.0.4</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>com.google.cloud</groupId>
                <artifactId>spring-cloud-gcp-dependencies</artifactId>
                <version>2.0.2</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>
<repositories>
        <repository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </repository>
        <repository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <releases>
                <enabled>false</enabled>
            </releases>
        </repository>
        <repository>
            <id>spring-release</id>
            <name>Spring release</name>
            <url>https://repo.spring.io/release</url>
        </repository>
    </repositories>
   <pluginRepositories>
        <pluginRepository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </pluginRepository>
        <pluginRepository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <releases>
                <enabled>false</enabled>
            </releases>
        </pluginRepository>
        <pluginRepository>
            <id>spring-release</id>
            <name>Spring release</name>
            <url>https://repo.spring.io/release</url>
        </pluginRepository>
    </pluginRepositories>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>5.0.1</version>
        </dependency>
        <dependency>
            <groupId>com.google.cloud.sql</groupId>
            <artifactId>postgres-socket-factory</artifactId>
            <version>1.7.2</version>
        </dependency>

Errors

o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 28000
o.h.engine.jdbc.spi.SqlExceptionHelper   : Pool-1 - Connection is not available, request timed out after 60001ms.
o.h.engine.jdbc.spi.SqlExceptionHelper   : FATAL: Cloud SQL IAM service account authentication failed for user "[email protected]"

Once that exception occurs I am deleting that pod and recreating it, which fixes for time being. Though it’s weird that recreating pod uses same authentication which works afterwards. Any help or pointer will be appreciated.

Update:

Upgraded postgres-socket-factory to 1.8.3 but still getting same issue.

Update 2:

Upgraded postgres-socket-factory to 1.9.0 but still getting same issue.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks @jan-hoeve. I also noticed these issues on gcp side few days ago and then I updated socket factory version but the problem was the latest socket factory driver was still using 0.26 version of auth library from gcp which as you pointed out has that issue. So I updated below two libraries as well after which I am not getting any issues so far.

            <dependency>
            <groupId>com.google.cloud.sql</groupId>
            <artifactId>postgres-socket-factory</artifactId>
            <version>1.9.0</version>
        </dependency>
        <dependency>
            <groupId>com.google.auth</groupId>
            <artifactId>google-auth-library-oauth2-http</artifactId>
            <version>1.15.0</version>
        </dependency>
        <dependency>
            <groupId>com.google.auth</groupId>
            <artifactId>google-auth-library-credentials</artifactId>
            <version>1.15.0</version>
        </dependency>

  2. 0

    I had the same issue and after contacting GCP support they pointed me to a new version of the Oauth2 lib like said above, version 1.12.1
    https://github.com/googleapis/google-auth-library-java/pull/1031

    It works indeed, but in my case the issue resurfaced with newer versions.
    It also happens again with this said 1.12.1 Oauth2 lib, but with other deps increased, like the socket factory. But YMMV 🙂

    In my case the issue pops up once or twice during 24H, but when it happens, it complains for e.g. 10 minutes straight. And indeed, killing a pod ‘helps’.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search