Search code examples
rsecurityr-dbirpostgresql

How to secure database username and password with R DBI?

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?

Solution

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