[SOLVED] Error connecting to postgres 13 using RPostgreSQL package in R. "authentication method 10 not supported"

Error connecting to postgres 13 using RPostgreSQL package in R. "authentication method 10 not supported"

I am attempting to connect to a local postgres 13 server using the RPostgreSQL package in R studio. I am receiving the error below. I am able to connect to a remote postgres 9 server using the exact same code. The link below gave me some clues, but didn't resolve the issue. It suggests to update my psql client, but it's up to date. It seems to have something to do with the authentication method used by postgres, but I am not really making any progress. Any help would be appreciated. Thanks.

PostgreSQL authentication method 10 not supported

UPDATE: I have raised an issue on GitHub as suggested by Adrian Klaver below: https://github.com/tomoakin/RPostgreSQL/issues/112

# Restart R before running
library(RPostgreSQL)


# connection to the MIMIC database
mimic <- dbConnect(
  PostgreSQL(),
  dbname = "mimic",
  host = "localhost",
  port = 5432,
  user = "UN",
  password = "PW"
)

This is the error

Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (could not connect postgres@localhost:5432 on dbname "mimic": authentication method 10 not supported
)

My session info is……..

> sessionInfo()
R version 4.0.2 (2020-06-22)
Platform: x86_64-apple-darwin17.0 (64-bit)
Running under: macOS Catalina 10.15.7

Matrix products: default
BLAS:   /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
LAPACK: /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRlapack.dylib

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

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

other attached packages:
[1] dplyr_1.0.2       RPostgreSQL_0.6-2 DBI_1.1.0         stringr_1.4.0    

loaded via a namespace (and not attached):
 [1] dbplyr_1.4.4     crayon_1.3.4     assertthat_0.2.1 R6_2.4.1         lifecycle_0.2.0 
 [6] magrittr_1.5     pillar_1.4.4     stringi_1.4.6    rlang_0.4.8      rstudioapi_0.11 
[11] blob_1.2.1       generics_0.0.2   vctrs_0.3.4      ellipsis_0.3.1   tools_4.0.2     
[16] glue_1.4.1       purrr_0.3.4      compiler_4.0.2   pkgconfig_2.0.3  tidyselect_1.1.0
[21] tibble_3.0.1

Solution

I'm going to say the issue is this RPostgreSQL_0.6-2.

From most recent CHANGELOG:

2017-06-19 Tomoaki NISHIYAMA tomoakin@staff.kanazawa-u.ac.jp * new libpq from postgresql-9.6.3 and Makefiles generated on Sierra (macOS 10.12)

So it is using a version of libpq(9.6.3) that does not understand SCRAM authentication. You are going to need to file an issue here:

https://github.com/tomoakin/RPostgreSQL/issues

to get the project to update the driver.