User functions for building SCRAM secrets

diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 30a048f6b0..4aa76b81d9 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -594,6 +594,12 @@ LANGUAGE internal
 STRICT IMMUTABLE PARALLEL SAFE
 AS 'unicode_is_normalized';
 
+-- defaults for building a "scram-sha-256" secret
+CREATE OR REPLACE FUNCTION
+  scram_build_secret_sha256(text, bytea DEFAULT NULL, int DEFAULT NULL)
+RETURNS text
+LANGUAGE INTERNAL
+VOLATILE AS 'scram_build_secret_sha256';
 --
 -- The default permissions for functions mean that anyone can execute them.
 -- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 0de0bbb1b8..7ddb186f96 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -22,6 +22,7 @@ OBJS = \
 	arraysubs.o \
 	arrayutils.o \
 	ascii.o \
+	authfuncs.o \
 	bool.o \
 	cash.o \
 	char.o \
diff --git a/src/backend/utils/adt/authfuncs.c b/src/backend/utils/adt/authfuncs.c
new file mode 100644
index 0000000000..4b4bbd7b7f
--- /dev/null
+++ b/src/backend/utils/adt/authfuncs.c
@@ -0,0 +1,120 @@
+/*-------------------------------------------------------------------------
+ *
+ * authfuncs.c
+ *	  Functions that assist with authentication management
+ *
+ * Portions Copyright (c) 2022, PostgreSQL Global Development Group
+ *
+ *
+ * IDENTIFICATION
+ *	  src/backend/utils/adt/authfuncs.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "common/scram-common.h"
+#include "libpq/scram.h"
+#include "utils/builtins.h"
+
+static char *
+scram_build_secret_sha256_internal(const char *password, char *salt_str_enc,
+								int iterations);
+
+
+/*
+ * Build a SCRAM secret that can be used for SCRAM-SHA-256 authentication.
+ */
+Datum
+scram_build_secret_sha256(PG_FUNCTION_ARGS)
+{
+	const char	  *password;
+  char 		*salt_str = NULL;
+	int		iterations = 0;
+	char	   *secret;
+
+	if (PG_ARGISNULL(0))
+	{
+		ereport(ERROR,
+			(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+			 errmsg("password must not be null")));
+  }
+
+  password = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+	if (!PG_ARGISNULL(1))
+		salt_str = text_to_cstring((text *) PG_GETARG_BYTEA_PP(1));
+
+	if (!PG_ARGISNULL(2))
+		iterations = PG_GETARG_INT32(2);
+
+	secret = scram_build_secret_sha256_internal(password, salt_str, iterations);
+
+	Assert(secret != NULL);
+
+	/*
+   * convert the SCRAM secret to text which matches the type for
+   * pg_authid.rolpassword
+   */
+	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,
+								int iterations)
+{
+	char		salt_default[SCRAM_DEFAULT_SALT_LEN];
+	char	   *secret;
+	const char *errstr = NULL;
+
+	Assert(password != NULL);
+
+	if (salt_str == NULL && iterations <= 0)
+	{
+		return pg_be_scram_build_secret(password);
+	}
+
+  /*
+   * If the salt is still null, generate a random salt based on the defaults.
+   * Otherwise, work to base64 decode the salt.
+   */
+	if (salt_str == NULL)
+	{
+		if (!pg_strong_random(salt_default, SCRAM_DEFAULT_SALT_LEN))
+			ereport(ERROR,
+					(errcode(ERRCODE_INTERNAL_ERROR),
+					 errmsg("could not generate random salt")));  
+		salt_str = pnstrdup(salt_default, SCRAM_DEFAULT_SALT_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;
+}
diff --git a/src/backend/utils/adt/meson.build b/src/backend/utils/adt/meson.build
index ed9ceadfef..910f141e83 100644
--- a/src/backend/utils/adt/meson.build
+++ b/src/backend/utils/adt/meson.build
@@ -9,6 +9,7 @@ backend_sources += files(
   'arraysubs.c',
   'arrayutils.c',
   'ascii.c',
+  'authfuncs.c',
   'bool.c',
   'cash.c',
   'char.c',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 20f5aa56ea..04b662c348 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7531,6 +7531,10 @@
 { oid => '3422', descr => 'SHA-512 hash',
   proname => 'sha512', proleakproof => 't', prorettype => 'bytea',
   proargtypes => 'bytea', prosrc => 'sha512_bytea' },
+{ oid => '8557', descr => 'Build a SCRAM secret',
+  proname => 'scram_build_secret_sha256', prorettype => 'text',
+  proisstrict => 'f', proargtypes => 'text bytea int4',
+  prosrc => 'scram_build_secret_sha256' },
 
 # crosstype operations for date vs. timestamp and timestamptz
 { oid => '2338',
diff --git a/src/test/regress/expected/scram.out b/src/test/regress/expected/scram.out
new file mode 100644
index 0000000000..4783310ca9
--- /dev/null
+++ b/src/test/regress/expected/scram.out
@@ -0,0 +1,81 @@
+-- Test building SCRAM functions
+-- test all nulls
+-- fail
+SELECT scram_build_secret_sha256(NULL);
+ERROR:  password must not be null
+SELECT scram_build_secret_sha256(NULL, NULL);
+ERROR:  password must not be null
+SELECT scram_build_secret_sha256(NULL, NULL, NULL);
+ERROR:  password must not be null
+-- 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',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'));
+                                                       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',
+  decode('abc', 'base64'));
+ERROR:  invalid base64 end sequence
+HINT:  Input data is missing padding, is truncated, or is otherwise corrupted.
+-- test building a SCRAM secret with a salt that looks like a string but is
+-- cast to a bytea
+SELECT scram_build_secret_sha256('secret password', 'abc');
+                                             scram_build_secret_sha256                                             
+-------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$4096:YWJj$L27WlKwqjMDY5ZNsyaxGSMii2mhmoUB7xONbxjykmw4=:u1ofGUXUqTbMwfiH+ANWDCpwEjk3j1Xrocy3va/jaCU=
+(1 row)
+
+-- test building a SCRAM secret with a bytea salt using the hex format
+SELECT scram_build_secret_sha256('secret password', '\xabba5432');
+                                               scram_build_secret_sha256                                               
+-----------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$4096:q7pUMg==$05Nb9QHwHkMA0CRcYaEfwtgZ+3kStIefz8fLMjTEtio=:P126h1ycyP938E69yxktEfhoAILbiwL/UMsMk3Efb6o=
+(1 row)
+
+-- test building a SCRAM secret with a valid salt and a different set of
+-- iterations
+SELECT scram_build_secret_sha256('secret password',
+  decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), 10000);
+                                                       scram_build_secret_sha256                                                        
+----------------------------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$10000:MTIzNDU2Nzg5MGFiY2RlZg==$9NkDu1TFpx3L30zMgHUqjRNSq3GRZRrdWU4TuGOnT3Q=:svuIH9L6HH8loyKWguT64XXoOLCrr4FkVViPd2JVR4M=
+(1 row)
+
+-- test what happens when the salt is a NULL value.
+-- this should build a SCRAM secret using a random salt.
+SELECT regexp_replace(
+  scram_build_secret_sha256('secret password', NULL, 10000),
+    '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]{24})\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)',
+    '\1$\2:<salt>$<storedkey>:<serverkey>') AS scram_secret;
+                    scram_secret                    
+----------------------------------------------------
+ SCRAM-SHA-256$10000:<salt>$<storedkey>:<serverkey>
+(1 row)
+
+-- test what happens when iterations is a null value. this should set iterations
+-- to be the default, currently 4096.
+SELECT
+  scram_build_secret_sha256('secret password',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), NULL) ~
+  '^SCRAM-SHA-256\$4096' AS has_default_iterations;
+ has_default_iterations 
+------------------------
+ t
+(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..53f87530f8
--- /dev/null
+++ b/src/test/regress/sql/scram.sql
@@ -0,0 +1,50 @@
+-- Test building SCRAM functions
+
+-- test all nulls
+-- fail
+SELECT scram_build_secret_sha256(NULL);
+SELECT scram_build_secret_sha256(NULL, NULL);
+SELECT scram_build_secret_sha256(NULL, NULL, NULL);
+
+-- 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',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'));
+
+-- test building a SCRAM secret with a predefined salt that is not a valid
+-- base64 string
+-- fail
+SELECT scram_build_secret_sha256('secret password',
+  decode('abc', 'base64'));
+
+-- test building a SCRAM secret with a salt that looks like a string but is
+-- cast to a bytea
+SELECT scram_build_secret_sha256('secret password', 'abc');
+
+-- test building a SCRAM secret with a bytea salt using the hex format
+SELECT scram_build_secret_sha256('secret password', '\xabba5432');
+
+-- test building a SCRAM secret with a valid salt and a different set of
+-- iterations
+SELECT scram_build_secret_sha256('secret password',
+  decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), 10000);
+
+-- test what happens when the salt is a NULL value.
+-- this should build a SCRAM secret using a random salt.
+SELECT regexp_replace(
+  scram_build_secret_sha256('secret password', NULL, 10000),
+    '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]{24})\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)',
+    '\1$\2:<salt>$<storedkey>:<serverkey>') AS scram_secret;
+
+-- test what happens when iterations is a null value. this should set iterations
+-- to be the default, currently 4096.
+SELECT
+  scram_build_secret_sha256('secret password',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), NULL) ~
+  '^SCRAM-SHA-256\$4096' AS has_default_iterations;

