Our company is upgrading our database server from Postgres 9.6 to 13.7 on Azure; these databases store data used for persistence in our shinyapps.io deployed apps. We're running into a deployment challenge and I'm wondering if there's a best practice for this situation.
Before, we could connect to our postgres 9.6 server from shinyapps.io with the usual connection string through dbConnect(), setting sslmode = "require" and providing the host, port, dbname, etc. Very simple.
library(tidyverse) library(dbplyr) library(RPostgres) library(DBI) server_config <- config::get(config = "new_remote") con <- dbConnect( RPostgres::Postgres(), user = server_config$db_user, password = server_config$db_pass, dbname = server_config$db_name, host = server_config$db_host, port = server_config$db_port, sslmode = 'require' )
Unfortunately, on our new server, this sort of approach now results in:
# Error in connection_create(names(opts), as.vector(opts)) : # SSL error: certificate verify failed
Now, we can only connect to with sslmode="verify-full", which also requires the argument sslrootcert and a path to the certificate stored on a local machine.
con <- dbConnect( RPostgres::Postgres(), user = server_config$db_user, password = server_config$db_pass, dbname = server_config$db_name, host = server_config$db_host, port = server_config$db_port, sslmode = 'verify-full', sslrootcert = server_config$db_cert )
where server_config$db_cert is a path that points to a "DigiCertGlobalRootCA.crt.pem" file
Here's my question: What is the recommended way for transmitting a Postgres SSL certificate to shinyapps.io when deploying? Stick it in my repo and make sure it isn't tracked, then reference the file in code?
To be clear, this is NOT:
- An issue with allowlisting Shinyapps.io IP addresses for connections to our postgres database; I've already done that.
- An issue with using a custom certificate to deploy the shiny app to shinyapps.io with rsconnect, as a few other posts like this one have raised.
- An outage or other database connection issue. I can remotely connect via pgAdmin or a local R script when i use verify-full when I have the .ca.pem file on my computer.
- The usual RStudio documents (example, and online help on connecting to Postgres, but nothing about dealing with certificates when deploying. If someone knows how to tweak Postgres/Azure's settings to not require verify-full, i'd be eager to hear, but I don't think this is the forum for that.