R Shiny breaks PostgreSQL authentication with .pgpass

Question

I have my password to database stored in pgpass.conf file. I am connecting to database from R with RPostgres, without specifying password so it is read from pgpass.conf, like this:

con <- dbConnect(RPostgres::Postgres(), 
                 dbname = "dbname",
                 user = "username",
                 host = "localhost",
                 port = "5432")

It usually works perfectly, however when I try to connect to database from shiny app it doesn't work. Connection definition is exactly the same as above and placed in server.R script. When I run Shiny app with default arguments I get an error:

FATAL:  password authentication failed for user "username"
password retrieved from file "C:\Users\...\AppData\Roaming/postgresql/pgpass.conf"

When password is explicitly given in connection definition:

con <- dbConnect(RPostgres::Postgres(), 
                 dbname = "dbname",
                 user = "username",
                 host = "localhost",
                 password = "mypass",
                 port = "5432")

everything works.

To make things stranger, when port for shiny is set to some value, for example: shiny::runApp(port = 4000), connection is established without specifying password, but ONLY for the first time - that means when app is closed and reopened in the same R session, the error occurs again.

I've tested package 'RPostgreSQL' - it doesn't work neither, only error message is different:

Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (could not connect postgres@localhost on dbname "dbname")

I use 32-bit R but I've tested it on 64-bit and it was the same. Shiny app was run both in browser (Chrome) and in Rstudio Viewer.

Here my session info:

R version 3.2.2 (2015-08-14)
Platform: i386-w64-mingw32/i386 (32-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

locale:
[1] LC_COLLATE=Polish_Poland.1250  LC_CTYPE=Polish_Poland.1250        LC_MONETARY=Polish_Poland.1250
[4] LC_NUMERIC=C                   LC_TIME=Polish_Poland.1250    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RPostgres_0.1  DBI_0.3.1.9008 shiny_0.12.2  

loaded via a namespace (and not attached):
[1] R6_2.1.1         htmltools_0.2.6  tools_3.2.2      rstudioapi_0.3.1     Rcpp_0.12.1.3    jsonlite_0.9.17  digest_0.6.8    
[8] xtable_1.7-4     httpuv_1.3.3     mime_0.4         RPostgreSQL_0.4

Jason French · Accepted Answer · 2015-12-01 17:52:24Z

3

There's likely something different about the environment in which the command is run between Shiny and your system R GUI. I get around this by storing my credentials in an Renviron file:

readRenviron("~/.Renviron")
con <- dbConnect(RPostgres::Postgres(), 
                 dbname = Sys.getenv('pg_db'),
                 user = Sys.getenv('api_user'),
                 ...)

The thing about that is you could maintain separate Renvirons for staging and production environments. This allows your script to take a commandArgs() to specify which DB credentials it should use:

#!/usr/bin/env Rscript
environ_path <- switch(commandArgs(),
                  'staging' = {"~/staging.Renviron"},
                  'production' = {"~/production/Renviron"})

readRenviron(environ_path)

Then from BASH:

Rscript analysis.R staging

answered Dec 1, 2015 at 17:52

Jason French

4043 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BartekCh Over a year ago

This is indeed possible and quite simple solution to my problem. Anyway, I'm still curious how it could be that standard pgpass file doesn't work properly in Shiny. I've tried to find something in source files for both Shiny (httpuv actually) and psql but I'm not fluent in C so I found nothing.

aalbagarcia · Accepted Answer · 2020-10-23 13:54:10Z

0

The Error is in Postgresql:

C:\Users\...\AppData\Roaming/postgresql/pgpass.conf

the file path contains "/" instead of "\"

edited Oct 23, 2020 at 13:54

aalbagarcia

1,0447 silver badges20 bronze badges

answered Oct 22, 2020 at 19:35

Michael Himbert

1

1 Comment

Brits Over a year ago

Modern Windows can generally use both \ and / interchangeably for file paths (see this question)

Collectives™ on Stack Overflow

R Shiny breaks PostgreSQL authentication with .pgpass

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related