From 2b04b1aca0415b726fdacc7c0cc4903ee864257c Mon Sep 17 00:00:00 2001
From: "Jonathan S. Katz" <jonathan.katz@excoventures.com>
Date: Mon, 31 Oct 2022 16:13:08 -0400
Subject: [PATCH] Add "scram_build_secret_sha_256" SQL function

This function lets users build SCRAM secrets from SQL
functions and provides the ability for the user to select
the password, salt, and number of iterations for the password
hashing algorithm.
---
 doc/src/sgml/func.sgml                   | 42 +++++++++++
 src/backend/catalog/system_functions.sql |  6 ++
 src/backend/libpq/auth-scram.c           | 29 +++++---
 src/backend/libpq/crypt.c                |  2 +-
 src/backend/utils/adt/Makefile           |  1 +
 src/backend/utils/adt/authfuncs.c        | 69 ++++++++++++++++++
 src/backend/utils/adt/meson.build        |  1 +
 src/include/catalog/pg_proc.dat          |  4 ++
 src/include/libpq/scram.h                |  3 +-
 src/test/regress/expected/scram.out      | 91 ++++++++++++++++++++++++
 src/test/regress/parallel_schedule       |  2 +-
 src/test/regress/sql/scram.sql           | 56 +++++++++++++++
 12 files changed, 295 insertions(+), 11 deletions(-)
 create mode 100644 src/backend/utils/adt/authfuncs.c
 create mode 100644 src/test/regress/expected/scram.out
 create mode 100644 src/test/regress/sql/scram.sql

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6e0425cb3d..f582da138f 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3485,6 +3485,48 @@ repeat('Pg', 4) <returnvalue>PgPgPgPg</returnvalue>
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>scram_build_secret_sha256</primary>
+        </indexterm>
+        <function>scram_build_secret_sha256</function> ( <parameter>password</parameter> <type>text</type>
+        [, <parameter>salt</parameter> <type>bytea</type>
+        [, <parameter>iterations</parameter> <type>integer</type> ] ])
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Using the value provided in <parameter>password</parameter>, builds a
+        SCRAM secret equilvaent to what is stored in
+        <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolpassword</structfield>
+        and used with <link linkend="sasl-scram-sha-256"><literal>scram-sha-256</literal></link>
+        authentication. If not provided or set to <literal>NULL</literal>,
+        <parameter>salt</parameter> is randomly generated and
+        <parameter>iterations</parameter> defaults to <literal>4096</literal>.
+       </para>
+       <para>
+        <literal>SELECT scram_build_secret_sha256('secret password', decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'));</literal>
+        <returnvalue></returnvalue>
+<programlisting>
+  SCRAM-SHA-256$4096:MTIzNDU2Nzg5MGFiY2RlZg==$D5BmucT796UQKargx2k3fdqjDYR7cH/L0viKKhGo6kA=:M33+iHFOESP8C3DKLDb2k5QAhkNVWEbp/YUIFd2CxN4=
+</programlisting>
+       </para>
+       <para>
+        <literal>SELECT scram_build_secret_sha256('secret password', '\xabba5432');</literal>
+        <returnvalue></returnvalue>
+<programlisting>
+  SCRAM-SHA-256$4096:q7pUMg==$05Nb9QHwHkMA0CRcYaEfwtgZ+3kStIefz8fLMjTEtio=:P126h1ycyP938E69yxktEfhoAILbiwL/UMsMk3Efb6o=
+</programlisting>
+       </para>
+       <para>
+        <literal>SELECT scram_build_secret_sha256('secret password', decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), 10000);</literal>
+        <returnvalue></returnvalue>
+<programlisting>
+  SCRAM-SHA-256$10000:MTIzNDU2Nzg5MGFiY2RlZg==$9NkDu1TFpx3L30zMgHUqjRNSq3GRZRrdWU4TuGOnT3Q=:svuIH9L6HH8loyKWguT64XXoOLCrr4FkVViPd2JVR4M=
+</programlisting>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 30a048f6b0..4aa76b81d9 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -594,6 +594,12 @@ LANGUAGE internal
 STRICT IMMUTABLE PARALLEL SAFE
 AS 'unicode_is_normalized';
 
+-- defaults for building a "scram-sha-256" secret
+CREATE OR REPLACE FUNCTION
+  scram_build_secret_sha256(text, bytea DEFAULT NULL, int DEFAULT NULL)
+RETURNS text
+LANGUAGE INTERNAL
+VOLATILE AS 'scram_build_secret_sha256';
 --
 -- The default permissions for functions mean that anyone can execute them.
 -- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/libpq/auth-scram.c b/src/backend/libpq/auth-scram.c
