Hi,
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
password.
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.
Thanks,
Jonathan
P.S. I used this as a forcing function to get the meson build system set
up and thus far I quite like it!
