3

I need to connect to PostgreSQL db via SSL. I received 2 certificates and 1 key -> sslrootcert=root.crt sslcert=postgresql.crt and sslkey=postgresql.key.der

Here is my import config from Dataproc:

import
-Dmapreduce.job.user.classpath.first=true
-Dhadoop.security.credential.provider.path=jceks://hdfs/secrets/prod/db.jceks
--connect=jdbc:postgresql://xxx.x.x/DBNAME?sslrootcert=root.crt&sslcert=postgresql.crt&sslkey=postgresql.key.der
--username=user1
--table=db1
--target-dir=gs://bucket
--delete-target-dir
--as-avrodatafile
--password-alias=password

The Dataproc coundn't find my SSL certificates: Error: java.lang.RuntimeException: java.lang.RuntimeException: org.postgresql.util.PSQLException: Could not open SSL certificate file postgresql.crt

How I could add certificates and key to dataproc to be sure that these certs be accessible in connection config.

Where I should store SSL certificates to use them in import config during run job :

--connect=jdbc:postgresql://xxx.x.x/DBNAME?sslrootcert=/???/??/root.crt&sslcert=/???/??/postgresql.crt&sslkey=/???/??/postgresql.key.der
Dagang
  • 24,586
  • 26
  • 88
  • 133
vamper1234
  • 104
  • 8
  • Dataproc doesn't include PostgreSQL, are you trying to connect to an external PostgreSQL from Dataproc? Also what is the code snippet above? Is it a PySpark job? – Dagang Aug 22 '21 at 21:33
  • @Dagang, yes I'm trying to run sqoop job to connect to an external PostgreSQL. The data need to be transferred from PostgreSQL to GCS bucket – vamper1234 Aug 23 '21 at 06:42
  • @Dagang, I updated the comment a bit – vamper1234 Aug 23 '21 at 12:29
  • I think you want to add it to Java's default certificates location, `${JAVA_HOME}/jre/lib/security/cacerts`, for Dataproc it is `/usr/lib/jvm/adoptopenjdk-8-hotspot-amd64/jre/lib/security/cacerts` – Dagang Aug 24 '21 at 05:42

1 Answers1

0

I think you can add the server certificate or its root CA certificate to Dataproc VMs at ${JAVA_HOME}/jre/lib/security/cacerts, for Dataproc it is /usr/lib/jvm/adoptopenjdk-8-hotspot-amd64/jre/lib/security/cacerts. You can use an init action or create a custom image to put the cert in the dir.

See this doc and this question for more details.

Dagang
  • 24,586
  • 26
  • 88
  • 133