On Mon, Oct 9, 2023 at 2:31 AM Gurjeet Singh <gurj...@singh.im> wrote:
>
> Next steps:
> - Break the patch into a series of smaller patches.
Please see attached the same v3 patch, but now split into 3 separate
patches. Each patch in the series depends on the previous patch to
have been applied. I have made sure that each patch passes `make
check` individually.
First patch adds the two new columns, rolsecondpassword and
rolsecondvaliduntil to the pg_authid shared catalog. This patch also
updates the corresponding pg_authid.dat file to set these values to
null for the rows populated during bootstrap. Finally, it adds code to
CreateRole() to set these columns' values to NULL for a role being
created.
The second patch updates the password extraction, verification
functions as well as authentication functions to honor the second
password, if any. There is more detailed description in the commit
message/body of the patch.
The third patch adds the SQL support to the ALTER ROLE command which
allows manipulation of both, the rolpassword and rolsecondpassword,
columns and their respective expiration timestamps,
rol[second]validuntil. This patch also adds regression tests for the
new SQL command, demonstrating the use of the new grammar.
v3-0001-Add-new-columns-to-pg_authid.patch
v3-0002-Update-password-verification-infrastructure-to-ha.patch
v3-0003-Added-SQL-support-for-ALTER-ROLE-to-manage-two-pa.patch
Best regards,
Gurjeet
http://Gurje.et
From bc7c35e53e421157c9425c198bc2557ad118a650 Mon Sep 17 00:00:00 2001
From: Gurjeet Singh <gurjeet@singh.im>
Date: Mon, 9 Oct 2023 11:36:05 -0700
Subject: [PATCH v3 1/3] Add new columns to pg_authid
Add two columns to pg_authid, namely rolsecondpassword and
rolsecondvaliduntil. These columns are added in preparation for the
password-rollover feature. These columns will store the hash and the
expiration time, repspectively, of a second password that the role can
use for login authentication.
---
src/backend/commands/user.c | 4 +++
src/include/catalog/pg_authid.dat | 45 ++++++++++++++++++++-----------
src/include/catalog/pg_authid.h | 2 ++
3 files changed, 36 insertions(+), 15 deletions(-)
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index ce77a055e5..0afaf74865 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -452,9 +452,13 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
else
new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
+ new_record_nulls[Anum_pg_authid_rolsecondpassword - 1] = true;
+
new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum;
new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null;
+ new_record_nulls[Anum_pg_authid_rolsecondvaliduntil - 1] = true;
+
new_record[Anum_pg_authid_rolbypassrls - 1] = BoolGetDatum(bypassrls);
/*
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 6b4a0aaaad..b326e48376 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -23,76 +23,91 @@
rolname => 'POSTGRES', rolsuper => 't', rolinherit => 't',
rolcreaterole => 't', rolcreatedb => 't', rolcanlogin => 't',
rolreplication => 't', rolbypassrls => 't', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '6171', oid_symbol => 'ROLE_PG_DATABASE_OWNER',
rolname => 'pg_database_owner', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '6181', oid_symbol => 'ROLE_PG_READ_ALL_DATA',
rolname => 'pg_read_all_data', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '6182', oid_symbol => 'ROLE_PG_WRITE_ALL_DATA',
rolname => 'pg_write_all_data', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '3373', oid_symbol => 'ROLE_PG_MONITOR',
rolname => 'pg_monitor', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '3374', oid_symbol => 'ROLE_PG_READ_ALL_SETTINGS',
rolname => 'pg_read_all_settings', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '3375', oid_symbol => 'ROLE_PG_READ_ALL_STATS',
rolname => 'pg_read_all_stats', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '3377', oid_symbol => 'ROLE_PG_STAT_SCAN_TABLES',
rolname => 'pg_stat_scan_tables', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '4569', oid_symbol => 'ROLE_PG_READ_SERVER_FILES',
rolname => 'pg_read_server_files', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '4570', oid_symbol => 'ROLE_PG_WRITE_SERVER_FILES',
rolname => 'pg_write_server_files', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '4571', oid_symbol => 'ROLE_PG_EXECUTE_SERVER_PROGRAM',
rolname => 'pg_execute_server_program', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '4200', oid_symbol => 'ROLE_PG_SIGNAL_BACKEND',
rolname => 'pg_signal_backend', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '4544', oid_symbol => 'ROLE_PG_CHECKPOINT',
rolname => 'pg_checkpoint', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '4550', oid_symbol => 'ROLE_PG_USE_RESERVED_CONNECTIONS',
rolname => 'pg_use_reserved_connections', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
{ oid => '6304', oid_symbol => 'ROLE_PG_CREATE_SUBSCRIPTION',
rolname => 'pg_create_subscription', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolpassword => '_null_', rolvaliduntil => '_null_',
+ rolsecondpassword => '_null_', rolsecondvaliduntil => '_null_' },
]
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index 0e7ddc56ea..2a27ae3e10 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -45,6 +45,8 @@ CATALOG(pg_authid,1260,AuthIdRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(284
#ifdef CATALOG_VARLEN /* variable-length fields start here */
text rolpassword; /* password, if any */
timestamptz rolvaliduntil; /* password expiration time, if any */
+ text rolsecondpassword; /* second password, if any */
+ timestamptz rolsecondvaliduntil; /* second password expiration time, if any */
#endif
} FormData_pg_authid;
--
2.41.0
From f8909979c17d64fb14f4074b1b811052d67aba4e Mon Sep 17 00:00:00 2001
From: Gurjeet Singh <gurjeet@singh.im>
Date: Mon, 9 Oct 2023 11:48:11 -0700
Subject: [PATCH v3 2/3] Update password verification infrastructure to handle
two passwords
After the addition of rolsecondpassword and rolsecondvaliduntil columns
to pg_authid, this commit adds the ability to honor the second
password as well, if any, to authenticate the roles.
The get_role_passwords() function retrieves and returns all valid
passwords for a role. It does so by inspecting rolpassword and
rolsecondpassword column values, and their respective rol*validuntil
column values.
get_salt(), a local function in user.c helps to extract the salt, needed
for generating hash of new passwords, from currently stored password
hashes, if any. For md5 it simply uses the role name, and for
scram-sha-256 it extracts and returns the salt from the stored hash.
The salt provided by get_salt() is used by CreateRole() and AlterRole()
to hash the new passwords of a role.
The following functions used to accept and peruse just one password /
secret. They are now updated to accept and use two passwords, along with
the data-structure changes needed for state management needed by these
functions.
CheckMD5Auth(), CheckPasswordAuth(), CheckSASLAuth(), scram_init(), verify_client_proof(),
pg_be_scram_build_secret() now uses the passed-in salt to generate the
hash. If one is not provided, then it generates a new random salt, like
before. Similarly, +encrypt_password(), pg_md5_encrypt(), and
pg_be_scram_build_secret() now accept a salt for password hashing.
---
src/backend/commands/user.c | 112 ++++++++++++++++--
src/backend/libpq/auth-sasl.c | 4 +-
src/backend/libpq/auth-scram.c | 210 ++++++++++++++++++++-------------
src/backend/libpq/auth.c | 114 +++++++++++-------
src/backend/libpq/crypt.c | 78 +++++++++---
src/include/libpq/crypt.h | 2 +-
src/include/libpq/sasl.h | 4 +-
src/include/libpq/scram.h | 2 +-
8 files changed, 374 insertions(+), 152 deletions(-)
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 0afaf74865..9ad02e4092 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -30,7 +30,9 @@
#include "commands/defrem.h"
#include "commands/seclabel.h"
#include "commands/user.h"
+#include "common/scram-common.h"
#include "libpq/crypt.h"
+#include "libpq/scram.h"
#include "miscadmin.h"
#include "storage/lmgr.h"
#include "utils/acl.h"
@@ -125,6 +127,86 @@ have_createrole_privilege(void)
return has_createrole_privilege(GetUserId());
}
+/*
+ * Inspect the current passwords of a role, and return the salt that can be used
+ * for hashing of newer passwords.
+ *
+ * Returns success on error, and false otherwise. On error the reason is stored in
+ * logdetail. On success, salt may be null which indicates that the caller is
+ * free to generate a new salt.
+ */
+static bool
+get_salt(char *rolename, char **salt, const char **logdetail)
+{
+ char **current_secrets;
+ int i, num_secrets;
+ char *salt1, *salt2 = NULL;
+ PasswordType passtype;
+
+ if (Password_encryption == PASSWORD_TYPE_MD5)
+ {
+ *salt = rolename; /* md5 always uses role name, no need to look through the passwords */
+ return true;
+ }
+ else if (Password_encryption == PASSWORD_TYPE_PLAINTEXT)
+ {
+ *salt = NULL; /* Plaintext does not have a salt */
+ return true;
+ }
+
+ current_secrets = get_role_passwords(rolename, logdetail, &num_secrets);
+ if (num_secrets == 0)
+ {
+ *salt = NULL; /* No existing passwords, allow salt to be generated */
+ return true;
+ }
+
+ for (i = 0; i < num_secrets; i++)
+ {
+ passtype = get_password_type(current_secrets[i]);
+
+ if (passtype == PASSWORD_TYPE_MD5 || passtype == PASSWORD_TYPE_PLAINTEXT)
+ continue; /* md5 uses rolename as salt so it is always the same, and plaintext has no salt */
+ else if (passtype == PASSWORD_TYPE_SCRAM_SHA_256)
+ {
+ int iterations;
+ int key_length = 0;
+ pg_cryptohash_type hash_type;
+ uint8 stored_key[SCRAM_MAX_KEY_LEN];
+ uint8 server_key[SCRAM_MAX_KEY_LEN];
+
+ if (!parse_scram_secret(current_secrets[i], &iterations, &hash_type, &key_length,
+ &salt1, stored_key, server_key))
+ {
+ *logdetail = psprintf(_("could not parse SCRAM secret"));
+ *salt = NULL;
+ return false;
+ }
+
+ if (salt2 != NULL)
+ {
+ if (strcmp(salt1, salt2))
+ {
+ *logdetail = psprintf(_("inconsistent salts, clearing password")); // TODO: Better message
+ *salt = NULL;
+ return false;
+ }
+ }
+ else
+ salt2 = salt1;
+ }
+ }
+
+ for (i = 0; i < num_secrets; i++)
+ pfree(current_secrets[i]);
+ if (current_secrets)
+ pfree(current_secrets);
+
+ if (salt2)
+ *salt = pstrdup(salt2);
+
+ return true;
+}
/*
* CREATE ROLE
@@ -153,8 +235,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
List *addroleto = NIL; /* roles to make this a member of */
List *rolemembers = NIL; /* roles to be members of this role */
List *adminmembers = NIL; /* roles to be admins of this role */
- char *validUntil = NULL; /* time the login is valid until */
- Datum validUntil_datum; /* same, as timestamptz Datum */
+ char *validUntil = NULL; /* time the password is valid until */
+ Datum validUntil_datum; /* validuntil, as timestamptz Datum */
bool validUntil_null;
DefElem *dpassword = NULL;
DefElem *dissuper = NULL;
@@ -442,11 +524,16 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
}
else
{
+ char *salt;
+
+ if (!get_salt(stmt->role, &salt, &logdetail))
+ ereport(ERROR,
+ (errmsg("could not get a valid salt for password"),
+ errdetail("%s", logdetail)));
+
/* Encrypt the password to the requested format. */
- shadow_pass = encrypt_password(Password_encryption, stmt->role,
- password);
- new_record[Anum_pg_authid_rolpassword - 1] =
- CStringGetTextDatum(shadow_pass);
+ shadow_pass = encrypt_password(Password_encryption, salt, password);
+ new_record[Anum_pg_authid_rolpassword - 1] = CStringGetTextDatum(shadow_pass);
}
}
else
@@ -772,7 +859,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
"SUPERUSER", "SUPERUSER")));
/*
- * Most changes to a role require that you both have CREATEROLE privileges
+ * Most changes to a role require that you have both CREATEROLE privileges
* and also ADMIN OPTION on the role.
*/
if (!have_createrole_privilege() ||
@@ -931,9 +1018,16 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
}
else
{
+ char *salt;
+
+ if (!get_salt(rolename, &salt, &logdetail))
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("could not get a valid salt for password"),
+ errdetail("%s", logdetail)));
+
/* Encrypt the password to the requested format. */
- shadow_pass = encrypt_password(Password_encryption, rolename,
- password);
+ shadow_pass = encrypt_password(Password_encryption, salt, password);
new_record[Anum_pg_authid_rolpassword - 1] =
CStringGetTextDatum(shadow_pass);
}
diff --git a/src/backend/libpq/auth-sasl.c b/src/backend/libpq/auth-sasl.c
index c535bc5383..56bd165885 100644
--- a/src/backend/libpq/auth-sasl.c
+++ b/src/backend/libpq/auth-sasl.c
@@ -49,7 +49,7 @@
* should just pass NULL.
*/
int
-CheckSASLAuth(const pg_be_sasl_mech *mech, Port *port, char *shadow_pass,
+CheckSASLAuth(const pg_be_sasl_mech *mech, Port *port, const char **passwords, int num_passwords,
const char **logdetail)
{
StringInfoData sasl_mechs;
@@ -136,7 +136,7 @@ CheckSASLAuth(const pg_be_sasl_mech *mech, Port *port, char *shadow_pass,
* This is because we don't want to reveal to an attacker what
* usernames are valid, nor which users have a valid password.
*/
- opaq = mech->init(port, selected_mech, shadow_pass);
+ opaq = mech->init(port, selected_mech, passwords, num_passwords);
inputlen = pq_getmsgint(&buf, 4);
if (inputlen == -1)
diff --git a/src/backend/libpq/auth-scram.c b/src/backend/libpq/auth-scram.c
index 118d15b1a1..a0ebfecd69 100644
--- a/src/backend/libpq/auth-scram.c
+++ b/src/backend/libpq/auth-scram.c
@@ -109,7 +109,7 @@
static void scram_get_mechanisms(Port *port, StringInfo buf);
static void *scram_init(Port *port, const char *selected_mech,
- const char *shadow_pass);
+ const char **secrets, const int num_secrets);
static int scram_exchange(void *opaq, const char *input, int inputlen,
char **output, int *outputlen,
const char **logdetail);
@@ -132,6 +132,12 @@ typedef enum
SCRAM_AUTH_FINISHED
} scram_state_enum;
+typedef struct
+{
+ uint8 StoredKey[SCRAM_MAX_KEY_LEN];
+ uint8 ServerKey[SCRAM_MAX_KEY_LEN];
+} scram_secret;
+
typedef struct
{
scram_state_enum state;
@@ -145,10 +151,16 @@ typedef struct
pg_cryptohash_type hash_type;
int key_length;
+ /*
+ * The salt and iterations must be the same for all
+ * secrets since they are sent as part of the initial message
+ */
int iterations;
char *salt; /* base64-encoded */
- uint8 StoredKey[SCRAM_MAX_KEY_LEN];
- uint8 ServerKey[SCRAM_MAX_KEY_LEN];
+ /* Array of possible secrets */
+ scram_secret *secrets;
+ int num_secrets;
+ int chosen_secret; /* secret chosen during final client message */
/* Fields of the first message from client */
char cbind_flag;
@@ -231,17 +243,20 @@ scram_get_mechanisms(Port *port, StringInfo buf)
* It should be one of the mechanisms that we support, as returned by
* scram_get_mechanisms().
*
- * 'shadow_pass' is the role's stored secret, from pg_authid.rolpassword.
+ * 'passwords' are the role's stored secrets, from pg_authid.rolpassword.
* The username was provided by the client in the startup message, and is
* available in port->user_name. If 'shadow_pass' is NULL, we still perform
* an authentication exchange, but it will fail, as if an incorrect password
* was given.
*/
static void *
-scram_init(Port *port, const char *selected_mech, const char *shadow_pass)
+scram_init(Port *port, const char *selected_mech, const char **secrets, const int num_secrets)
{
scram_state *state;
- bool got_secret;
+ bool got_secret = false;
+ int i;
+ int iterations;
+ char *salt = NULL; /* base64-encoded */
state = (scram_state *) palloc0(sizeof(scram_state));
state->port = port;
@@ -270,49 +285,54 @@ scram_init(Port *port, const char *selected_mech, const char *shadow_pass)
/*
* Parse the stored secret.
*/
- if (shadow_pass)
+ if (secrets)
{
- int password_type = get_password_type(shadow_pass);
-
- if (password_type == PASSWORD_TYPE_SCRAM_SHA_256)
+ state->secrets = palloc0(sizeof(scram_secret) * num_secrets);
+ state->num_secrets = num_secrets;
+ for (i = 0; i < num_secrets; i++)
{
- if (parse_scram_secret(shadow_pass, &state->iterations,
- &state->hash_type, &state->key_length,
- &state->salt,
- state->StoredKey,
- state->ServerKey))
- got_secret = true;
- else
+ int password_type = get_password_type(secrets[i]);
+
+ if (password_type == PASSWORD_TYPE_SCRAM_SHA_256)
{
- /*
- * The password looked like a SCRAM secret, but could not be
- * parsed.
- */
- ereport(LOG,
- (errmsg("invalid SCRAM secret for user \"%s\"",
- state->port->user_name)));
- got_secret = false;
+ if (parse_scram_secret(secrets[i], &state->iterations,
+ &state->hash_type, &state->key_length,
+ &state->salt,
+ state->secrets[i].StoredKey,
+ state->secrets[i].ServerKey))
+ {
+ if (salt)
+ {
+ /* The stored iterations and salt must match or we cannot proceed, allow failure via mock */
+ if (strcmp(salt, state->salt) || iterations != state->iterations)
+ {
+ ereport(WARNING, (errmsg("inconsistent salt or iterations for user \"%s\"",
+ state->port->user_name)));
+ got_secret = false; /* fail and allow mock creditials to be created */
+ pfree(state->secrets);
+ state->num_secrets = 0;
+ break;
+ }
+ }
+ else
+ {
+ salt = state->salt;
+ iterations = state->iterations;
+ got_secret = true; /* We got at least one good SCRAM secret */
+ }
+ }
+ else
+ {
+ /*
+ * The password looked like a SCRAM secret, but could not be
+ * parsed.
+ */
+ ereport(LOG,
+ (errmsg("invalid SCRAM secret for user \"%s\"",
+ state->port->user_name)));
+ }
}
}
- else
- {
- /*
- * The user doesn't have SCRAM secret. (You cannot do SCRAM
- * authentication with an MD5 hash.)
- */
- state->logdetail = psprintf(_("User \"%s\" does not have a valid SCRAM secret."),
- state->port->user_name);
- got_secret = false;
- }
- }
- else
- {
- /*
- * The caller requested us to perform a dummy authentication. This is
- * considered normal, since the caller requested it, so don't set log
- * detail.
- */
- got_secret = false;
}
/*
@@ -323,10 +343,13 @@ scram_init(Port *port, const char *selected_mech, const char *shadow_pass)
*/
if (!got_secret)
{
+ state->secrets = palloc0(sizeof(scram_secret));
+ state->num_secrets = 1;
+
mock_scram_secret(state->port->user_name, &state->hash_type,
&state->iterations, &state->key_length,
&state->salt,
- state->StoredKey, state->ServerKey);
+ state->secrets[0].StoredKey, state->secrets[0].ServerKey);
state->doomed = true;
}
@@ -474,7 +497,7 @@ scram_exchange(void *opaq, const char *input, int inputlen,
* 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, const char *salt)
{
char *prep_password;
pg_saslprep_rc rc;
@@ -491,11 +514,20 @@ 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))
+ /* Use passed-in salt, or generate random salt */
+ if (!salt && !pg_strong_random(saltbuf, SCRAM_DEFAULT_SALT_LEN))
+ {
ereport(ERROR,
(errcode(ERRCODE_INTERNAL_ERROR),
errmsg("could not generate random salt")));
+ }
+ else if (salt)
+ {
+ if (pg_b64_decode(salt, strlen(salt), saltbuf, SCRAM_DEFAULT_SALT_LEN) == -1)
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("could not decode SCRAM salt")));
+ }
result = scram_build_secret(PG_SHA256, SCRAM_SHA_256_KEY_LEN,
saltbuf, SCRAM_DEFAULT_SALT_LEN,
@@ -1142,48 +1174,62 @@ verify_client_proof(scram_state *state)
uint8 ClientSignature[SCRAM_MAX_KEY_LEN];
uint8 ClientKey[SCRAM_MAX_KEY_LEN];
uint8 client_StoredKey[SCRAM_MAX_KEY_LEN];
- pg_hmac_ctx *ctx = pg_hmac_create(state->hash_type);
- int i;
+ pg_hmac_ctx *ctx;
+ int i, j;
const char *errstr = NULL;
-
/*
* Calculate ClientSignature. Note that we don't log directly a failure
* here even when processing the calculations as this could involve a mock
* authentication.
*/
- if (pg_hmac_init(ctx, state->StoredKey, state->key_length) < 0 ||
- pg_hmac_update(ctx,
- (uint8 *) state->client_first_message_bare,
- strlen(state->client_first_message_bare)) < 0 ||
- pg_hmac_update(ctx, (uint8 *) ",", 1) < 0 ||
- pg_hmac_update(ctx,
- (uint8 *) state->server_first_message,
- strlen(state->server_first_message)) < 0 ||
- pg_hmac_update(ctx, (uint8 *) ",", 1) < 0 ||
- pg_hmac_update(ctx,
- (uint8 *) state->client_final_message_without_proof,
- strlen(state->client_final_message_without_proof)) < 0 ||
- pg_hmac_final(ctx, ClientSignature, state->key_length) < 0)
+ for (j = 0; j < state->num_secrets; j++)
{
- elog(ERROR, "could not calculate client signature: %s",
- pg_hmac_error(ctx));
+ ctx = pg_hmac_create(state->hash_type);
+ elog(LOG, "Trying to verify password %d", j); // TODO: Convert to DEBUG2
+
+ if (pg_hmac_init(ctx, state->secrets[j].StoredKey, state->key_length) < 0 ||
+ pg_hmac_update(ctx,
+ (uint8 *) state->client_first_message_bare,
+ strlen(state->client_first_message_bare)) < 0 ||
+ pg_hmac_update(ctx, (uint8 *) ",", 1) < 0 ||
+ pg_hmac_update(ctx,
+ (uint8 *) state->server_first_message,
+ strlen(state->server_first_message)) < 0 ||
+ pg_hmac_update(ctx, (uint8 *) ",", 1) < 0 ||
+ pg_hmac_update(ctx,
+ (uint8 *) state->client_final_message_without_proof,
+ strlen(state->client_final_message_without_proof)) < 0 ||
+ pg_hmac_final(ctx, ClientSignature, state->key_length) < 0)
+ {
+ // TODO: Convert to DEBUG2
+ elog(LOG, "could not calculate client signature for secret %d", j);
+ pg_hmac_free(ctx);
+ continue;
+ }
+
+ // TODO: Convert to DEBUG2
+ elog(LOG, "succeeded on %d password", j);
+
+ pg_hmac_free(ctx);
+
+ /* Extract the ClientKey that the client calculated from the proof */
+ for (i = 0; i < state->key_length; i++)
+ ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
+
+ /* Hash it one more time, and compare with StoredKey */
+ if (scram_H(ClientKey, state->hash_type, state->key_length,
+ client_StoredKey, &errstr) < 0)
+ elog(ERROR, "could not hash stored key: %s", errstr);
+
+ if (memcmp(client_StoredKey, state->secrets[j].StoredKey, state->key_length) == 0) {
+ // TODO: Convert to DEBUG2
+ elog(LOG, "Moving forward with Password %d", j);
+ state->chosen_secret = j;
+ return true;
+ }
}
- pg_hmac_free(ctx);
-
- /* Extract the ClientKey that the client calculated from the proof */
- for (i = 0; i < state->key_length; i++)
- ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
-
- /* Hash it one more time, and compare with StoredKey */
- if (scram_H(ClientKey, state->hash_type, state->key_length,
- client_StoredKey, &errstr) < 0)
- elog(ERROR, "could not hash stored key: %s", errstr);
-
- if (memcmp(client_StoredKey, state->StoredKey, state->key_length) != 0)
- return false;
-
- return true;
+ return false;
}
/*
@@ -1409,7 +1455,7 @@ build_server_final_message(scram_state *state)
pg_hmac_ctx *ctx = pg_hmac_create(state->hash_type);
/* calculate ServerSignature */
- if (pg_hmac_init(ctx, state->ServerKey, state->key_length) < 0 ||
+ if (pg_hmac_init(ctx, state->secrets[state->chosen_secret].ServerKey, state->key_length) < 0 ||
pg_hmac_update(ctx,
(uint8 *) state->client_first_message_bare,
strlen(state->client_first_message_bare)) < 0 ||
diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c
index 81dabb9c27..7cc9b13645 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -57,8 +57,7 @@ static void set_authn_id(Port *port, const char *id);
static int CheckPasswordAuth(Port *port, const char **logdetail);
static int CheckPWChallengeAuth(Port *port, const char **logdetail);
-static int CheckMD5Auth(Port *port, char *shadow_pass,
- const char **logdetail);
+static int CheckMD5Auth(Port *port, const char **passwords, int num_passwords, const char **logdetail);
/*----------------------------------------------------------------
@@ -789,8 +788,9 @@ static int
CheckPasswordAuth(Port *port, const char **logdetail)
{
char *passwd;
- int result;
- char *shadow_pass;
+ int result = STATUS_ERROR;
+ int i, num_passwords;
+ char **passwords;
sendAuthRequest(port, AUTH_REQ_PASSWORD, NULL, 0);
@@ -798,17 +798,22 @@ CheckPasswordAuth(Port *port, const char **logdetail)
if (passwd == NULL)
return STATUS_EOF; /* client wouldn't send password */
- shadow_pass = get_role_password(port->user_name, logdetail);
- if (shadow_pass)
+ passwords = get_role_passwords(port->user_name, logdetail, &num_passwords);
+ if (passwords != NULL)
{
- result = plain_crypt_verify(port->user_name, shadow_pass, passwd,
- logdetail);
- }
- else
- result = STATUS_ERROR;
+ for (i = 0; i < num_passwords; i++)
+ {
+ result = plain_crypt_verify(port->user_name, passwords[i], passwd,
+ logdetail);
+ if (result == STATUS_OK)
+ break; /* Found a matching password, no need to try any others */
+ }
+ for (i = 0; i < num_passwords; i++)
+ pfree(passwords[i]);
+
+ pfree(passwords);
+ }
- if (shadow_pass)
- pfree(shadow_pass);
pfree(passwd);
if (result == STATUS_OK)
@@ -823,54 +828,81 @@ CheckPasswordAuth(Port *port, const char **logdetail)
static int
CheckPWChallengeAuth(Port *port, const char **logdetail)
{
- int auth_result;
- char *shadow_pass;
- PasswordType pwtype;
+ bool scram_pw_avail = false;
+ int auth_result = STATUS_ERROR;
+ int i, num_passwords;
+ char **passwords;
+ PasswordType pwtype;
Assert(port->hba->auth_method == uaSCRAM ||
port->hba->auth_method == uaMD5);
- /* First look up the user's password. */
- shadow_pass = get_role_password(port->user_name, logdetail);
+ /* First look up the user's passwords. */
+ passwords = get_role_passwords(port->user_name, logdetail, &num_passwords);
/*
- * If the user does not exist, or has no password or it's expired, we
- * still go through the motions of authentication, to avoid revealing to
+ * If the user does not exist, or has no passwords or they're all expired,
+ * we still go through the motions of authentication, to avoid revealing to
* the client that the user didn't exist. If 'md5' is allowed, we choose
* whether to use 'md5' or 'scram-sha-256' authentication based on current
* password_encryption setting. The idea is that most genuine users
* probably have a password of that type, and if we pretend that this user
* had a password of that type, too, it "blends in" best.
*/
- if (!shadow_pass)
+ if (!passwords)
pwtype = Password_encryption;
- else
- pwtype = get_password_type(shadow_pass);
/*
* If 'md5' authentication is allowed, decide whether to perform 'md5' or
* 'scram-sha-256' authentication based on the type of password the user
- * has. If it's an MD5 hash, we must do MD5 authentication, and if it's a
- * SCRAM secret, we must do SCRAM authentication.
+ * has. If there's a SCRAM password available then we'll do SCRAM, otherwise we
+ * will fall back to trying to use MD5.
*
* If MD5 authentication is not allowed, always use SCRAM. If the user
* had an MD5 password, CheckSASLAuth() with the SCRAM mechanism will
* fail.
*/
- if (port->hba->auth_method == uaMD5 && pwtype == PASSWORD_TYPE_MD5)
- auth_result = CheckMD5Auth(port, shadow_pass, logdetail);
+ if (passwords == NULL)
+ {
+ if (port->hba->auth_method == uaMD5 && pwtype == PASSWORD_TYPE_MD5)
+ auth_result = CheckMD5Auth(port, (const char **) NULL, 0, logdetail);
+ else
+ auth_result = CheckSASLAuth(&pg_be_scram_mech, port,
+ (const char **) NULL, 0, logdetail);
+ }
else
- auth_result = CheckSASLAuth(&pg_be_scram_mech, port, shadow_pass,
- logdetail);
+ {
+ for (i = 0; i < num_passwords; i++)
+ {
+ if (get_password_type(passwords[i]) == PASSWORD_TYPE_SCRAM_SHA_256)
+ {
+ scram_pw_avail = true;
+ break;
+ }
+ }
- if (shadow_pass)
- pfree(shadow_pass);
+ if (port->hba->auth_method == uaMD5 && !scram_pw_avail)
+ auth_result = CheckMD5Auth(port, (const char **) passwords, num_passwords, logdetail);
+ else
+ auth_result = CheckSASLAuth(&pg_be_scram_mech, port, (const char **) passwords, num_passwords,
+ logdetail);
+
+ for (i = 0; i < num_passwords; i++)
+ {
+ if (passwords[i] != NULL)
+ pfree(passwords[i]);
+ else
+ ereport(DEBUG2,
+ (errmsg("Password %d was null", i)));
+ }
+ pfree(passwords);
+ }
/*
- * If get_role_password() returned error, return error, even if the
+ * If get_role_passwords() returned error, return error, even if the
* authentication succeeded.
*/
- if (!shadow_pass)
+ if (!passwords)
{
Assert(auth_result != STATUS_OK);
return STATUS_ERROR;
@@ -883,11 +915,12 @@ CheckPWChallengeAuth(Port *port, const char **logdetail)
}
static int
-CheckMD5Auth(Port *port, char *shadow_pass, const char **logdetail)
+CheckMD5Auth(Port *port, const char **passwords, int num_passwords, const char **logdetail)
{
char md5Salt[4]; /* Password salt */
char *passwd;
- int result;
+ int result = STATUS_ERROR;
+ int i;
/* include the salt to use for computing the response */
if (!pg_strong_random(md5Salt, 4))
@@ -903,12 +936,13 @@ CheckMD5Auth(Port *port, char *shadow_pass, const char **logdetail)
if (passwd == NULL)
return STATUS_EOF; /* client wouldn't send password */
- if (shadow_pass)
- result = md5_crypt_verify(port->user_name, shadow_pass, passwd,
+ for (i = 0; i < num_passwords; i++)
+ {
+ result = md5_crypt_verify(port->user_name, passwords[i], passwd,
md5Salt, 4, logdetail);
- else
- result = STATUS_ERROR;
-
+ if (result == STATUS_OK)
+ break;
+ }
pfree(passwd);
return result;
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index ef496a0bea..3b47af1269 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -27,20 +27,29 @@
/*
- * Fetch stored password for a user, for authentication.
+ * Fetch valid stored passwords for a user, for authentication.
*
* On error, returns NULL, and stores a palloc'd string describing the reason,
* for the postmaster log, in *logdetail. The error reason should *not* be
* sent to the client, to avoid giving away user information!
*/
-char *
-get_role_password(const char *role, const char **logdetail)
+char **
+get_role_passwords(const char *role, const char **logdetail, int *num_passwords)
{
TimestampTz vuntil = 0;
+ TimestampTz second_vuntil = 0;
+ TimestampTz current_ts;
HeapTuple roleTup;
Datum datum;
- bool isnull;
+ Datum second_datum;
+ bool vuntil_isnull;
+ bool second_vuntil_isnull;
+ bool password_isnull;
+ bool second_password_isnull;
char *shadow_pass;
+ char *second_shadow_pass;
+
+ *num_passwords = 0;
/* Get role info from pg_authid */
roleTup = SearchSysCache1(AUTHNAME, PointerGetDatum(role));
@@ -52,34 +61,73 @@ get_role_password(const char *role, const char **logdetail)
}
datum = SysCacheGetAttr(AUTHNAME, roleTup,
- Anum_pg_authid_rolpassword, &isnull);
- if (isnull)
+ Anum_pg_authid_rolpassword,
+ &password_isnull);
+ second_datum = SysCacheGetAttr(AUTHNAME, roleTup,
+ Anum_pg_authid_rolsecondpassword,
+ &second_password_isnull);
+ if (password_isnull && second_password_isnull)
{
ReleaseSysCache(roleTup);
*logdetail = psprintf(_("User \"%s\" has no password assigned."),
role);
return NULL; /* user has no password */
}
- shadow_pass = TextDatumGetCString(datum);
+
+ if (!password_isnull)
+ shadow_pass = TextDatumGetCString(datum);
+ if (!second_password_isnull)
+ second_shadow_pass = TextDatumGetCString(second_datum);
datum = SysCacheGetAttr(AUTHNAME, roleTup,
- Anum_pg_authid_rolvaliduntil, &isnull);
- if (!isnull)
+ Anum_pg_authid_rolvaliduntil, &vuntil_isnull);
+ second_datum = SysCacheGetAttr(AUTHNAME, roleTup,
+ Anum_pg_authid_rolsecondvaliduntil,
+ &second_vuntil_isnull);
+ if (!vuntil_isnull)
vuntil = DatumGetTimestampTz(datum);
+ if (!second_vuntil_isnull)
+ second_vuntil = DatumGetTimestampTz(second_datum);
ReleaseSysCache(roleTup);
/*
* Password OK, but check to be sure we are not past rolvaliduntil
*/
- if (!isnull && vuntil < GetCurrentTimestamp())
+ current_ts = GetCurrentTimestamp();
+ *num_passwords = (!password_isnull &&
+ !vuntil_isnull &&
+ vuntil >= current_ts)
+ + (!second_password_isnull &&
+ !second_vuntil_isnull &&
+ second_vuntil >= current_ts);
+
+ if (*num_passwords >= 1)
+ {
+ int i = 0;
+ char **passwords = palloc(sizeof(char *) * (*num_passwords));
+
+ if (!password_isnull && !vuntil_isnull && vuntil >= current_ts)
+ {
+ passwords[i] = shadow_pass;
+ i++;
+ }
+
+ if (!second_password_isnull && !second_vuntil_isnull &&
+ second_vuntil >= current_ts)
+ {
+ passwords[i] = second_shadow_pass;
+ i++;
+ }
+
+ return passwords;
+ }
+ else
{
*logdetail = psprintf(_("User \"%s\" has an expired password."),
role);
return NULL;
}
-
- return shadow_pass;
}
/*
@@ -113,7 +161,7 @@ get_password_type(const char *shadow_pass)
* hash, so it is stored as it is regardless of the requested type.
*/
char *
-encrypt_password(PasswordType target_type, const char *role,
+encrypt_password(PasswordType target_type, const char *salt,
const char *password)
{
PasswordType guessed_type = get_password_type(password);
@@ -134,13 +182,13 @@ encrypt_password(PasswordType target_type, const char *role,
case PASSWORD_TYPE_MD5:
encrypted_password = palloc(MD5_PASSWD_LEN + 1);
- if (!pg_md5_encrypt(password, role, strlen(role),
+ if (!pg_md5_encrypt(password, salt, strlen(salt),
encrypted_password, &errstr))
elog(ERROR, "password encryption failed: %s", errstr);
return encrypted_password;
case PASSWORD_TYPE_SCRAM_SHA_256:
- return pg_be_scram_build_secret(password);
+ return pg_be_scram_build_secret(password, salt);
case PASSWORD_TYPE_PLAINTEXT:
elog(ERROR, "cannot encrypt password with 'plaintext'");
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index ddcd27469a..966eb4e627 100644
--- a/src/include/libpq/crypt.h
+++ b/src/include/libpq/crypt.h
@@ -35,7 +35,7 @@ extern PasswordType get_password_type(const char *shadow_pass);
extern char *encrypt_password(PasswordType target_type, const char *role,
const char *password);
-extern char *get_role_password(const char *role, const char **logdetail);
+extern char **get_role_passwords(const char *role, const char **logdetail, int *num);
extern int md5_crypt_verify(const char *role, const char *shadow_pass,
const char *client_pass, const char *md5_salt,
diff --git a/src/include/libpq/sasl.h b/src/include/libpq/sasl.h
index 7a1b1ed0a0..12c5c9602b 100644
--- a/src/include/libpq/sasl.h
+++ b/src/include/libpq/sasl.h
@@ -77,7 +77,7 @@ typedef struct pg_be_sasl_mech
* disclosing valid user names.
*---------
*/
- void *(*init) (Port *port, const char *mech, const char *shadow_pass);
+ void *(*init) (Port *port, const char *mech, const char **secrets, const int num_secrets);
/*---------
* exchange()
@@ -131,6 +131,6 @@ typedef struct pg_be_sasl_mech
/* Common implementation for auth.c */
extern int CheckSASLAuth(const pg_be_sasl_mech *mech, Port *port,
- char *shadow_pass, const char **logdetail);
+ const char **passwords, int num_passwords, const char **logdetail);
#endif /* PG_SASL_H */
diff --git a/src/include/libpq/scram.h b/src/include/libpq/scram.h
index 310bc36517..07d9cc3990 100644
--- a/src/include/libpq/scram.h
+++ b/src/include/libpq/scram.h
@@ -25,7 +25,7 @@ extern PGDLLIMPORT int scram_sha_256_iterations;
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, const char *salt);
extern bool parse_scram_secret(const char *secret,
int *iterations,
pg_cryptohash_type *hash_type,
--
2.41.0
From 6e272f44b48ce9749468f6289b23d0f7569f7862 Mon Sep 17 00:00:00 2001
From: Gurjeet Singh <gurjeet@singh.im>
Date: Mon, 9 Oct 2023 11:54:11 -0700
Subject: [PATCH v3 3/3] Added SQL support for ALTER ROLE to manage two
passwords
---
src/backend/commands/user.c | 252 +++++++++++++++++-
src/backend/parser/gram.y | 53 +++-
.../regress/expected/password_rollover.out | 140 ++++++++++
src/test/regress/parallel_schedule | 5 +
src/test/regress/sql/password_rollover.sql | 107 ++++++++
5 files changed, 544 insertions(+), 13 deletions(-)
create mode 100644 src/test/regress/expected/password_rollover.out
create mode 100644 src/test/regress/sql/password_rollover.sql
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 9ad02e4092..4721185e71 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -721,11 +721,16 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
ListCell *option;
char *rolename;
char *password = NULL; /* user password */
+ char *second_password = NULL; /* user's second password */
int connlimit = -1; /* maximum connections allowed */
- char *validUntil = NULL; /* time the login is valid until */
- Datum validUntil_datum; /* same, as timestamptz Datum */
+ char *validUntil = NULL; /* time the password is valid until */
+ Datum validUntil_datum; /* validUntil, as timestamptz Datum */
bool validUntil_null;
+ char *secondValidUntil = NULL;/* time the second password is valid until */
+ Datum secondValidUntil_datum; /* secondValidUntil, as timestamptz Datum */
+ bool secondValidUntil_null;
DefElem *dpassword = NULL;
+ DefElem *dsecondpassword = NULL;
DefElem *dissuper = NULL;
DefElem *dinherit = NULL;
DefElem *dcreaterole = NULL;
@@ -735,10 +740,18 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
DefElem *dconnlimit = NULL;
DefElem *drolemembers = NULL;
DefElem *dvalidUntil = NULL;
+ DefElem *dfirstValidUntil = NULL;
+ DefElem *dsecondValidUntil = NULL;
DefElem *dbypassRLS = NULL;
Oid roleid;
Oid currentUserId = GetUserId();
GrantRoleOptions popt;
+ bool overwriteFirstPassword = false;
+ bool addFirstPassword = false;
+ bool addSecondPassword = false;
+ bool dropFirstPassword = false;
+ bool dropSecondPassword = false;
+ bool dropAllPasswords = false;
check_rolespec_name(stmt->role,
_("Cannot alter reserved roles."));
@@ -750,9 +763,95 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
if (strcmp(defel->defname, "password") == 0)
{
- if (dpassword)
+ if (overwriteFirstPassword || addFirstPassword)
errorConflictingDefElem(defel, pstate);
dpassword = defel;
+ overwriteFirstPassword = true;
+
+ if (dpassword->arg != NULL)
+ {
+ /* PASSWORD 'sometext' syntax was used */
+
+ /*
+ * Adding and dropping passwords in the same command is not
+ * supported.
+ */
+ if (dropFirstPassword || dropSecondPassword || dropAllPasswords)
+ errorConflictingDefElem(defel, pstate);
+ }
+ else
+ {
+ /* PASSWORD NULL syntax was used */
+
+ if (dropFirstPassword)
+ errorConflictingDefElem(defel, pstate);
+
+ /*
+ * Adding and dropping passwords in the same command is not
+ * supported.
+ */
+ if (addFirstPassword || addSecondPassword)
+ errorConflictingDefElem(defel, pstate);
+
+ dropFirstPassword = true;
+ }
+ }
+ else if (strcmp(defel->defname, "add-first-password") == 0)
+ {
+ if (addFirstPassword || overwriteFirstPassword)
+ errorConflictingDefElem(defel, pstate);
+ dpassword = defel;
+ addFirstPassword = true;
+
+ /*
+ * Adding and dropping passwords in the same command is not
+ * supported.
+ */
+ if (dropFirstPassword || dropSecondPassword || dropAllPasswords)
+ errorConflictingDefElem(defel, pstate);
+ }
+ else if (strcmp(defel->defname, "add-second-password") == 0)
+ {
+ if (dsecondpassword)
+ errorConflictingDefElem(defel, pstate);
+ dsecondpassword = defel;
+ addSecondPassword = true;
+ /*
+ * Adding and dropping passwords in the same command is not
+ * supported.
+ */
+ if (dropFirstPassword || dropSecondPassword || dropAllPasswords)
+ errorConflictingDefElem(defel, pstate);
+ }
+ else if (strcmp(defel->defname, "drop-password") == 0)
+ {
+ char *which = strVal(defel->arg);
+
+ if (strcmp(which, "first") == 0)
+ {
+ if (dropFirstPassword || dropAllPasswords)
+ errorConflictingDefElem(defel, pstate);
+ dropFirstPassword = true;
+ }
+ else if (strcmp(which, "second") == 0)
+ {
+ if (dropSecondPassword || dropAllPasswords)
+ errorConflictingDefElem(defel, pstate);
+ dropSecondPassword = true;
+ }
+ else
+ {
+ if (dropAllPasswords || dropFirstPassword || dropSecondPassword)
+ errorConflictingDefElem(defel, pstate);
+ dropAllPasswords = true;
+ }
+
+ /*
+ * Adding and dropping passwords in the same command is not
+ * supported.
+ */
+ if (addFirstPassword || addSecondPassword || overwriteFirstPassword)
+ errorConflictingDefElem(defel, pstate);
}
else if (strcmp(defel->defname, "superuser") == 0)
{
@@ -809,6 +908,18 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
errorConflictingDefElem(defel, pstate);
dvalidUntil = defel;
}
+ else if (strcmp(defel->defname, "first-password-valid-until") == 0)
+ {
+ if (dfirstValidUntil)
+ errorConflictingDefElem(defel, pstate);
+ dfirstValidUntil = defel;
+ }
+ else if (strcmp(defel->defname, "second-password-valid-until") == 0)
+ {
+ if (dsecondValidUntil)
+ errorConflictingDefElem(defel, pstate);
+ dsecondValidUntil = defel;
+ }
else if (strcmp(defel->defname, "bypassrls") == 0)
{
if (dbypassRLS)
@@ -822,6 +933,8 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
if (dpassword && dpassword->arg)
password = strVal(dpassword->arg);
+ if (dsecondpassword)
+ second_password = strVal(dsecondpassword->arg);
if (dconnlimit)
{
connlimit = intVal(dconnlimit->arg);
@@ -830,8 +943,30 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("invalid connection limit: %d", connlimit)));
}
+
+ /*
+ * Disallow mixing VALID UNTIL with ADD FIRST/SECOND PASSWORD.
+ *
+ * VALID UNTIL and FIRST PASSWORD VALID UNTIL are functionally identical,
+ * but we track them separately to prevent the confusing invocation like the
+ * following.
+ *
+ * ALTER ROLE x ADD SECOND PASSWORD 'y' VALID UNTIL '2020/01/01';
+ *
+ * In the above command the user may expect the expiration of the _second_
+ * password to be set to '2020/01/01', but it will lead to second password's
+ * expiration set to NULL and first password's expiration set to
+ * '2020/01/01', because a plain VALIF UNTIL applies to the _first_
+ * password.
+ */
+ if (dvalidUntil && (addFirstPassword || addSecondPassword))
+ errorConflictingDefElem(dvalidUntil, pstate);
+ dvalidUntil = dfirstValidUntil;
+
if (dvalidUntil)
validUntil = strVal(dvalidUntil->arg);
+ if (dsecondValidUntil)
+ secondValidUntil = strVal(dsecondValidUntil->arg);
/*
* Scan the pg_authid relation to be certain the user exists.
@@ -867,7 +1002,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
{
/* things an unprivileged user certainly can't do */
if (dinherit || dcreaterole || dcreatedb || dcanlogin || dconnlimit ||
- dvalidUntil || disreplication || dbypassRLS)
+ dvalidUntil || dsecondValidUntil || disreplication || dbypassRLS)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied to alter role"),
@@ -875,7 +1010,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
"CREATEROLE", "ADMIN", rolename)));
/* an unprivileged user can change their own password */
- if (dpassword && roleid != currentUserId)
+ if ((dpassword || dsecondpassword) && roleid != currentUserId)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied to alter role"),
@@ -934,15 +1069,42 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
&validUntil_null);
}
+ /* Convert secondvaliduntil to internal form */
+ if (dsecondValidUntil)
+ {
+ secondValidUntil_datum = DirectFunctionCall3(timestamptz_in,
+ CStringGetDatum(secondValidUntil),
+ ObjectIdGetDatum(InvalidOid),
+ Int32GetDatum(-1));
+ secondValidUntil_null = false;
+ }
+ else
+ {
+ /* fetch existing setting in case hook needs it */
+ secondValidUntil_datum = SysCacheGetAttr(AUTHNAME, tuple,
+ Anum_pg_authid_rolsecondvaliduntil,
+ &secondValidUntil_null);
+ }
+
/*
* Call the password checking hook if there is one defined
*/
- if (check_password_hook && password)
- (*check_password_hook) (rolename,
- password,
- get_password_type(password),
- validUntil_datum,
- validUntil_null);
+ if (check_password_hook)
+ {
+ if (password)
+ (*check_password_hook) (rolename,
+ password,
+ get_password_type(password),
+ validUntil_datum,
+ validUntil_null);
+
+ if (second_password)
+ (*check_password_hook) (rolename,
+ second_password,
+ get_password_type(second_password),
+ secondValidUntil_datum,
+ secondValidUntil_null);
+ }
/*
* Build an updated tuple, perusing the information just obtained
@@ -1008,6 +1170,20 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
char *shadow_pass;
const char *logdetail = NULL;
+ if (addFirstPassword)
+ {
+ bool firstPassword_null;
+
+ SysCacheGetAttr(AUTHNAME, tuple,
+ Anum_pg_authid_rolpassword,
+ &firstPassword_null);
+
+ if (!firstPassword_null)
+ ereport(ERROR,
+ (errmsg("'first' password is already in use"),
+ errdetail("Use ALTER ROLE DROP FIRST PASSWORD")));
+ }
+
/* Like in CREATE USER, don't allow an empty password. */
if (password[0] == '\0' ||
plain_crypt_verify(rolename, password, "", &logdetail) == STATUS_OK)
@@ -1034,17 +1210,69 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
}
+ /* second password */
+ if (second_password)
+ {
+ char *shadow_pass;
+ const char *logdetail = NULL;
+ bool secondPassword_null;
+
+ SysCacheGetAttr(AUTHNAME, tuple,
+ Anum_pg_authid_rolsecondpassword,
+ &secondPassword_null);
+
+ if (!secondPassword_null)
+ ereport(ERROR,
+ (errmsg("'second' password is already in use"),
+ errdetail("Use ALTER ROLE DROP SECOND PASSWORD")));
+
+ /* Like in CREATE USER, don't allow an empty password. */
+ if (second_password[0] == '\0' ||
+ plain_crypt_verify(rolename, second_password, "", &logdetail) == STATUS_OK)
+ {
+ ereport(NOTICE,
+ (errmsg("empty string is not a valid password, clearing password")));
+ new_record_nulls[Anum_pg_authid_rolsecondpassword - 1] = true;
+ }
+ else
+ {
+ char *salt;
+
+ if (!get_salt(rolename, &salt, &logdetail))
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("could not get a valid salt for password"),
+ errdetail("%s", logdetail)));
+
+ /* Encrypt the password to the requested format. */
+ shadow_pass = encrypt_password(Password_encryption, salt, second_password);
+ new_record[Anum_pg_authid_rolsecondpassword - 1] =
+ CStringGetTextDatum(shadow_pass);
+ }
+ new_record_repl[Anum_pg_authid_rolsecondpassword - 1] = true;
+ }
+
/* unset password */
- if (dpassword && dpassword->arg == NULL)
+ if (dropFirstPassword || dropAllPasswords)
{
new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
}
+ if (dropSecondPassword || dropAllPasswords)
+ {
+ new_record_repl[Anum_pg_authid_rolsecondpassword - 1] = true;
+ new_record_nulls[Anum_pg_authid_rolsecondpassword - 1] = true;
+ }
+
/* valid until */
new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum;
new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null;
new_record_repl[Anum_pg_authid_rolvaliduntil - 1] = true;
+ /* second password valid until */
+ new_record[Anum_pg_authid_rolsecondvaliduntil - 1] = secondValidUntil_datum;
+ new_record_nulls[Anum_pg_authid_rolsecondvaliduntil - 1] = secondValidUntil_null;
+ new_record_repl[Anum_pg_authid_rolsecondvaliduntil - 1] = true;
if (dbypassRLS)
{
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e56cbe77cb..6447ac4056 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -361,7 +361,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> opt_nowait_or_skip
%type <list> OptRoleList AlterOptRoleList
-%type <defelt> CreateOptRoleElem AlterOptRoleElem
+%type <defelt> CreateOptRoleElem AlterOptRoleElem AlterOnlyOptRoleElem
+%type <boolean> OptFirstOrSecond
%type <str> opt_type
%type <str> foreign_server_version opt_foreign_server_version
@@ -1168,6 +1169,7 @@ OptRoleList:
AlterOptRoleList:
AlterOptRoleList AlterOptRoleElem { $$ = lappend($1, $2); }
+ | AlterOptRoleList AlterOnlyOptRoleElem { $$ = lappend($1, $2); }
| /* EMPTY */ { $$ = NIL; }
;
@@ -1263,6 +1265,55 @@ AlterOptRoleElem:
}
;
+OptFirstOrSecond:
+ FIRST_P { $$ = true; }
+ | SECOND_P { $$ = false; }
+ ;
+
+/*
+ * AlterOnlyOptRoleElem is separate from AlterOptRoleElem because these options
+ * are not available to the CREATE ROLE command.
+ */
+AlterOnlyOptRoleElem:
+ ADD_P OptFirstOrSecond PASSWORD Sconst
+ {
+ bool first = $2;
+
+ if (first)
+ $$ = makeDefElem("add-first-password",
+ (Node *) makeString($4), @1);
+ else
+ $$ = makeDefElem("add-second-password",
+ (Node *) makeString($4), @1);
+ }
+ | DROP OptFirstOrSecond PASSWORD
+ {
+ bool first = $2;
+
+ if (first)
+ $$ = makeDefElem("drop-password",
+ (Node *) makeString("first"), @1);
+ else
+ $$ = makeDefElem("drop-password",
+ (Node *) makeString("second"), @1);
+ }
+ | DROP ALL PASSWORD
+ {
+ $$ = makeDefElem("drop-all-password", (Node *) NULL, @1);
+ }
+ | OptFirstOrSecond PASSWORD VALID UNTIL Sconst
+ {
+ bool first = $1;
+
+ if (first)
+ $$ = makeDefElem("first-password-valid-until",
+ (Node *) makeString($5), @1);
+ else
+ $$ = makeDefElem("second-password-valid-until",
+ (Node *) makeString($5), @1);
+ }
+ ;
+
CreateOptRoleElem:
AlterOptRoleElem { $$ = $1; }
/* The following are not supported by ALTER ROLE/USER/GROUP */
diff --git a/src/test/regress/expected/password_rollover.out b/src/test/regress/expected/password_rollover.out
new file mode 100644
index 0000000000..bad6d01b61
--- /dev/null
+++ b/src/test/regress/expected/password_rollover.out
@@ -0,0 +1,140 @@
+--
+-- Tests for password rollovers
+--
+SET password_encryption = 'md5';
+-- Create a user, as usual
+CREATE ROLE regress_password_rollover1 PASSWORD 'p1' LOGIN;
+-- the rolpassword field should be non-null, and others should be null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+ rolname | rolpassword | rolvaliduntil | rolsecondpassword | rolsecondvaliduntil
+----------------------------+-------------------------------------+---------------+-------------------+---------------------
+ regress_password_rollover1 | md54ec11153dc2e0022e0d556740a238e94 | | |
+(1 row)
+
+-- Add another password that the user can use for authentication.
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p2';
+-- the rolpassword and rolsecondpassword fields should be non-null, and others should be null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+ rolname | rolpassword | rolvaliduntil | rolsecondpassword | rolsecondvaliduntil
+----------------------------+-------------------------------------+---------------+-------------------------------------+---------------------
+ regress_password_rollover1 | md54ec11153dc2e0022e0d556740a238e94 | | md5c72e860974ea678511e200ded12780b6 |
+(1 row)
+
+-- Set second password's expiration time.
+ALTER ROLE regress_password_rollover1 SECOND PASSWORD VALID UNTIL '2021/01/01';
+-- the rolvaliduntil field should be null, and other should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+ rolname | rolpassword | rolvaliduntil | rolsecondpassword | rolsecondvaliduntil
+----------------------------+-------------------------------------+---------------+-------------------------------------+------------------------------
+ regress_password_rollover1 | md54ec11153dc2e0022e0d556740a238e94 | | md5c72e860974ea678511e200ded12780b6 | Fri Jan 01 00:00:00 2021 PST
+(1 row)
+
+ALTER ROLE regress_password_rollover1 FIRST PASSWORD VALID UNTIL '2022/01/01';
+-- All fields should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+ rolname | rolpassword | rolvaliduntil | rolsecondpassword | rolsecondvaliduntil
+----------------------------+-------------------------------------+------------------------------+-------------------------------------+------------------------------
+ regress_password_rollover1 | md54ec11153dc2e0022e0d556740a238e94 | Sat Jan 01 00:00:00 2022 PST | md5c72e860974ea678511e200ded12780b6 | Fri Jan 01 00:00:00 2021 PST
+(1 row)
+
+-- Setting a password to null does not set its expiration time to null
+ALTER ROLE regress_password_rollover1 PASSWORD NULL;
+-- the rolpassword field should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+ rolname | rolpassword | rolvaliduntil | rolsecondpassword | rolsecondvaliduntil
+----------------------------+-------------+------------------------------+-------------------------------------+------------------------------
+ regress_password_rollover1 | | Sat Jan 01 00:00:00 2022 PST | md5c72e860974ea678511e200ded12780b6 | Fri Jan 01 00:00:00 2021 PST
+(1 row)
+
+-- If, for some reason, the user wants to get rid of the latest password added.
+ALTER ROLE regress_password_rollover1 DROP SECOND PASSWORD;
+-- the rolpassword and rolsecondpassword fields should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+ rolname | rolpassword | rolvaliduntil | rolsecondpassword | rolsecondvaliduntil
+----------------------------+-------------+------------------------------+-------------------+------------------------------
+ regress_password_rollover1 | | Sat Jan 01 00:00:00 2022 PST | | Fri Jan 01 00:00:00 2021 PST
+(1 row)
+
+-- Add a new password in 'second' slot
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p3' SECOND PASSWORD VALID UNTIL '2023/01/01';
+-- the rolpassword field should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+ rolname | rolpassword | rolvaliduntil | rolsecondpassword | rolsecondvaliduntil
+----------------------------+-------------+------------------------------+-------------------------------------+------------------------------
+ regress_password_rollover1 | | Sat Jan 01 00:00:00 2022 PST | md53dff5d9eee2beb63399f1900a2371fcb | Sun Jan 01 00:00:00 2023 PST
+(1 row)
+
+-- VALID UNTIL must not be allowed when ADDing a password, to avoid the
+-- confusing invocation where the command may seem to do one thing but actually
+-- does something else. The following may seem like it will add a 'second'
+-- password with a new expiration, but, if allowed, this will set the expiration
+-- time on the _first_ password.
+ALTER USER regress_password_rollover1 ADD SECOND PASSWORD 'p4' VALID UNTIL '2023/01/01';
+ERROR: conflicting or redundant options
+LINE 1: ...gress_password_rollover1 ADD SECOND PASSWORD 'p4' VALID UNTI...
+ ^
+-- Even though both, the password and the expiration, refer to the first
+-- password, we disallow it to be consistent with the previous command's
+-- behaviour.
+ALTER USER regress_password_rollover1 ADD FIRST PASSWORD 'p4' VALID UNTIL '2023/01/01';
+ERROR: conflicting or redundant options
+LINE 1: ...egress_password_rollover1 ADD FIRST PASSWORD 'p4' VALID UNTI...
+ ^
+-- Set the first password
+ALTER ROLE regress_password_rollover1 ADD FIRST PASSWORD 'p5';
+-- Attempting to add a password while the respective slot is occupied
+-- results in error
+ALTER ROLE regress_password_rollover1 ADD FIRST PASSWORD 'p6';
+ERROR: 'first' password is already in use
+DETAIL: Use ALTER ROLE DROP FIRST PASSWORD
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p6';
+ERROR: 'second' password is already in use
+DETAIL: Use ALTER ROLE DROP SECOND PASSWORD
+ALTER ROLE regress_password_rollover1 DROP SECOND PASSWORD;
+-- the rolsecondpassword field should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+ rolname | rolpassword | rolvaliduntil | rolsecondpassword | rolsecondvaliduntil
+----------------------------+-------------------------------------+------------------------------+-------------------+------------------------------
+ regress_password_rollover1 | md5cc8c5dac5560a2fead71cfba4625a2c7 | Sat Jan 01 00:00:00 2022 PST | | Sun Jan 01 00:00:00 2023 PST
+(1 row)
+
+-- Use scram-sha-256 for password storage
+SET password_encryption = 'scram-sha-256';
+ALTER USER regress_password_rollover1 ADD SECOND PASSWORD 'p7'
+ SECOND PASSWORD VALID UNTIL 'Infinity';
+-- the rolsecondpassword field should now contain a SCRAM secret
+SELECT rolname, rolpassword, rolvaliduntil, regexp_replace(rolsecondpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolsecondpassword_masked, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+ rolname | rolpassword | rolvaliduntil | rolsecondpassword_masked | rolsecondvaliduntil
+----------------------------+-------------------------------------+------------------------------+---------------------------------------------------+---------------------
+ regress_password_rollover1 | md5cc8c5dac5560a2fead71cfba4625a2c7 | Sat Jan 01 00:00:00 2022 PST | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> | infinity
+(1 row)
+
+-- Drop the less secure, md5, password
+ALTER USER regress_password_rollover1 DROP FIRST PASSWORD;
+-- the rolpassword field should now be null
+SELECT rolname, rolpassword, rolvaliduntil, regexp_replace(rolsecondpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolsecondpassword_masked, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+ rolname | rolpassword | rolvaliduntil | rolsecondpassword_masked | rolsecondvaliduntil
+----------------------------+-------------+------------------------------+---------------------------------------------------+---------------------
+ regress_password_rollover1 | | Sat Jan 01 00:00:00 2022 PST | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> | infinity
+(1 row)
+
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 4df9d8503b..5efad7f3ad 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -68,6 +68,11 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
# ----------
test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator password identity generated join_hash
+# ----------
+# Another group of parallel tests
+# ----------
+test: password_rollover
+
# ----------
# Additional BRIN tests
# ----------
diff --git a/src/test/regress/sql/password_rollover.sql b/src/test/regress/sql/password_rollover.sql
new file mode 100644
index 0000000000..73a42f97ab
--- /dev/null
+++ b/src/test/regress/sql/password_rollover.sql
@@ -0,0 +1,107 @@
+--
+-- Tests for password rollovers
+--
+
+SET password_encryption = 'md5';
+
+-- Create a user, as usual
+CREATE ROLE regress_password_rollover1 PASSWORD 'p1' LOGIN;
+
+-- the rolpassword field should be non-null, and others should be null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+
+-- Add another password that the user can use for authentication.
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p2';
+
+-- the rolpassword and rolsecondpassword fields should be non-null, and others should be null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+
+-- Set second password's expiration time.
+ALTER ROLE regress_password_rollover1 SECOND PASSWORD VALID UNTIL '2021/01/01';
+
+-- the rolvaliduntil field should be null, and other should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+
+ALTER ROLE regress_password_rollover1 FIRST PASSWORD VALID UNTIL '2022/01/01';
+
+-- All fields should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+
+-- Setting a password to null does not set its expiration time to null
+ALTER ROLE regress_password_rollover1 PASSWORD NULL;
+
+-- the rolpassword field should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+
+-- If, for some reason, the user wants to get rid of the latest password added.
+ALTER ROLE regress_password_rollover1 DROP SECOND PASSWORD;
+
+-- the rolpassword and rolsecondpassword fields should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+
+-- Add a new password in 'second' slot
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p3' SECOND PASSWORD VALID UNTIL '2023/01/01';
+
+-- the rolpassword field should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+
+-- VALID UNTIL must not be allowed when ADDing a password, to avoid the
+-- confusing invocation where the command may seem to do one thing but actually
+-- does something else. The following may seem like it will add a 'second'
+-- password with a new expiration, but, if allowed, this will set the expiration
+-- time on the _first_ password.
+ALTER USER regress_password_rollover1 ADD SECOND PASSWORD 'p4' VALID UNTIL '2023/01/01';
+
+-- Even though both, the password and the expiration, refer to the first
+-- password, we disallow it to be consistent with the previous command's
+-- behaviour.
+ALTER USER regress_password_rollover1 ADD FIRST PASSWORD 'p4' VALID UNTIL '2023/01/01';
+
+-- Set the first password
+ALTER ROLE regress_password_rollover1 ADD FIRST PASSWORD 'p5';
+
+-- Attempting to add a password while the respective slot is occupied
+-- results in error
+ALTER ROLE regress_password_rollover1 ADD FIRST PASSWORD 'p6';
+
+ALTER ROLE regress_password_rollover1 ADD SECOND PASSWORD 'p6';
+
+ALTER ROLE regress_password_rollover1 DROP SECOND PASSWORD;
+
+-- the rolsecondpassword field should be null, and others should be non-null
+SELECT rolname, rolpassword, rolvaliduntil, rolsecondpassword, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+
+-- Use scram-sha-256 for password storage
+SET password_encryption = 'scram-sha-256';
+
+ALTER USER regress_password_rollover1 ADD SECOND PASSWORD 'p7'
+ SECOND PASSWORD VALID UNTIL 'Infinity';
+
+-- the rolsecondpassword field should now contain a SCRAM secret
+SELECT rolname, rolpassword, rolvaliduntil, regexp_replace(rolsecondpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolsecondpassword_masked, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
+
+-- Drop the less secure, md5, password
+ALTER USER regress_password_rollover1 DROP FIRST PASSWORD;
+
+-- the rolpassword field should now be null
+SELECT rolname, rolpassword, rolvaliduntil, regexp_replace(rolsecondpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolsecondpassword_masked, rolsecondvaliduntil
+ FROM pg_authid
+ WHERE rolname LIKE 'regress_password_rollover%';
--
2.41.0
