This page explains how to stage files on Dataflow worker VMsto use SSL certificates with the Google-provided Java Database Connectivity(JDBC) templates. You can also use the steps in this page to stage filesdynamically on the worker VM before processing begins.

Stage files on worker VMs

With some templates, you can stage files on Dataflow worker VMs by usingtheextraFilesToStage template parameter. Use this parameter tostage files dynamically on the worker VM before data processing begins.You might use this parameter in the following cases:

• Store certificate and key files on each worker for SSL connections.
• Because Dataflow doesn't include built-in support forSecret Manager, use this parameter to configure templates to storeand access Secret Manager secrets.

This parameter is available in some but not all templates.To see if the parameter is available with the template that you're using,see the template parameters table for your template inGoogle-provided Dataflow streaming templatesorGoogle-provided Dataflow batch templates.

When you use this parameter, comma separated Cloud Storage paths orSecret Manager secrets are passed to the worker and saved in the/extra_files directory on each worker.When the files are saved in the/extra_files directory, other parameters canreference them. To reference the files, in the configuration properties, specifyfile paths relative to the/extra_files directory.

All Cloud Storage files passed using this parameter arestored in the/extra_files directory with the same filename.Secret Manager secrets are stored in the/extra_files directory with thesecret name used as the filename.

For example, when you setextraFilesToStage to the following path, the files arestored in the/extra_files directory of each worker with the filenamesfile.txt andSECRET_ID.

gs://BUCKET_NAME/file.txt,projects/PROJECT_ID/secrets/SECRET_ID/versions/VERSION_ID

Configure SSL certificates

With templates that use JDBC to read or write, you can use theextraFilesToStage template parameter to stage certificates as local fileson worker VMs, and then reference the local filename in the JDBC connection URLor in the connection properties.

JDBC SSL connections differ based on the database. This section providesexamples for PostgreSQL and MySQL databases.

If you need to set Java system properties, useJvmInitializer.You can useJvmInitializer to perform one-time initialization on all Dataflow Java workers.

PostgreSQL

For SSL connections in PostgreSQL databases, the following parameters areavailable:ssl,sslmode,sslcert,sslkey, andsslrootcert.

• To specify that the connection should use SSL and what mode to use, use thessl andsslmode parameters.
• For client validation, use thesslcert andsslkey parameters.
• For server validation, use thesslrootcert parameter.

When you connect to PostgreSQL, set the following parameters:

extraFilesToStage=gs://BUCKET_NAME/root_cert.crt,gs://BUCKET_NAME/cert.crt,gs://BUCKET_NAME/key.keyconnectionUrl=jdbc:postgresql://HOST/DATABASE?ssl=true&sslrootcert=/extra_files/root_cert.crt&sslcert=/extra_files/cert.crt&sslkey=/extra_files/key.key&OTHER_PARAMETERS...

For additional PostgreSQL JDBC configuration parameters and more details aboutthe parameters, seeConnection Parameters in the JDBC PostgreSQL documentation.

MySQL

For SSL connections in MySQL databases, the following parameters areavailable:sslmode,trustCertificateKeyStoreUrl, andclientCertificateKeyStoreUrl.

• To specify that the connection should use SSL and what mode to use, use thesslmode parameter.
• For client validation, use theclientCertificateKeyStoreUrl parameter.
• For server validation, use thetrustCertificateKeyStoreUrl parameter.

When you connect to MySQL, set the following parameters:

extraFilesToStage=gs://BUCKET_NAME/truststore.jks,gs://BUCKET_NAME/cert.crt,gs://BUCKET_NAME/keystore.jksconnectionUrl=jdbc:mysql://HOST/DATABASE?sslmode=REQUIRED&trustCertificateKeyStoreUrl=file:/extra_files/truststore.jks&sslcert=file:/extra_files/keystore.jks&OTHER_PARAMETERS...

For additional MySQL JDBC configuration parameters and more details aboutthe parameters, see thesecurity connectors page in the MySQL documentation.

SSL connection example

The following example demonstrates how to run thePub/Sub to JDBCtemplate with SSL connections in a MySQL database.

#!/bin/bashgcloudconfigsetprojectPROJECT_IDecho"Running template ..."gclouddataflowflex-templaterunmy-job\--template-file-gcs-location="gs://dataflow-templates-REGION_NAME/latest/flex/Pubsub_to_Jdbc"\--region="REGION"\--parameters=\driverClassName="com.mysql.jdbc.Driver",\connectionUrl="jdbc:mysql://HOST/DATABASE?sslmode=REQUIRED&trustCertificateKeyStoreUrl=file:/extra_files/truststore.jks&sslcert=file:/extra_files/keystore.jks",\driverJars="gs://BUCKET_NAME/mysql.jar",\statement="INSERT INTO tableName (column1, column2) VALUES (?,?)",\inputSubscription="projects/PROJECT_ID/subscriptions/subscription",\outputDeadletterTopic="projects/PROJECT_ID/topics/topic",\extraFilesToStage="gs://BUCKET_NAME/truststore.jks,gs://BUCKET_NAME/cert.crt,gs://BUCKET_NAME/keystore.jks"