Postgress Data Source and using sslrootcertFile #7365
Description
Issue Summary
I am creating a custom Dockerfile from redash/redash:latest. During the build process of the image, I run a .sh script that uses the Redash CLI to create a new "pg" data source. This data source has sslmode="verify-full" and the contents of my .pem file base64 encoded and set tosslrootcertFile.
Once my server is running, when I try to test my connection to my DB I added on build, I get the error, Connection Test Failed: root certificate file "/home/redash/.postgresql/root.crt" does not exist Either provide the file or change sslmode to disable server certificate verification. This error message appears to be a psycopg2 error that's passed along.
I believe there is some issue with the use of the sslrootcertFile value and translating this to be set as the sslrootcert argument when connecting using psycopg2.
Steps to Reproduce
- In a
.shscript, use the CLI to create a new data source:
... rest of .sh script ...
ssl_root_cert_base64=$(python -c "import base64; print(base64.b64encode(open('/app/certs/rds-bundle.pem', 'rb').read()).decode('utf-8'))")
cat <<EOF > /tmp/db_config.json
{
"dbname": "$db_name",
"host": "$db_host",
"port": $db_port,
"user": "$username",
"password": "$password",
"sslmode": "verify-full",
"sslrootcertFile": "$ssl_root_cert_base64"
}
EOF
python /app/manage.py ds new "$DATA_SOURCE_NAME" \
--type "pg" \
--options "$(cat /tmp/db_config.json)"
... rest of .sh script ...- Confirm in logs that the inserted data is correct. When I run this, I can see from logs that Redash puts out from the CLI command that the data I am adding is in fact what I intend it to be. All longs up to launching the server look successful.
- When the server is up and running, I go to the pre-created data source to test the connection and get this toast error message:
root certificate file "/home/redash/.postgresql/root.crt" does not exist Either provide the file or change sslmode to disable server certificate verification
Any other info e.g. Why do you consider this to be a bug? What did you expect to happen instead?
Something seems off either in my implementation or how Redash is configured to reference the sslrootcertFile content. When I looked into how "pg" data sources connect, I looked at this function and it reads to me that sslrootcertFile is supposed to be the contents of the .pem file and be base64 encoded. When I test the connection, the error reads like the sslrootcert argument isn't being passed at all to the psycopg2.connect object. Because /.postgresql/root.crt is the default path psycopg2 will use when that arg is not passed.
Technical details:
- Redash Version: v25.1.0
- Browser/OS: Google Chrome (133.0.6943.142) / Linux (AWS)
- How did you install Redash: Custom Docker file, starting with
FROM redash/redash:latest
*Additional info: I am hosting Redash as a Fargate App on AWS. So the actual container is running on a Linux server.