Best practice for handling SSL Certificate for Postgres Database connection when deploying to shinyapps.io?

[wooly_at_worky]

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.

I've reviewed:

• 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.

[andy_kipp]

The certificate authority PEM shouldn't contain any private information. Assuming the private keys are not located in the CA PEM file, there is no reason not to include it in your application files when deploying to shinyapps.io.

[wooly_at_worky]

Thanks for the quick response and answer!