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 SCRAM2. 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 insrc/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 tests4. 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, JonathanP.S. I used this as a forcing function to get the meson build system set up and thus far I quite like it!
diff --git a/src/backend/utils/adt/cryptohashfuncs.c
b/src/backend/utils/adt/cryptohashfuncs.c
index 03d84ea217..bdff4d795c 100644
--- a/src/backend/utils/adt/cryptohashfuncs.c
+++ b/src/backend/utils/adt/cryptohashfuncs.c
@@ -13,9 +13,12 @@
*/
#include "postgres.h"
+#include "common/base64.h"
#include "common/cryptohash.h"
#include "common/md5.h"
+#include "common/scram-common.h"
#include "common/sha2.h"
+#include "libpq/scram.h"
#include "utils/builtins.h"
@@ -26,6 +29,10 @@
/* MD5 produces a 16 byte (128 bit) hash; double it for hex */
#define MD5_HASH_LEN 32
+static char *
+scram_build_secret_sha256_internal(const char *password, char *salt_str_enc,
+ int iterations);
+
/*
* Create an MD5 hash of a text value and return it as hex string.
*/
@@ -166,3 +173,129 @@ sha512_bytea(PG_FUNCTION_ARGS)
PG_RETURN_BYTEA_P(result);
}
+
+/*
+ * Create a SCRAM secret from a password
+ */
+Datum
+scram_build_secret_sha256_from_password(PG_FUNCTION_ARGS)
+{
+ const char *password = PG_GETARG_CSTRING(0);
+ char *secret;
+
+ secret = scram_build_secret_sha256_internal(password, NULL, 0);
+
+ PG_RETURN_TEXT_P(cstring_to_text(secret));
+}
+
+/*
+ * Create a SCRAM secret from a password and salt.
+ * The salt should be passed in as a base64 encoded string
+ */
+Datum
+scram_build_secret_sha256_from_password_and_salt(PG_FUNCTION_ARGS)
+{
+ const char *password = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ char *salt_str_enc = text_to_cstring(PG_GETARG_TEXT_PP(1));
+ char *secret;
+ /*
+ * Generate the SCRAM secret, using the default number of iterations
for the
+ * hash.
+ */
+ secret = scram_build_secret_sha256_internal(password, salt_str_enc, 0);
+
+ Assert(secret != NULL);
+
+ /* convert to text and return it */
+ PG_RETURN_TEXT_P(cstring_to_text(secret));
+}
+
+/*
+ * Create a SCRAM secret from a password and salt.
+ * The salt should be passed in as a base64 encoded string
+ */
+Datum
+scram_build_secret_sha256_from_password_and_salt_and_iterations(PG_FUNCTION_ARGS)
+{
+ const char *password = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ char *salt_str_enc = text_to_cstring(PG_GETARG_TEXT_PP(1));
+ int iterations = PG_GETARG_INT32(2);
+ char *secret;
+ /*
+ * Generate the SCRAM secret, using the default number of iterations
for the
+ * hash.
+ */
+ secret = scram_build_secret_sha256_internal(password, salt_str_enc,
iterations);
+
+ Assert(secret != NULL);
+
+ /* convert to text and return it */
+ PG_RETURN_TEXT_P(cstring_to_text(secret));
+}
+
+/*
+ * Workhorse function to that creates SCRAM secrets from user provided info.
+ * Returns the SCRAM secret in "text" form.
+ *
+ * This function can take three parameters:
+ *
+ * - password: a plaintext password. This argument is required. If none of the
+ * other arguments is set, the function short circuits to use a
+ * SCRAM secret generation function that relies on defaults.
+ * - salt_str_enc: a base64 encoded salt. If this is not provided, a salt using
+ * the defaults is generated.
+ * - iterations: the number of iterations to hash the password. If set to 0
+ * or less, the default number of iterations is used.
+ */
+static char *
+scram_build_secret_sha256_internal(const char *password, char *salt_str_enc,
+ int iterations)
+{
+ char *salt_str;
+ char *salt_str_dec;
+ char *secret;
+ int salt_str_dec_len;
+ const char *errstr = NULL;
+
+ Assert(password != NULL);
+
+ if (salt_str_enc == NULL && iterations <= 0)
+ {
+ return pg_be_scram_build_secret(password);
+ }
+
+ Assert(salt_str_enc != NULL);
+
+ /*
+ * determine if this a valid base64 encoded string
+ * TODO: look into refactoring the SCRAM decode code in
libpq/auth-scram.c
+ */
+ salt_str_dec_len = pg_b64_dec_len(strlen(salt_str_enc));
+ salt_str_dec = palloc(salt_str_dec_len);
+ salt_str_dec_len = pg_b64_decode(salt_str_enc, strlen(salt_str_enc),
+ salt_str_dec,
salt_str_dec_len);
+ if (salt_str_dec_len < 0)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_EXCEPTION),
+ errmsg("invalid base64 encoded string"),
+ errhint("Use the \"encode\" function to
convert to valid base64 string.")));
+ }
+ salt_str = pnstrdup(salt_str_dec, salt_str_dec_len);
+
+ /* if iterations is <= 0, set to the default */
+ if (iterations <= 0)
+ iterations = SCRAM_DEFAULT_ITERATIONS;
+
+ /*
+ * As this is a backend function, the "errstr" will not be set.
+ * The current behavior is to elog an ERROR. We will at least assert
that we
+ * don't return a NULL secret.
+ */
+ secret = scram_build_secret(salt_str, strlen(salt_str), iterations,
password,
+ &errstr);
+
+ Assert(secret != NULL);
+
+ return secret;
+}
\ No newline at end of file
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 20f5aa56ea..9ad0492e6f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7531,6 +7531,17 @@
{ oid => '3422', descr => 'SHA-512 hash',
proname => 'sha512', proleakproof => 't', prorettype => 'bytea',
proargtypes => 'bytea', prosrc => 'sha512_bytea' },
+{ oid => '8555', descr => 'Build a SCRAM secret',
+ proname => 'scram_build_secret_sha256', proleakproof => 't', prorettype =>
'text',
+ proargtypes => 'text', prosrc => 'scram_build_secret_sha256_from_password' },
+{ oid => '8556', descr => 'Build a SCRAM secret',
+ proname => 'scram_build_secret_sha256', proleakproof => 't',
+ provolatile => 'i', prorettype => 'text',
+ proargtypes => 'text text', prosrc =>
'scram_build_secret_sha256_from_password_and_salt' },
+{ oid => '8557', descr => 'Build a SCRAM secret',
+ proname => 'scram_build_secret_sha256', proleakproof => 't',
+ provolatile => 'i', prorettype => 'text',
+ proargtypes => 'text text int4', prosrc =>
'scram_build_secret_sha256_from_password_and_salt_and_iterations' },
# crosstype operations for date vs. timestamp and timestamptz
{ oid => '2338',
diff --git a/src/test/regress/expected/opr_sanity.out
b/src/test/regress/expected/opr_sanity.out
index 330eb0f765..0411c3202c 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -841,6 +841,9 @@ xid8ge(xid8,xid8)
xid8eq(xid8,xid8)
xid8ne(xid8,xid8)
xid8cmp(xid8,xid8)
+scram_build_secret_sha256(text)
+scram_build_secret_sha256(text,text)
+scram_build_secret_sha256(text,text,integer)
-- restore normal output mode
\a\t
-- List of functions used by libpq's fe-lobj.c
diff --git a/src/test/regress/expected/scram.out
b/src/test/regress/expected/scram.out
new file mode 100644
index 0000000000..2f318b9620
--- /dev/null
+++ b/src/test/regress/expected/scram.out
@@ -0,0 +1,40 @@
+-- Test building SCRAM functions
+-- generated a SCRAM secret from a plaintext password
+SELECT regexp_replace(
+ scram_build_secret_sha256('secret password'),
+
'(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)',
+ '\1$\2:<salt>$<storedkey>:<serverkey>') AS scram_secret;
+ scram_secret
+---------------------------------------------------
+ SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
+(1 row)
+
+-- test building a SCRAM secret with a predefined salt with a valid base64
+-- encoded string
+SELECT scram_build_secret_sha256('secret password',
'MTIzNDU2Nzg5MGFiY2RlZg==');
+
scram_build_secret_sha256
+---------------------------------------------------------------------------------------------------------------------------------------
+
SCRAM-SHA-256$4096:MTIzNDU2Nzg5MGFiY2RlZg==$D5BmucT796UQKargx2k3fdqjDYR7cH/L0viKKhGo6kA=:M33+iHFOESP8C3DKLDb2k5QAhkNVWEbp/YUIFd2CxN4=
+(1 row)
+
+-- test building a SCRAM secret with a predefined salt that is not a valid
+-- base64 string
+-- fail
+SELECT scram_build_secret_sha256('secret password', 'abc');
+ERROR: invalid base64 encoded string
+HINT: Use the "encode" function to convert to valid base64 string.
+-- test building a SCRAM secret with a valid salt and a different set of
+-- iterations
+SELECT scram_build_secret_sha256('secret password',
'MTIzNDU2Nzg5MGFiY2RlZg==', 10000);
+
scram_build_secret_sha256
+----------------------------------------------------------------------------------------------------------------------------------------
+
SCRAM-SHA-256$10000:MTIzNDU2Nzg5MGFiY2RlZg==$9NkDu1TFpx3L30zMgHUqjRNSq3GRZRrdWU4TuGOnT3Q=:svuIH9L6HH8loyKWguT64XXoOLCrr4FkVViPd2JVR4M=
+(1 row)
+
+-- test what happens when the salt is a NULL value
+SELECT scram_build_secret_sha256('secret password', NULL::text, 10000);
+ scram_build_secret_sha256
+---------------------------
+
+(1 row)
+
diff --git a/src/test/regress/parallel_schedule
b/src/test/regress/parallel_schedule
index 9a139f1e24..a02c9c0322 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -33,7 +33,7 @@ test: strings md5 numerology point lseg line box path polygon
circle date time t
# geometry depends on point, lseg, line, box, path, polygon, circle
# horology depends on date, time, timetz, timestamp, timestamptz, interval
# ----------
-test: geometry horology tstypes regex type_sanity opr_sanity misc_sanity
comments expressions unicode xid mvcc
+test: geometry horology tstypes regex type_sanity opr_sanity misc_sanity
comments expressions unicode xid mvcc scram
# ----------
# Load huge amounts of data
diff --git a/src/test/regress/sql/scram.sql b/src/test/regress/sql/scram.sql
new file mode 100644
index 0000000000..0d140239d8
--- /dev/null
+++ b/src/test/regress/sql/scram.sql
@@ -0,0 +1,23 @@
+-- Test building SCRAM functions
+
+-- generated a SCRAM secret from a plaintext password
+SELECT regexp_replace(
+ scram_build_secret_sha256('secret password'),
+
'(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)',
+ '\1$\2:<salt>$<storedkey>:<serverkey>') AS scram_secret;
+
+-- test building a SCRAM secret with a predefined salt with a valid base64
+-- encoded string
+SELECT scram_build_secret_sha256('secret password',
'MTIzNDU2Nzg5MGFiY2RlZg==');
+
+-- test building a SCRAM secret with a predefined salt that is not a valid
+-- base64 string
+-- fail
+SELECT scram_build_secret_sha256('secret password', 'abc');
+
+-- test building a SCRAM secret with a valid salt and a different set of
+-- iterations
+SELECT scram_build_secret_sha256('secret password',
'MTIzNDU2Nzg5MGFiY2RlZg==', 10000);
+
+-- test what happens when the salt is a NULL value
+SELECT scram_build_secret_sha256('secret password', NULL::text, 10000);
OpenPGP_signature
Description: OpenPGP digital signature
