Cannot connect to Cloud SQL with Java SocketFactory Library

[Daniel Franco]

Hello, I'm using Cloud Dataflow to run some pipelines using Cloud SQL as a sink in my personal account. After the pipeline is executed, I want to make a connection to Cloud SQL to run a delete statement. I can't run deletes with JdbcIO (only selects and inserts). Even though I can connect to Cloud SQL with JdbcIO, I cannot connect to it using plain Jdbc. 

Below is the code I'm using to connect to my instance

static void cleanUpPipeline() {

String jdbcUrl = String.format(

    "jdbc:postgresql://google/%s?cloudSqlInstance=%s"

        + "&socketFactory=com.google.cloud.sql.postgres.SocketFactory",

    "xxxxxx",

    "xxxxxx");

try {

Class.forName("org.postgresql.Driver");

Connection connection = DriverManager.getConnection(jdbcUrl, "xxxxxx", "xxxxxx");

} catch (SQLException | ClassNotFoundException e) {

e.printStackTrace();

}

}

My pom.xml has the following dependencies:

<dependency>

    <groupId>com.google.cloud.sql</groupId>

    <artifactId>postgres-socket-factory</artifactId>

    <version>1.0.11</version>

</dependency>

<dependency>

    <groupId>org.postgresql</groupId>

    <artifactId>postgresql</artifactId>

    <version>42.2.5</version>

</dependency>

The error I receive is: 

INFO: Connecting to Cloud SQL instance [xxxxxxxxx] via ssl socket.

nov 13, 2018 2:53:49 PM com.google.cloud.sql.core.SslSocketFactory getInstance

INFO: First Cloud SQL connection, generating RSA key pair.

nov 13, 2018 2:53:51 PM com.google.cloud.sql.core.SslSocketFactory fetchInstanceSslInfo

INFO: Obtaining ephemeral certificate for Cloud SQL instance [xxxxxx].

nov 13, 2018 2:53:54 PM com.google.cloud.sql.core.SslSocketFactory createAndConfigureSocket

INFO: Connecting to Cloud SQL instance [xxxxxxxx] on IP [xxxxxxx]

org.postgresql.util.PSQLException: Connection to google:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

I believe I'm doing everything right here. Can anyone help me? 

[Emerson Moraes]

Hi, I do not know what is happening exactly, but I have a Dataflow streaming job that uses Postgres Cloud SQL with JdbcIO and with plain Jdbc inside a custom function. The only diference between you and me is that I'm using postgres-socket-factory on 1.0.10 version and consequently my url connection use diferent params. For example: jdbc:postgresql://google/<database_name>?useSSL=false&socketFactoryArg=<instance_name>&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=<user_login>&password=<user_password>.

This URL make connection with my Cloud SQL both locally and in Google Cloud Dataflow environments. The job is running now. I think that you can try to use this version os postgres socket to make a test.

And why is not possible to use delete with JdbcIO on your case? It's a project limitation? Because I'm using JdbcIO to make delete statements.

Example:

movementsToDelete.apply("DeleteMovimentacao", JdbcIO.<Movimentacao> write()

            .withDataSourceConfiguration(

                    JdbcIO.DataSourceConfiguration

                            .create("org.postgresql.Driver", Constants.POSTGRES_URL)

            )

            .withStatement(DELETE_MOVIMENTACAO_SQL)

            .withPreparedStatementSetter(new MovimentacaoDeletePreparedStatementSetter()));

My Apache Beam version is 2.5.0.

Att.,

Emerson Leite de Moraes

[George (Cloud Platform Support)]

The Java programming language does not provide native support for Unix sockets. The easiest way to connect to a Second Generation instance without whitelisting IP addresses is to use the JDBC socket factory. You may gather more detail from the "Connecting to Cloud SQL from External Applications" online document. 

In what concerns JdbcIO, details and code samples are to be found on the "Class JdbcIO" page. What host name and port have you chosen? You seem to attempt connecting to a PostgreSQL instance. Relevant information can be found on the "Connecting from App Engine" page. 

[Daniel Franco]

Thanks Emerson, 
Unfortunately, I've tried changing the library, but I still can't get the Dataflow job to connect to Cloud SQL with plain JDBC in the main function.

