How to make admin mode available for Shinymanager with SQL database

Question

I have a working Rshiny app with shinymanager using postgres database hosted on AWS. I wonder if there is any way to make the admin mode available in this app. According to shinymanager documentation, admin mode is available only with sqlite database which has certain limitations for apps hosted on shinyapps.io

secondly, it is possible to add cookie-based authentication with shinymanager so that the users don't have to retype credentials every time the page gets refreshed.

require(RPostgreSQL)
library(shiny)
library(shinymanager)
library(DBI)
library(glue)

dbname = "*****"
host = "localhost"
port = *****
user = "*****"
password = "******"

con <- dbConnect(dbDriver("PostgreSQL"), dbname = dbname , host = host, port = port ,
                 user = user, password = password )


DBI::dbWriteTable(con, "my_table", overwrite = TRUE,
                  data.frame(user = c("shiny", "admin"),
                             password = c("shiny", "admin"),
                             admin = c(FALSE, TRUE),
                             stringsAsFactors = FALSE))

# or a config .yml file or others arguments
my_custom_check_creds <- function(dbname, host, port, db_user, db_password) {
  
 # finally one function of user and password
  function(user, password) {
    
    con <- dbConnect(dbDriver("PostgreSQL"), dbname = dbname, 
                     host = host, port = port,
                     user = db_user, password = db_password)
    
    on.exit(dbDisconnect(con))
    
    req <- glue_sql("SELECT * FROM my_table WHERE \"user\" = ({user}) AND \"password\" = ({password})", 
             user = user, password = password, .con = con
    )
    
    req <- dbSendQuery(con, req)
    res <- dbFetch(req)
    if (nrow(res) > 0) {
      list(result = TRUE, user_info = list(user = user, something = 123))
    } else {
      list(result = FALSE)
    }
  }
}

ui <- fluidPage(
  tags$h2("My secure application"),
  verbatimTextOutput("auth_output")
)
ui <- secure_app(ui)


server <- function(input, output, session) {
  res_auth <- secure_server(
    check_credentials = my_custom_check_creds(
      dbname = "******",
      host = "*****",
      port = ****,
      db_user = "*****",
      db_password = "*******"
    )
  )  
  auth_output <- reactive({
    reactiveValuesToList(res_auth)
  })
  
  # access info
  observe({
    print(auth_output())
  })
}

shinyApp(ui, server)

