password_rollover_v3.diff
application/octet-stream
Filename: password_rollover_v3.diff
Type: application/octet-stream
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: unified
Series: patch v3
| File | + | − |
|---|---|---|
| .cirrus.star | 1 | 1 |
| contrib/citext/expected/citext_utf8_1.out | 1 | 1 |
| contrib/citext/expected/citext_utf8.out | 1 | 1 |
| contrib/citext/sql/citext_utf8.sql | 1 | 1 |
| doc/src/sgml/logical-replication.sgml | 1 | 1 |
| doc/src/sgml/ref/create_subscription.sgml | 1 | 1 |
| src/backend/access/nbtree/nbtsearch.c | 2 | 2 |
| src/backend/access/transam/xlogreader.c | 1 | 1 |
| src/backend/commands/user.c | 347 | 21 |
| src/backend/executor/nodeHashjoin.c | 1 | 1 |
| src/backend/libpq/auth.c | 74 | 41 |
| src/backend/libpq/auth-sasl.c | 2 | 2 |
| src/backend/libpq/auth-scram.c | 128 | 82 |
| src/backend/libpq/crypt.c | 63 | 15 |
| src/backend/optimizer/path/allpaths.c | 325 | 3 |
| src/backend/optimizer/util/pathnode.c | 1 | 17 |
| src/backend/parser/gram.y | 52 | 1 |
| src/backend/utils/adt/array_userfuncs.c | 12 | 12 |
| src/backend/utils/adt/numeric.c | 24 | 24 |
| src/backend/utils/adt/varlena.c | 6 | 6 |
| src/backend/utils/misc/guc_tables.c | 1 | 0 |
| src/include/catalog/pg_authid.dat | 30 | 15 |
| src/include/catalog/pg_authid.h | 2 | 0 |
| src/include/commands/user.h | 1 | 0 |
| src/include/libpq/crypt.h | 1 | 1 |
| src/include/libpq/sasl.h | 2 | 2 |
| src/include/libpq/scram.h | 1 | 1 |
| src/include/optimizer/paths.h | 4 | 0 |
| src/test/regress/expected/password_rollover.out | 140 | 0 |
| src/test/regress/expected/tuplesort.out | 2 | 2 |
| src/test/regress/parallel_schedule | 5 | 0 |
| src/test/regress/sql/password_rollover.sql | 107 | 0 |
| src/test/regress/sql/tuplesort.sql | 2 | 2 |
diff --git a/.cirrus.star b/.cirrus.star
index 36233872d1..d2d6ceca20 100644
--- a/.cirrus.star
+++ b/.cirrus.star
@@ -46,7 +46,7 @@ def main():
def config_from(config_src):
"""return contents of config file `config_src`, surrounded by markers
- indicating start / end of the included file
+ indicating start / end of the the included file
"""
config_contents = fs.read(config_src)
diff --git a/contrib/citext/expected/citext_utf8.out b/contrib/citext/expected/citext_utf8.out
index 5d988dcd48..6630e09a4d 100644
--- a/contrib/citext/expected/citext_utf8.out
+++ b/contrib/citext/expected/citext_utf8.out
@@ -2,7 +2,7 @@
* This test must be run in a database with UTF-8 encoding
* and a Unicode-aware locale.
*
- * Also disable this file for ICU, because the test for the
+ * Also disable this file for ICU, because the test for the the
* Turkish dotted I is not correct for many ICU locales. citext always
* uses the default collation, so it's not easy to restrict the test
* to the "tr-TR-x-icu" collation where it will succeed.
diff --git a/contrib/citext/expected/citext_utf8_1.out b/contrib/citext/expected/citext_utf8_1.out
index 7065a5da19..3caa7a00d4 100644
--- a/contrib/citext/expected/citext_utf8_1.out
+++ b/contrib/citext/expected/citext_utf8_1.out
@@ -2,7 +2,7 @@
* This test must be run in a database with UTF-8 encoding
* and a Unicode-aware locale.
*
- * Also disable this file for ICU, because the test for the
+ * Also disable this file for ICU, because the test for the the
* Turkish dotted I is not correct for many ICU locales. citext always
* uses the default collation, so it's not easy to restrict the test
* to the "tr-TR-x-icu" collation where it will succeed.
diff --git a/contrib/citext/sql/citext_utf8.sql b/contrib/citext/sql/citext_utf8.sql
index 34b232d64e..1f51df134b 100644
--- a/contrib/citext/sql/citext_utf8.sql
+++ b/contrib/citext/sql/citext_utf8.sql
@@ -2,7 +2,7 @@
* This test must be run in a database with UTF-8 encoding
* and a Unicode-aware locale.
*
- * Also disable this file for ICU, because the test for the
+ * Also disable this file for ICU, because the test for the the
* Turkish dotted I is not correct for many ICU locales. citext always
* uses the default collation, so it's not easy to restrict the test
* to the "tr-TR-x-icu" collation where it will succeed.
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 3b2fa1129e..fbf8ad669e 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -1788,7 +1788,7 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
</para>
<para>
- To create a subscription, the user must have the privileges of
+ To create a subscription, the user must have the privileges of the
the <literal>pg_create_subscription</literal> role, as well as
<literal>CREATE</literal> privileges on the database.
</para>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index c1bafbfa06..71652fd918 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -51,7 +51,7 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
</para>
<para>
- To be able to create a subscription, you must have the privileges of
+ To be able to create a subscription, you must have the privileges of the
the <literal>pg_create_subscription</literal> role, as well as
<literal>CREATE</literal> privileges on the current database.
</para>
diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c
index efc5284e5b..72b052b249 100644
--- a/src/backend/access/nbtree/nbtsearch.c
+++ b/src/backend/access/nbtree/nbtsearch.c
@@ -1664,7 +1664,7 @@ _bt_readpage(IndexScanDesc scan, ScanDirection dir, OffsetNumber offnum)
/*
* If the result of prechecking required keys was true, then in
- * assert-enabled builds we also recheck that the _bt_checkkeys()
+ * assert-enabled builds we also recheck that _bt_checkkeys()
* result is the same.
*/
Assert(!requiredMatchedByPrecheck ||
@@ -1783,7 +1783,7 @@ _bt_readpage(IndexScanDesc scan, ScanDirection dir, OffsetNumber offnum)
/*
* If the result of prechecking required keys was true, then in
- * assert-enabled builds we also recheck that the _bt_checkkeys()
+ * assert-enabled builds we also recheck that _bt_checkkeys()
* result is the same.
*/
Assert(!requiredMatchedByPrecheck ||
diff --git a/src/backend/access/transam/xlogreader.c b/src/backend/access/transam/xlogreader.c
index e0baa86bd3..a1363e3b8f 100644
--- a/src/backend/access/transam/xlogreader.c
+++ b/src/backend/access/transam/xlogreader.c
@@ -846,7 +846,7 @@ restart:
/*
* If we got here without a DecodedXLogRecord, it means we needed to
- * validate total_len before trusting it, but by now we've done that.
+ * validate total_len before trusting it, but by now now we've done that.
*/
if (decoded == NULL)
{
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index ce77a055e5..4721185e71 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,19 +524,28 @@ 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
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);
/*
@@ -630,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;
@@ -644,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."));
@@ -659,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)
{
@@ -718,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)
@@ -731,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);
@@ -739,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.
@@ -768,7 +994,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() ||
@@ -776,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"),
@@ -784,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"),
@@ -843,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
@@ -917,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)
@@ -927,26 +1194,85 @@ 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);
}
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/executor/nodeHashjoin.c b/src/backend/executor/nodeHashjoin.c
index 25a2d78f15..aea44a9d56 100644
--- a/src/backend/executor/nodeHashjoin.c
+++ b/src/backend/executor/nodeHashjoin.c
@@ -1306,7 +1306,7 @@ ExecParallelHashJoinNewBatch(HashJoinState *hjstate)
* The data recorded in the file for each tuple is its hash value,
* then the tuple in MinimalTuple format.
*
- * fileptr points to a batch file in one of the hashtable arrays.
+ * fileptr points to a batch file in one of the the hashtable arrays.
*
* The batch files (and their buffers) are allocated in the spill context
* created for the hashtable.
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..c6a90939cb 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,21 @@ 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)
- {
- result = plain_crypt_verify(port->user_name, shadow_pass, passwd,
- logdetail);
- }
- else
- result = STATUS_ERROR;
+ passwords = get_role_passwords(port->user_name, logdetail, &num_passwords);
+ if (passwords != NULL) {
+ 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 +827,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 +914,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 +935,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/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 7af001feaa..4012901626 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -563,7 +563,7 @@ set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
set_cheapest(rel);
#ifdef OPTIMIZER_DEBUG
- pprint(rel);
+ debug_print_rel(root, rel);
#endif
}
@@ -3504,7 +3504,7 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
set_cheapest(rel);
#ifdef OPTIMIZER_DEBUG
- pprint(rel);
+ debug_print_rel(root, rel);
#endif
}
}
@@ -4372,7 +4372,7 @@ generate_partitionwise_join_paths(PlannerInfo *root, RelOptInfo *rel)
continue;
#ifdef OPTIMIZER_DEBUG
- pprint(child_rel);
+ debug_print_rel(root, child_rel);
#endif
live_children = lappend(live_children, child_rel);
@@ -4389,3 +4389,325 @@ generate_partitionwise_join_paths(PlannerInfo *root, RelOptInfo *rel)
add_paths_to_append_rel(root, rel, live_children);
list_free(live_children);
}
+
+
+/*****************************************************************************
+ * DEBUG SUPPORT
+ *****************************************************************************/
+
+#ifdef OPTIMIZER_DEBUG
+
+static void
+print_relids(PlannerInfo *root, Relids relids)
+{
+ int x;
+ bool first = true;
+
+ x = -1;
+ while ((x = bms_next_member(relids, x)) >= 0)
+ {
+ if (!first)
+ printf(" ");
+ if (x < root->simple_rel_array_size &&
+ root->simple_rte_array[x])
+ printf("%s", root->simple_rte_array[x]->eref->aliasname);
+ else
+ printf("%d", x);
+ first = false;
+ }
+}
+
+static void
+print_restrictclauses(PlannerInfo *root, List *clauses)
+{
+ ListCell *l;
+
+ foreach(l, clauses)
+ {
+ RestrictInfo *c = lfirst(l);
+
+ print_expr((Node *) c->clause, root->parse->rtable);
+ if (lnext(clauses, l))
+ printf(", ");
+ }
+}
+
+static void
+print_path(PlannerInfo *root, Path *path, int indent)
+{
+ const char *ptype;
+ bool join = false;
+ Path *subpath = NULL;
+ int i;
+
+ switch (nodeTag(path))
+ {
+ case T_Path:
+ switch (path->pathtype)
+ {
+ case T_SeqScan:
+ ptype = "SeqScan";
+ break;
+ case T_SampleScan:
+ ptype = "SampleScan";
+ break;
+ case T_FunctionScan:
+ ptype = "FunctionScan";
+ break;
+ case T_TableFuncScan:
+ ptype = "TableFuncScan";
+ break;
+ case T_ValuesScan:
+ ptype = "ValuesScan";
+ break;
+ case T_CteScan:
+ ptype = "CteScan";
+ break;
+ case T_NamedTuplestoreScan:
+ ptype = "NamedTuplestoreScan";
+ break;
+ case T_Result:
+ ptype = "Result";
+ break;
+ case T_WorkTableScan:
+ ptype = "WorkTableScan";
+ break;
+ default:
+ ptype = "???Path";
+ break;
+ }
+ break;
+ case T_IndexPath:
+ ptype = "IdxScan";
+ break;
+ case T_BitmapHeapPath:
+ ptype = "BitmapHeapScan";
+ break;
+ case T_BitmapAndPath:
+ ptype = "BitmapAndPath";
+ break;
+ case T_BitmapOrPath:
+ ptype = "BitmapOrPath";
+ break;
+ case T_TidPath:
+ ptype = "TidScan";
+ break;
+ case T_TidRangePath:
+ ptype = "TidRangePath";
+ break;
+ case T_SubqueryScanPath:
+ ptype = "SubqueryScan";
+ break;
+ case T_ForeignPath:
+ ptype = "ForeignScan";
+ break;
+ case T_CustomPath:
+ ptype = "CustomScan";
+ break;
+ case T_NestPath:
+ ptype = "NestLoop";
+ join = true;
+ break;
+ case T_MergePath:
+ ptype = "MergeJoin";
+ join = true;
+ break;
+ case T_HashPath:
+ ptype = "HashJoin";
+ join = true;
+ break;
+ case T_AppendPath:
+ ptype = "Append";
+ break;
+ case T_MergeAppendPath:
+ ptype = "MergeAppend";
+ break;
+ case T_GroupResultPath:
+ ptype = "GroupResult";
+ break;
+ case T_MaterialPath:
+ ptype = "Material";
+ subpath = ((MaterialPath *) path)->subpath;
+ break;
+ case T_MemoizePath:
+ ptype = "Memoize";
+ subpath = ((MemoizePath *) path)->subpath;
+ break;
+ case T_UniquePath:
+ ptype = "Unique";
+ subpath = ((UniquePath *) path)->subpath;
+ break;
+ case T_GatherPath:
+ ptype = "Gather";
+ subpath = ((GatherPath *) path)->subpath;
+ break;
+ case T_GatherMergePath:
+ ptype = "GatherMerge";
+ subpath = ((GatherMergePath *) path)->subpath;
+ break;
+ case T_ProjectionPath:
+ ptype = "Projection";
+ subpath = ((ProjectionPath *) path)->subpath;
+ break;
+ case T_ProjectSetPath:
+ ptype = "ProjectSet";
+ subpath = ((ProjectSetPath *) path)->subpath;
+ break;
+ case T_SortPath:
+ ptype = "Sort";
+ subpath = ((SortPath *) path)->subpath;
+ break;
+ case T_IncrementalSortPath:
+ ptype = "IncrementalSort";
+ subpath = ((SortPath *) path)->subpath;
+ break;
+ case T_GroupPath:
+ ptype = "Group";
+ subpath = ((GroupPath *) path)->subpath;
+ break;
+ case T_UpperUniquePath:
+ ptype = "UpperUnique";
+ subpath = ((UpperUniquePath *) path)->subpath;
+ break;
+ case T_AggPath:
+ ptype = "Agg";
+ subpath = ((AggPath *) path)->subpath;
+ break;
+ case T_GroupingSetsPath:
+ ptype = "GroupingSets";
+ subpath = ((GroupingSetsPath *) path)->subpath;
+ break;
+ case T_MinMaxAggPath:
+ ptype = "MinMaxAgg";
+ break;
+ case T_WindowAggPath:
+ ptype = "WindowAgg";
+ subpath = ((WindowAggPath *) path)->subpath;
+ break;
+ case T_SetOpPath:
+ ptype = "SetOp";
+ subpath = ((SetOpPath *) path)->subpath;
+ break;
+ case T_RecursiveUnionPath:
+ ptype = "RecursiveUnion";
+ break;
+ case T_LockRowsPath:
+ ptype = "LockRows";
+ subpath = ((LockRowsPath *) path)->subpath;
+ break;
+ case T_ModifyTablePath:
+ ptype = "ModifyTable";
+ break;
+ case T_LimitPath:
+ ptype = "Limit";
+ subpath = ((LimitPath *) path)->subpath;
+ break;
+ default:
+ ptype = "???Path";
+ break;
+ }
+
+ for (i = 0; i < indent; i++)
+ printf("\t");
+ printf("%s", ptype);
+
+ if (path->parent)
+ {
+ printf("(");
+ print_relids(root, path->parent->relids);
+ printf(")");
+ }
+ if (path->param_info)
+ {
+ printf(" required_outer (");
+ print_relids(root, path->param_info->ppi_req_outer);
+ printf(")");
+ }
+ printf(" rows=%.0f cost=%.2f..%.2f\n",
+ path->rows, path->startup_cost, path->total_cost);
+
+ if (path->pathkeys)
+ {
+ for (i = 0; i < indent; i++)
+ printf("\t");
+ printf(" pathkeys: ");
+ print_pathkeys(path->pathkeys, root->parse->rtable);
+ }
+
+ if (join)
+ {
+ JoinPath *jp = (JoinPath *) path;
+
+ for (i = 0; i < indent; i++)
+ printf("\t");
+ printf(" clauses: ");
+ print_restrictclauses(root, jp->joinrestrictinfo);
+ printf("\n");
+
+ if (IsA(path, MergePath))
+ {
+ MergePath *mp = (MergePath *) path;
+
+ for (i = 0; i < indent; i++)
+ printf("\t");
+ printf(" sortouter=%d sortinner=%d materializeinner=%d\n",
+ ((mp->outersortkeys) ? 1 : 0),
+ ((mp->innersortkeys) ? 1 : 0),
+ ((mp->materialize_inner) ? 1 : 0));
+ }
+
+ print_path(root, jp->outerjoinpath, indent + 1);
+ print_path(root, jp->innerjoinpath, indent + 1);
+ }
+
+ if (subpath)
+ print_path(root, subpath, indent + 1);
+}
+
+void
+debug_print_rel(PlannerInfo *root, RelOptInfo *rel)
+{
+ ListCell *l;
+
+ printf("RELOPTINFO (");
+ print_relids(root, rel->relids);
+ printf("): rows=%.0f width=%d\n", rel->rows, rel->reltarget->width);
+
+ if (rel->baserestrictinfo)
+ {
+ printf("\tbaserestrictinfo: ");
+ print_restrictclauses(root, rel->baserestrictinfo);
+ printf("\n");
+ }
+
+ if (rel->joininfo)
+ {
+ printf("\tjoininfo: ");
+ print_restrictclauses(root, rel->joininfo);
+ printf("\n");
+ }
+
+ printf("\tpath list:\n");
+ foreach(l, rel->pathlist)
+ print_path(root, lfirst(l), 1);
+ if (rel->cheapest_parameterized_paths)
+ {
+ printf("\n\tcheapest parameterized paths:\n");
+ foreach(l, rel->cheapest_parameterized_paths)
+ print_path(root, lfirst(l), 1);
+ }
+ if (rel->cheapest_startup_path)
+ {
+ printf("\n\tcheapest startup path:\n");
+ print_path(root, rel->cheapest_startup_path, 1);
+ }
+ if (rel->cheapest_total_path)
+ {
+ printf("\n\tcheapest total path:\n");
+ print_path(root, rel->cheapest_total_path, 1);
+ }
+ printf("\n");
+ fflush(stdout);
+}
+
+#endif /* OPTIMIZER_DEBUG */
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index b65323532b..211ba65389 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3133,26 +3133,10 @@ create_agg_path(PlannerInfo *root,
pathnode->path.parallel_safe = rel->consider_parallel &&
subpath->parallel_safe;
pathnode->path.parallel_workers = subpath->parallel_workers;
-
if (aggstrategy == AGG_SORTED)
- {
- /*
- * Attempt to preserve the order of the subpath. Additional pathkeys
- * may have been added in adjust_group_pathkeys_for_groupagg() to
- * support ORDER BY / DISTINCT aggregates. Pathkeys added there
- * belong to columns within the aggregate function, so we must strip
- * these additional pathkeys off as those columns are unavailable
- * above the aggregate node.
- */
- if (list_length(subpath->pathkeys) > root->num_groupby_pathkeys)
- pathnode->path.pathkeys = list_copy_head(subpath->pathkeys,
- root->num_groupby_pathkeys);
- else
- pathnode->path.pathkeys = subpath->pathkeys; /* preserves order */
- }
+ pathnode->path.pathkeys = subpath->pathkeys; /* preserves order */
else
pathnode->path.pathkeys = NIL; /* output is unordered */
-
pathnode->subpath = subpath;
pathnode->aggstrategy = aggstrategy;
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/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index 7f87df45df..5c4fdcfba4 100644
--- a/src/backend/utils/adt/array_userfuncs.c
+++ b/src/backend/utils/adt/array_userfuncs.c
@@ -723,13 +723,12 @@ array_agg_deserialize(PG_FUNCTION_ARGS)
sstate = PG_GETARG_BYTEA_PP(0);
/*
- * Fake up a StringInfo pointing to the bytea's value so we can "receive"
- * the serialized aggregate state value.
+ * Copy the bytea into a StringInfo so that we can "receive" it using the
+ * standard recv-function infrastructure.
*/
- buf.data = VARDATA_ANY(sstate);
- buf.len = VARSIZE_ANY_EXHDR(sstate);
- buf.maxlen = 0;
- buf.cursor = 0;
+ initStringInfo(&buf);
+ appendBinaryStringInfo(&buf,
+ VARDATA_ANY(sstate), VARSIZE_ANY_EXHDR(sstate));
/* element_type */
element_type = pq_getmsgint(&buf, 4);
@@ -826,6 +825,7 @@ array_agg_deserialize(PG_FUNCTION_ARGS)
}
pq_getmsgend(&buf);
+ pfree(buf.data);
PG_RETURN_POINTER(result);
}
@@ -1134,13 +1134,12 @@ array_agg_array_deserialize(PG_FUNCTION_ARGS)
sstate = PG_GETARG_BYTEA_PP(0);
/*
- * Fake up a StringInfo pointing to the bytea's value so we can "receive"
- * the serialized aggregate state value.
+ * Copy the bytea into a StringInfo so that we can "receive" it using the
+ * standard recv-function infrastructure.
*/
- buf.data = VARDATA_ANY(sstate);
- buf.len = VARSIZE_ANY_EXHDR(sstate);
- buf.maxlen = 0;
- buf.cursor = 0;
+ initStringInfo(&buf);
+ appendBinaryStringInfo(&buf,
+ VARDATA_ANY(sstate), VARSIZE_ANY_EXHDR(sstate));
/* element_type */
element_type = pq_getmsgint(&buf, 4);
@@ -1198,6 +1197,7 @@ array_agg_array_deserialize(PG_FUNCTION_ARGS)
memcpy(result->lbs, temp, sizeof(result->lbs));
pq_getmsgend(&buf);
+ pfree(buf.data);
PG_RETURN_POINTER(result);
}
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index f4b885005f..3c3184f15b 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -5190,13 +5190,12 @@ numeric_avg_deserialize(PG_FUNCTION_ARGS)
init_var(&tmp_var);
/*
- * Fake up a StringInfo pointing to the bytea's value so we can "receive"
- * the serialized aggregate state value.
+ * Copy the bytea into a StringInfo so that we can "receive" it using the
+ * standard recv-function infrastructure.
*/
- buf.data = VARDATA_ANY(sstate);
- buf.len = VARSIZE_ANY_EXHDR(sstate);
- buf.maxlen = 0;
- buf.cursor = 0;
+ initStringInfo(&buf);
+ appendBinaryStringInfo(&buf,
+ VARDATA_ANY(sstate), VARSIZE_ANY_EXHDR(sstate));
result = makeNumericAggStateCurrentContext(false);
@@ -5223,6 +5222,7 @@ numeric_avg_deserialize(PG_FUNCTION_ARGS)
result->nInfcount = pq_getmsgint64(&buf);
pq_getmsgend(&buf);
+ pfree(buf.data);
free_var(&tmp_var);
@@ -5306,13 +5306,12 @@ numeric_deserialize(PG_FUNCTION_ARGS)
init_var(&tmp_var);
/*
- * Fake up a StringInfo pointing to the bytea's value so we can "receive"
- * the serialized aggregate state value.
+ * Copy the bytea into a StringInfo so that we can "receive" it using the
+ * standard recv-function infrastructure.
*/
- buf.data = VARDATA_ANY(sstate);
- buf.len = VARSIZE_ANY_EXHDR(sstate);
- buf.maxlen = 0;
- buf.cursor = 0;
+ initStringInfo(&buf);
+ appendBinaryStringInfo(&buf,
+ VARDATA_ANY(sstate), VARSIZE_ANY_EXHDR(sstate));
result = makeNumericAggStateCurrentContext(false);
@@ -5343,6 +5342,7 @@ numeric_deserialize(PG_FUNCTION_ARGS)
result->nInfcount = pq_getmsgint64(&buf);
pq_getmsgend(&buf);
+ pfree(buf.data);
free_var(&tmp_var);
@@ -5677,13 +5677,12 @@ numeric_poly_deserialize(PG_FUNCTION_ARGS)
init_var(&tmp_var);
/*
- * Fake up a StringInfo pointing to the bytea's value so we can "receive"
- * the serialized aggregate state value.
+ * Copy the bytea into a StringInfo so that we can "receive" it using the
+ * standard recv-function infrastructure.
*/
- buf.data = VARDATA_ANY(sstate);
- buf.len = VARSIZE_ANY_EXHDR(sstate);
- buf.maxlen = 0;
- buf.cursor = 0;
+ initStringInfo(&buf);
+ appendBinaryStringInfo(&buf,
+ VARDATA_ANY(sstate), VARSIZE_ANY_EXHDR(sstate));
result = makePolyNumAggStateCurrentContext(false);
@@ -5707,6 +5706,7 @@ numeric_poly_deserialize(PG_FUNCTION_ARGS)
#endif
pq_getmsgend(&buf);
+ pfree(buf.data);
free_var(&tmp_var);
@@ -5868,13 +5868,12 @@ int8_avg_deserialize(PG_FUNCTION_ARGS)
init_var(&tmp_var);
/*
- * Fake up a StringInfo pointing to the bytea's value so we can "receive"
- * the serialized aggregate state value.
+ * Copy the bytea into a StringInfo so that we can "receive" it using the
+ * standard recv-function infrastructure.
*/
- buf.data = VARDATA_ANY(sstate);
- buf.len = VARSIZE_ANY_EXHDR(sstate);
- buf.maxlen = 0;
- buf.cursor = 0;
+ initStringInfo(&buf);
+ appendBinaryStringInfo(&buf,
+ VARDATA_ANY(sstate), VARSIZE_ANY_EXHDR(sstate));
result = makePolyNumAggStateCurrentContext(false);
@@ -5890,6 +5889,7 @@ int8_avg_deserialize(PG_FUNCTION_ARGS)
#endif
pq_getmsgend(&buf);
+ pfree(buf.data);
free_var(&tmp_var);
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 1aff04fa77..72e1e24fe0 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -5289,13 +5289,12 @@ string_agg_deserialize(PG_FUNCTION_ARGS)
sstate = PG_GETARG_BYTEA_PP(0);
/*
- * Fake up a StringInfo pointing to the bytea's value so we can "receive"
- * the serialized aggregate state value.
+ * Copy the bytea into a StringInfo so that we can "receive" it using the
+ * standard recv-function infrastructure.
*/
- buf.data = VARDATA_ANY(sstate);
- buf.len = VARSIZE_ANY_EXHDR(sstate);
- buf.maxlen = 0;
- buf.cursor = 0;
+ initStringInfo(&buf);
+ appendBinaryStringInfo(&buf,
+ VARDATA_ANY(sstate), VARSIZE_ANY_EXHDR(sstate));
result = makeStringAggState(fcinfo);
@@ -5308,6 +5307,7 @@ string_agg_deserialize(PG_FUNCTION_ARGS)
appendBinaryStringInfo(result, data, datalen);
pq_getmsgend(&buf);
+ pfree(buf.data);
PG_RETURN_POINTER(result);
}
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 16ec6c5ef0..3a1d846db5 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -611,6 +611,7 @@ static char *recovery_target_xid_string;
static char *recovery_target_name_string;
static char *recovery_target_lsn_string;
+
/* should be static, but commands/variable.c needs to get at this */
char *role_string;
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;
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index 97dcb93791..cdc6673357 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -21,6 +21,7 @@
extern PGDLLIMPORT int Password_encryption; /* values from enum PasswordType */
extern PGDLLIMPORT char *createrole_self_grant;
+
/* Hook to check passwords in CreateRole() and AlterRole() */
typedef void (*check_password_hook_type) (const char *username, const char *shadow_pass, PasswordType password_type, Datum validuntil_time, bool validuntil_null);
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,
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 7b896d821e..50bc3b503a 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -63,6 +63,10 @@ extern void create_partial_bitmap_paths(PlannerInfo *root, RelOptInfo *rel,
extern void generate_partitionwise_join_paths(PlannerInfo *root,
RelOptInfo *rel);
+#ifdef OPTIMIZER_DEBUG
+extern void debug_print_rel(PlannerInfo *root, RelOptInfo *rel);
+#endif
+
/*
* indxpath.c
* routines to generate index paths
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/expected/tuplesort.out b/src/test/regress/expected/tuplesort.out
index 0e8b5bf4a3..a2efa179fc 100644
--- a/src/test/regress/expected/tuplesort.out
+++ b/src/test/regress/expected/tuplesort.out
@@ -401,7 +401,7 @@ FETCH NEXT FROM c;
00000000-0000-0000-0000-000000000000
(1 row)
--- scroll beyond end
+-- scroll beyond end end
FETCH LAST FROM c;
noabort_decreasing
--------------------
@@ -498,7 +498,7 @@ FETCH NEXT FROM c;
00000000-0000-0000-0000-000000000000
(1 row)
--- scroll beyond end
+-- scroll beyond end end
FETCH LAST FROM c;
noabort_decreasing
--------------------
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%';
diff --git a/src/test/regress/sql/tuplesort.sql b/src/test/regress/sql/tuplesort.sql
index 658fe98dc5..846484d561 100644
--- a/src/test/regress/sql/tuplesort.sql
+++ b/src/test/regress/sql/tuplesort.sql
@@ -169,7 +169,7 @@ FETCH BACKWARD FROM c;
FETCH BACKWARD FROM c;
FETCH NEXT FROM c;
--- scroll beyond end
+-- scroll beyond end end
FETCH LAST FROM c;
FETCH BACKWARD FROM c;
FETCH NEXT FROM c;
@@ -200,7 +200,7 @@ FETCH BACKWARD FROM c;
FETCH BACKWARD FROM c;
FETCH NEXT FROM c;
--- scroll beyond end
+-- scroll beyond end end
FETCH LAST FROM c;
FETCH BACKWARD FROM c;
FETCH NEXT FROM c;