User functions for building SCRAM secrets

From: "Jonathan S(dot) Katz" <jkatz(at)postgresql(dot)org>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: User functions for building SCRAM secrets
Date: 2022-10-31 20:27:08
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


We currently do not provide any SQL functions for generating SCRAM
secrets, whereas we have this support for other passwords types
(plaintext and md5 via `md5(password || username)`). If a user wants to
build a SCRAM secret via SQL, they have to implement our SCRAM hashing
funcs on their own.

Having a set of SCRAM secret building functions would help in a few areas:

1. Ensuring we have a SQL-equivalent of CREATE/ALTER ROLE ... PASSWORD
where we can compute a pre-hashed password.

2. Keeping a history file of user-stored passwords or checking against a
common-password dictionary.

3. Allowing users to build SQL-functions that can precompute SCRAM
secrets on a local server before sending it to a remote server.

Attached is a (draft) patch that adds a function called
"scram_build_secret_sha256" that can take 3 arguments:

* password (text) - a plaintext password
* salt (text) - a base64 encoded salt
* iterations (int) - the number of iterations to hash the plaintext

There are three variations of the function:

1. password only -- this defers to the PG defaults for SCRAM
2. password + salt -- this is useful for the password history /
dictionary case to allow for a predictable way to check a password.
3. password + salt + iterations -- this allows the user to modify the
number of iterations to hash a password.

The design of the patch primarily delegates to the existing SCRAM secret
building code and provides a few wrapper functions around it that
evaluate user input.

There are a few open items on this patch, i.e.:

1. Location of the functions. I put them in
src/backend/utils/adt/cryptohashfuncs.c as I wasn't sure where it would
make sense to have them (and they could easily go into their own file).

2. I noticed a common set of base64 function calls that could possibly
be refactored into one; I left a TODO comment around that.

3. More tests

4. Docs -- if it seems like we're OK with including these functions,
I'll write these up.

Please let me know if you have any questions. I'll add a CF entry for this.



P.S. I used this as a forcing function to get the meson build system set
up and thus far I quite like it!

Attachment Content-Type Size
scram-funcs-v1.patch text/plain 10.5 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2022-10-31 20:27:21 Re: Schema variables - new implementation for Postgres 15
Previous Message Matthias van de Meent 2022-10-31 19:42:17 Re: [PATCHES] Post-special page storage TDE support