index ee7f52218a..8d778f4346 100644
--- a/src/backend/libpq/auth-scram.c
+++ b/src/backend/libpq/auth-scram.c
@@ -456,10 +456,15 @@ scram_exchange(void *opaq, const char *input, int inputlen,
 /*
  * Construct a SCRAM secret, for storing in pg_authid.rolpassword.
  *
+ * "salt_str" can be NULL. If it is, this function will generate a random salt.
+ *
+ * If "iterations" is 0 or less, this function will set it to the default value.
+ *
  * The result is palloc'd, so caller is responsible for freeing it.
  */
 char *
-pg_be_scram_build_secret(const char *password)
+pg_be_scram_build_secret(const char *password, char *salt_str, int salt_str_len,
+				   int iterations)
 {
 	char	   *prep_password;
 	pg_saslprep_rc rc;
@@ -476,14 +481,22 @@ pg_be_scram_build_secret(const char *password)
 	if (rc == SASLPREP_SUCCESS)
 		password = (const char *) prep_password;
 
-	/* Generate random salt */
-	if (!pg_strong_random(saltbuf, SCRAM_DEFAULT_SALT_LEN))
-		ereport(ERROR,
-				(errcode(ERRCODE_INTERNAL_ERROR),
-				 errmsg("could not generate random salt")));
+	/* If salt_str is NULL, generate random salt */
+	if (salt_str == NULL)
+	{
+		if (!pg_strong_random(saltbuf, SCRAM_DEFAULT_SALT_LEN))
+			ereport(ERROR,
+					(errcode(ERRCODE_INTERNAL_ERROR),
+					 errmsg("could not generate random salt")));
+
+		salt_str = saltbuf;
+		salt_str_len = SCRAM_DEFAULT_SALT_LEN;
+	}
+
+	if (iterations <= 0)
+		iterations = SCRAM_DEFAULT_ITERATIONS;
 
-	result = scram_build_secret(saltbuf, SCRAM_DEFAULT_SALT_LEN,
-								SCRAM_DEFAULT_ITERATIONS, password,
+	result = scram_build_secret(salt_str, salt_str_len, iterations, password,
 								&errstr);
 
 	if (prep_password)
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 1ff8b0507d..649c51f990 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -138,7 +138,7 @@ encrypt_password(PasswordType target_type, const char *role,
 			return encrypted_password;
 
 		case PASSWORD_TYPE_SCRAM_SHA_256:
-			return pg_be_scram_build_secret(password);
+			return pg_be_scram_build_secret(password, NULL, -1, 0);
 
 		case PASSWORD_TYPE_PLAINTEXT:
 			elog(ERROR, "cannot encrypt password with 'plaintext'");
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 0de0bbb1b8..7ddb186f96 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -22,6 +22,7 @@ OBJS = \
 	arraysubs.o \
 	arrayutils.o \
 	ascii.o \
+	authfuncs.o \
 	bool.o \
 	cash.o \
 	char.o \
diff --git a/src/backend/utils/adt/authfuncs.c b/src/backend/utils/adt/authfuncs.c
new file mode 100644
index 0000000000..a08489248e
--- /dev/null
+++ b/src/backend/utils/adt/authfuncs.c
@@ -0,0 +1,69 @@
+/*-------------------------------------------------------------------------
+ *
+ * authfuncs.c
+ *	  Functions that assist with authentication management
+ *
+ * Portions Copyright (c) 2022, PostgreSQL Global Development Group
+ *
+ *
+ * IDENTIFICATION
+ *	  src/backend/utils/adt/authfuncs.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "libpq/scram.h"
+#include "utils/builtins.h"
+
+/*
+ * Build a SCRAM secret that can be used for SCRAM-SHA-256 authentication.
+ *
+ * This function can take three arguments:
+ *
+ * - 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.
+ */
+Datum
+scram_build_secret_sha256(PG_FUNCTION_ARGS)
+{
+	const char	  *password;
+  char 		*salt_str = NULL;
+  int	     salt_str_len = -1;
+	int		iterations = 0;
+	char	   *secret;
+
+	if (PG_ARGISNULL(0))
+	{
+		ereport(ERROR,
+			(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+			 errmsg("password must not be null")));
+  }
+
+  password = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+	if (!PG_ARGISNULL(1))
+	{
+		salt_str = text_to_cstring((text *) PG_GETARG_BYTEA_PP(1));
+		salt_str_len = strlen(salt_str);
+	}
+
+	if (!PG_ARGISNULL(2))
+		iterations = PG_GETARG_INT32(2);
+
+	secret = pg_be_scram_build_secret(password, salt_str, salt_str_len,
+		iterations);
+
+	Assert(secret != NULL);
+
+	/*
+   * convert the SCRAM secret to text which matches the type for
+   * pg_authid.rolpassword
+   */
+	PG_RETURN_TEXT_P(cstring_to_text(secret));
+}
diff --git a/src/backend/utils/adt/meson.build b/src/backend/utils/adt/meson.build
index ed9ceadfef..910f141e83 100644
--- a/src/backend/utils/adt/meson.build
+++ b/src/backend/utils/adt/meson.build
@@ -9,6 +9,7 @@ backend_sources += files(
   'arraysubs.c',
   'arrayutils.c',
   'ascii.c',
+  'authfuncs.c',
   'bool.c',
   'cash.c',
   'char.c',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 20f5aa56ea..04b662c348 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7531,6 +7531,10 @@
 { oid => '3422', descr => 'SHA-512 hash',
   proname => 'sha512', proleakproof => 't', prorettype => 'bytea',
   proargtypes => 'bytea', prosrc => 'sha512_bytea' },
+{ oid => '8557', descr => 'Build a SCRAM secret',
+  proname => 'scram_build_secret_sha256', prorettype => 'text',
+  proisstrict => 'f', proargtypes => 'text bytea int4',
+  prosrc => 'scram_build_secret_sha256' },
 
 # crosstype operations for date vs. timestamp and timestamptz
 { oid => '2338',
diff --git a/src/include/libpq/scram.h b/src/include/libpq/scram.h
index c51e848c24..ba0e5f624f 100644
--- a/src/include/libpq/scram.h
+++ b/src/include/libpq/scram.h
@@ -21,7 +21,8 @@
 extern PGDLLIMPORT const pg_be_sasl_mech pg_be_scram_mech;
 
 /* Routines to handle and check SCRAM-SHA-256 secret */
-extern char *pg_be_scram_build_secret(const char *password);
+extern char *pg_be_scram_build_secret(const char *password, char *salt_str,
+										int salt_str_len, int iterations);
 extern bool parse_scram_secret(const char *secret, int *iterations, char **salt,
 							   uint8 *stored_key, uint8 *server_key);
 extern bool scram_verify_plain_password(const char *username,
diff --git a/src/test/regress/expected/scram.out b/src/test/regress/expected/scram.out
new file mode 100644
index 0000000000..6ff14d9979
--- /dev/null
+++ b/src/test/regress/expected/scram.out
@@ -0,0 +1,91 @@
+-- Test building SCRAM functions
+-- test all nulls
+-- fail
+SELECT scram_build_secret_sha256(NULL);
+ERROR:  password must not be null
+SELECT scram_build_secret_sha256(NULL, NULL);
+ERROR:  password must not be null
+SELECT scram_build_secret_sha256(NULL, NULL, NULL);
+ERROR:  password must not be null
+-- 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',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'));
+                                                       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',
+  decode('abc', 'base64'));
+ERROR:  invalid base64 end sequence
+HINT:  Input data is missing padding, is truncated, or is otherwise corrupted.
+-- test building a SCRAM secret with a salt that looks like a string but is
+-- cast to a bytea
+SELECT scram_build_secret_sha256('secret password', 'abc');
+                                             scram_build_secret_sha256                                             
+-------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$4096:YWJj$L27WlKwqjMDY5ZNsyaxGSMii2mhmoUB7xONbxjykmw4=:u1ofGUXUqTbMwfiH+ANWDCpwEjk3j1Xrocy3va/jaCU=
+(1 row)
+
+-- test building a SCRAM secret with a bytea salt using the hex format
+SELECT scram_build_secret_sha256('secret password', '\xabba5432');
+                                               scram_build_secret_sha256                                               
+-----------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$4096:q7pUMg==$05Nb9QHwHkMA0CRcYaEfwtgZ+3kStIefz8fLMjTEtio=:P126h1ycyP938E69yxktEfhoAILbiwL/UMsMk3Efb6o=
+(1 row)
+
+-- test building a SCRAM secret with a valid salt and a different set of
+-- iterations
+SELECT scram_build_secret_sha256('secret password',
+  decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), 10000);
+                                                       scram_build_secret_sha256                                                        
+----------------------------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$10000:MTIzNDU2Nzg5MGFiY2RlZg==$9NkDu1TFpx3L30zMgHUqjRNSq3GRZRrdWU4TuGOnT3Q=:svuIH9L6HH8loyKWguT64XXoOLCrr4FkVViPd2JVR4M=
+(1 row)
+
+-- test what happens when the salt is a NULL value.
+-- this should build a SCRAM secret using a random salt.
+SELECT regexp_replace(
+  scram_build_secret_sha256('secret password', NULL, 10000),
+    '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]{24})\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)',
+    '\1$\2:<salt>$<storedkey>:<serverkey>') AS scram_secret;
+                    scram_secret                    
+----------------------------------------------------
+ SCRAM-SHA-256$10000:<salt>$<storedkey>:<serverkey>
+(1 row)
+
+-- test what happens when iterations is a null value. this should set iterations
+-- to be the default, currently 4096.
+SELECT
+  scram_build_secret_sha256('secret password',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), NULL) ~
+  '^SCRAM-SHA-256\$4096' AS has_default_iterations;
+ has_default_iterations 
+------------------------
+ t
+(1 row)
+
+-- test SASLprep. This tests the case where a user supplies a non-ASCII space
+-- character.
+SELECT
+  scram_build_secret_sha256(U&'one\1680space', decode('h2y81+nUwWp5uIJc4PgyXA==', 'base64')) =
+  'SCRAM-SHA-256$4096:h2y81+nUwWp5uIJc4PgyXA==$EiywEpO6rM3z3DGehubeoRpp8Orq0XuDUbdT9fQWwz8=:Wh7fq4C+bageihh3vTrkCr7YrlcDTG+JhfcFAuHn/6E=';
+ ?column? 
+----------
+ t
+(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..fc89d782a5
--- /dev/null
+++ b/src/test/regress/sql/scram.sql
@@ -0,0 +1,56 @@
+-- Test building SCRAM functions
+
+-- test all nulls
+-- fail
+SELECT scram_build_secret_sha256(NULL);
+SELECT scram_build_secret_sha256(NULL, NULL);
+SELECT scram_build_secret_sha256(NULL, NULL, NULL);
+
+-- 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',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'));
+
+-- test building a SCRAM secret with a predefined salt that is not a valid
+-- base64 string
+-- fail
+SELECT scram_build_secret_sha256('secret password',
+  decode('abc', 'base64'));
+
+-- test building a SCRAM secret with a salt that looks like a string but is
+-- cast to a bytea
+SELECT scram_build_secret_sha256('secret password', 'abc');
+
+-- test building a SCRAM secret with a bytea salt using the hex format
+SELECT scram_build_secret_sha256('secret password', '\xabba5432');
+
+-- test building a SCRAM secret with a valid salt and a different set of
+-- iterations
+SELECT scram_build_secret_sha256('secret password',
+  decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), 10000);
+
+-- test what happens when the salt is a NULL value.
+-- this should build a SCRAM secret using a random salt.
+SELECT regexp_replace(
+  scram_build_secret_sha256('secret password', NULL, 10000),
+    '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]{24})\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)',
+    '\1$\2:<salt>$<storedkey>:<serverkey>') AS scram_secret;
+
+-- test what happens when iterations is a null value. this should set iterations
+-- to be the default, currently 4096.
+SELECT
+  scram_build_secret_sha256('secret password',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), NULL) ~
+  '^SCRAM-SHA-256\$4096' AS has_default_iterations;
+
+-- test SASLprep. This tests the case where a user supplies a non-ASCII space
+-- character.
+SELECT
+  scram_build_secret_sha256(U&'one\1680space', decode('h2y81+nUwWp5uIJc4PgyXA==', 'base64')) =
+  'SCRAM-SHA-256$4096:h2y81+nUwWp5uIJc4PgyXA==$EiywEpO6rM3z3DGehubeoRpp8Orq0XuDUbdT9fQWwz8=:Wh7fq4C+bageihh3vTrkCr7YrlcDTG+JhfcFAuHn/6E=';
-- 
2.32.1 (Apple Git-133)

