Unexpected System.cmd PSQL Certificate Challenge

ragamuf

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

4 comments

#system #ecto-migration

2 1117 4

2022-09-02 05:49:51 UTC

First 4 of 4 Posts

al2o3cr

sslmode is relevant to libpq, the client library that ships as part of the Postgresql distribution.

Postgrex doesn’t use it - see this issue for discussion.

psql, however, does use it and is looking for a root certificate in ~/.postgresql/postgresql.crt. See also the libpq documentation

Post #1

ragamuf

Thanks for making the distinction between the behavior exhibited by Postgrex and psql.

The fact that I can manually invoke psql on the command line and connect whereas System.cmd fails makes me believe it is a file permission issue. I know System.cmd uses Port and it looks to me like the process has no idea about the crt file existing even though it is there.

Any thoughts on how to set the permission in this case?

Post #2

ragamuf

I contacted Digital Ocean and was directed to generate a client cert and key to use in concert with the root CA which I downloaded:

openssl req -x509 -newkey rsa:2048 -keyout client-key.pem -out client-cert.pem -days 3650 -nodes -subj ‘/CN=localhost’

Then set the permissions based on these rules:

Add chmod a=r to:

root.crt and client-cert.pem

Change the permission and the owner on the key to:

Chmod 0600 client-key.pem

Chown app client-key.pem

-rw------- 1 app app client-key.pem

-r–r–r-- 1 app app client-cert.pem

-r–r–r-- 1 app app root.crt

Then as before, included these three credentials in the environment variables for psql

{“PGSSLROOTCERT”, “/etc/db/root.crt”},

{“PGSSLCERT”, “/etc/db/client-cert.pem”},

{“PGSSLKEY”, “/etc/db/client-key.pem”}

And now the error with sslmode=require is:

SSL error: tlsv1 alert unknown ca

None of this should have even been needed AFAIK, because of the sslmode=require setting, which instructs the handshake to ignore certificate validation.

I feel like sslmode is not been honored when using System.cmd to invoke psql.

Invoking the same command directly on the terminal with only the sslmode specification and no certs works as expected.

Thoughts?

Post #3

ragamuf

For those that follow, I finally resolved this communication challenge between System.cmd and psql by setting the root CA via the PGSSLCert environment variable and forgoing the others. Also, it helped to exclude the .crt suffix.

{“PGSSLCERT”, “/etc/db/root”}

I documented this entire saga here Accelerate Multitenant Migrations with Schema Snapshots

Post #4

— All posts loaded —