How to handle DB passwords in R connection strings?

Question

Though I don't know what the SO quorum would be, the question itself is simple: How do y'all handle passwords in db connection string when you connect to a database from R?

Tutorials often show an example like this.

con <- dbConnect(MySQL(), user="root", password="test", 
             dbname="research_db", host="localhost",
             client.flag=CLIENT_MULTI_STATEMENTS)

If the database is indeed your experimental localhost, this might be somewhat realistic. However if you use it with multiple users on a server you might not want to expose the db credentials like this. Particularly when combining RStudio Server with a SQL database you might want to do something encrypted. What is your experience?

Aby

@Duncan has a pretty good answer here #6102105 – Annabal

You could use the keyringr package to connect to the OS keyring/keychain. – Studer

@JoshGilfillan thanks, i'll check it as soon as I have time and give feedback. Is the package new? – Aby

@MattBannert Yes its new, first release in October 2016. – Studer

Answer 1

Here is a piece of example code that uses the tcltk package to prompt for a password while hiding the actual value:

library(tcltk)
tt <- tktoplevel()
pass <- tclVar()
tkpack(tklabel(tt,text='Password:'))
tkpack(tkentry(tt,textvariable=pass,show='*'))
tkpack(tkbutton(tt,text="Done",command=function()tkdestroy(tt)))
tkwait.window(tt)
tclvalue(pass)

In this case it just prints out the unhidden password at the end, but you could wrap this in a function to return that value, then use that as the value for the password argument. Or you could put this and the connect call (with the tclvalue line as the password) inside a call to local so that the variable containing the password disappears as soon as it is used.

Edit

For RStudio and RStudio server there is a function .rs.askForPassword. Use it like:

psswd <- .rs.askForPassword("Database Password:")
con <- dbConnect(MySQL(), user="root", password=psswd, 
             dbname="research_db", host="localhost",
             client.flag=CLIENT_MULTI_STATEMENTS)

Tuff

cool idea, something in the back of mind doubts though that tcltk works with RStudio server. Could you imagine a solution with readline that also hides the pw? – Aby

Oops, I missed the 'server' part of the question. This will work on a local machine, but I doubt over the web. There are ways to get an encrypted/hidden password via .cgi scripts, so possibly the server could send a .cgi to pop up a browser window to get the password, but that is beyond my experience to comment on. I doubt that readline will work, because it uses the interface to get the information, this needs to be programmed at the interface level, not the readline level. – Tuff

Anyway this was a helpful answer, +1. Making people enter their password is a good suggestion no matter how it's implemented. – Aby

there is a nice solution for the server part to if you use rstudio server you can use .rs.askForPassword. Entering password interactively is definitely a good suggestion. – Aby

tcltk package seems unavailable for download. What is the library name? – Quint

@sanjmeh, the tcltk package should have been installed with R, no need to download it. – Tuff

@MattBannert, Rstudio has a solution for this now, see the edit above. – Tuff

Is there a way to use .rs.askForPassword in RGui? This is for a use case where RStudio is not available to the user. @GregSnow @MattBannert – Robber

@SusieDerkins, The tcl/tk solution above will work with RGui. The .rs.askForPassword might work in RGui, but you need to have the RStudio package(s) installed and loaded, but it may still depend on the RStudio Gui, try the tcl/tk solution. – Tuff

Answer 2

So I like the solution of using the config file - that is a great answer. There are also some good comments on the password prompting answer that led me to this solution:

conn <- dbConnect(drv, "jdbc:sqlserver://host:port", 'username', password=.rs.askForPassword("Enter password:"))

Leaden

Answer 3

I have a different solution for the same problem, which doesn't require the user to type in their password every time they are connecting. I'm using the .my.cnf file functionality. Basically every user has a .my.cnf file in the root of their RStudio Server home directory which contains their password(s) to all MySQL databases, so in the R script I just refer to the database through the 'group' functionality.

R scripts:

library("RMySQL")
m <- dbDriver("MySQL")
# connect using .my.cnf
con <- dbConnect(m, group = "theDatabase")

.my.cnf file:

[client]
user = userName
host = mysql.server.com
password = MyPassword
[theDatabase]
database = hr
[theDatabase2]
user = opto
database = opto
password = pure-light
host = merced

Annals

2017: the dbDriver function is deprecated. Still looking for a solution. – Quint

Answer 4

Using the Keyring package you could implement your functionality in the following manner.

require(keyring)
require(RMySQL)
keyring::keyring_create("set_keyring_password_here") #Remember this password
keyring::key_set("dbname", keyring = "Your_set_keyring_password_here") 
keyring::key_set("host", keyring = "Your_set_keyring_password_here")
keyring::key_set("port", keyring = "Your_set_keyring_password_here")
keyring::key_set("user", keyring = "Your_set_keyring_password_here")
keyring::key_set("pass", keyring = "Your_set_keyring_password_here")
keyring::key_set("unix.sock", keyring = "Your_set_keyring_password_here")

m<-MySQL() #set the driver to mysql check your database driver and edit
summary(m)
    con<-dbConnect(m, dbname = keyring::key_get("dbname",
                      keyring = "Your_set_keyring_password_here"),
                   host=keyring::key_get("host",
                      keyring = "Your_set_keyring_password_here"),
                   port=as.numeric(keyring::key_get("port",
                      keyring = "Your_set_keyring_password_here")),
                   user=keyring::key_get("user",
                      keyring = "Your_set_keyring_password_here"),
                   pass=keyring::key_get("pass",
                      keyring = "Your_set_keyring_password_here"),
                   unix.sock=keyring::key_get("unix.sock",
                      keyring = "Your_set_keyring_password_here"),
                     )
keyring::keyring_lock("Your_set_keyring_password_here") #Lock keyring after using it.

Compressive