Java connection String for Google Cloud Postgresql with SSL - Dataflow

Question

Need to write the data to Google Cloud PostgreSQL using Dataflow Job (Java code). This PostgreSQL instance has SSL enabled. So want to know the connection string of this PostgreSQL database with SSL enabled (Client certification, Key and Server certification). With out SSL the below connection string works fine in Dataflow Job. But wants to know the connection string with SSL.

JdbcIO.<KV<Integer,Integer>>write()
.withDataSourceConfiguration( DataSourceConfiguration.create("org.postgresql.Driver","jdbc:postgresql://<PrivateIP>:5432/db")
               .withUsername("***")
               .withPassword("password"))

Answer 1

You can configure connections to PostgreSQL using the following driver properties: sslcert, sslkey, and sslrootcert to point to different certificates.

You can also use the Cloud SQL JDBC Socket Factory, which creates temporary certificates and uses them automatically.

Answer 2

Below code works for me.

String conUrl="jdbc:postgresql://google/<dbName>?cloudSqlInstance=<InstanceName>&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=<UserName>&password=<PWD>";

p.apply(JdbcIO.<KV<Integer,Integer>>write()
    .withDataSourceConfiguration( DataSourceConfiguration.create("org.postgresql.Driver",conUrl)
            .withUsername(<UserName>)
            .withPassword(<PWD>)
            )
    .withStatement("insert into public.testTable(id,order_number) values(?, ?)")
    .withPreparedStatementSetter(new JdbcIO.PreparedStatementSetter<KV<Integer,Integer>>() {
        public void setParameters(KV<Integer,Integer> element, PreparedStatement query)
                throws SQLException {
            query.setInt(1, element.getKey());
            query.setInt(2, element.getValue());
        }
    })
);

Answer 3

this works for me for establishing a connection with jdbcIO

String conUrl="jdbc:postgresql://google/dbName?cloudSqlInstance=projectId:us-central1:databaseId&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=postgres&password=*****";