I am running into a certificate challenge whenever I use System.cmd to shell out to psql on my prod server, which speaks to a remote server running the database. The database is set to enforce SSL.
I can connect my Phoenix app on boot to the database with ?sslmode=require connection string query parameter. No additional certs are needed for the app to work. The same is true when I SSH to the prod server and connect directly to the database via psql on the terminal.
However, whenever I issue the System.cmd from the application to invoke psql, the server challenges for certificates.
FATAL: SSL required… could not open certificate file "/home/main/.postgresql/postgresql.crt": Permission denied\n".
All the env parameters to System.cmd are taken from the repo config.
args = ["--quiet", "--file", "/tmp/file.sql", "-vON_ERROR_STOP=1",
"--single-transaction"]
env=[
{"PGPASSWORD", config[:password]},
{"PGPORT", config[:port]},
{"PGUSER", config[:username]},
{"PGDATABASE", config[:database]},
{"PGHOST", config[:hostname]},
{"PGSSLMODE", "require"}
]
System.cmd "psql", args, env: env, stderr_to_stdout: true
Does anyone know why the requirements to communicate with the database would change so drastically when invoking psql via System.cmd vs. using psql directly on the command line in the shell?
P.S The database is managed and only one certificate is provided which has not been used to date for the application to work and for which no private key is included. System.cmd is a recent feature change I am testing with the app with migrations.
And yes, I have downloaded the cert and added it to the mix in various forms but the SSL failed error still persist.
Thanks, Michael