Connect my mysql database with airflow composer

[pbouras]

I have a airflow implementation that running in composer. I establish the database connection using airflow ui, but i am having this error 

ERROR - Exception: (2026, 'SSL connection error: SSL_CTX_set_default_verify_paths failed')

In the database connection i provide the Host, the user, the passrowd, the schema, the port and i provide the ssl in json format like this 

{
"ssl": {
"cert": "tmp/....pem",
"ca": "tmp/....pem",
"key": "tmp/....pem"
}
}

Sometimes it's work, but when i re-run the environment then i can not connect. Can anyone help me to find the best practices and make a mysql connection that it's solid ?

[marckevin · Accepted solution]

Hi @pbouras,

Welcome to Google Cloud Community!

 "SSL_CTX_set_default_verify_paths failed" error means that SSL library cannot find or access the certificate you defined, usually due to invalid path or permission where the certificates are stored. While the intermittent nature of the error possibly suggests an issue on persistence of certificate files or inconsistencies in file system access within the Composer environment.

Ensure you have the correct file path configuration. Try storing the certificates on persistent locations like Google Cloud Storage(GCS) and use absolute paths to ensure SSL certificates are available even on re-run. However, kindly note that even if you secure it with IAM permissions, the certificates may still be accessible to other users with read access to that GCS bucket.

On the other hand, if stricter security is required, it is recommended to use Google Cloud Secret Manager as the best practice for storing sensitive information like API keys, passwords, and certificates. Kindly note that fetching the secret and writing it to a temporary file requires additional steps than storing it from GCS.

To further troubleshoot your issue, check Cloud Logging to analyze for deeper diagnostics and examine logs related to the error.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[pbouras]

Hello @marckevin, thanks for your response. I add my certs into bucket and seems not run without problem. For now i do not want to have stricter security, but i think in the future i will have to enhance it.  So if i want to use google secret manager then i add my certificates there, i add airflow configurations for backend, i create a dag that fetch the ssl and store it into tmp files and then in the ui i add this paths into connection tab ?