Driver does not support socket connection

[anthonydenecheau]

Describe the bug
I try to connect a projet, build in native mode and deploy on google cloud run, to google sql.

Expected behavior
My app cannot reach google sql

Actual behavior
I get an error :
ERROR [org.hib.eng.jdb.spi.SqlExceptionHelper] (main) Driver does not support the provided URL: jdbc:postgres://[user]:[pwd]@/[db-name]?unix_sock=/cloudsql/[db-connection-name]"
To Reproduce
Steps to reproduce the behavior:

1. Build image in native mode
2. deploy on google run

Configuration
Pom.xml

  <properties>
    <quarkus-plugin.version>1.5.1.Final</quarkus-plugin.version>
    <quarkus.platform.artifact-id>quarkus-bom</quarkus.platform.artifact-id>
    <quarkus.platform.group-id>io.quarkus</quarkus.platform.group-id>
    <quarkus.platform.version>1.5.1.Final</quarkus.platform.version>
	<testcontainers.version>1.14.1</testcontainers.version>
    <surefire-plugin.version>2.22.1</surefire-plugin.version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	<docker-plugin.version>0.28.0</docker-plugin.version>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

	<dependency>
		<groupId>io.quarkus</groupId>
		<artifactId>quarkus-hibernate-orm-panache</artifactId>
	</dependency>	
	<dependency>
		<groupId>io.quarkus</groupId>
		<artifactId>quarkus-jdbc-postgresql</artifactId>
	</dependency>   
	<!--dependency>
		<groupId>io.quarkus</groupId>
		<artifactId>quarkus-reactive-pg-client</artifactId>
	</dependency-->	
	<dependency>
		<groupId>com.google.cloud.sql</groupId>
		<artifactId>postgres-socket-factory</artifactId>
		<version>1.0.16</version>
	</dependency>	

application.properties

quarkus.datasource.db-kind=postgresql
quarkus.datasource.jdbc.url=jdbc:postgres://${DATASOURCE_USER}:${DATASOURCE_PWD}@/${DATASOURCE_DBNAME}?unix_sock=/cloudsql/${DATASOURCE_URL}

Where variables are environnements variables on google cloud run

Environment (please complete the following information):

• Output of uname -a or ver:
• Output of java -version:
• GraalVM version (if different from Java):
• Quarkus version or git rev: 1.5.1.Final
• Build tool (ie. output of mvnw --version or gradlew --version):

Additional context
Dockerfile.multistage

## Stage 1 : build with maven builder image with native capabilities
FROM quay.io/quarkus/ubi-quarkus-native-image:19.3.1-java11 as nativebuilder
RUN mkdir -p /tmp/ssl \
  && cp /opt/graalvm/lib/security/cacerts /tmp/ssl/
  
FROM quay.io/quarkus/centos-quarkus-maven:19.3.1-java11 AS build
RUN mkdir -p /tmp/ssl \
  && cp /opt/graalvm/lib/security/cacerts /tmp/ssl/
COPY pom.xml /usr/src/app/
RUN mvn -f /usr/src/app/pom.xml -B de.qaware.maven:go-offline-maven-plugin:1.2.5:resolve-dependencies
COPY src /usr/src/app/src
USER root
RUN chown -R quarkus /usr/src/app
USER quarkus
RUN mvn -f /usr/src/app/pom.xml -Pnative clean package -DskipTests=true