From 756c93f83869b7f8cbb87a7e4ccd967cbd8e8553 Mon Sep 17 00:00:00 2001
From: "Jonathan S. Katz" <jonathan.katz@excoventures.com>
Date: Mon, 31 Oct 2022 16:13:08 -0400
Subject: [PATCH] Add "scram_build_secret" SQL function

This function lets users build SCRAM secrets from SQL
functions and provides the ability for the user to select
the password, salt, and number of iterations for the password
hashing algorithm. Currently this only supports the "sha256"
hash, but can be modified to support additional hashes in the
future.
---
 doc/src/sgml/func.sgml                   |  46 ++++++++++
 src/backend/catalog/system_functions.sql |   7 ++
 src/backend/libpq/auth-scram.c           |  31 +++++--
 src/backend/libpq/crypt.c                |   2 +-
 src/backend/utils/adt/Makefile           |   1 +
 src/backend/utils/adt/authfuncs.c        | 109 +++++++++++++++++++++++
 src/backend/utils/adt/meson.build        |   1 +
 src/include/catalog/pg_proc.dat          |   4 +
 src/include/libpq/scram.h                |   4 +-
 src/test/regress/expected/scram.out      |  99 ++++++++++++++++++++
 src/test/regress/parallel_schedule       |   2 +-
 src/test/regress/sql/scram.sql           |  62 +++++++++++++
 12 files changed, 356 insertions(+), 12 deletions(-)
 create mode 100644 src/backend/utils/adt/authfuncs.c
 create mode 100644 src/test/regress/expected/scram.out
 create mode 100644 src/test/regress/sql/scram.sql

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index b8dac9ef46..68f11e953a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3513,6 +3513,52 @@ repeat('Pg', 4) <returnvalue>PgPgPgPg</returnvalue>
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>scram_build_secret</primary>
+        </indexterm>
+        <function>scram_build_secret</function> ( <parameter>hash_type</parameter> <type>text</type>
+        , <parameter>password</parameter> <type>text</type>
+        [, <parameter>salt</parameter> <type>bytea</type>
+        [, <parameter>iterations</parameter> <type>integer</type> ] ])
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Using the values provided in <parameter>hash type</parameter> and
+        <parameter>password</parameter>, builds a SCRAM secret equilvaent to
+        what is stored in
+        <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolpassword</structfield>
+        and used with <link linkend="sasl-scram-sha-256"><literal>scram-sha-256</literal></link>
+        authentication. If not provided or set to <literal>NULL</literal>,
+        <parameter>salt</parameter> is randomly generated and
+        <parameter>iterations</parameter> defaults to <literal>4096</literal>.
+        Currently <parameter>hash type</parameter> only supports
+        <literal>sha256</literal>.
+       </para>
+       <para>
+        <literal>SELECT scram_build_secret('sha256', 'secret password', decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'));</literal>
+        <returnvalue></returnvalue>
+<programlisting>
+  SCRAM-SHA-256$4096:MTIzNDU2Nzg5MGFiY2RlZg==$D5BmucT796UQKargx2k3fdqjDYR7cH/L0viKKhGo6kA=:M33+iHFOESP8C3DKLDb2k5QAhkNVWEbp/YUIFd2CxN4=
+</programlisting>
+       </para>
+       <para>
+        <literal>SELECT scram_build_secret('sha256', 'secret password', '\xabba5432');</literal>
+        <returnvalue></returnvalue>
+<programlisting>
+  SCRAM-SHA-256$4096:q7pUMg==$05Nb9QHwHkMA0CRcYaEfwtgZ+3kStIefz8fLMjTEtio=:P126h1ycyP938E69yxktEfhoAILbiwL/UMsMk3Efb6o=
+</programlisting>
+       </para>
+       <para>
+        <literal>SELECT scram_build_secret('sha256', 'secret password', decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), 10000);</literal>
+        <returnvalue></returnvalue>
+<programlisting>
+  SCRAM-SHA-256$10000:MTIzNDU2Nzg5MGFiY2RlZg==$9NkDu1TFpx3L30zMgHUqjRNSq3GRZRrdWU4TuGOnT3Q=:svuIH9L6HH8loyKWguT64XXoOLCrr4FkVViPd2JVR4M=
+</programlisting>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 83ca893444..5a7444df84 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -627,6 +627,13 @@ CREATE OR REPLACE FUNCTION
  STABLE PARALLEL SAFE
  AS 'sql_localtimestamp';
 
+-- defaults for building a SCRAM secret
+CREATE OR REPLACE FUNCTION
+  scram_build_secret(text, text, bytea DEFAULT NULL, int DEFAULT NULL)
+RETURNS text
+LANGUAGE INTERNAL
+VOLATILE AS 'scram_build_secret_str';
+
 --
 -- The default permissions for functions mean that anyone can execute them.
 -- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/libpq/auth-scram.c b/src/backend/libpq/auth-scram.c
