I am working on an internal package that will act as a front end to different databases and return data in a structured tidy format. The databases are all accessed via DSN but still require the username and password for odbc::dbConnect() to work. The usernames have read-only privileges on the databases
The package will be hosted on a private CRAN mirror behind my corporations firewall. My question is what is the best way to store the credentials of the database in a secure way in the package.
To make things more interesting, this will also likely be used on a Connect server, which may make a few of the options given here a little more difficult.
I have read through the linked article several times and spent a lot of time thinking about how these options could potentially be applied for a private package, but can't seem to come up with any solutions, other than having to manually create keyrings on each users computer every time a new user wants to use the package. This seems pretty inefficient in my mind. I also am not sure how that would work with Connect since it uses packrat to bundle all of the packages used.
Does anyone have any suggestions?
Hi @tbradley,
There's a very similar page that I'd suggest to look at, it centers on content that is to be deployed: http://db.rstudio.com/best-practices/deployment/
More specifically to Connect, there's a new feature that will allow you to store Environment Variables per applications, and is encrypted at-rest. Using that feature to store DB credentials may be the best option at this time: http://db.rstudio.com/best-practices/deployment/#credentials-inside-environment-variables-in-rstudio-connect
I have read through that page too but it seems to focus mostly on deployment to shiny apps. And while the package is used in shiny apps, the first hurdle seems to be how to deploy the credentials in the package functions so that the functions can be used interactively.
Is the only way to do this to set environment variables for every person who wants to use the package outside of shiny?
Ideally I would like to set it up where as long as the person has the DSN setup, they will be able to use the function to query the database, but at the same time, won't be able to see the username and password if they render the function's code in the console. Does that make sense?
Yeah, each can setup their credentials inside an .Renviron file
1 Like
Ok thanks @edgararuiz. Do you know if there are plans to be able to set system-wide environment variables on connect? The same ones will need to be set for a large number of apps which could be tedious to maintain.
Also, if not, do these variables need to be set every time the app is redeployed?
1 Like
Not that I know of, but I can open a feature request with the engineering team
No, it's a one time thing, unless you need to change it at a later time
1 Like