## Stage 2 : create the docker final image
FROM registry.access.redhat.com/ubi8/ubi-minimal
WORKDIR /work/
COPY --from=nativebuilder /tmp/ssl/ /work/
COPY --from=build /usr/src/app/target/*-runner /work/application

# set up permissions for user `1001`
RUN chmod 775 /work /work/application \
  && chown -R 1001 /work \
  && chmod -R "g+rwX" /work \
  && chown -R 1001:root /work

EXPOSE 8080
USER 1001
ENV DISABLE_SIGNAL_HANDLERS true
CMD ["./application", "-Dquarkus.http.host=0.0.0.0", "-Djavax.net.ssl.trustStore=/work/cacerts"]

Note : find this feature : https://github.com/jtama-op/quarkus/tree/18280ee018d82acef601fc022a5c6923bfb7a451/extensions/agroal

[loicmathieu]

In order to use Cloud Sql you need to use a dedicated socker factory. You need to configure your datasource to use it. You also need to configure the instance name.

Can you try adding these two properties to the additional-jdbc-properties ?

Something like this should work:

quarkus.datasource.additional-jdbc-properties.socketFactory=com.google.cloud.sql.postgres.SocketFactory
quarkus.datasource.additional-jdbc-properties.cloudSqlInstance=<project:zone:instance-name>

[anthonydenecheau]

I fixed this issue with cloud sql proxy. So I backtracked and set these 2 additionals property, get application credentials too.
I notice during build step :
Unrecognized configuration key "quarkus.datasource.additional-jdbc-properties.cloudSqlInstance" was provided; it will be ignored; verify that the dependency extension for this configuration is set or you did not make a typo

So either quarkus.datasource.additional-jdbc-properties.socketFactory or quarkus.datasource.socket-factory don't fix this issue

I see an active PR here (https://github.com/quarkusio/quarkus/pull/6634/files)... Means that it has not been deployed yet ?

[loicmathieu]

@anthonydenecheau you're right, the additional-jdbc-property is not supported yet, I was mis-guided by the following PR #6634 I thought it already exists and this PR only use them.

We should implement it !

[anthonydenecheau]

Ok merci Loïc. I hope to see this new feature in the next realease 1.5.2.

Keep it up guys, great work

[loicmathieu]

@anthonydenecheau additional-jdbc-property has been added recently, can you test with Quarkus 1.10 and see if it works ?

[lferna]

@loicmathieu I’ve tested this afternoon and It works for me. But I have to put in this way:
quarkus.datasource.jdbc.additional-jdbc-properties.socketFactory instead a Map.
Is that correct?

[loicmathieu]

@lferna a map means you can put whatever key you want after quarkus.datasource.jdbc.additional-jdbc-properties and its value.
So yes, this is correct.

@gsmet now that the PR for additinal JDBC properties is merged, I think we need to put inside our documentation how to configure the datasouce for Google Cloud SQL with the two needed properties. Where do you think we need to document this ? Inside our Datasource guide or inside our Deploy to Google Cloud Guide (or both) ?

[vorburger]

Documentation for how to use Quarkus with Google Cloud SQL would be neat; especially because with a random web search and finding e.g. #5901 and #6634 and stumbling across related https://github.com/jtama-op/quarkus/commit/eb27113c050b2c98a0ef046a84d366484adf6e21, one could think this needs more work - when, actually, it does seem to work just fine using e.g. Quarkus 1.10.5 as-is, simply using a quarkus.datasource.jdbc.url=jdbc:mysql:///DB-NAME?ipTypes=PRIVATE&cloudSqlInstance=PROJECT-NAME:REGION:DB-INSTANCE&socketFactory=com.google.cloud.sql.mysql.SocketFactory (and an quarkus.datasource.db-kind=mysql and quarkus.datasource.username= & quarkus.datasource.password=, as always).

The trickiest part can be how to correctly set up all the required pre-requisites on GCP for this connection to work from a Quarkus service running on AppEngine Standard; see GoogleCloudPlatform/cloud-sql-jdbc-socket-factory#375.

[jtama]

@loicmathieu Do you know if the same is available for quarkus-reactive-pg-client ?

[jtama]

Documentation for how to use Quarkus with Google Cloud SQL would be neat; especially because with a random web search and finding e.g. #5901 and #6634 and stumbling across related jtama@eb27113, one could think this needs more work - when, actually, it does seem to work just fine using e.g. Quarkus 1.10.5 as-is, simply using a quarkus.datasource.jdbc.url=jdbc:mysql:///DB-NAME?ipTypes=PRIVATE&cloudSqlInstance=PROJECT-NAME:REGION:DB-INSTANCE&socketFactory=com.google.cloud.sql.mysql.SocketFactory (and an quarkus.datasource.db-kind=mysql and quarkus.datasource.username= & quarkus.datasource.password=, as always).

The trickiest part can be how to correctly set up all the required pre-requisites on GCP for this connection to work from a Quarkus service running on AppEngine Standard; see GoogleCloudPlatform/cloud-sql-jdbc-socket-factory#375.

You are write, but that jdbc url feels like a hack, and additional-jdbc-properties are the right way.

[loicmathieu]

@jtama the reactive clients are based on Vert.x reactive database driver. I don't know if those supports all the configuration options as the JDBC variants.

[jtama]

@loicmathieu So I have answer bits :

• additional-jdbc-properties are processed by agroal/DataSourceJdbcRuntimeConfigwhich is not used by the reactive PgPoolRecorder that uses directly DataSourceRuntimeConfig. So if it works it's by directly passing those properties in the URL.
• Even included in URL this doesn't seems to work with the following error : Connection refused: localhost/127.0.0.1:5432 I guess the socket factory is simply ignored...

Creating an issue (eclipse-vertx/vertx-sql-client#929).

[jtama]

ok so it does work with reactive client with the following dependencies (choose one depending on your os):

<dependency>
    <groupId>io.netty</groupId>
    <artifactId>netty-transport-native-kqueue</artifactId>
    <classifier>osx-x86_64</classifier>
    <version>4.1.49.Final</version>
</dependency>

<dependency>
  <groupId>io.netty</groupId>
  <artifactId>netty-transport-native-epoll</artifactId>
  <classifier>linux-x86_64</classifier>
  <version>4.1.49.Final</version>
</dependency>

and the following configuration :

%gcp.quarkus.datasource.reactive.url=postgresql://:5432/db-name?host=/cloudsql/project-id:zone:db-name    
%gcp.quarkus.vertx.prefer-native-transport=true

I think we should document it.

[jtama]

@gsmet We still need to document how to connect using unix domain socket for use with reactive pg driver.

[compasspenguin]

@jtama As per your comment, I'm trying to connect my quarkus app on GAE with a GCP cloud SQL instance. The below configuration did not help and I'm still getting the same error.

%prod.quarkus.datasource.reactive.url=postgresql://:5432/{db_name}?host=/{xxx.xxx.xxx.xxx}/{project-id}:us-east1:{instance-name}
%prod.quarkus.vertx.prefer-native-transport=true

Also added this to my pom.xml

      <dependency>
            <groupId>io.netty</groupId>
            <artifactId>netty-transport-native-epoll</artifactId>
            <classifier>linux-x86_64</classifier>
            <version>4.1.49.Final</version>
       </dependency>

When you say the OS for netty do you mean the OS that runs the jar or the OS that built it?

I believe GCP GAE runs Linux by deafult. Any help here would be greatly appreciated. I'm building a solution for big billion $ company on GCP and we are fixated with reactive but sadly this doesn't work. I believe in the potential of quarkus but need this to work for my demo.

Thanks in advance, you are also right about adding documentation for reactive, the problem isn't resolved yet.

@gsmet This thread should not be closed yet. This still doesn't work with reactive pg.

[jtama]

It’s a bit old but i think you should remove the host IP.

[compasspenguin]

@jtama Appreciate your quick response. Can you please share a working example for reactive pgsql. Tried removing the host but it still doesn't work for me.

[jtama]

And for the dependency, it depends on the host running the application.
My dependencies are as followed :

        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-reactive-pg-client</artifactId>
        </dependency>
        <dependency>
            <groupId>io.netty</groupId>
            <artifactId>netty-transport-native-epoll</artifactId>
            <classifier>linux-x86_64</classifier>
            <version>4.1.49.Final</version>
        </dependency>
        <dependency>
            <groupId>com.google.cloud.sql</groupId>
            <artifactId>postgres-socket-factory</artifactId>
            <version>1.2.1</version>
        </dependency>

And my application.properties :

quarkus.datasource.db-kind=postgresql quarkus.datasource.reactive.url=?host=/cloudsql/project-id:zone:db-name quarkus.vertx.prefer-native-transport=true
Please note it was tested against quarkus 1.13.0.Final

[compasspenguin]

@jtama Apologies I just got confused by the configuration provided in https://quarkus.io/guides/deploying-to-google-cloud#using-cloud-sql. If I am using reactive, do I need any of the configurations provided below:

quarkus.datasource.db-kind=other 

<dependency>
    <groupId>com.google.cloud.sql</groupId>
    <artifactId>postgres-socket-factory</artifactId>
    <version>${postgres-socket-factory.version}</version>
</dependency>

It will be very helpful to list the configuration for reactive separately as I've noticed that some others are facing the same confusion as in comments of eclipse-vertx/vertx-sql-client#929.

[loicmathieu]

@compasspenguin I created a PR to add this to the documentation last week, you can check it here:
https://github.com/quarkusio/quarkus/pull/20801/files

With the reactive client: no need for the socker factory lib, only the netty-transport-native-epoll library and without the version that is managed by Quarkus.
The only configurations needed is to enable netty native transport and define the URL without host.

quarkus.datasource.reactive.url=postgresql://:5432/db-name?host=/cloudsql/project-id:zone:db-name
quarkus.vertx.prefer-native-transport=true

Be aware that this only works inside a Google managed runtime like Appengine.

[compasspenguin]

Awesome, thank you! This worked perfectly. As it turns out, specifying the epoll version was causing issues.