CODEWITHSUNDEEP
google-cloud-dataflowgoogle-cloud-sqlapache-beam
lourdu rajan
lourdu rajan

Reputation: 379

Java connection String for Google Cloud Postgresql with SSL - Dataflow

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"))

Upvotes: 1

Views: 1940

Answers (3)

Saransh Dhyani
Saransh Dhyani

Reputation: 415

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=*****";

Upvotes: 0

lourdu rajan
lourdu rajan

Reputation: 379

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());
        }
    })
);

Upvotes: 0

kurtisvg
kurtisvg

Reputation: 3565

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.

Upvotes: 2

Related Questions