index 4441e0d774..44efffd044 100644
--- a/src/backend/libpq/auth-scram.c
+++ b/src/backend/libpq/auth-scram.c
@@ -468,10 +468,15 @@ scram_exchange(void *opaq, const char *input, int inputlen,
 /*
  * Construct a SCRAM secret, for storing in pg_authid.rolpassword.
  *
+ * "salt_str" can be NULL. If it is, this function will generate a random salt.
+ *
+ * If "iterations" is 0 or less, this function will set it to the default value.
+ *
  * The result is palloc'd, so caller is responsible for freeing it.
  */
 char *
-pg_be_scram_build_secret(const char *password)
+pg_be_scram_build_secret(const char *password, char *salt_str, int salt_str_len,
+						 int iterations, pg_cryptohash_type hash_type)
 {
 	char	   *prep_password;
 	pg_saslprep_rc rc;
@@ -488,15 +493,23 @@ pg_be_scram_build_secret(const char *password)
 	if (rc == SASLPREP_SUCCESS)
 		password = (const char *) prep_password;
 
-	/* Generate random salt */
-	if (!pg_strong_random(saltbuf, SCRAM_DEFAULT_SALT_LEN))
-		ereport(ERROR,
-				(errcode(ERRCODE_INTERNAL_ERROR),
-				 errmsg("could not generate random salt")));
+	/* If salt_str is NULL, generate random salt */
+	if (salt_str == NULL)
+	{
+		if (!pg_strong_random(saltbuf, SCRAM_DEFAULT_SALT_LEN))
+			ereport(ERROR,
+					(errcode(ERRCODE_INTERNAL_ERROR),
+					 errmsg("could not generate random salt")));
+		salt_str = saltbuf;
+		salt_str_len = SCRAM_DEFAULT_SALT_LEN;
+	}
+
+	if (iterations <= 0)
+		iterations = SCRAM_DEFAULT_ITERATIONS;
 
-	result = scram_build_secret(PG_SHA256, SCRAM_SHA_256_KEY_LEN,
-								saltbuf, SCRAM_DEFAULT_SALT_LEN,
-								SCRAM_DEFAULT_ITERATIONS, password,
+	result = scram_build_secret(hash_type, SCRAM_SHA_256_KEY_LEN,
+								salt_str, salt_str_len,
+								iterations, password,
 								&errstr);
 
 	if (prep_password)
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index ef496a0bea..edf94444b6 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -140,7 +140,7 @@ encrypt_password(PasswordType target_type, const char *role,
 			return encrypted_password;
 
 		case PASSWORD_TYPE_SCRAM_SHA_256:
-			return pg_be_scram_build_secret(password);
+			return pg_be_scram_build_secret(password, NULL, -1, 0, PG_SHA256);
 
 		case PASSWORD_TYPE_PLAINTEXT:
 			elog(ERROR, "cannot encrypt password with 'plaintext'");
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 0de0bbb1b8..7ddb186f96 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -22,6 +22,7 @@ OBJS = \
 	arraysubs.o \
 	arrayutils.o \
 	ascii.o \
+	authfuncs.o \
 	bool.o \
 	cash.o \
 	char.o \
diff --git a/src/backend/utils/adt/authfuncs.c b/src/backend/utils/adt/authfuncs.c
new file mode 100644
index 0000000000..43631abbeb
--- /dev/null
+++ b/src/backend/utils/adt/authfuncs.c
@@ -0,0 +1,109 @@
+/*-------------------------------------------------------------------------
+ *
+ * authfuncs.c
+ *	  Functions that assist with authentication management
+ *
+ * Portions Copyright (c) 2022, PostgreSQL Global Development Group
+ *
+ *
+ * IDENTIFICATION
+ *	  src/backend/utils/adt/authfuncs.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "libpq/scram.h"
+#include "utils/builtins.h"
+
+#define SCRAM_BUILD_SECRET_HASH_STR_LEN	6
+
+void		parse_cryptohash_type(const char *hash_type_str,
+								  pg_cryptohash_type *hash_type);
+
+/*
+ * Build a SCRAM secret that can be used for SCRAM-SHA-256 authentication.
+ *
+ * This function can take four arguments:
+ *
+ * - hash_type_str: the type of hash to use when building the SCRAM secret.
+ *              Currently only "sha256" is supported.
+ * - 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.
+ */
+Datum
+scram_build_secret_str(PG_FUNCTION_ARGS)
+{
+	const char *hash_type_str;
+	pg_cryptohash_type hash_type;
+	const char *password;
+	char	   *salt_str = NULL;
+	int			salt_str_len = -1;
+	int			iterations = 0;
+	char	   *secret;
+
+	if (PG_ARGISNULL(0))
+	{
+		ereport(ERROR,
+				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+				 errmsg("hash type must not be null")));
+	}
+
+	hash_type_str = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	parse_cryptohash_type(hash_type_str, &hash_type);
+
+	if (PG_ARGISNULL(1))
+	{
+		ereport(ERROR,
+				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+				 errmsg("password must not be null")));
+	}
+
+	password = text_to_cstring(PG_GETARG_TEXT_PP(1));
+
+	if (!PG_ARGISNULL(2))
+	{
+		salt_str = text_to_cstring((text *) PG_GETARG_BYTEA_PP(2));
+		salt_str_len = strlen(salt_str);
+	}
+
+	if (!PG_ARGISNULL(3))
+		iterations = PG_GETARG_INT32(3);
+
+	secret = pg_be_scram_build_secret(password, salt_str, salt_str_len,
+									  iterations, hash_type);
+
+	Assert(secret != NULL);
+
+	/*
+	 * convert the SCRAM secret to text which matches the type for
+	 * pg_authid.rolpassword
+	 */
+	PG_RETURN_TEXT_P(cstring_to_text(secret));
+}
+
+/*
+ * If "hash_type_str" is a valid cryptohash type that can be used for SCRAM
+ * authetnication, set the value to "hash_type". Otherwise, return an
+ * unsupported error.
+ */
+void
+parse_cryptohash_type(const char *hash_type_str, pg_cryptohash_type *hash_type)
+{
+	pg_cryptohash_type result_type;
+
+	if (pg_strncasecmp(hash_type_str, "sha256", SCRAM_BUILD_SECRET_HASH_STR_LEN) == 0)
+		result_type = PG_SHA256;
+	else
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("hash not supported: \"%s\"", hash_type_str),
+				 errmsg("supported hashes are \"sha256\"")));
+
+	*hash_type = result_type;
+}
diff --git a/src/backend/utils/adt/meson.build b/src/backend/utils/adt/meson.build
index 8515cd9365..52363a2f26 100644
--- a/src/backend/utils/adt/meson.build
+++ b/src/backend/utils/adt/meson.build
@@ -11,6 +11,7 @@ backend_sources += files(
   'arraysubs.c',
   'arrayutils.c',
   'ascii.c',
+  'authfuncs.c',
   'bool.c',
   'cash.c',
   'char.c',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 86eb8e8c58..b55eac911c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7583,6 +7583,10 @@
 { oid => '3422', descr => 'SHA-512 hash',
   proname => 'sha512', proleakproof => 't', prorettype => 'bytea',
   proargtypes => 'bytea', prosrc => 'sha512_bytea' },
+{ oid => '8557', descr => 'Build a SCRAM secret',
+  proname => 'scram_build_secret', prorettype => 'text',
+  proisstrict => 'f', proargtypes => 'text text bytea int4',
+  prosrc => 'scram_build_secret_str' },
 
 # crosstype operations for date vs. timestamp and timestamptz
 { oid => '2338',
diff --git a/src/include/libpq/scram.h b/src/include/libpq/scram.h
index b275e1e87e..400f551778 100644
--- a/src/include/libpq/scram.h
+++ b/src/include/libpq/scram.h
@@ -22,7 +22,9 @@
 extern PGDLLIMPORT const pg_be_sasl_mech pg_be_scram_mech;
 
 /* Routines to handle and check SCRAM-SHA-256 secret */
-extern char *pg_be_scram_build_secret(const char *password);
+extern char *pg_be_scram_build_secret(const char *password, char *salt_str,
+									  int salt_str_len, int iterations,
+									  pg_cryptohash_type hash_type);
 extern bool parse_scram_secret(const char *secret,
 							   int *iterations,
 							   pg_cryptohash_type *hash_type,
diff --git a/src/test/regress/expected/scram.out b/src/test/regress/expected/scram.out
new file mode 100644
index 0000000000..0f3c5467b1
--- /dev/null
+++ b/src/test/regress/expected/scram.out
@@ -0,0 +1,99 @@
+-- Test building SCRAM functions
+-- test all nulls
+-- fail
+SELECT scram_build_secret(NULL, NULL);
+ERROR:  hash type must not be null
+SELECT scram_build_secret('sha256', NULL);
+ERROR:  password must not be null
+SELECT scram_build_secret('sha256', NULL, NULL);
+ERROR:  password must not be null
+SELECT scram_build_secret('sha256', NULL, NULL, NULL);
+ERROR:  password must not be null
+-- test unsupported hashes
+-- fail
+SELECT scram_build_secret('sha384', 'password');
+ERROR:  supported hashes are "sha256"
+SELECT scram_build_secret('sha512', 'password');
+ERROR:  supported hashes are "sha256"
+-- 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',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'));
+                                                          scram_build_secret                                                           
+---------------------------------------------------------------------------------------------------------------------------------------
+ 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',
+  decode('abc', 'base64'));
+ERROR:  invalid base64 end sequence
+HINT:  Input data is missing padding, is truncated, or is otherwise corrupted.
+-- test building a SCRAM secret with a salt that looks like a string but is
+-- cast to a bytea
+SELECT scram_build_secret('sha256', 'secret password', 'abc');
+                                                scram_build_secret                                                 
+-------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$4096:YWJj$L27WlKwqjMDY5ZNsyaxGSMii2mhmoUB7xONbxjykmw4=:u1ofGUXUqTbMwfiH+ANWDCpwEjk3j1Xrocy3va/jaCU=
+(1 row)
+
+-- test building a SCRAM secret with a bytea salt using the hex format
+SELECT scram_build_secret('sha256', 'secret password', '\xabba5432');
+                                                  scram_build_secret                                                   
+-----------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$4096:q7pUMg==$05Nb9QHwHkMA0CRcYaEfwtgZ+3kStIefz8fLMjTEtio=:P126h1ycyP938E69yxktEfhoAILbiwL/UMsMk3Efb6o=
+(1 row)
+
+-- test building a SCRAM secret with a valid salt and a different set of
+-- iterations
+SELECT scram_build_secret('sha256', 'secret password',
+  decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), 10000);
+                                                           scram_build_secret                                                           
+----------------------------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$10000:MTIzNDU2Nzg5MGFiY2RlZg==$9NkDu1TFpx3L30zMgHUqjRNSq3GRZRrdWU4TuGOnT3Q=:svuIH9L6HH8loyKWguT64XXoOLCrr4FkVViPd2JVR4M=
+(1 row)
+
+-- test what happens when the salt is a NULL value.
+-- this should build a SCRAM secret using a random salt.
+SELECT regexp_replace(
+  scram_build_secret('sha256', 'secret password', NULL, 10000),
+    '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]{24})\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)',
+    '\1$\2:<salt>$<storedkey>:<serverkey>') AS scram_secret;
+                    scram_secret                    
+----------------------------------------------------
+ SCRAM-SHA-256$10000:<salt>$<storedkey>:<serverkey>
+(1 row)
+
+-- test what happens when iterations is a null value. this should set iterations
+-- to be the default, currently 4096.
+SELECT
+  scram_build_secret('sha256', 'secret password',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), NULL) ~
+  '^SCRAM-SHA-256\$4096' AS has_default_iterations;
+ has_default_iterations 
+------------------------
+ t
+(1 row)
+
+-- test SASLprep. This tests the case where a user supplies a non-ASCII space
+-- character.
+SELECT
+  scram_build_secret('sha256', U&'one\1680space', decode('h2y81+nUwWp5uIJc4PgyXA==', 'base64')) =
+  'SCRAM-SHA-256$4096:h2y81+nUwWp5uIJc4PgyXA==$EiywEpO6rM3z3DGehubeoRpp8Orq0XuDUbdT9fQWwz8=:Wh7fq4C+bageihh3vTrkCr7YrlcDTG+JhfcFAuHn/6E=';
+ ?column? 
+----------
+ t
+(1 row)
+
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index a930dfe48c..b526d610e2 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..0ca3291969
--- /dev/null
+++ b/src/test/regress/sql/scram.sql
@@ -0,0 +1,62 @@
+-- Test building SCRAM functions
+
+-- test all nulls
+-- fail
+SELECT scram_build_secret(NULL, NULL);
+SELECT scram_build_secret('sha256', NULL);
+SELECT scram_build_secret('sha256', NULL, NULL);
+SELECT scram_build_secret('sha256', NULL, NULL, NULL);
+
+-- test unsupported hashes
+-- fail
+SELECT scram_build_secret('sha384', 'password');
+SELECT scram_build_secret('sha512', 'password');
+
+-- 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',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'));
+
+-- test building a SCRAM secret with a predefined salt that is not a valid
+-- base64 string
+-- fail
+SELECT scram_build_secret('sha256', 'secret password',
+  decode('abc', 'base64'));
+
+-- test building a SCRAM secret with a salt that looks like a string but is
+-- cast to a bytea
+SELECT scram_build_secret('sha256', 'secret password', 'abc');
+
+-- test building a SCRAM secret with a bytea salt using the hex format
+SELECT scram_build_secret('sha256', 'secret password', '\xabba5432');
+
+-- test building a SCRAM secret with a valid salt and a different set of
+-- iterations
+SELECT scram_build_secret('sha256', 'secret password',
+  decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), 10000);
+
+-- test what happens when the salt is a NULL value.
+-- this should build a SCRAM secret using a random salt.
+SELECT regexp_replace(
+  scram_build_secret('sha256', 'secret password', NULL, 10000),
+    '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]{24})\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)',
+    '\1$\2:<salt>$<storedkey>:<serverkey>') AS scram_secret;
+
+-- test what happens when iterations is a null value. this should set iterations
+-- to be the default, currently 4096.
+SELECT
+  scram_build_secret('sha256', 'secret password',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), NULL) ~
+  '^SCRAM-SHA-256\$4096' AS has_default_iterations;
+
+-- test SASLprep. This tests the case where a user supplies a non-ASCII space
+-- character.
+SELECT
+  scram_build_secret('sha256', U&'one\1680space', decode('h2y81+nUwWp5uIJc4PgyXA==', 'base64')) =
+  'SCRAM-SHA-256$4096:h2y81+nUwWp5uIJc4PgyXA==$EiywEpO6rM3z3DGehubeoRpp8Orq0XuDUbdT9fQWwz8=:Wh7fq4C+bageihh3vTrkCr7YrlcDTG+JhfcFAuHn/6E=';
-- 
2.37.1 (Apple Git-137.1)

