0

I am trying to connect to postgreSQL database using R but I am getting an error :

pg_connect(): Unable to connect to PostgreSQL server: SCRAM authentication requires libpq version 10 or above.

I have looked at few pages including this link I tried changing

password_encryption = md5

and restarted PostgreSQL application but it did not work and still giving me the same error.

Could anyone please help me in resolving in error.

I am using PostgreSQL version 15.3

Output of sessionInfo()

R version 4.2.3 (2023-03-15 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19045)

Matrix products: default

locale:
[1] LC_COLLATE=English_India.utf8  LC_CTYPE=English_India.utf8    
LC_MONETARY=English_India.utf8
[4] LC_NUMERIC=C                   LC_TIME=English_India.utf8    

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

other attached packages:
[1] RPostgreSQL_0.7-5 DBI_1.1.3        

loaded via a namespace (and not attached):
[1] compiler_4.2.3 tools_4.2.3
Michael_Brun
  • 51
  • 6
  • Please add the output from `sessionInfo()` (for OS and versions), as well as the version of `libpq` (OS package) is installed. – r2evans Jul 12 '23 at 14:02
  • Thank you for response. I have added output of sessionInfo(). How can I check version of libpq – Michael_Brun Jul 12 '23 at 14:10
  • 1
    You are using `RPostgreSQL` which is using [`libpq-9.3.2`](https://github.com/r-dbi/RPostgres/blob/58a052b20f046c95723c332a0bb06fdb9ed362c4/NEWS.md?plain=1#L196). Switch to `RPostgres` which is using [`libpq-13.2.0`](https://github.com/r-dbi/RPostgres/blob/58a052b20f046c95723c332a0bb06fdb9ed362c4/NEWS.md?plain=1#L196) and you should be good. (Since you're using `DBI`, the shift from RPostgreSQL to RPostgres should be transparent, once you update the call to `dbConnect`.) – r2evans Jul 12 '23 at 14:32
  • And perhaps put in a bug-report to RPostgreSQL suggesting that they upgrade ... I think libpq-9.3.2 was [released in early 2013](https://www.postgresql.org/docs/release/9.3.2/) :-( – r2evans Jul 12 '23 at 14:35
  • fyi, if your server was otherwise working fine with scram, I suggest sticking that that vice changing to md5; I believe that postgres' use of md5 is prone to security problems because of md5's weaknesses, but scram has been considered a good change since it was introduced (in 10, I think). – r2evans Jul 12 '23 at 14:47
  • @r2evans Thank you very much. It worked successfully. Thanks for the info, will follow. – Michael_Brun Jul 12 '23 at 14:51
  • Upgrade your PostgreSQL client software. – Laurenz Albe Jul 12 '23 at 14:55
  • For the record, that dupe-link is a bad resolution for this problem: on windows in R, the user has no control over the version of `libpq` being used, since it is packaged internally within the `RPostgreSQL` and `RPostgres` packages. While the underlying fix _is_ to use a newer version of `libpq`, that cannot be done from R or from the client packages, it can solely be done by choosing the package with the newer version of `libpq` (which is `RPostgres` in this case). If this were linux or macos where the user has control over the external packages then it would be appropriate. @LaurenzAlbe – r2evans Jul 12 '23 at 15:43
  • @r2evans If the R packages use an ancient PostgreSQL version, that's a bug that they have to fix, right? And nothing keeps you from building R from source. – Laurenz Albe Jul 12 '23 at 16:18
  • I don't argue that the premise is correct, but it has little application here. For instance, installing R from source has nothing to do with compiling a package for R from source. Further, for these two R packages, updating the internally-stored dependency of `libpq` is far from easy, often requiring more than just extracting a tarball in-place (for several reasons). (If that were simple then yes that would work as well, but ... no.) Again, I agree with the underlying need to update the libpq version, you are correct, but it doesn't fit well in the context for users of these R packages. – r2evans Jul 12 '23 at 16:22

0 Answers0