Error connecting to postgres 13 using RPostgres package in R. “authentication method 10 not supported”

[hammoire]

trafficstars

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.

I posted on stack over flow and it was suggested a raise an issue here.

Thanks

https://stackoverflow.com/questions/64614493/error-connecting-to-postgres-13-using-rpostgres-package-in-r-authentication-me/64614787#64614787

# 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

[jangorecki]

Your first sentence might be confusing because RPostgres is a different package than RPostgreSQL. https://github.com/r-dbi/RPostgres From code we see you mean RPostgreSQL, so you choose proper repository to report your issue.

Don't know exactly what is the root cause of the issue, but you can see if you can enable older authentication method on your SQL server till the issue is addressed here.

[hammoire]

Thanks and sorry about that, I'll go ahead and edit the post, thanks for the advice.

[ctoner]

I'm having the same issue. I can connect to remote servers, and even my own server via PSQL, but not my own server via RPostgreSQL.

I changed file authentication settings on pg_hba.config from trust to md5 to scram and back to trust without luck.

library(RPostgreSQL) library(knitr) options(scipen=999,digits=2) knitr::opts_chunk$set(echo = FALSE, cache = TRUE, max.print = -1) drv <- dbDriver("PostgreSQL") con <- dbConnect(drv, dbname=dbname, port=port, host=host, user=user, password=password)

This is the error

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

And my session info

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] knitr_1.30        RPostgreSQL_0.6-2 DBI_1.1.1.9000   

loaded via a namespace (and not attached):
 [1] rstudioapi_0.11  magrittr_1.5     tidyselect_1.1.0 munsell_0.5.0   
 [5] colorspace_1.4-1 R6_2.5.0         rlang_0.4.8      dplyr_1.0.2     
 [9] tools_4.0.2      grid_4.0.2       gtable_0.3.0     xfun_0.19       
[13] htmltools_0.5.0  ellipsis_0.3.1   yaml_2.2.1       digest_0.6.27   
[17] tibble_3.0.4     lifecycle_0.2.0  crayon_1.3.4     purrr_0.3.4     
[21] ggplot2_3.3.2    vctrs_0.3.4      glue_1.4.2       evaluate_0.14   
[25] rmarkdown_2.5    compiler_4.0.2   pillar_1.4.6     generics_0.1.0  
[29] scales_1.1.1     pkgconfig_2.0.3

[tomoakin]

I hope you have found some work arround. https://stackoverflow.com/questions/64210167/unable-to-connect-to-postgres-db-due-to-the-authentication-type-10-is-not-suppor

[tomoakin]

Alternatively you can compile the R library from source with the latest libpq with appropriate setup. I see that the accompanied libpq should be updated. But that takes some more time.

[JacobusS]

So I found the solution to be to install the RPostgres package. install.packages("RPostgres") I hate to recommend another package here. But if you are like me and need this for work then there is no other option until it gets fixed in RPostgreSQL.