r-dbi
Enabling secure (SSL/TLS) client connections, an example in documentation or a vignette would be nice
The documentation could use an example of how to enable SSL/TLS connectivity without authentication (i.e., transport encryption). I would expect that this is fairly common scenario where the MariaDB server is configured to require secure transport via the require-secure-transport system variable.
In the legacy RMySQL library this is done with the via sslmode argument. It's not clear from the documentation how to do this with RMariaDB. The ssl.key, ssl.cert, ssl.ca and ssl.capath arguments all appear to deal with two-way TLS authentication. The client.flags argument appears to reference a CLIENT_SECURE_CONNECTION flag in the underlying C connector which appears to do what I want, however there doesn't seem to be any CLIENT_SECURE_CONNECTION flag documented in the connector API's documentation on RMariaDB's website.
I'm able to successfully connect to my particular database using RMySQL:
library("RMySQL")
# connect to MySQL database
mysql_dbconn <- dbConnect(
drv = RMySQL::MySQL(),
dbname = "internal_equities",
user = "MYSUSERNAME",
password = "MYPASSWORD",
sslmode = "require")
I'm sure I've missed something obvious so if someone has a working configuration please share it.
I'd also love to see the documentation include an example and the client.flags documentation updated if it is indeed the case that the CLIENT_SECURE_CONNECTION flag is no longer used by the connector.
Thanks
Thanks. Looking at the code in RMySQL, the sslmode argument doesn't seem to be picked up anywhere. There's no explicit arguments, and the ellipsis is unused: https://github.com/r-dbi/RMySQL/blob/49a859b775414dbb5deb629623a01da7297e3a6d/R/connection.R#L66-L104 . Is the package perhaps relying on a different mechanism?
What operating system are you using? What error message are you seeing?
Are you by any chance using a .mylogin.cnf file with hard-coded password? https://github.com/r-dbi/RMariaDB/issues/156
Kirill,
Thanks for getting back to me. I appreciate all your hard work on this library.
Server: Debian 11.2 and MariaDB 10.5.12 Client: Windows 10 1909, R 4.12, DBI 1.1.1, RMariaDB 1.2.0
The SQL server is configured to with a SSL certificate issued from my organization's Certificate Authority (which is trusted by client). require-secure-transport is enabled as well.
I get this error from the client when connecting with the following snippet:
library("RMariaDB") library("askpass") con <- dbConnect( drv = RMariaDB::MariaDB(), username = "MYUSERNAME", password = askpass(), dbname = "MYDATABASE", host = "MYFQDN", port = "3306" )
Error: Failed to connect: Access denied for user 'MYUSERNAME'@'10.233.224.136' (using password: YES)
I was able to get an ODBC() based connection working today by enabling the Force TLS Use option.
I feel like I'm missing something simple in my configuration but I'm not seeing it.
Thank you.
Sent with ProtonMail Secure Email.
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Sunday, January 2nd, 2022 at 9:24 PM, Kirill Müller @.***> wrote:
Are you by any chance using a .mylogin.cnf file with hard-coded password? #156
— Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.Message ID: @.***>
Thanks. I understand that connectivity works for RMySQL and ODBC, but not for RMariaDB. I looked at the code to establish the connection, from the arguments you supply it's virtually identical. The only difference between RMariaDB and RMySQL is the client library used.
Can you please double-check which of the following still works for connecting with RMySQL:
library("RMySQL")
# connect to MySQL database
mysql_dbconn <- dbConnect(
drv = RMySQL::MySQL(),
dbname = "internal_equities",
user = "MYSUSERNAME",
password = "MYPASSWORD"
)
library("RMySQL")
# connect to MySQL database
mysql_dbconn <- dbConnect(
drv = RMySQL::MySQL(),
dbname = "internal_equities",
user = "MYSUSERNAME"
)
OK. My apologizes. I have an important correction.
The above referenced RMySQL code does NOT work remotely from my Windows client, with or without the sslmode argument. I was conflating it with the server environment where Unix sockets are used to connect (confirmed with dbGetInfo()).
Server-side it looks I'm using R 4.0.4 which is the version included in the Debian repositories. I was unable to test RMariaDB on the server since it appears that it is not available for this version of R. I'd rather not upgrade the version of R running there but that can be arranged if possible.
On the Windows client, the only method I have been successful with is by using an ODBC() connection. That might just be the best path forward.
Thanks again.
Sent with ProtonMail Secure Email.
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Tuesday, January 4th, 2022 at 6:12 PM, Kirill Müller @.***> wrote:
Thanks. I understand that connectivity works for RMySQL and ODBC, but not for RMariaDB. I looked at the code to establish the connection, from the arguments you supply it's virtually identical. The only difference between RMariaDB and RMySQL is the client library used.
Can you please double-check which of the following still works for connecting with RMySQL:
library(
"
RMySQL
"
)
connect to MySQL database
mysql_dbconn
<-
dbConnect(
drv
=
RMySQL
::
MySQL(),
dbname
=
"
internal_equities
"
,
user
=
"
MYSUSERNAME
"
,
password
=
"
MYPASSWORD
"
)
library(
"
RMySQL
"
)
connect to MySQL database
mysql_dbconn
<-
dbConnect(
drv
=
RMySQL
::
MySQL(),
dbname
=
"
internal_equities
"
,
user
=
"
MYSUSERNAME
"
)
— Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.Message ID: @.***>
Thanks. I advise to use the same database setup for both development and production. Using ODBC for dev and RMariaDB or RMySQL for prod might lead to headaches later on, depending on the complexity of the data queried.
I'm happy to review your configuration over a short call, I'm curious myself what might lead to those problems. Please get in touch via e-mail if you're interested.
After quite a bit of trial and error plus digging into the MariaDB C Connector docs, I was able to get the following to work. This will connect to an Amazon Aurora RDS that is configured to enforce SSL:
First, create an option files named something like ~/my.cnf. This is necessary because the RMariaDB library currently only sets custom options that are read from a file. The contents of the file should be:
[client]
ssl-enforce=1
ssl-verify-server-cert=0
Then, create an R script as follows that references this file:
library(RMariaDB)
library(DBI)
con <- dbConnect(RMariaDB::MariaDB(),
default.file = normalizePath("~/my.cnf"),
host="<replace with your hostname>",
dbname="<your db name>",
username="<your db user>",
password="<your db password>",
port=<your db port>,
timeout=20,
mysql = FALSE,
)
dbListTables(con)
dbDisconnect(con)
The mysql = FALSE line is also important even if you are connecting to a MySQL database. The options are MariaDB connector options, but this does still seem to work when connecting to MySQL.
Hopefully this helps someone with similar problems. It'd be nice if this was better documented as I had to try quite a few combinations of settings to find the one that worked.
It would be helpful if the documentation included information on how to configure client options directly in the code. For instance, how to enable SSL while disabling server certificate verification, as opposed to the current method of using a file, as shown in the example above.
Thanks. Curious to know why mysql = FALSE is important. What fails if you omit it?
@krlmlr I'm not sure, but I think the configuration parameters in my.cnf are specific to MariaDB and so potentially don't load or apply. When I remove that statement, it won't connect.
@ipimpat It's not currently possible due to the way the library is written. Would require a change to the code to allow setting those parameters via code rather than via a file.
This is weird because the mysql argument only controls the returned class. What do you mean by "it won't connect" -- is there a timeout, or an error message?
What else do we need besides the ssl.* arguments to dbConnect() to make SSL fully configurable without files?
What else do we need besides the
ssl.*arguments todbConnect()to make SSL fully configurable without files?
What about including all the same options as the command line client?
$ mysql --help | grep ssl
--ssl Enable SSL for connection (automatically enabled with
--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name X509 cert in PEM format (implies --ssl).
--ssl-cipher=name SSL cipher to use (implies --ssl).
--ssl-key=name X509 key in PEM format (implies --ssl).
--ssl-crl=name Certificate revocation list (implies --ssl).
--ssl-crlpath=name Certificate revocation list path (implies --ssl).
--ssl-verify-server-cert
ssl FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
ssl-verify-server-cert FALSE
However, the most important options are --ssl and --ssl-verify-server-cert.
After spending quite a bit of time getting this to finally work with the MariaDB Connector/C, I have determined the following simple trick (long story, but I upgraded to AWS AL2023 and they only provide the MariaDB Connector/C library).
Note: just setting client.flag=CLIENT_SSL enables SSL when using the Native MySQL C Connector library whereas it does not enable SSL when using the MariaDB Connector/C library.
What I have found is that just setting ssl.ciper="" causes mysql_ssl_set() to get called which then enables using SSL (this currently works with RMariaDB v1.3.1):
con <- dbConnect(RMariaDB::MariaDB(),
host="<replace with your hostname>",
dbname="<your db name>",
username="<your db user>",
password="<your db password>",
port=<your db port>,
ssl.cipher="",
client.flag = CLIENT_SSL,
)
Alternatively, if you do specify a default file, you can just set the flag ssl-enforce=1, and it also works fine. Same if you use group=, you can just add ssl-enforce=1 to your group definition.
Interestingly, setting the cipher to an empty string seems to be harmless and still ends up using a cipher from the server's returned capabilities. This can be verified by querying the server and checking the session cipher:
> dbGetQuery(sqlCon, 'show status where variable_name = "ssl_cipher"')
Variable_name Value
1 Ssl_cipher TLS_AES_256_GCM_SHA384
The interesting thing is the the Native MySQL C Connector library works just fine with only the client.flag=CLIENT_SSL. I believe their library must actually look at the client_flag for CLIENT_SSL in their version mysql_real_connect() and then enable it, whereas the MariaDB Connector-C library is definitely not doing this and is expecting the caller to properly interpret and set the options before calling mysql_real_connect().
NOTE: @krlmlr you have actually broken enabling SSL using the client.flag in #319 since you removed the call to mysql_ssl_set(). This was the one thing that was very useful that mysql_ssl_set() was doing:
char enable= 1;
return (mysql_optionsv(mysql, MYSQL_OPT_SSL_ENFORCE, &enable) |
So, to fix this, what really needs happen is the following code should be added to DbConnection::connect():
+ if (client_flag & CLIENT_SSL) {
+ my_bool use_ssl_ = 1;
+ mysql_options(this->pConn_, MYSQL_OPT_SSL_ENFORCE, (void *)&use_ssl_);
+ }
if (!Rf_isNull(ssl_key)) {
mysql_options(this->pConn_, MYSQL_OPT_SSL_KEY, cpp11::as_cpp<std::string>(ssl_key).c_str());
}
You may additionally want to add some similar logic for CLIENT_SSL_VERIFY_SERVER_CERT as I do not see this getting propagated anywher in the MariaDB Connector/C either:
+ // Set SSL options
+ if (client_flag & CLIENT_SSL) {
+ my_bool use_ssl_ = 1;
+ mysql_options(this->pConn_, MYSQL_OPT_SSL_ENFORCE, (void *)&use_ssl_);
+ }
+ if (client_flag & CLIENT_SSL_VERIFY_SERVER_CERT) {
+ my_bool verify_server_cert_ = 1;
+ mysql_options(this->pConn_, MYSQL_OPT_SSL_VERIFY_SERVER_CERT, (void *)&verify_server_cert_);
+ }
if (!Rf_isNull(ssl_key)) {
mysql_options(this->pConn_, MYSQL_OPT_SSL_KEY, cpp11::as_cpp<std::string>(ssl_key).c_str());
}
