[pgpool-hackers: 2331] SCRAM auth in PostgreSQL 10

Tatsuo Ishii ishii at sraoss.co.jp
Sun May 14 08:33:12 JST 2017


Now that PostgreSQL 10 allows to choose SCRAM authentication instead
of MD5, I a little bit looked into it and found it appears that the
password data stored in pg_authid is different among PostgreSQL
backends. I have created PostgreSQL clusters and did followings.

# set password_encryption = 'scram-sha-256';
# create user foo with encrypted password 'foo';
test=# select * from pg_authid where rolname = 'foo';
-[ RECORD 1 ]--+------------------------------------------------------------------------------------------------------------------------------
rolname        | foo
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcanlogin    | t
rolreplication | f
rolbypassrls   | f
rolconnlimit   | -1
rolpassword    | SCRAM-SHA-256$4096:B1uIBdyuZpMka6BQ$SPHKcjZLx6NbhEweoT6abtWSQvi0fIf0143vrqi2ft4=:pF58aOT0y+zJGmd6QJZzY8Y7y5US04TWAe1X1DN4iF8=
rolvaliduntil  | 

I got different value from another backend.

rolpassword    | SCRAM-SHA-256$4096:XlJwnuVZFGjlGcSO$AYBltwFYfMGqRdJv6HHUwxxccrlf7uOQKL8gqwX2yPE=:4V649ZWHr4M0KlsiAwigwe/VoJboM85OHD+POJa4CdQ=

This suggests that we cannot adopt to SCRAM authentication in the same
as we are doing with md5 because it assumes that rolpassword has the
same value among backends as long as user name and password is
identical.

http://pgpool.net/mediawiki/index.php/FAQ#How_does_pgpool-II_handle_md5_authentication.3F

Any idea how to deal with SCRAM authentication in Pgpool-II?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


More information about the pgpool-hackers mailing list