Verify a password against a SCRAM-SHA-256 hash

[fjf2002]

Hello,

I once had postgrest running with "SQL user management" as described in https://postgrest.org/en/stable/auth.html#sql-user-management .

In the MD5 hashing days I even went one step further, not having a password hash in the described basic_auth.users table, but I used the MD5 password hash rolpassword from the pg_catalog.pg_authid table. I thought this is a nice idea, having the same password for plain database connections and the postgrest API.

Now MD5 is considered as too insecure, and SCRAM-SHA-256 is standard in the pg_catalog.pg_authid table. How can I acheive my goal with SCRAM?

This means in particular, how can I verify a plaintext password against a SCRAM-SHA-256 password hash stored in pg_catalog.pg_authid.rolpassword, using the pgcrypto extension?

[steve-chavez]

Hey @fjf2002,

I thought this is a nice idea, having the same password for plain database connections and the postgrest API.

Ah, so you are using the one role for each webuser approach right.

This means in particular, how can I verify a plaintext password against a SCRAM-SHA-256 password hash stored in pg_catalog.pg_authid.rolpassword, using the pgcrypto extension?

Yeah, there's no builtin way in pgcrypto for doing that.

I was pointed out to this python script on IRC. It might be possible to wrap that into a plpython function to do the verifying there.

[fjf2002]

@steve-chavez: Thank you very much for your investigation, even although this topic is not purely PostgREST-related. I managed to get it to work with pl/pgsql, using the pgcrypto extension and the PBKDF2 function from https://stackoverflow.com/questions/47162200/pbkdf2-function-in-postgresql .

My basic_auth.user_role function now looks like this (there are currently further minor deviations from your docs):

CREATE OR REPLACE FUNCTION basic_auth.user_role(username text, pass text)
 RETURNS name
 LANGUAGE plpgsql
AS $function$
begin
  return (
    select basic_auth."user".username from basic_auth."user"
    join pg_authid on rolname = basic_auth."user".username and rolpassword IS NOT NULL
    cross join lateral regexp_match(rolpassword, '^SCRAM-SHA-256\$(.*):(.*)\$(.*):(.*)$') as rm
    cross join lateral (SELECT rm[1]::integer AS iteration_count, decode(rm[2], 'base64') AS salt, decode(rm[3], 'base64') as stored_key, decode(rm[4], 'base64') as server_key, 32 AS digest_length) as stored_password_part
    cross join lateral (SELECT basic_auth.pbkdf2(salt, check_user_pass.pass, iteration_count, digest_length, 'sha256')) AS digest_key(digest_key)
    cross join lateral (SELECT ext_pgcrypto.digest(ext_pgcrypto.hmac('Client Key', digest_key, 'sha256'), 'sha256') as stored_key, ext_pgcrypto.hmac('Server Key', digest_key, 'sha256') as server_key) as check_password_part
    where basic_auth."user".username = check_user_pass.username
      and check_password_part.stored_key = stored_password_part.stored_key
      and check_password_part.server_key = stored_password_part.server_key
  );
end;
$function$;

If you are interested to add that into the docs, please let me know.

[steve-chavez]

If you are interested to add that into the docs, please let me know.

@fjf2002 That would be great! Please go ahead.

[fjf2002]

Gladly I will try that. Before that, some questions:

• Shall I edit/replace parts of the chapter "Storing users and passwords", or present a new chapter, as an alternative to the other chapter?
• If the latter shall happen: Should a chapter be added there or in the "how-to" section?
• The whole section depends on the idea of the email address being used in the authentication and the JWT. My whole issue is re-using the user's database password for postgrest authentication, which somehow would sound saner in conjunction with the sql user name (instead of the mail address) being used in the JWT - what do you think?

[steve-chavez]

Hmm, at first I was thinking we could have it as a tabbed snippet like we do for HTTP/CURL snippets on https://postgrest.org/en/stable/api.html.

But as you mention, since it differs from the "Storing users and passwords" I think it would be better as a new "how-to".

[steve-chavez]

Solved on https://github.com/PostgREST/postgrest-docs/pull/581