Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Enabling secure (SSL/TLS) client connections, an example in documentation or a vignette would be nice #256

Closed
akkce opened this issue Dec 29, 2021 · 15 comments
Closed

Enabling secure (SSL/TLS) client connections, an example in documentation or a vignette would be nice #256

akkce opened this issue Dec 29, 2021 · 15 comments
Labels
docs help wanted

Comments

@akkce
Copy link

akkce commented Dec 29, 2021

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
@krlmlr
Copy link
Member

krlmlr commented Jan 3, 2022 •
edited
Loading

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?

@krlmlr
Copy link
Member

krlmlr commented Jan 3, 2022

Are you by any chance using a .mylogin.cnf file with hard-coded password? #156

@akkce
Copy link
Author

akkce commented Jan 4, 2022 via email

@krlmlr
Copy link
Member

krlmlr commented Jan 5, 2022

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"
)

@akkce
Copy link
Author

akkce commented Jan 5, 2022 via email

@krlmlr
Copy link
Member

krlmlr commented Jan 6, 2022

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.

@schroedad
Copy link

schroedad commented Oct 27, 2023 •
edited
Loading

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.

@ipimpat
Copy link

ipimpat commented Nov 8, 2023

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.

@krlmlr
Copy link
Member

krlmlr commented Nov 8, 2023

Thanks. Curious to know why mysql = FALSE is important. What fails if you omit it?

@schroedad
Copy link

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

@schroedad
Copy link

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

@krlmlr
Copy link
Member

krlmlr commented Nov 8, 2023

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?

@ipimpat
Copy link

ipimpat commented Feb 19, 2024

What else do we need besides the ssl.* arguments to dbConnect() 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.

@d-hansen
Copy link
Contributor

d-hansen commented Mar 28, 2024 •
edited
Loading

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());
   }

@krlmlr krlmlr mentioned this issue Apr 1, 2024
Merged
@krlmlr
Copy link
Member

krlmlr commented Apr 1, 2024

Fixed in #322.

@krlmlr krlmlr closed this as completed Apr 1, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
docs help wanted
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@ipimpat @krlmlr @schroedad @d-hansen @akkce