From 789fb67e5dec1627a1d20427403cee70184a01f6 Mon Sep 17 00:00:00 2001
From: "Jonathan S. Katz" <jonathan.katz@excoventures.com>
Date: Mon, 31 Oct 2022 16:13:08 -0400
Subject: [PATCH] Add "scram_build_secret" SQL function

This function lets users build SCRAM secrets from SQL
functions and provides the ability for the user to select
the password, salt, and number of iterations for the password
hashing algorithm. Currently this only supports the "sha256"
hash, but can be modified to support additional hashes in the
future.
---
 doc/src/sgml/func.sgml                   |  46 ++++++++++
 src/backend/catalog/system_functions.sql |   7 ++
 src/backend/libpq/auth-scram.c           |  31 +++++--
 src/backend/libpq/crypt.c                |   2 +-
 src/backend/utils/adt/Makefile           |   1 +
 src/backend/utils/adt/authfuncs.c        | 109 +++++++++++++++++++++++
 src/backend/utils/adt/meson.build        |   1 +
 src/include/catalog/pg_proc.dat          |   4 +
 src/include/libpq/scram.h                |   4 +-
 src/test/regress/expected/scram.out      | 104 +++++++++++++++++++++
 src/test/regress/expected/scram_1.out    |  93 +++++++++++++++++++
 src/test/regress/parallel_schedule       |   2 +-
 src/test/regress/sql/scram.sql           |  68 ++++++++++++++
 13 files changed, 460 insertions(+), 12 deletions(-)
 create mode 100644 src/backend/utils/adt/authfuncs.c
 create mode 100644 src/test/regress/expected/scram.out
 create mode 100644 src/test/regress/expected/scram_1.out
 create mode 100644 src/test/regress/sql/scram.sql

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e09e289a43..a305767e30 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3513,6 +3513,52 @@ repeat('Pg', 4) <returnvalue>PgPgPgPg</returnvalue>
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>scram_build_secret</primary>
+        </indexterm>
+        <function>scram_build_secret</function> ( <parameter>hash_type</parameter> <type>text</type>
+        , <parameter>password</parameter> <type>text</type>
+        [, <parameter>salt</parameter> <type>bytea</type>
+        [, <parameter>iterations</parameter> <type>integer</type> ] ])
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Using the values provided in <parameter>hash type</parameter> and
+        <parameter>password</parameter>, builds a SCRAM secret equilvaent to
+        what is stored in
+        <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolpassword</structfield>
+        and used with <link linkend="sasl-scram-sha-256"><literal>scram-sha-256</literal></link>
+        authentication. If not provided or set to <literal>NULL</literal>,
+        <parameter>salt</parameter> is randomly generated and
+        <parameter>iterations</parameter> defaults to <literal>4096</literal>.
+        Currently <parameter>hash type</parameter> only supports
+        <literal>sha256</literal>.
+       </para>
+       <para>
+        <literal>SELECT scram_build_secret('sha256', 'secret password', decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'));</literal>
+        <returnvalue></returnvalue>
+<programlisting>
+  SCRAM-SHA-256$4096:MTIzNDU2Nzg5MGFiY2RlZg==$D5BmucT796UQKargx2k3fdqjDYR7cH/L0viKKhGo6kA=:M33+iHFOESP8C3DKLDb2k5QAhkNVWEbp/YUIFd2CxN4=
+</programlisting>
+       </para>
+       <para>
+        <literal>SELECT scram_build_secret('sha256', 'secret password', '\xabba5432');</literal>
+        <returnvalue></returnvalue>
+<programlisting>
+  SCRAM-SHA-256$4096:q7pUMg==$05Nb9QHwHkMA0CRcYaEfwtgZ+3kStIefz8fLMjTEtio=:P126h1ycyP938E69yxktEfhoAILbiwL/UMsMk3Efb6o=
+</programlisting>
+       </para>
+       <para>
+        <literal>SELECT scram_build_secret('sha256', 'secret password', decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), 10000);</literal>
+        <returnvalue></returnvalue>
+<programlisting>
+  SCRAM-SHA-256$10000:MTIzNDU2Nzg5MGFiY2RlZg==$9NkDu1TFpx3L30zMgHUqjRNSq3GRZRrdWU4TuGOnT3Q=:svuIH9L6HH8loyKWguT64XXoOLCrr4FkVViPd2JVR4M=
+</programlisting>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 83ca893444..5a7444df84 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -627,6 +627,13 @@ CREATE OR REPLACE FUNCTION
  STABLE PARALLEL SAFE
  AS 'sql_localtimestamp';
 
+-- defaults for building a SCRAM secret
+CREATE OR REPLACE FUNCTION
+  scram_build_secret(text, text, bytea DEFAULT NULL, int DEFAULT NULL)
+RETURNS text
+LANGUAGE INTERNAL
+VOLATILE AS 'scram_build_secret_str';
+
 --
 -- The default permissions for functions mean that anyone can execute them.
 -- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/libpq/auth-scram.c b/src/backend/libpq/auth-scram.c
