[Jonathan S. Katz <jkatz@postgresql.org>]

Hi,

With some guidance from Stephen, I've discovered some scenarios where
one can store invalid SCRAM-SHA-256 passwords.

Scenario #1: Directly from CREATE/ALTER ROLE

for example on PostgreSQL 11:

CREATE ROLE test1 PASSWORD 'SCRAM-SHA-256$1234' LOGIN;

In the logs, one sees:

	2019-04-20 18:36:07.883 UTC [22251] postgres@postgres LOG:  invalid
SCRAM verifier for user "test1"
	2019-04-20 18:36:07.883 UTC [22251] postgres@postgres STATEMENT:
CREATE USER test1 PASSWORD 'SCRAM-SHA-256$1234' LOGIN;

pg_authid contains:

-[ RECORD 1 ]--+-------------------
rolname        | test1
rolcanlogin    | t
rolpassword    | SCRAM-SHA-256$1234

and when I try to login with the password "SCRAM-SHA-256$1234" e.g.

psql -U test1 postgres

psql: FATAL:  password authentication failed for user "test1"
FATAL:  password authentication failed for user "test1"

Scenario #2: On an upgrade from PG < 10 => PG >= 10

On a PostgreSQL 9.6.12, I created a user as the following:

CREATE ROLE test2 WITH UNENCRYPTED PASSWORD 'SCRAM-SHA-256$1234' LOGIN;

with pg_authid contents:

-[ RECORD 1 ]--+-------------------
rolname        | test2
rolcanlogin    | t
rolpassword    | SCRAM-SHA-256$1234

And was able to **successfully login.**

I installed PostgreSQL 11 and upgrading from 9.6.12 => 11.2

When I attempt to login as test2, I get the following error:

psql: FATAL:  password authentication failed for user "tester"
FATAL:  password authentication failed for user "tester"

While my hunch is that Scenario #2 is less likely to happen in the wild,
Scenario #1 is a real possibility. Examples, a non-libpq passed driver
wants to send a hashed password directly to a server and has a mistake
in the algorithm, or a program calls "ALTER ROLE" and modifies a
password with an invalid SCRAM-SHA-256 hash in it, etc.

Jonathan

Attachments:

  [application/pgp-signature] signature.asc (832B, ../016deb6b-1f0a-8e9f-1833-a8675b170aa9@postgresql.org/2-signature.asc)
  download