How to connect PostgreSQL from Databricks

[akj2784]

I am trying to connect PostgreSQL from Azure Databricks.

I am using the below code to connect.

jdbcHostname = "Test"

jdbcPort = 1234

jdbcDatabase = "Test1"

jdbcUrl = "jdbc:postgresql://{0}:{1}/{2}".format(jdbcHostname, jdbcPort, jdbcDatabase)

Connection was successful

connectionProps = { "user": "userid", "password": "pass!" }

Now, when I run the below command

accountDF = spark.read.jdbc(url=jdbcUrl, table="POC_TABLE", properties=connectionProps) display(accountDF)

I get the below error.

org.postgresql.util.PSQLException: FATAL: SSL connection is required. Please specify SSL options and retry.

How do I fix this error. I have SSL enabled in the postgreSQL at Azure portal.

Regards,

Akash

[shyam_9]

It seems that you didn’t specify SSL options correctly. Please enable SSL and make sure to save it.

Please go through the steps in Documentation and follow them,

https://docs.microsoft.com/en-us/azure/postgresql/concepts-ssl-connection-security

[akj2784]

SSL enforce status :ENABLED

I see it in Azure portal for this database. What else is missing ?

[shyam_9]

Can you please follow the steps in documentation and still if you are facing issue, reply here.

[charlesgreen]

Thank you Akash for posting the JDBC connection config for Postgres.

Really wish it was part of the docs.

[JavierDe_La_Tor]

Hi,

I am trying the same. What I am stuck is figuring out how to upload the Key and Certificate files for the JDBC driver to use when using SSL. The connection should look like:

remote_table = spark.read.format("jdbc")\
  .option("driver", driver)\
  .option("url", url)\
  .option("dbtable", table)\
  .option("user", user)\
  .option("password", password)\
  .option("ssl", True) \
  .options("sslmode", "verify-full" ) \
  .options("sslcert", "path_to_cert" ) \
  .options("sslkey", "path_to_key" ) \
  .load()

But I dont know how or where to upload the certificate and and key, so when I ran it I get the following error:

org.postgresql.util.PSQLException: SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

Any idea how to upload the cert and key to the cluster so that JDBC can find it?

[TheodoreVadpey]

Did you resolve this? I'm having the same problem

[BorjaMuñoz]

Hi,

There are different possibilities:

- You can upload them to the cluster using wget as explained in this article:

https://carto.com/help/tutorials/sql-direct-databricks/

- You can upload the files to an S3 bucket / Azure Blob and then read them from there

- You can import them using the "Import and Explore Data" functionality in the Databricks Welcome page

[TheodoreVadpey]

Hi, thanks for your reply! I couldn't get it to work from dbfs location or driver-- just need to point to ssl-ca... I made a question for it here: https://forums.databricks.com/questions/48860/connect-to-azure-mysql-with-ssl-through-jdbc.html. Am I missing some option?

[BorjaMuñoz]

You are getting a SSL error. It seems you are using SSL mode "verify-full" that requires you to include certificate files to verify your identity, not only the server identity. I'm not familiar with MySQL but it seems "verify-full" is not a supported option.

[RashidQuamar]

https://docs.microsoft.com/en-us/azure/databricks/data/data-sources/sql-databases

The above link shows how to generate and add the required certificates

[Anonymous]

@Javier De La Torre do you really need two-way SSL (verify-full)? In most cases one way SSL (sslmode=require) should be enough.

@akj2784​  When you say "Connection was successful", where do you mean you established a successful connection? You might be missing the

.option("ssl", True) \ .options("sslmode", "require")