CODEWITHSUNDEEP
rr-markdown
S31
S31

Reputation: 934

Prompt for connection string in sql chunk within rmarkdown

Been digging around, but can't find the answer for what I'm looking for. I'm trying to find a way to prompt for the db connection username from the user since readlines isn't working when I run knitr.

What I've tried;

Upvotes: 1

Views: 500

Answers (1)

r2evans
r2evans

Reputation: 160687

As @therog1 said, rendering an rmarkdown document cannot have any user interaction: all outputs are sink'd (captured), and no inputs are opened/allowed (other than explicit file/network operations). As such, if you need to set connection credentials, they need to determined (and optionally verified) before rendering the document.

To get those credentials into the rmarkdown document for connections, I strongly suggest using parameterized documents, as already suggested. While it is possible to do without this, it is far better to do it the formal/proper way. (To me, it is analogous to function declarations: if you look at your Rmd file as a "function", you want to call that function with specific arguments instead of forcing it to assume presence of variables/data etc. In rmarkdown-speak, the "function arguments" are the document's params=.)

There are a few ways to go about this; the two most prominent to me are:

  • If in RStudio, as therog1 suggested, you can use params="ask", and RStudio should ask you for the parameters; this also works if the user selects "Knit with parameters" in the RStudio interface. If any users are not using RStudio, this method will not work.

    As an example, here is an Rmd that starts this:

    ---
title: hello world
params:
  dbuser: 
    label: "Username"
    value: ""
    input: text
  dbpass: 
    label: "Password"
    value: ""
    input: password
---

```{r echo = FALSE}
con <- DBI::dbConnect(...) # using params$dbuser and params$dbpass
```

    When the user selects "Knit with parameters", they'll see

    rstudio prompting for username and password

    (Both https://bookdown.org/yihui/rmarkdown/params-knit.html and https://rmarkdown.rstudio.com/lesson-6.html are good references for this.)

  • An alternative (that will in RStudio as well as other interactive R interfaces including the dreaded Rterm) is to use the keyring package (CRAN, website, github). One benefit is that once set up, the user never has to enter a username or password for this report, and the credentials are stored securely in the OS-specific encrypted vault.

    ---
title: hello world
params:
  dbservice: companydb
  dbuser: null
  dbpass: null
---

```{r echo = FALSE}
dbuser <-
  if (is.null(params$dbuser)) {
    keyring::key_list(params$dbservice)$username[1] # arbitrarily pick the first if multiple found
  } else params$dbuser
stopifnot(length(dbuser), !is.na(dbuser))
dbpass <-
  if (is.null(params$dbpass)) {
    keyring::key_get(params$dbservice, dbuser)
  } else params$dbuser
con <- DBI::dbConnect(...) # using dbuser and dbpass
```

    If the OS credentials vault is not immediately available, the OS may prompt the user for their OS password (not the db password) in order to verify that the vault can be accessed by the R code. On windows this is not necessary, I have not tested on macos/linux.

    The one-time setup for each user is:

    # install.packages("keyring") # if necessary
keyring::key_set("companydb", "theirusername")

    And the user will be prompted (via a GUI pop-up window) for secure (masked) password entry. This is stored in the OS keyring/credentials vault. The string "companydb" here is completely arbitrary, but must be a "known" thing that you agree on with (or force on) your users. To make it easy for you, it needs to be the same for all users. (Savvy users could override this manually with rmarkdown::render("path/to/doc.Rmd", params=list(dbservice="otherservicename")), though that hardly seems necessary.) The database never sees this, so it can and arguably should be readable and unambiguous (i.e., specific to a particular database/environment). (It should be unique so that it does not unintentionally overwrite users' other uses of the OS credentials vault.)

    If any user will be rendering the document in a non-interactive environment (such that the credentials vault is not guaranteed to be available), then this may not work as-is. However, those situations can usually be handled using environment variables or other tricks as described in https://db.rstudio.com/best-practices/managing-credentials/.

Upvotes: 2

Related Questions