[FEATURE] Add support for postgres scram-sha-256 authentication

[cryptomilk]

If you create a postgres database with scram-sha-256 authentication, pgloader is not able to authenticate!

initdb --data-checksums --pwprompt --auth=scram-sha-256 --encoding=UTF-8 --locale=C.UTF-8 .

pgloader mysql://root@localhost/mydb postgresql:///mydb

pgloader segfaults in this case:

socket(AF_UNIX, SOCK_STREAM, 0)         = 5
connect(5, {sa_family=AF_UNIX, sun_path="/var/run/postgresql/.s.PGSQL.5432"}, 110) = 0                                                                                                       
getsockname(5, {sa_family=AF_UNIX}, [110->2]) = 0
getpeername(5, {sa_family=AF_UNIX, sun_path="/var/run/postgresql/.s.PGSQL.5432"}, [110->36]) = 0
fstat(5, {st_mode=S_IFSOCK|0777, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_ANONYMOUS|MAP_NORESERVE, -1, 0) = 0x7ffff7e4e000
write(5, "\0\0\0H\0\3\0\0user\0postgres\0database\0c"..., 72) = 72            
poll([{fd=5, events=POLLIN|POLLPRI}], 1, 0) = 0 (Timeout)
poll([{fd=5, events=POLLIN|POLLPRI}], 1, -1) = 1 ([{fd=5, revents=POLLIN}])
read(5, "R\0\0\0\27\0\0\0\nSCRAM-SHA-256\0\0", 4096) = 24                  
close(5)                                = 0                                                                                                                                                  
--- SIGSEGV {si_signo=SIGSEGV, si_code=SEGV_ACCERR, si_addr=0x10004a1210} ---                                                                                                                
mprotect(0x10004a0000, 32768, PROT_READ|PROT_WRITE|PROT_EXEC) = 0            
rt_sigreturn({mask=[]})                 = 2                                                                                                                                                  
--- SIGSEGV {si_signo=SIGSEGV, si_code=SEGV_ACCERR, si_addr=0x20301128} ---                                                                                                                  
mprotect(0x20301000, 4096, PROT_READ|PROT_WRITE|PROT_EXEC) = 0
rt_sigreturn({mask=[]})                 = 540020995                               
write(2, "KABOOM!\n", 8KABOOM!

[mecampbellsoup]

Yea it looks like at some point between PG 13.5 and 14.1, the default pg_hba.conf password auth strategy changed from md5 to scram-sha-256. For a workaround you can migrate your DB to PG 13 via pgloader and then after completing that migration upgrade from PG 13 to 14.

[rseward]

Are there plans to support scram-sha-256 anytime soon. Our data load process integrates pgloader and we recently migrated to PG14. However we are having difficulty connecting to the database configured with that password auth strategy.

[diop78]

Hello,

scram-sha-256 support in pgloader latest version 3.6.3 ?

[marinmo]

Another workaround to this is to set password_encryption in postgresql.conf to md5, changing the relevant user's password (this encrypts with md5 - if you skip this the password will still be encrypted with scram-sha-256) and run pgloader. After the migration finishes, you can set password_encryption back to scram-sha-256 if you will and change the user's password again to re-encrypt the password. A hassle for sure, but allows for migration of data.

[simowilso]

Hello,

scram-sha-256 support in pgloader latest version 3.6.3 ?

No, at least it's not working for me.

[simowilso]

Another workaround to this is to set password_encryption in postgresql.conf to md5, changing the relevant user's password (this encrypts with md5 - if you skip this the password will still be encrypted with scram-sha-256) and run pgloader. After the migration finishes, you can set password_encryption back to scram-sha-256 if you will and change the user's password again to re-encrypt the password. A hassle for sure, but allows for migration of data.

Not really an option for me - I use pgloader on a regular basis, not just for one off uploads.

[lvogt]

Disclaimer: I have no clue about LISP ;)

However, it seems to me that the "low level" postgres connection handling is done by the Postmodern library. Postmodern has added support for SHA256 for quite some time - maybe only a "dependency update" is necessary to make the newer authentication work?

[mdavranche]

According to this, looks like you're right, @lvogt

So, if 20200101 is the minimal cl-postmodern version required to support scram-sha-256, then:

• If you use Debian, use version >= 12 (Bookworm), that bundles version 20211113 (source) The previous version (11, Bullseye) bundles version 20180430 (source)
• If you use Ubuntu, use version >= 22.04 (Jammy), that bundles version 20211113 (source) The previous version (20.04, Focal) bundles version 20180430 (source)