cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to connect PostgreSQL from Databricks

akj2784
New Contributor II

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

11 REPLIES 11

shyam_9
Valued Contributor

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
New Contributor II

SSL enforce status :ENABLED

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

shyam_9
Valued Contributor

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

charlesgreen
New Contributor II

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

Really wish it was part of the docs.

JavierDe_La_Tor
New Contributor II

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?

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

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

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?

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.

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
Not applicable

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

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group