odbc cant handle SCRAM-SHA-256 password encryption

[andresrcs]

Issue Description and Expected Result

odbc::dbConnect(odbc::odbc(),
    driver = 'PostgreSQL ANSI',
    server = 'server.address'
    database = 'database_name',
    uid = 'user',
    pwd = 'password')

Is unable to connect with SCRAM-SHA-256 password encryption, whereas RPostgreSQL::PostgreSQL() can

Database

Postgresql 13

Reproducible Example

library(odbc)
library(DBI)
con <- odbc::dbConnect(odbc::odbc(),
                       driver ='PostgreSQL ANSI',
                       server = config::get('server'),
                       database = config::get('database'),
                       port = config::get('port'),
                       uid = Sys.getenv('MY_UID'),
                       pwd = Sys.getenv('MY_PWD'),
                       encoding = config::get('encoding'))

#> Error: nanodbc/nanodbc.cpp:1135: 00000: FATAL:  password authentication failed for user «user_name»

Session Info

```r > devtools::session_info() ─ Session info ──────────────────────────────────────────────────────────────────────────────────────────────── setting value version R version 4.3.1 (2023-06-16) os Debian GNU/Linux 11 (bullseye) system aarch64, linux-gnu ui RStudio language (EN) collate es_PE.UTF-8 ctype es_PE.UTF-8 tz America/Lima date 2023-10-01 rstudio 2023.12.0-daily+105 Ocean Storm (server) pandoc 3.1.3 @ /usr/bin/pandoc ─ Packages ──────────────────────────────────────────────────────────────────────────────────────────────────── package * version date (UTC) lib source bit 4.0.5 2022-11-15 [1] CRAN (R 4.3.0) bit64 4.0.5 2020-08-30 [1] CRAN (R 4.3.0) blob 1.2.4 2023-03-17 [1] CRAN (R 4.3.0) cachem 1.0.8 2023-05-01 [1] CRAN (R 4.3.0) callr 3.7.3 2022-11-02 [1] CRAN (R 4.3.0) cli 3.6.1 2023-03-23 [1] CRAN (R 4.3.0) config 0.3.2 2023-08-30 [1] CRAN (R 4.3.1) crayon 1.5.2 2022-09-29 [1] CRAN (R 4.3.0) DBI 1.1.3 2022-06-18 [1] CRAN (R 4.3.0) devtools 2.4.5 2022-10-11 [1] CRAN (R 4.3.1) digest 0.6.33 2023-07-07 [1] CRAN (R 4.3.0) ellipsis 0.3.2 2021-04-29 [1] CRAN (R 4.3.0) fastmap 1.1.1 2023-02-24 [1] CRAN (R 4.3.0) fs 1.6.3 2023-07-20 [1] CRAN (R 4.3.1) glue 1.6.2 2022-02-24 [1] CRAN (R 4.3.0) hms 1.1.3 2023-03-21 [1] CRAN (R 4.3.0) htmltools 0.5.6 2023-08-10 [1] CRAN (R 4.3.1) htmlwidgets 1.6.2 2023-03-17 [1] CRAN (R 4.3.0) httpuv 1.6.11 2023-05-11 [1] CRAN (R 4.3.0) later 1.3.1 2023-05-02 [1] CRAN (R 4.3.0) lifecycle 1.0.3 2022-10-07 [1] CRAN (R 4.3.0) magrittr 2.0.3 2022-03-30 [1] CRAN (R 4.3.0) memoise 2.0.1 2021-11-26 [1] CRAN (R 4.3.0) mime 0.12 2021-09-28 [1] CRAN (R 4.3.0) miniUI 0.1.1.1 2018-05-18 [1] CRAN (R 4.3.0) odbc 1.3.5 2023-06-29 [1] CRAN (R 4.3.0) pkgbuild 1.4.2 2023-06-26 [1] CRAN (R 4.3.0) pkgconfig 2.0.3 2019-09-22 [1] CRAN (R 4.3.0) pkgload 1.3.3 2023-09-22 [1] CRAN (R 4.3.1) prettyunits 1.2.0 2023-09-24 [1] CRAN (R 4.3.1) processx 3.8.2 2023-06-30 [1] CRAN (R 4.3.0) profvis 0.3.8 2023-05-02 [1] CRAN (R 4.3.1) promises 1.2.1 2023-08-10 [1] CRAN (R 4.3.1) ps 1.7.5 2023-04-18 [1] CRAN (R 4.3.0) purrr 1.0.2 2023-08-10 [1] CRAN (R 4.3.1) R6 2.5.1 2021-08-19 [1] CRAN (R 4.3.0) Rcpp 1.0.11 2023-07-06 [1] CRAN (R 4.3.0) remotes 2.4.2.1 2023-07-18 [1] CRAN (R 4.3.1) rlang 1.1.1 2023-04-28 [1] CRAN (R 4.3.0) rstudioapi 0.15.0 2023-07-07 [1] CRAN (R 4.3.0) sessioninfo 1.2.2 2021-12-06 [1] CRAN (R 4.3.1) shiny 1.7.5 2023-08-12 [1] CRAN (R 4.3.1) stringi 1.7.12 2023-01-11 [1] CRAN (R 4.3.0) stringr 1.5.0 2022-12-02 [1] CRAN (R 4.3.0) urlchecker 1.0.1 2021-11-30 [1] CRAN (R 4.3.1) usethis 2.2.2 2023-07-06 [1] CRAN (R 4.3.1) vctrs 0.6.3 2023-06-14 [1] CRAN (R 4.3.0) xtable 1.8-4 2019-04-21 [1] CRAN (R 4.3.0) yaml 2.3.7 2023-01-23 [1] CRAN (R 4.3.0) [1] /opt/R/release/lib/R/site-library [2] /opt/R/release/lib/R/library #> output ```

