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?

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)