The bounty attracted a [ChatGPT](https://meta.stackoverflow.com/questions/421831/temporary-policy-chatgpt-is-banned) plagiariser. — Peter Mortensen, Jul 23 '23 at 22:17

score 2 · Accepted Answer · answered Jul 24 '23 at 00:00

The current version of the shinymanager package supports the admin page with SQLite only. Making this functionality available in SQL-based databases require a significant amount of work to make this work.

I have updated the shinymanager package with new functions to add admin page capability while using SQL databases like PostgreSQL.

I have added a pull request to add additional updates to the main package. In the meantime, you can install the R-Package from here:

remotes::install_github("devops-qubits/shinymanager")

You would need to add your own DB creds in the conn.

library(RPostgres)
library(shiny)
library(shinymanager)
library(DBI)



conn <- DBI::dbConnect(RPostgres::Postgres(),
                       host   = "localhost",
                       dbname = "postgres_db",
                       user = "postgres_user",
                       password = "postgres_user",
                       port = 5432)

 

## Credentials data.frame
credentials <- data.frame(
   user = c("shiny", "admin"),
   password = c("shiny", "admin"),
   start = c(NA, NA),
   expire = c(NA, NA),
   admin = c(FALSE, TRUE),
   stringsAsFactors = FALSE)

 

## Write SQL tables
create_sql_tables(conn,
                  credentials_data = credentials,
                  passphrase = NULL
)

 

ui <- fluidPage(
  h2("My secure application"),
  verbatimTextOutput("auth_output")
)

 

ui <- secure_app(ui, enable_admin = TRUE)

 


server <- function(input, output, session) {

  res_auth <- secure_server(
    check_credentials = check_credentials(conn, passphrase = NULL, sql = TRUE)
  )

  output$auth_output <- renderPrint({ reactiveValuesToList(res_auth) })

}

 

shinyApp(ui, server)

score -1 · Answer 2 · edited Jul 29 '23 at 00:36

I think to try this

library(shiny)
library(shinymanager)

# Define credentials (replace these with your actual username and password)
credentials <- data.frame(
  user = c("admin", "user"),
  password = c("adminpass", "userpass"),
  stringsAsFactors = FALSE
)

# Define your Shiny app UI
ui <- fluidPage(
  # Wrap your app UI with secure_app()
  secure_app(
    # Wrap the parts of your app that require authentication with secure_ui()
    secure_ui(
      fluidPage(
        titlePanel("My Shiny App"),
        # Your app content goes here
        # ...
      )
    )
  )
)

# Define your Shiny app server
server <- function(input, output, session) {
  # Wrap your app server with secure_server()
  secure_server(
    function(input, output, session) {
      # Your app server logic goes here
      # ...
    },
    # Pass the credentials to secure_server()
    user_data = credentials
  )`enter code here`
}

# Run the Shiny app
shinyApp(ui, server)

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? — Jeremy Caney, Jul 29 '23 at 00:37

score -4 · Answer 3 · answered Jul 16 '23 at 09:32

To enable admin mode with Shiny Manager using a PostgreSQL database, you can create a separate table in your PostgreSQL database to store admin user credentials and implement the necessary logic to check for admin rights in the my_custom_check_creds function. Below are the steps to achieve this:

Create a new table in your PostgreSQL database to store admin user credentials. You can call this table "admin_table" or any other name you prefer.

# Create a new table "admin_table" in the PostgreSQL database
DBI::dbWriteTable(con, "admin_table", data.frame(
  user = c("admin"),     # Add the admin username(s)
  password = c("123"),   # Add the admin password(s)
  isAdmin = TRUE,        # Add a column to mark admin users
  stringsAsFactors = FALSE
))

Modify the my_custom_check_creds function to check for admin rights.

my_custom_check_creds <- function(dbname, host, port, db_user, db_password) {

  function(user, password) {

    con <- dbConnect(dbDriver("PostgreSQL"), dbname = dbname, 
                     host = host, port = port,
                     user = db_user, password = db_password)

    on.exit(dbDisconnect(con))

    # Check if the user is an admin
    admin_req <- glue_sql("SELECT * FROM admin_table WHERE \"user\" = ({user}) AND \"password\" = ({password})",
                          user = user, password = password, .con = con)

    admin_req <- dbSendQuery(con, admin_req)
    admin_res <- dbFetch(admin_req)
    isAdmin <- nrow(admin_res) > 0

    # Check if the user is a regular user
    req <- glue_sql("SELECT * FROM my_table WHERE \"user\" = ({user}) AND \"password\" = ({password})",
                    user = user, password = password, .con = con)

    req <- dbSendQuery(con, req)
    res <- dbFetch(req)

    if (nrow(res) > 0) {
      list(result = TRUE, user_info = list(user = user, isAdmin = isAdmin))
    } else {
      list(result = FALSE)
    }
  }
}

Now, in your server function, you can access the user information and check for admin rights. Based on the user's role (admin or regular user), you can show or hide certain elements in the Shiny app.

server <- function(input, output, session) {
  res_auth <- secure_server(
    check_credentials = my_custom_check_creds(
      dbname = "******",
      host = "*****",
      port = ****,
      db_user = "*****",
      db_password = "*******"
    )
  )

  auth_output <- reactive({
    reactiveValuesToList(res_auth)
  })

  # access info
  observe({
    user_info <- auth_output()$user_info
    if (!is.null(user_info)) {
      user <- user_info$user
      isAdmin <- user_info$isAdmin
      print(paste("User:", user, "isAdmin:", isAdmin))
      # Implement your logic based on isAdmin value (e.g., show/hide certain UI elements)
    }
  })
}

Regarding the second part of your question about adding cookie-based authentication to avoid re-typing credentials, you can use the shinymanager::shinymanager_auth function with the cookie = TRUE argument to enable cookie-based authentication. The shinymanager_auth function can be used before setting up your Shiny app's UI and server.

# Before setting up the UI and server
shinymanager::shinymanager_auth(
  appTitle = "My Secure Application",
  check_credentials = my_custom_check_creds(
    dbname = "******",
    host = "*****",
    port = ****,
    db_user = "*****",
    db_password = "*******"
  ),
  cookie = TRUE  # Enable cookie-based authentication
)

This will allow users to stay authenticated across page refreshes and sessions.

Please make sure to adapt the code according to your specific PostgreSQL setup and requirements.

It looks like ChatGPT generated solution which does not work for the above problem. For example, shinymanager::shinymanager_auth function does not exist. — M.Qasim, Jul 17 '23 at 06:01
It is a complex problem, and the above solution doesn't work. `write/read_db` functions would have to be updated to interact with `logs` and `pwd_mngt` tables in Postgres database in the following shinymanager files `R/utils.R`, `‎R/credentials-db.R`, `‎R/module-admin.R` — M.Qasim, Jul 17 '23 at 12:26

How to make admin mode available for Shinymanager with SQL database

3 Answers3

Linked