[detule]

Hi:

I just checked, and I am able to authenticate with my SCRAM-SHA256 hashed password stored in the DB just fine. I am using the Unicode OEM driver, on a Debian box. I checked the ANSI driver, and was able to connect with this one as well, without an issue.

Is it possible that this is not an issue with the password encryption, but something else?

> dbGetInfo(conn)
...
$dbms.name
[1] "PostgreSQL"
...
$db.version
[1] "14.0.5"
...
$drivername
[1] "psqlodbcw.so"
...
$driver.version
[1] "13.02.0000"

> dbGetQuery(conn, "SHOW password_encryption")
  password_encryption
1       scram-sha-256
>

I use a DSN to specify my connection attributes. They look something like this:

[postgres_db]
Description = PostgresUnicode
Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so
Servername = <server>
Port = <port>
Database = <db>
BoolsAsChar = 0

[andresrcs]

I'm trying to connect to an IPv6 server, without SCRAM-SHA256 password encoding it connects without any issues but fails with SCRAM-SHA256 enabled I get the error message I showed in the original post. As you suggested, I have tried setting a DSN but the result is the same.

[db_internet]
Description=SpeedTest
Driver=PostgreSQL ANSI
Servername=<server.address.com>
UserName=<user>
Password=<password>
Port=5432
Database=internet
ByteaAsLongVarBinary=1
UseDeclareFetch=1

This doesn't work (With the same error message as before)

con <- dbConnect(odbc::odbc(),
                 dsn = 'db_internet')

But this works

con <- odbc::dbConnect(RPostgreSQL::PostgreSQL(),
                       host = config::get('server'),
                       dbname = config::get('database'),
                       port = config::get('port'),
                       user = Sys.getenv('MY_UID'),
                       password = Sys.getenv('MY_PWD'),
                       list(sslmode="require")
)

dbGetInfo(con)
$host
[1] "<server.address.com>"

$port
[1] "5432"

$user
[1] "<user>"

$dbname
[1] "internet"

$serverVersion
[1] "13.0.11"

$protocolVersion
[1] 3

$backendPId
[1] 1005596

$rsId
list()

dbGetQuery(con, "SHOW password_encryption")
  password_encryption
1       scram-sha-256

[detule]

Hi / thanks:

What ODBC driver / version are you using?

I gather, adding:

SSLmode=require

To the dsn specification is ineffective?

[andresrcs]

I believe is the same as you do (the one that comes from the Ubuntu deb repositories)

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

I have configured the server to accept both SSL and non-SSL connections while I make sure all my devices can manage SSL encryption so it should make no difference. I can connect with RpostgreSQL without SSL as well.

[detule]

Out of ideas.

I turned on connection logging on the server to make sure the client is indeed authenticating using SCRAM-SHA256. This is what I see:

2023-10-18 21:40:21.042 UTC [1] LOG:  database system is ready to accept connections
2023-10-18 21:41:08.268 UTC [88] LOG:  connection received: host=**** port=*****
2023-10-18 21:41:08.285 UTC [88] LOG:  connection authenticated: identity="****" method=scram-sha-256 (/var/lib/postgresql/data/pg_hba.conf:100)
2023-10-18 21:41:08.286 UTC [88] LOG:  connection authorized: user=**** database=****
....

If you have the ability to turn on logging on the server side and inspect the logs we can perhaps get a better idea why the connection is rejected.

[andresrcs]

I found it works fine using a .pgpass file instead of defining the password argument (i.e. pwd = Sys.getenv('MY_PWD'),). For some reason, it doesn't pass the password to the connection string when SCRAM-SHA-256 is set

[simonpcouch]

As we've been unable to reproduce the issue and you've been able to find a workaround, I'm going to go ahead and close. Thanks for the issue!