Disconnected from Server (postgres on aws) - Unix domain socket

[Willem]

I created a Postgres Db in AWS RDS for testing (free tier).
In the Db Security group I added my IP as well as I whitelisted the servers listed here.
Everything works fine when I connect to the DB from PgAdmin as well as from RStudio via package DBI I can normally SELECT from tables and INSERT as well using these commands.

  server <- function(input, output, session) {
    env_vars <- config::get("dataconnection")
    conn_expr <- DBI::dbConnect(
      RPostgres::Postgres(), 
      host = env_vars$server,
      user    = env_vars$uid,
      password  = env_vars$pwd,
      port   = env_vars$port,
      dbname = env_vars$database
    )
    user_tbl <- DBI::dbGetQuery(
      conn_expr,
      "SELECT id, username, password, email FROM userbase"
    )

When deploying on shinyapps.io the application does not work! Apparently is not able to connect to the server.
This is the application log message screenshot:

2021-05-22T14:26:22.486101+00:00 shinyapps[4158774]: Starting R with process ID: '26'
2021-05-22T14:26:22.913065+00:00 shinyapps[4158774]: 
2021-05-22T14:26:22.913067+00:00 shinyapps[4158774]: Listening on http://127.0.0.1:40904
2021-05-22T14:26:30.262245+00:00 shinyapps[4158774]: Warning: Error in : could not connect to server: No such file or directory
2021-05-22T14:26:30.262247+00:00 shinyapps[4158774]: 	Is the server running locally and accepting
2021-05-22T14:26:30.262248+00:00 shinyapps[4158774]: 	connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
2021-05-22T14:26:30.262248+00:00 shinyapps[4158774]: 
2021-05-22T14:26:30.274316+00:00 shinyapps[4158774]:   65: <Anonymous>
2021-05-22T14:26:30.274385+00:00 shinyapps[4158774]: Error : could not connect to server: No such file or directory
2021-05-22T14:26:30.274386+00:00 shinyapps[4158774]: 	Is the server running locally and accepting
2021-05-22T14:26:30.274387+00:00 shinyapps[4158774]: 	connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
2021-05-22T14:26:30.274387+00:00 shinyapps[4158774]: 

app URL: https://bluetti.shinyapps.io/microtest/

I am not sure if it depends on how the .Renviron file is created. I have all the credentials stored there.
My app is running correctly when launched from RStudio, so if there is a good soul who already encounter this issue and is keen to provide some tips on what I am doing wrong, or what I should read, I am grateful since now.
Thanks

[andresrcs]

@Willem

env_vars <- config::get("dataconnection")

Print this values somewhere, in an output in the app or in the console so you can see them in the logs and make sure you get the proper values to be able to connect from shinyapps.io servers, most likely your problem lies here.

[Willem]

Hi @andresrcs thank you so much for your help.
I printed the environment variable and they are correct, in fact I can connect to the db from Rstudio.
The problem is I am not sure what happens with these environment variables when in shinyapps.io. Here is how I set up my config.yml file:

default:
  dataconnection:
    driver: !expr RPostgres::Postgres()
    server: !expr Sys.getenv("R_SHINY_TEST_HOST")
    uid: !expr Sys.getenv("R_SHINY_TEST_USER")
    pwd: !expr Sys.getenv("R_SHINY_TEST_PASS")
    port: 5432
    database: 'myDatabase'
    
shinyapps:
  dataconnection:
    driver: !expr RPostgres::Postgres()
    server: !expr Sys.getenv("R_SHINY_TEST_HOST")
    uid: !expr Sys.getenv("R_SHINY_TEST_USER")
    pwd: !expr Sys.getenv("R_SHINY_TEST_PASS")
    port: 5432
    database: 'myDatabase'

R_SHINY_TEST_HOST, R_SHINY_TEST_USER, R_SHINY_TEST_PASS are defined in the .Renviron file.
default and shinyapps are identically defined

[andresrcs]

I think you have misunderstood my advice, I meant you should print the values during run time, while the app is running on shinyapps.io's servers, that way you can be sure if the app is reading the startup files when the container is launched.

I might be wrong but I think shinyapps.io doesn't read startup files, at least I don't remember having seen a reference to it in the support articles, that is why I asked you to confirm empirically.

If you indeed get valid connection parameters, then I think you should double check your AWS security settings, because there will be no reason for the connection to fail on the shiny side of things.

[Willem]

Thanks @andresrcs and sorry for the misunderstanding but it leads to a solution!
The problem was that the .Renviron file was not imported in shinyapps.io because not in the app folder project (i thought it was a dangerous place there) and I was confused with what they meant with 'save it in your home directory' here
I created a copy of the .Renviron file in the app project and now I can see all the environment variables. And it works!

I only doubt if it is safe/secure to have the .Renviron file published on shinyapps.io?
Printing the environment variables on the app while running on shiny server helped me identify the issue and then solve it. Thank you so much for your help

[andresrcs]

@Willem

I only doubt if it is safe/secure to have the .Renviron file published on shinyapps.io?

It is as secure as you can get, app users don't get to see the code or the content of your files, most security measures on the server side are provided by AWS (the cloud provider for shinyapps.io) and you would have to trust RStudio people they don't sniff into your container (I don't see a reason the would want to).

[Willem]

@andresrcs thanks for the further explanation, I feel now relieved to know is in a safe place.