index 4441e0d774..44efffd044 100644
--- a/src/backend/libpq/auth-scram.c
+++ b/src/backend/libpq/auth-scram.c
@@ -468,10 +468,15 @@ scram_exchange(void *opaq, const char *input, int inputlen,
 /*
  * Construct a SCRAM secret, for storing in pg_authid.rolpassword.
  *
+ * "salt_str" can be NULL. If it is, this function will generate a random salt.
+ *
+ * If "iterations" is 0 or less, this function will set it to the default value.
+ *
  * The result is palloc'd, so caller is responsible for freeing it.
  */
 char *
-pg_be_scram_build_secret(const char *password)
+pg_be_scram_build_secret(const char *password, char *salt_str, int salt_str_len,
+						 int iterations, pg_cryptohash_type hash_type)
 {
 	char	   *prep_password;
 	pg_saslprep_rc rc;
@@ -488,15 +493,23 @@ pg_be_scram_build_secret(const char *password)
 	if (rc == SASLPREP_SUCCESS)
 		password = (const char *) prep_password;
 
-	/* Generate random salt */
-	if (!pg_strong_random(saltbuf, SCRAM_DEFAULT_SALT_LEN))
-		ereport(ERROR,
-				(errcode(ERRCODE_INTERNAL_ERROR),
-				 errmsg("could not generate random salt")));
+	/* If salt_str is NULL, generate random salt */
+	if (salt_str == NULL)
+	{
+		if (!pg_strong_random(saltbuf, SCRAM_DEFAULT_SALT_LEN))
+			ereport(ERROR,
+					(errcode(ERRCODE_INTERNAL_ERROR),
+					 errmsg("could not generate random salt")));
+		salt_str = saltbuf;
+		salt_str_len = SCRAM_DEFAULT_SALT_LEN;
+	}
+
+	if (iterations <= 0)
+		iterations = SCRAM_DEFAULT_ITERATIONS;
 
-	result = scram_build_secret(PG_SHA256, SCRAM_SHA_256_KEY_LEN,
-								saltbuf, SCRAM_DEFAULT_SALT_LEN,
-								SCRAM_DEFAULT_ITERATIONS, password,
+	result = scram_build_secret(hash_type, SCRAM_SHA_256_KEY_LEN,
+								salt_str, salt_str_len,
+								iterations, password,
 								&errstr);
 
 	if (prep_password)
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index ef496a0bea..edf94444b6 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -140,7 +140,7 @@ encrypt_password(PasswordType target_type, const char *role,
 			return encrypted_password;
 
 		case PASSWORD_TYPE_SCRAM_SHA_256:
-			return pg_be_scram_build_secret(password);
+			return pg_be_scram_build_secret(password, NULL, -1, 0, PG_SHA256);
 
 		case PASSWORD_TYPE_PLAINTEXT:
 			elog(ERROR, "cannot encrypt password with 'plaintext'");
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 0de0bbb1b8..7ddb186f96 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -22,6 +22,7 @@ OBJS = \
 	arraysubs.o \
 	arrayutils.o \
 	ascii.o \
+	authfuncs.o \
 	bool.o \
 	cash.o \
 	char.o \
diff --git a/src/backend/utils/adt/authfuncs.c b/src/backend/utils/adt/authfuncs.c
new file mode 100644
index 0000000000..43631abbeb
--- /dev/null
+++ b/src/backend/utils/adt/authfuncs.c
@@ -0,0 +1,109 @@
+/*-------------------------------------------------------------------------
+ *
+ * authfuncs.c
+ *	  Functions that assist with authentication management
+ *
+ * Portions Copyright (c) 2022, PostgreSQL Global Development Group
+ *
+ *
+ * IDENTIFICATION
+ *	  src/backend/utils/adt/authfuncs.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "libpq/scram.h"
+#include "utils/builtins.h"
+
+#define SCRAM_BUILD_SECRET_HASH_STR_LEN	6
+
+void		parse_cryptohash_type(const char *hash_type_str,
+								  pg_cryptohash_type *hash_type);
+
+/*
+ * Build a SCRAM secret that can be used for SCRAM-SHA-256 authentication.
+ *
+ * This function can take four arguments:
+ *
+ * - hash_type_str: the type of hash to use when building the SCRAM secret.
+ *              Currently only "sha256" is supported.
+ * - 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.
+ */
+Datum
+scram_build_secret_str(PG_FUNCTION_ARGS)
+{
+	const char *hash_type_str;
+	pg_cryptohash_type hash_type;
+	const char *password;
+	char	   *salt_str = NULL;
+	int			salt_str_len = -1;
+	int			iterations = 0;
+	char	   *secret;
+
+	if (PG_ARGISNULL(0))
+	{
+		ereport(ERROR,
+				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+				 errmsg("hash type must not be null")));
+	}
+
+	hash_type_str = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	parse_cryptohash_type(hash_type_str, &hash_type);
+
+	if (PG_ARGISNULL(1))
+	{
+		ereport(ERROR,
+				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+				 errmsg("password must not be null")));
+	}
+
+	password = text_to_cstring(PG_GETARG_TEXT_PP(1));
+
+	if (!PG_ARGISNULL(2))
+	{
+		salt_str = text_to_cstring((text *) PG_GETARG_BYTEA_PP(2));
+		salt_str_len = strlen(salt_str);
+	}
+
+	if (!PG_ARGISNULL(3))
+		iterations = PG_GETARG_INT32(3);
+
+	secret = pg_be_scram_build_secret(password, salt_str, salt_str_len,
+									  iterations, hash_type);
+
+	Assert(secret != NULL);
+
+	/*
+	 * convert the SCRAM secret to text which matches the type for
+	 * pg_authid.rolpassword
+	 */
+	PG_RETURN_TEXT_P(cstring_to_text(secret));
+}
+
+/*
+ * If "hash_type_str" is a valid cryptohash type that can be used for SCRAM
+ * authetnication, set the value to "hash_type". Otherwise, return an
+ * unsupported error.
+ */
+void
+parse_cryptohash_type(const char *hash_type_str, pg_cryptohash_type *hash_type)
+{
+	pg_cryptohash_type result_type;
+
+	if (pg_strncasecmp(hash_type_str, "sha256", SCRAM_BUILD_SECRET_HASH_STR_LEN) == 0)
+		result_type = PG_SHA256;
+	else
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("hash not supported: \"%s\"", hash_type_str),
+				 errmsg("supported hashes are \"sha256\"")));
+
+	*hash_type = result_type;
+}
diff --git a/src/backend/utils/adt/meson.build b/src/backend/utils/adt/meson.build
index 8515cd9365..52363a2f26 100644
--- a/src/backend/utils/adt/meson.build
+++ b/src/backend/utils/adt/meson.build
@@ -11,6 +11,7 @@ backend_sources += files(
   'arraysubs.c',
   'arrayutils.c',
   'ascii.c',
+  'authfuncs.c',
   'bool.c',
   'cash.c',
   'char.c',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 66b73c3900..0e5484c8d9 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7619,6 +7619,10 @@
 { oid => '3422', descr => 'SHA-512 hash',
   proname => 'sha512', proleakproof => 't', prorettype => 'bytea',
   proargtypes => 'bytea', prosrc => 'sha512_bytea' },
+{ oid => '8557', descr => 'Build a SCRAM secret',
+  proname => 'scram_build_secret', prorettype => 'text',
+  proisstrict => 'f', proargtypes => 'text text bytea int4',
+  prosrc => 'scram_build_secret_str' },
 
 # crosstype operations for date vs. timestamp and timestamptz
 { oid => '2338',
diff --git a/src/include/libpq/scram.h b/src/include/libpq/scram.h
index b275e1e87e..400f551778 100644
--- a/src/include/libpq/scram.h
+++ b/src/include/libpq/scram.h
@@ -22,7 +22,9 @@
 extern PGDLLIMPORT const pg_be_sasl_mech pg_be_scram_mech;
 
 /* Routines to handle and check SCRAM-SHA-256 secret */
-extern char *pg_be_scram_build_secret(const char *password);
+extern char *pg_be_scram_build_secret(const char *password, char *salt_str,
+									  int salt_str_len, int iterations,
+									  pg_cryptohash_type hash_type);
 extern bool parse_scram_secret(const char *secret,
 							   int *iterations,
 							   pg_cryptohash_type *hash_type,
diff --git a/src/test/regress/expected/scram.out b/src/test/regress/expected/scram.out
new file mode 100644
index 0000000000..77313320b5
--- /dev/null
+++ b/src/test/regress/expected/scram.out
@@ -0,0 +1,104 @@
+-- Test building SCRAM functions
+-- test all nulls
+-- fail
+SELECT scram_build_secret(NULL, NULL);
+ERROR:  hash type must not be null
+SELECT scram_build_secret('sha256', NULL);
+ERROR:  password must not be null
+SELECT scram_build_secret('sha256', NULL, NULL);
+ERROR:  password must not be null
+SELECT scram_build_secret('sha256', NULL, NULL, NULL);
+ERROR:  password must not be null
+-- test unsupported hashes
+-- fail
+SELECT scram_build_secret('sha384', 'password');
+ERROR:  supported hashes are "sha256"
+SELECT scram_build_secret('sha512', 'password');
+ERROR:  supported hashes are "sha256"
+-- 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',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'));
+                                                          scram_build_secret                                                           
+---------------------------------------------------------------------------------------------------------------------------------------
+ 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',
+  decode('abc', 'base64'));
+ERROR:  invalid base64 end sequence
+HINT:  Input data is missing padding, is truncated, or is otherwise corrupted.
+-- test building a SCRAM secret with a salt that looks like a string but is
+-- cast to a bytea
+SELECT scram_build_secret('sha256', 'secret password', 'abc');
+                                                scram_build_secret                                                 
+-------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$4096:YWJj$L27WlKwqjMDY5ZNsyaxGSMii2mhmoUB7xONbxjykmw4=:u1ofGUXUqTbMwfiH+ANWDCpwEjk3j1Xrocy3va/jaCU=
+(1 row)
+
+-- test building a SCRAM secret with a bytea salt using the hex format
+SELECT scram_build_secret('sha256', 'secret password', '\xabba5432');
+                                                  scram_build_secret                                                   
+-----------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$4096:q7pUMg==$05Nb9QHwHkMA0CRcYaEfwtgZ+3kStIefz8fLMjTEtio=:P126h1ycyP938E69yxktEfhoAILbiwL/UMsMk3Efb6o=
+(1 row)
+
+-- test building a SCRAM secret with a valid salt and a different set of
+-- iterations
+SELECT scram_build_secret('sha256', 'secret password',
+  decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), 10000);
+                                                           scram_build_secret                                                           
+----------------------------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$10000:MTIzNDU2Nzg5MGFiY2RlZg==$9NkDu1TFpx3L30zMgHUqjRNSq3GRZRrdWU4TuGOnT3Q=:svuIH9L6HH8loyKWguT64XXoOLCrr4FkVViPd2JVR4M=
+(1 row)
+
+-- test what happens when the salt is a NULL value.
+-- this should build a SCRAM secret using a random salt.
+SELECT regexp_replace(
+  scram_build_secret('sha256', 'secret password', NULL, 10000),
+    '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]{24})\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)',
+    '\1$\2:<salt>$<storedkey>:<serverkey>') AS scram_secret;
+                    scram_secret                    
+----------------------------------------------------
+ SCRAM-SHA-256$10000:<salt>$<storedkey>:<serverkey>
+(1 row)
+
+-- test what happens when iterations is a null value. this should set iterations
+-- to be the default, currently 4096.
+SELECT
+  scram_build_secret('sha256', 'secret password',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), NULL) ~
+  '^SCRAM-SHA-256\$4096' AS has_default_iterations;
+ has_default_iterations 
+------------------------
+ t
+(1 row)
+
+-- skip the remaining tests if this not a UTF8 server
+SELECT getdatabaseencoding() <> 'UTF8' AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+-- test SASLprep. This tests the case where a user supplies a non-ASCII space
+-- character.
+SELECT
+  scram_build_secret('sha256', U&'one\1680space', decode('h2y81+nUwWp5uIJc4PgyXA==', 'base64')) =
+  'SCRAM-SHA-256$4096:h2y81+nUwWp5uIJc4PgyXA==$EiywEpO6rM3z3DGehubeoRpp8Orq0XuDUbdT9fQWwz8=:Wh7fq4C+bageihh3vTrkCr7YrlcDTG+JhfcFAuHn/6E=';
+ ?column? 
+----------
+ t
+(1 row)
+
diff --git a/src/test/regress/expected/scram_1.out b/src/test/regress/expected/scram_1.out
new file mode 100644
index 0000000000..27cd91c01d
--- /dev/null
+++ b/src/test/regress/expected/scram_1.out
@@ -0,0 +1,93 @@
+-- Test building SCRAM functions
+-- test all nulls
+-- fail
+SELECT scram_build_secret(NULL, NULL);
+ERROR:  hash type must not be null
+SELECT scram_build_secret('sha256', NULL);
+ERROR:  password must not be null
+SELECT scram_build_secret('sha256', NULL, NULL);
+ERROR:  password must not be null
+SELECT scram_build_secret('sha256', NULL, NULL, NULL);
+ERROR:  password must not be null
+-- test unsupported hashes
+-- fail
+SELECT scram_build_secret('sha384', 'password');
+ERROR:  supported hashes are "sha256"
+SELECT scram_build_secret('sha512', 'password');
+ERROR:  supported hashes are "sha256"
+-- 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',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'));
+                                                          scram_build_secret                                                           
+---------------------------------------------------------------------------------------------------------------------------------------
+ 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',
+  decode('abc', 'base64'));
+ERROR:  invalid base64 end sequence
+HINT:  Input data is missing padding, is truncated, or is otherwise corrupted.
+-- test building a SCRAM secret with a salt that looks like a string but is
+-- cast to a bytea
+SELECT scram_build_secret('sha256', 'secret password', 'abc');
+                                                scram_build_secret                                                 
+-------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$4096:YWJj$L27WlKwqjMDY5ZNsyaxGSMii2mhmoUB7xONbxjykmw4=:u1ofGUXUqTbMwfiH+ANWDCpwEjk3j1Xrocy3va/jaCU=
+(1 row)
+
+-- test building a SCRAM secret with a bytea salt using the hex format
+SELECT scram_build_secret('sha256', 'secret password', '\xabba5432');
+                                                  scram_build_secret                                                   
+-----------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$4096:q7pUMg==$05Nb9QHwHkMA0CRcYaEfwtgZ+3kStIefz8fLMjTEtio=:P126h1ycyP938E69yxktEfhoAILbiwL/UMsMk3Efb6o=
+(1 row)
+
+-- test building a SCRAM secret with a valid salt and a different set of
+-- iterations
+SELECT scram_build_secret('sha256', 'secret password',
+  decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), 10000);
+                                                           scram_build_secret                                                           
+----------------------------------------------------------------------------------------------------------------------------------------
+ SCRAM-SHA-256$10000:MTIzNDU2Nzg5MGFiY2RlZg==$9NkDu1TFpx3L30zMgHUqjRNSq3GRZRrdWU4TuGOnT3Q=:svuIH9L6HH8loyKWguT64XXoOLCrr4FkVViPd2JVR4M=
+(1 row)
+
+-- test what happens when the salt is a NULL value.
+-- this should build a SCRAM secret using a random salt.
+SELECT regexp_replace(
+  scram_build_secret('sha256', 'secret password', NULL, 10000),
+    '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]{24})\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)',
+    '\1$\2:<salt>$<storedkey>:<serverkey>') AS scram_secret;
+                    scram_secret                    
+----------------------------------------------------
+ SCRAM-SHA-256$10000:<salt>$<storedkey>:<serverkey>
+(1 row)
+
+-- test what happens when iterations is a null value. this should set iterations
+-- to be the default, currently 4096.
+SELECT
+  scram_build_secret('sha256', 'secret password',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), NULL) ~
+  '^SCRAM-SHA-256\$4096' AS has_default_iterations;
+ has_default_iterations 
+------------------------
+ t
+(1 row)
+
+-- skip the remaining tests if this not a UTF8 server
+SELECT getdatabaseencoding() <> 'UTF8' AS skip_test \gset
+\if :skip_test
+\quit
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 15e015b3d6..b360ed07f2 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -28,7 +28,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..be6216f6d3
--- /dev/null
+++ b/src/test/regress/sql/scram.sql
@@ -0,0 +1,68 @@
+-- Test building SCRAM functions
+
+-- test all nulls
+-- fail
+SELECT scram_build_secret(NULL, NULL);
+SELECT scram_build_secret('sha256', NULL);
+SELECT scram_build_secret('sha256', NULL, NULL);
+SELECT scram_build_secret('sha256', NULL, NULL, NULL);
+
+-- test unsupported hashes
+-- fail
+SELECT scram_build_secret('sha384', 'password');
+SELECT scram_build_secret('sha512', 'password');
+
+-- 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',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'));
+
+-- test building a SCRAM secret with a predefined salt that is not a valid
+-- base64 string
+-- fail
+SELECT scram_build_secret('sha256', 'secret password',
+  decode('abc', 'base64'));
+
+-- test building a SCRAM secret with a salt that looks like a string but is
+-- cast to a bytea
+SELECT scram_build_secret('sha256', 'secret password', 'abc');
+
+-- test building a SCRAM secret with a bytea salt using the hex format
+SELECT scram_build_secret('sha256', 'secret password', '\xabba5432');
+
+-- test building a SCRAM secret with a valid salt and a different set of
+-- iterations
+SELECT scram_build_secret('sha256', 'secret password',
+  decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), 10000);
+
+-- test what happens when the salt is a NULL value.
+-- this should build a SCRAM secret using a random salt.
+SELECT regexp_replace(
+  scram_build_secret('sha256', 'secret password', NULL, 10000),
+    '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]{24})\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)',
+    '\1$\2:<salt>$<storedkey>:<serverkey>') AS scram_secret;
+
+-- test what happens when iterations is a null value. this should set iterations
+-- to be the default, currently 4096.
+SELECT
+  scram_build_secret('sha256', 'secret password',
+    decode('MTIzNDU2Nzg5MGFiY2RlZg==', 'base64'), NULL) ~
+  '^SCRAM-SHA-256\$4096' AS has_default_iterations;
+
+-- skip the remaining tests if this not a UTF8 server
+SELECT getdatabaseencoding() <> 'UTF8' AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+
+-- test SASLprep. This tests the case where a user supplies a non-ASCII space
+-- character.
+SELECT
+  scram_build_secret('sha256', U&'one\1680space', decode('h2y81+nUwWp5uIJc4PgyXA==', 'base64')) =
+  'SCRAM-SHA-256$4096:h2y81+nUwWp5uIJc4PgyXA==$EiywEpO6rM3z3DGehubeoRpp8Orq0XuDUbdT9fQWwz8=:Wh7fq4C+bageihh3vTrkCr7YrlcDTG+JhfcFAuHn/6E=';
-- 
2.37.1 (Apple Git-137.1)