Query using DSN to connect to PostgreSQL from R is failing

[falcon00]

PostgreSQL version: 11

R Version: 3.5.2

OS: Windows 10

Driver: PostgreSQL ANSI(x64)

The following code worked just fine two weeks ago but suddenly stopped working. The only change is, I had to go into windows settings and change the list separator setting from ',' to '|' for a different project. I've since changed it back. There have been no other changes to my machine. I have a DSN called DVDRental. The point of using the DSN is so people don't put passwords in code. I've tried specifying the database and that doesn't work either. The DSN connects properly. It's like sqldf isn't using RPostgreSQL anymore.

library(sqldf)
library(RPostgreSQL)
library(Hmisc)
library(rpart)
library(rpart.plot)

options(sqldf.RPostgreSQL.DSN = "DVDRental")


films_all_numbers = sqldf("SELECT * FROM total_revs_by_film_numbers_only")

Error message: Error in postgresqlNewConnection(drv, ...) : RS-DBI driver: (could not connect postgres@localhost:5432 on dbname "test": FATAL: password authentication failed for user "postgres" )

Error message with db specification:Error in postgresqlNewConnection(drv, ...) : RS-DBI driver: (could not connect postgres@localhost:5432 on dbname "DVDRental": FATAL: password authentication failed for user "postgres" )

[cderv]

It seems user or password is not correct. Are you sure nothing have changed on your database?

We can't really reproduce your error so it is not easy for us to help you on a password database connection.

[falcon00]

Password hasn't changed. When I test the DSN connection, it works. Same password. As I understand it, when RPostgreSQL is loaded, sqldf automatically uses it for connection information. THE DSN points to the proper database. Why would sqldf try to use the test database if the DSN option were set to the proper DSN?

[cderv]

Thanks for the additional informations. Unfortunately, I don't really know how sqldf works.

Personally, I use odbc and advices according to https://db.rstudio.com. I work with databases using dplyr or DBI not sqldf.

Maybe someone will have experience.

[falcon00]

You know what? This was for a class where I wanted to tie knowledge of SQL with R but I think you're right. I'm going to scrap this whole lab and just teach kids the Tidyverse.