pgloader
pgloader copied to clipboard
dimitri
[FEATURE] Add support for postgres scram-sha-256 authentication
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!
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.
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.
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.
Hello,
scram-sha-256 support in pgloader latest version 3.6.3 ?
No, at least it's not working for me.
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.
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?
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)
