How to secure database username and password with R DBI?

Question

When connecting to a database using the dbConnect function in the DBI package, what are the best practices for securing logon information such as database name, username and password?

Entering logon information as a character text such as

library(RPostgreSQL)
drv = dbDriver("PostgreSQL")
con = dbConnect(drv, dbname = "<DBNAME>", host = "<HOST>",
                port = 5432, user = "<USER>", password = "<PASSWORD>")

leaves the credentials open and in plain text. How can the logon information be protected when using it in an R script?

Since asking this question, I found RStudio has a [best practices page](https://db.rstudio.com/best-practices/managing-credentials/) for DB use. — ichbinallen, Sep 27 '18 at 18:29

score 4 · Accepted Answer · answered Sep 26 '18 at 03:14

The keyring package is a system-independent way to do the 6th solution in the page mentioned by JohnSG. It has the advantage over the other methods that the secret information is never stored in the clear on your disk.

The method is designed for storing passwords, but any string can be stored there. For example,

library(keyring)
key_set("DBPassword", "ichbinallen")

which will result in you being prompted for the password to store. Then your script would use

con = dbConnect(drv, dbname = "<DBNAME>", host = "<HOST>",
            port = 5432, user = "<USER>", 
            password = key_get("DBPassword", "ichbinallen"))

It is also possible to store other data besides single strings, but that's how I've used it.

It claims to use the Keychain on MacOS, the Credential Store on Windows, and the Secret Service API on Linux. I don't know how secure it actually is; for me it's good enough not to have it in the clear.

score 2 · Answer 2 · answered Sep 26 '18 at 02:13

If you don't want to install another package, there are a number of options listed here

My preference is to use .Renviron as explained in that post

# ~/.Renviron
id = "my login name"
pw = "my password"

# script.R

# id and pw are defined in the script by virtue of .Rprofile
call_service(id = Sys.getenv("id"), pw = Sys.getenv("pw"), ...)

How to secure database username and password with R DBI?

2 Answers2