Access MySQL database from shinyapps.io using require_secure_transport = ON

shiny
1

I follow this tutorial about how to configure SSL/TLS for MySQL.

When I set require_secure_transport = ON in /etc/mysql/my.cnf and create user using

For all shinyapps.io IPs 54.204.34.9, 54.204.37.78, 54.204.29.251
mysql > CREATE USER 'shinyuser'@'54.204.36.75' IDENTIFIED BY 'password' REQUIRE SSL;

I get this error in shiny logs:

Failed to connect to database: Error: Access denied for user 'shinyuser'@'ec2-54-204-36-75.compute-1.amazonaws.com' (using password: YES)

However, If I don't use REQUIRE SSL and I bind require_secure_transport = ON, I can connect successfully.

So, I have few questions:
1- Is it possible to connect from shinyapps.io securely, and if yes ... How?
2- Is it OK to connect from shinyapps.io without require_secure_transport = ON, i.e. In a normal way "from a security point of view".

PS: I generate the SSL/TLS certificates and keys using sudo mysql_ssl_rsa_setup --uid=mysql. Also, I can connect from client-server using require_secure_transport = ON and without publishing the public key and certificates to that client-server.

Thanks in advance for your kind help

2

Hi,

This question is for shinyapps.io. and should be asked in https://forum.posit.co/tags/c/shiny/shinyappsio channel.

Thanks for reaching out to the team.

3

@abubaker how are you connecting to your MySQL instance from shinyapps.io?
Could you share the connection call, with the address, username and password redacted please.

4

@josh thanks for your reply. Here is my connection call, which I define on top of my Server.R script.

library(DBI)
library(RMySQL)
library(pool)

pool <- dbPool(
  drv = RMySQL::MySQL(),
  dbname = "db_name",
  host = "XXX.XXX.XXX.XXX",
  username = 'user_name',
  password = 'password'
)

onStop(function() {
  poolClose(pool)
})
5

Have you tried passing the CLIENT_SSL or CLIENT_SECURE_CONNECTION flags when constructing the connection?

And just to clarify a bit, you phrased some of your questions as "connect to shinyapps.io", but you are actually trying to attempt to "connect from shinyapps.io" to a remote database, correct?

6

I didn't try passing flags, or actually I wasn't aware that is possible. However, how I can pass flags inside dbPool.
Sorry for mistake, yes I would like to connect from shinyapps.io to a remote database in digital ocean.