I've tried to use gcloud auth application-default login command to make sure I'm authenticated, but that still wasn't enough.
I'm also using GOOGLE_APPLICATION_CREDENTIALS environment variable to use a service account key that would let me connect to the Dataflow/Cloud SQL service. 
I still have no idea why I'm receiving connection refused errors. Have you done anything beyond that?

Below is the full stack trace:

org.postgresql.util.PSQLException: Connection to google:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:265)

at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)

at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)

at org.postgresql.Driver.makeConnection(Driver.java:450)

at org.postgresql.Driver.connect(Driver.java:252)

at java.sql.DriverManager.getConnection(DriverManager.java:664)

at java.sql.DriverManager.getConnection(DriverManager.java:270)

at org.eldorado.sigma.pipeline.PrevisaoJsonPipeline.postProcessingPipeline(PrevisaoJsonPipeline.java:124)

at org.eldorado.sigma.pipeline.PrevisaoJsonPipeline.main(PrevisaoJsonPipeline.java:138)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.codehaus.mojo.exec.ExecJavaMojo$1.run(ExecJavaMojo.java:282)

at java.lang.Thread.run(Thread.java:748)

Caused by: java.net.ConnectException: Connection refused: connect

at java.net.DualStackPlainSocketImpl.connect0(Native Method)

at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79)

at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)

at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)

at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)

at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)

at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)

at java.net.Socket.connect(Socket.java:589)

at sun.security.ssl.SSLSocketImpl.connect(SSLSocketImpl.java:673)

at sun.security.ssl.SSLSocketImpl.<init>(SSLSocketImpl.java:432)

at sun.security.ssl.SSLSocketFactoryImpl.createSocket(SSLSocketFactoryImpl.java:88)

at com.google.cloud.sql.core.SslSocketFactory.createAndConfigureSocket(SslSocketFactory.java:190)

at com.google.cloud.sql.core.SslSocketFactory.create(SslSocketFactory.java:152)

at com.google.cloud.sql.postgres.SocketFactory.createSocket(SocketFactory.java:50)

at org.postgresql.core.PGStream.<init>(PGStream.java:60)

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:144)

... 14 more

[Yasser Karout (Cloud Platform Support)]

Hello,

Are you connecting to Cloud SQL through a proxy? If you have a corporate firewall setup, you might need to add an exception to allow connections to the instance. It is worth looking into this if the connection is being blocked. 

I found a similar issue on the issue tracker website that suggest this is a proxy issue [1]. There is also a similar issue posted on Stackoverflow but for MySQL [2]. 

It might be better to post this issue on Stackoverflow as it is more suited for development questions.

[1] https://issuetracker.google.com/116257089

[2] https://stackoverflow.com/questions/46221131/using-cloud-sql-proxy-behind-corporate-firewall-cant-connect-to-mysql-instanc

[Daniel Franco]

Yeah, it was a firewall issue indeed.
Linked SO question: https://stackoverflow.com/questions/53306610/cannot-connect-to-cloud-sql-with-java-socketfactory-library

[Eric M]

So this is strange. 

I just tried connecting with the Postgres Socketfactory version 1.0.14 (Not working and just hangs forever)

Then switched to older SocketFactory 1.0.10 and then it works. 

The only change is switch to the older version and changed property "cloudSqlInstance" to "socketFactoryArg".

//config.addDataSourceProperty("cloudSqlInstance", CLOUD_SQL_CONNECTION_NAME);

config.addDataSourceProperty("socketFactoryArg", CLOUD_SQL_CONNECTION_NAME);

Or have I missed something in the new 1.0.14 version?

On Wednesday, 14 November 2018 21:59:49 UTC+1, Emerson Moraes wrote:

Hi, I do not know what is happening exactly, but I have a Dataflow streaming job that uses Postgres Cloud SQL with JdbcIO and with plain Jdbc inside a custom function. The only diference between you and me is that I'm using postgres-socket-factory on 1.0.10 version and consequently my url connection use diferent params. For example: jdbc:postgresql://google/<database_name>?useSSL=false&socketFactoryArg=<instance_name>&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=<user_login>&password=<user_password>.

.....

[Elliott (Google Cloud Platform Support)]

Hello Eric,

I see that you are having difficulty with your environment, which is why I suggest moving the troubleshooting to Stackoverflow to obtain assistance from our programming community for your question.