Support for Postgresql Connection

[bernorev]

Hi,

Is it possible to add support for a postgres connection? Would be especially useful for shinyapps deployment where sqlite won't work.

Thanks!

[tedmoorman]

This would be helpful.

[vikram-rawat]

I want to utilize a proper database as well. Please if we could use Postgres by creating a different schema for users and admin roles only that would be great

[tedmoorman]

Good suggestion @vikram-rawat . We could add an option to the necessary package functions to choose a database type. Then we could update the code to reflect a user's database.

[pvictor]

Hi, You can use your own function to check a user and use any DBI connection you want, minimal example:

library(shiny)
library(shinymanager)

my_custom_check_creds <- function(user, password) {
  con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
  DBI::dbWriteTable(con, "my_table", data.frame(
    user = c("test"),
    password = c("123"),
    stringsAsFactors = FALSE
  ))
  req <- DBI::sqlInterpolate(
    conn = con, 
    sql = "SELECT * FROM my_table WHERE user = ?user AND password = ?password", 
    user = user,
    password = password
  )
  res <- DBI::dbGetQuery(con, statement = 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
  )  
  output$auth_output <- renderPrint({
    reactiveValuesToList(res_auth)
  })

}

shinyApp(ui, server)

Victor

[tedmoorman]

When I do this, it says my username or password are incorrect. I verified that the user and password in the database are correct. There is also a row.names column. Not sure what the reason is.

[vikram-rawat]

In this scenario how can I utilize createDB function on a postgres database. Because that's an essential part of saving the credentials...

[rtrad89]

Hi, You can use your own function to check a user and use any DBI connection you want, minimal example:

library(shiny)
library(shinymanager)

my_custom_check_creds <- function(user, password) {
  con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
  DBI::dbWriteTable(con, "my_table", data.frame(
    user = c("test"),
    password = c("123"),
    stringsAsFactors = FALSE
  ))
  req <- DBI::sqlInterpolate(
    conn = con, 
    sql = "SELECT * FROM my_table WHERE user = ?user AND password = ?password", 
    user = user,
    password = password
  )
  res <- DBI::dbGetQuery(con, statement = 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
  )  
  output$auth_output <- renderPrint({
    reactiveValuesToList(res_auth)
  })

}

shinyApp(ui, server)

Victor

This example is quite handy, but I want to point out the following when working with PostgreSQL:

• Using ? to mark parameters in a query does not currently work with RPostgres, instead use $ (src)

• With PostgreSQL dbSendQuery() and dbBind() are supported and preferred to the manual interpolation seen in this example. For more details see this
• The example assumes the developer uses the same credential to connect to the database and to access the application for brevity. If this is not the case, and since it is not recommended to store DB connection details in the code, a yml configuration file can be use instead (see this), inter alia

@tedmoorman your problem may be related to the first point.

@pvictor a question please: how do you access the list user_info after login? or is it not possible?

[bthieurmel]

You can pass all arguments you want (.yml, config, ....) like this :

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", data.frame(
  user = c("test"),
  password = c("123"),
  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)

[wtimmerman-fitp]

For anyone who's stumbling onto this, I found that when using a postgres connection:

• You can't use encrypted passwords out-of-the-box using the related shinymanager functions
• Because you can't use secure_db() and others, the admin panel functionality of shinymanager is not available (reference)

[devops-qubits]

I have a working app with shinymanager and Postgres on AWS. Is there any way to enable admin mode with Postgres? Apparantly admin is only available using a SQLite database.

[bthieurmel]

SQL support in developpement, with admin & logs module. Coming soon