PowerBI/PowerQuery can't connect to Postgres database due to ssl validation error
Default postgres-ssl image used, using connection details from variables
14 Replies
Project ID:
f3f3aa54-1f32-4a2a-9e9a-61d26191712df3f3aa54-1f32-4a2a-9e9a-61d26191712d
All variables are stock. Double checked all credentials for errors.
What could be the culprit?
Previously a MySQL connection worked, about an year ago
Note for support person: My availability will be probably a bit limited in the next 18 hours, but I will do my best to answer texts
the postgres databases railway deploys for you have self signed certificates, if that's not something power bi supports then you might not be able to use it
Seems like power bi really doesn't like ssl :-?
I'll try to dig something up just so there's some documentation around
Hey @ChaoticKitten , not sure if you solved this but based on what i've researched the following is needed to connect to Power BI:
1. Install npgsql client driver on the machine you're using Power BI Desktop.
- unsure if you need to install "GAC" as well during the install wizard, I did
2. Trust SSL cert
- This is where I am stuck
- Need SSL public key from your Postgresql database on Railway
- Convert that key to a certificate of type PKCS#7/P7B
- Import that certificate to the Trusted Room Certificate
Do you or @Brody happen to know how to get the public key for the self-signed certificate?
you would need to mount the volume to a filebrowser service so that you could download the certificates
I'd like to give that a try if possible. Can you point me in the right direction to get started please?
https://railway.app/template/Nan7Bs
please read the overview
I have to mount the volume from the postgresql service to the filebrowser service?
correct
and then once done, mount it back to postgres
I've downloaded the certs folder from the volume and remounted it to the db. I'll work on figuring out how to proceed now. Thank you for your help sir!
no problem!
@volcanicislander
Thank you for the messages!
It's a splendid idea that could work in a pinch I believe. I'm specialized in Data Engineering so naturally I found a solution that's a bit overkill but works for my needs - in this case creating a flow that takes sql queries, runs them against the DB securely and saves encrypted files locally as parquet.
Upsides:
Parquet is smaller than csv
Total processing time is roughly a few seconds because I did a bit of extra wizardry with the database queries
I unencrypt and unpack the parquet files directly in PowerBI
Downsides:
The DAG is not flexible enough for quick emergency analytics
Parquet is finicky
Still not a direct connection
Better alternatives would be to:
* use SQLAlchemy or something similar straight in PowerBI's python.
* go your way with downloading certificates locally
* creating an intermediary database locally and daisy chaining the queries between railway and local
Thank you for the detailed response! That definitely got me thinking about workarounds.
However, I did manage to get Power BI Desktop to connect to Postgres on Railway by going to Data Source Settings and turning off Encrypt Connection for the data source:
I'm not sure if this would fit your use case but if you don't mind an unencrypted connection this could be an option. I don't think you would need the npgsql client driver and adding cert to trust store, but i'm not sure.
