RMariaDB unable to connect with .mylogin.cnf

[Osc2wall]

I'm developing a script in RStudio which connects to local MySQL Server using the R package RMariaDB (not RMySQL - for other reasons though the outcome is the same).

I can both connect via storing the password in the script like:

localuserpassword <- "password"

all_projectsDb <- dbConnect(RMariaDB::MariaDB(), user='user', password=localuserpassword, dbname='projects', host='localhost')

or by way of a .my.cnf using credentials:

[client]
[mygroup]
host=127.0.0.1
user=user
password=password
port=3306
database=projects

and R code as

settingsfile = '/Users/oscar_w/.my.cnf'

all_projectsDb <- dbConnect(RMariaDB::MariaDB(), default.file = settingsfile, group="mygroup", dbname = 'projects') 

The above work just fine but if I want to connect with .mylogin.cnf created in mysql_config_editor and looks like

[client]
[mygroup]
user = user
password = *****
host = 127.0.0.1
port = 3306

with the R script code like

# define location of config file
settingsfile = '/Users/oscar_w/.mylogin.cnf'

all_projectsDb <- dbConnect(RMariaDB::MariaDB(), default.file = settingsfile, group="mygroup", dbname = 'projects', password = NULL, user = NULL)

I get the error

Error: Failed to connect: Access denied for user 'root'@'localhost' (using password: NO)

I have tried various combinations of arguments expressing null or otherwise. And have entered my password with mysql_config_editor with double quotes around it. In https://cran.r-project.org/web/packages/RMariaDB/RMariaDB.pdf it specifies the use of .mylogin.cnf but I cannot find a way to make it work. Does anyone know a solution to this or has the same issue? Thanks

[krlmlr]

Thanks.

Generally, passing NULL is identical to omitting arguments in our implementation of dbConnect().

I see two differences in the default.file you provide:

1. The location of the file
2. The order of settings and the omission of database in one of the configuration files

Can you confirm that this problem occurs independently of the location of the default.file? Is the problem sensitive to the order of settings or to the database setting?

[Osc2wall]

Hi krlmlr

I have moved the location of the .mylogin.cnf to other directories but the result is the same error. In terms of recording the settings. I can only go by what mysql_config_editor provides as below.

Copyright (c) 2012, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

MySQL Configuration Utility.

Description: Write a login path to the login file.
Usage: mysql_config_editor [program options] [set [command options]]
  -?, --help          Display this help and exit.
  -h, --host=name     Host name to be entered into the login file.
  -G, --login-path=name 
                      Name of the login path to use in the login file. (Default
                      : client)
  -p, --password      Prompt for password to be entered into the login file.
  -u, --user=name     User name to be entered into the login file.
  -S, --socket=name   Socket path to be entered into login file.
  -P, --port=name     Port number to be entered into login file.
  -w, --warn          Warn and ask for confirmation if set command attempts to
                      overwrite an existing login path (enabled by default).
                      (Defaults to on; use --skip-warn to disable.)

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
host                              (No default value)
login-path                        client
user                              (No default value)
socket                            (No default value)
port                              (No default value)
warn                              TRUE

These are the settings I can enter to create the .mylogin.cnf and there is not the option to specify a database name. As below, no matter what order you write your settings in terminal, mysql_config_editor sets them in the order it wishes. After -p is a prompt for password, so -p has to be last.

mysql_config_editor  set -h localhost -u user -G mygroup -P 3306 -p

The resulting .mylogin.cnf is then encrypted. Adding database=projects to the encrypted file does not fix the problem.

In terminal, running mysql --login-path=mygroup , it connects me to my database fine so I know the .mylogin.cnf is correct. It's more that R cannot read the encryption of a .mylogin.cnf compared to just a 'self made' option file such as .my.cnf

I'm slowly resigning the fact it might not be possible, but if there is a solution, I'm all ears.

[krlmlr]

I was not aware of the obfuscation (=really bad encryption) of .mylogin.cnf .

From https://mariadb.com/kb/en/configuring-mariadb-connectorc-with-option-files/#option-groups:

MySQL 5.6 Obfuscated Authentication Credential Option File

MySQL 5.6 and above support an obfuscated authentication credential option file called .mylogin.cnf that is created with mysql_config_editor.

MariaDB Connector/C does not support this. The passwords in MySQL's .mylogin.cnf are only obfuscated, rather than encrypted, so the feature does not really add much from a security perspective. It is more likely to give users a false sense of security, rather than to seriously protect them.

Can you use a .my.cnf that's properly protected? Or perhaps keyring::key_get() to retrieve the password at connect time from your system's keyring?

[nickodell]

@krlmlr

I was not aware of the obfuscation (=really bad encryption) of .mylogin.cnf .

Can you use a .my.cnf that's properly protected? Or perhaps keyring::key_get() to retrieve the password at connect time from your system's keyring?

1. My general opinion on this is that the obfuscation is better than nothing. It prevents someone from shoulder-surfing you while editing your mysql config, for example. It also works on many platforms without a lot of fuss.

2. The documentation for RMariaDB does say that it reads from .my.cnf and .mylogin.cnf by default. When I read that, I assumed that meant, "it reads .mylogin.cnf, AND it understands the format that mysql_config_editor writes the file in." At the very least, this is highly misleading.

3. As I understand it, (feel free to correct me) this package can link against either MariaDB connector or MySQL connector. When I install the package from github, it links against MySQL:

   > install_github("r-dbi/RMariaDB", ref="v1.0.9")
   [...]
   ** using staged installation
   Using mysql_config
   Found mysql_config/mariadb_config cflags and libs!
   Using PKG_CFLAGS=-I/usr/local/Cellar/mysql/8.0.21/include/mysql 
   Using PKG_LIBS=-L/usr/local/opt/openssl/lib -L/usr/local/Cellar/mysql/8.0.21/lib -lmysqlclient -lssl -lcrypto
   ** libs
   

   When I install from GitHub, it understands the .mylogin.cnf format.

   On the other hand, when I install from CRAN, I get a version which is linked against MariaDB (I think) so it doesn't understand the format, so I get an error like this:

   [ERROR] Found option without preceding group in config file /Users/nick/.mylogin.cnf at line 2.
   [ERROR] Fatal error in defaults handling. Program aborted!
   

   This seems like an incredibly confusing behavior.

I admit I don't know how this should be solved. It seems like upstream has left you up shit creek by not supporting this. But I do think the current situation is not very good.

[krlmlr]

I guess we need to document the behavior at the very least. Maybe add a section about configuration files and securing passwords to ?dbConnect ?

Secure passwords

• MySQL client library (but not MariaDB) supports .mylogin.cnf. Can't pass.mylogin.cnf as default.file in MariaDB

• Password obfuscation may be a bad idea but is better than nothing if the user is aware of the restrictions. See other options (keyring, ...) for securing passwords

• CRAN binaries link against MariaDB

• ... (what else?)

[nickodell]

@krlmlr By the way, I wrote a library for R which is capable of decrypting a .mylogin.cnf file. Users who are dead-set on using password obfuscation may find it helpful.

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.