multipass-v3.patch
text/x-diff
Filename: multipass-v3.patch
Type: text/x-diff
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: format-patch
Series: patch v3-0001
Subject: Move rolpassword out of pg_authid into a new table
| File | + | − |
|---|---|---|
| src/backend/catalog/catalog.c | 6 | 2 |
| src/backend/catalog/Makefile | 1 | 1 |
| src/backend/catalog/system_views.sql | 4 | 1 |
| src/backend/commands/user.c | 455 | 90 |
| src/backend/commands/variable.c | 105 | 0 |
| src/backend/libpq/auth.c | 70 | 58 |
| src/backend/libpq/auth-sasl.c | 3 | 3 |
| src/backend/libpq/auth-scram.c | 108 | 77 |
| src/backend/libpq/crypt.c | 80 | 49 |
| src/backend/parser/gram.y | 22 | 3 |
| src/backend/utils/cache/catcache.c | 2 | 1 |
| src/backend/utils/cache/relcache.c | 10 | 3 |
| src/backend/utils/cache/syscache.c | 15 | 3 |
| src/backend/utils/misc/guc.c | 12 | 0 |
| src/bin/initdb/initdb.c | 1 | 1 |
| src/bin/pg_dump/pg_dumpall.c | 12 | 1 |
| src/common/scram-common.c | 1 | 1 |
| src/include/catalog/pg_authid.dat | 13 | 13 |
| src/include/catalog/pg_authid.h | 2 | 5 |
| src/include/catalog/pg_auth_password.h | 56 | 4 |
| src/include/commands/user.h | 5 | 1 |
| src/include/commands/variable.h | 4 | 0 |
| src/include/libpq/crypt.h | 2 | 2 |
| src/include/libpq/sasl.h | 2 | 2 |
| src/include/libpq/scram.h | 1 | 1 |
| src/include/parser/kwlist.h | 2 | 0 |
| src/include/utils/syscache.h | 2 | 1 |
| src/test/regress/expected/create_index.out | 7 | 7 |
| src/test/regress/expected/oidjoins.out | 1 | 0 |
| src/test/regress/expected/password.out | 34 | 22 |
| src/test/regress/expected/roleattributes.out | 128 | 128 |
| src/test/regress/expected/rules.out | 3 | 2 |
| src/test/regress/expected/tablespace.out | 6 | 6 |
| src/test/regress/sql/create_index.sql | 5 | 5 |
| src/test/regress/sql/password.sql | 22 | 10 |
| src/test/regress/sql/roleattributes.sql | 32 | 32 |
| src/test/regress/sql/tablespace.sql | 4 | 4 |
From 1203b8a92e4e6c51c7e5aa244d8ee9bbd2f74e66 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@snowman.net>
Date: Thu, 30 Jun 2022 18:15:52 -0400
Subject: [PATCH 1/3] Move rolpassword out of pg_authid into a new table
In preparation for more flexibility in password
management the rolpassword column needs to be
moved into a new table.
Author: Joshua Brindle
---
src/backend/catalog/Makefile | 2 +-
src/backend/catalog/catalog.c | 8 +-
src/backend/catalog/system_views.sql | 5 +-
src/backend/commands/user.c | 226 ++++++++++++----
src/backend/libpq/auth-sasl.c | 2 +-
src/backend/libpq/auth-scram.c | 4 +-
src/backend/libpq/auth.c | 16 ++
src/backend/libpq/crypt.c | 40 ++-
src/backend/utils/cache/catcache.c | 1 +
src/backend/utils/cache/relcache.c | 13 +-
src/backend/utils/cache/syscache.c | 12 +
src/bin/initdb/initdb.c | 2 +-
src/bin/pg_dump/pg_dumpall.c | 13 +-
src/common/scram-common.c | 2 +-
src/include/catalog/pg_auth_password.h | 50 ++++
src/include/catalog/pg_authid.dat | 26 +-
src/include/catalog/pg_authid.h | 7 +-
src/include/commands/user.h | 1 +
src/include/libpq/crypt.h | 2 +-
src/include/utils/syscache.h | 1 +
src/test/regress/expected/create_index.out | 14 +-
src/test/regress/expected/oidjoins.out | 1 +
src/test/regress/expected/password.out | 44 ++--
src/test/regress/expected/roleattributes.out | 256 +++++++++----------
src/test/regress/expected/rules.out | 5 +-
src/test/regress/expected/tablespace.out | 12 +-
src/test/regress/sql/create_index.sql | 10 +-
src/test/regress/sql/password.sql | 32 ++-
src/test/regress/sql/roleattributes.sql | 64 ++---
src/test/regress/sql/tablespace.sql | 8 +-
30 files changed, 563 insertions(+), 316 deletions(-)
create mode 100644 src/include/catalog/pg_auth_password.h
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index 89a0221ec9..450cfd9a58 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -63,7 +63,7 @@ CATALOG_HEADERS := \
pg_rewrite.h pg_trigger.h pg_event_trigger.h pg_description.h \
pg_cast.h pg_enum.h pg_namespace.h pg_conversion.h pg_depend.h \
pg_database.h pg_db_role_setting.h pg_tablespace.h \
- pg_authid.h pg_auth_members.h pg_shdepend.h pg_shdescription.h \
+ pg_authid.h pg_auth_members.h pg_auth_password.h pg_shdepend.h pg_shdescription.h \
pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
pg_ts_parser.h pg_ts_template.h pg_extension.h \
pg_foreign_data_wrapper.h pg_foreign_server.h pg_user_mapping.h \
diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index e784538aae..6a32e44c39 100644
--- a/src/backend/catalog/catalog.c
+++ b/src/backend/catalog/catalog.c
@@ -28,6 +28,7 @@
#include "catalog/catalog.h"
#include "catalog/namespace.h"
#include "catalog/pg_auth_members.h"
+#include "catalog/pg_auth_password.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
@@ -246,6 +247,7 @@ IsSharedRelation(Oid relationId)
{
/* These are the shared catalogs (look for BKI_SHARED_RELATION) */
if (relationId == AuthIdRelationId ||
+ relationId == AuthPasswordRelationId ||
relationId == AuthMemRelationId ||
relationId == DatabaseRelationId ||
relationId == DbRoleSettingRelationId ||
@@ -260,6 +262,8 @@ IsSharedRelation(Oid relationId)
/* These are their indexes */
if (relationId == AuthIdOidIndexId ||
relationId == AuthIdRolnameIndexId ||
+ relationId == AuthPasswordRoleOidIndexId ||
+ relationId == AuthMemRoleMemIndexId ||
relationId == AuthMemMemRoleIndexId ||
relationId == AuthMemRoleMemIndexId ||
relationId == DatabaseNameIndexId ||
@@ -279,8 +283,8 @@ IsSharedRelation(Oid relationId)
relationId == TablespaceOidIndexId)
return true;
/* These are their toast tables and toast indexes */
- if (relationId == PgAuthidToastTable ||
- relationId == PgAuthidToastIndex ||
+ if (relationId == PgAuthPasswordToastTable ||
+ relationId == PgAuthPasswordToastIndex ||
relationId == PgDatabaseToastTable ||
relationId == PgDatabaseToastIndex ||
relationId == PgDbRoleSettingToastTable ||
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index fedaed533b..6989594022 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -40,11 +40,14 @@ CREATE VIEW pg_shadow AS
rolsuper AS usesuper,
rolreplication AS userepl,
rolbypassrls AS usebypassrls,
- rolpassword AS passwd,
+ p.password AS passwd,
rolvaliduntil AS valuntil,
setconfig AS useconfig
FROM pg_authid LEFT JOIN pg_db_role_setting s
ON (pg_authid.oid = setrole AND setdatabase = 0)
+ LEFT JOIN pg_auth_password p
+ ON p.roleid = pg_authid.oid
+
WHERE rolcanlogin;
REVOKE ALL ON pg_shadow FROM public;
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 984305ba31..b224b4a2e9 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -23,6 +23,7 @@
#include "catalog/objectaccess.h"
#include "catalog/pg_auth_members.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_auth_password.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
#include "commands/comment.h"
@@ -64,6 +65,42 @@ have_createrole_privilege(void)
return has_createrole_privilege(GetUserId());
}
+/*
+ * Is the role able to log in
+*/
+bool
+is_role_valid(const char *rolename, char **logdetail)
+{
+ HeapTuple roleTup;
+ Datum datum;
+ bool isnull;
+ TimestampTz vuntil = 0;
+
+ /* Get role info from pg_authid */
+ roleTup = SearchSysCache1(AUTHNAME, PointerGetDatum(rolename));
+ if (!HeapTupleIsValid(roleTup))
+ {
+ *logdetail = psprintf(_("Role \"%s\" does not exist."),
+ rolename);
+ return false; /* no such user */
+ }
+
+ datum = SysCacheGetAttr(AUTHNAME, roleTup,
+ Anum_pg_authid_rolvaliduntil, &isnull);
+ ReleaseSysCache(roleTup);
+
+ if (!isnull)
+ vuntil = DatumGetTimestampTz(datum);
+
+ if (!isnull && vuntil < GetCurrentTimestamp())
+ {
+ *logdetail = psprintf(_("User \"%s\" has an expired password."), rolename);
+ return false;
+ }
+
+ return true;
+}
+
/*
* CREATE ROLE
@@ -351,43 +388,6 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin);
new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication);
new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
-
- if (password)
- {
- char *shadow_pass;
- const char *logdetail = NULL;
-
- /*
- * Don't allow an empty password. Libpq treats an empty password the
- * same as no password at all, and won't even try to authenticate. But
- * other clients might, so allowing it would be confusing. By clearing
- * the password when an empty string is specified, the account is
- * consistently locked for all clients.
- *
- * Note that this only covers passwords stored in the database itself.
- * There are also checks in the authentication code, to forbid an
- * empty password from being used with authentication methods that
- * fetch the password from an external system, like LDAP or PAM.
- */
- if (password[0] == '\0' ||
- plain_crypt_verify(stmt->role, password, "", &logdetail) == STATUS_OK)
- {
- ereport(NOTICE,
- (errmsg("empty string is not a valid password, clearing password")));
- new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
- }
- else
- {
- /* 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);
- }
- }
- else
- new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
-
new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum;
new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null;
@@ -422,6 +422,60 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
*/
CatalogTupleInsert(pg_authid_rel, tuple);
+ if (password)
+ {
+ char *shadow_pass;
+ char *logdetail;
+ Datum new_password_record[Natts_pg_auth_password];
+ bool new_password_record_nulls[Natts_pg_auth_password];
+ Relation pg_auth_password_rel;
+ TupleDesc pg_auth_password_dsc;
+ HeapTuple new_tuple;
+
+ /*
+ * Don't allow an empty password. Libpq treats an empty password the
+ * same as no password at all, and won't even try to authenticate. But
+ * other clients might, so allowing it would be confusing. By clearing
+ * the password when an empty string is specified, the account is
+ * consistently locked for all clients.
+ *
+ * Note that this only covers passwords stored in the database itself.
+ * There are also checks in the authentication code, to forbid an
+ * empty password from being used with authentication methods that
+ * fetch the password from an external system, like LDAP or PAM.
+ */
+ if (password[0] == '\0' ||
+ plain_crypt_verify(stmt->role, password, "", &logdetail) == STATUS_OK)
+ {
+ ereport(NOTICE,
+ (errmsg("empty string is not a valid password, clearing password")));
+ }
+ else
+ {
+ /* Encrypt the password to the requested format. */
+ shadow_pass = encrypt_password(Password_encryption, stmt->role,
+ password);
+
+ MemSet(new_password_record, 0, sizeof(new_password_record));
+ MemSet(new_password_record_nulls, false, sizeof(new_password_record_nulls));
+
+ /* open password table and insert it. */
+ pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
+ pg_auth_password_dsc = RelationGetDescr(pg_auth_password_rel);
+
+ new_password_record[Anum_pg_auth_password_password - 1] =
+ CStringGetTextDatum(shadow_pass);
+ new_password_record[Anum_pg_auth_password_roleid - 1] = roleid;
+
+ new_tuple = heap_form_tuple(pg_auth_password_dsc,
+ new_password_record, new_password_record_nulls);
+ CatalogTupleInsert(pg_auth_password_rel, new_tuple);
+ heap_freetuple(new_tuple);
+ table_close(pg_auth_password_rel, NoLock);
+
+ }
+ }
+
/*
* Advance command counter so we can see new record; else tests in
* AddRoleMems may fail.
@@ -495,6 +549,9 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
Datum new_record[Natts_pg_authid];
bool new_record_nulls[Natts_pg_authid];
bool new_record_repl[Natts_pg_authid];
+ Datum new_password_record[Natts_pg_auth_password];
+ bool new_password_record_nulls[Natts_pg_auth_password];
+ bool new_password_record_repl[Natts_pg_auth_password];
Relation pg_authid_rel;
TupleDesc pg_authid_dsc;
HeapTuple tuple,
@@ -624,6 +681,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
rolename = pstrdup(NameStr(authform->rolname));
roleid = authform->oid;
+
/*
* To mess with a superuser or replication role in any way you gotta be
* superuser. We also insist on superuser to change the BYPASSRLS
@@ -694,6 +752,10 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
MemSet(new_record, 0, sizeof(new_record));
MemSet(new_record_nulls, false, sizeof(new_record_nulls));
MemSet(new_record_repl, false, sizeof(new_record_repl));
+ MemSet(new_password_record, 0, sizeof(new_password_record));
+ MemSet(new_password_record_nulls, false, sizeof(new_password_record_nulls));
+ MemSet(new_password_record_repl, false, sizeof(new_password_record_repl));
+
/*
* issuper/createrole/etc
@@ -752,24 +814,24 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
{
ereport(NOTICE,
(errmsg("empty string is not a valid password, clearing password")));
- new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
+ new_password_record_nulls[Anum_pg_auth_password_password - 1] = true;
}
else
{
/* Encrypt the password to the requested format. */
shadow_pass = encrypt_password(Password_encryption, rolename,
password);
- new_record[Anum_pg_authid_rolpassword - 1] =
+ new_password_record[Anum_pg_auth_password_password - 1] =
CStringGetTextDatum(shadow_pass);
}
- new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
+ new_password_record_repl[Anum_pg_auth_password_password - 1] = true;
}
/* unset password */
if (dpassword && dpassword->arg == NULL)
{
- new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
- new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
+ new_password_record_repl[Anum_pg_auth_password_password - 1] = true;
+ new_password_record_nulls[Anum_pg_auth_password_password - 1] = true;
}
/* valid until */
@@ -786,12 +848,48 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
new_tuple = heap_modify_tuple(tuple, pg_authid_dsc, new_record,
new_record_nulls, new_record_repl);
CatalogTupleUpdate(pg_authid_rel, &tuple->t_self, new_tuple);
-
- InvokeObjectPostAlterHook(AuthIdRelationId, roleid, 0);
-
ReleaseSysCache(tuple);
heap_freetuple(new_tuple);
+ if (new_password_record_repl[Anum_pg_auth_password_password - 1] == true)
+ {
+ Relation pg_auth_password_rel;
+ TupleDesc pg_auth_password_dsc;
+ HeapTuple password_tuple;
+
+ pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
+ pg_auth_password_dsc = RelationGetDescr(pg_auth_password_rel);
+ password_tuple = SearchSysCache1(AUTHPASSWORD, ObjectIdGetDatum(roleid));
+
+ if (new_password_record_nulls[Anum_pg_auth_password_password - 1] == true) /* delete existing password */
+ {
+ if (HeapTupleIsValid(password_tuple)) {
+ CatalogTupleDelete(pg_auth_password_rel, &password_tuple->t_self);
+ ReleaseSysCache(password_tuple);
+ }
+ }
+ else if (HeapTupleIsValid(password_tuple)) /* update existing password */
+ {
+ new_tuple = heap_modify_tuple(password_tuple, pg_auth_password_dsc, new_password_record,
+ new_password_record_nulls, new_password_record_repl);
+ CatalogTupleUpdate(pg_auth_password_rel, &password_tuple->t_self, new_tuple);
+ ReleaseSysCache(password_tuple);
+ heap_freetuple(new_tuple);
+ }
+ else /* insert new password */
+ {
+ new_password_record[Anum_pg_auth_password_roleid - 1] = roleid;
+
+ new_tuple = heap_form_tuple(pg_auth_password_dsc,
+ new_password_record, new_password_record_nulls);
+ CatalogTupleInsert(pg_auth_password_rel, new_tuple);
+ heap_freetuple(new_tuple);
+ }
+
+ table_close(pg_auth_password_rel, NoLock);
+ }
+ InvokeObjectPostAlterHook(AuthIdRelationId, roleid, 0);
+
/*
* Advance command counter so we can see new record; else tests in
* AddRoleMems may fail.
@@ -901,7 +999,6 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
return roleid;
}
-
/*
* DROP ROLE
*/
@@ -909,7 +1006,8 @@ void
DropRole(DropRoleStmt *stmt)
{
Relation pg_authid_rel,
- pg_auth_members_rel;
+ pg_auth_members_rel,
+ pg_auth_password_rel;
ListCell *item;
if (!have_createrole_privilege())
@@ -923,6 +1021,8 @@ DropRole(DropRoleStmt *stmt)
*/
pg_authid_rel = table_open(AuthIdRelationId, RowExclusiveLock);
pg_auth_members_rel = table_open(AuthMemRelationId, RowExclusiveLock);
+ pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
+
foreach(item, stmt->roles)
{
@@ -1056,6 +1156,15 @@ DropRole(DropRoleStmt *stmt)
DeleteSharedComments(roleid, AuthIdRelationId);
DeleteSharedSecurityLabel(roleid, AuthIdRelationId);
+ /*
+ * Drop password
+ */
+ tuple = SearchSysCache1(AUTHPASSWORD, roleid);
+ if (HeapTupleIsValid(tuple)) {
+ CatalogTupleDelete(pg_auth_password_rel, &tuple->t_self);
+ ReleaseSysCache(tuple);
+ }
+
/*
* Remove settings for this role.
*/
@@ -1077,7 +1186,9 @@ DropRole(DropRoleStmt *stmt)
* Now we can clean up; but keep locks until commit.
*/
table_close(pg_auth_members_rel, NoLock);
+ table_close(pg_auth_password_rel, NoLock);
table_close(pg_authid_rel, NoLock);
+
}
/*
@@ -1087,11 +1198,12 @@ ObjectAddress
RenameRole(const char *oldname, const char *newname)
{
HeapTuple oldtuple,
- newtuple;
+ newtuple,
+ passtuple;
TupleDesc dsc;
Relation rel;
Datum datum;
- bool isnull;
+ bool isnull = true;
Datum repl_val[Natts_pg_authid];
bool repl_null[Natts_pg_authid];
bool repl_repl[Natts_pg_authid];
@@ -1189,14 +1301,24 @@ RenameRole(const char *oldname, const char *newname)
CStringGetDatum(newname));
repl_null[Anum_pg_authid_rolname - 1] = false;
- datum = heap_getattr(oldtuple, Anum_pg_authid_rolpassword, dsc, &isnull);
+ passtuple = SearchSysCache1(AUTHPASSWORD, roleid);
+
+ if (HeapTupleIsValid(passtuple))
+ datum = SysCacheGetAttr(AUTHPASSWORD, passtuple,
+ Anum_pg_auth_password_password, &isnull);
if (!isnull && get_password_type(TextDatumGetCString(datum)) == PASSWORD_TYPE_MD5)
{
+ Relation pg_auth_password_rel;
+
/* MD5 uses the username as salt, so just clear it on a rename */
- repl_repl[Anum_pg_authid_rolpassword - 1] = true;
- repl_null[Anum_pg_authid_rolpassword - 1] = true;
+ pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
+ if (HeapTupleIsValid(passtuple)) {
+ CatalogTupleDelete(pg_auth_password_rel, &passtuple->t_self);
+ ReleaseSysCache(passtuple);
+ }
+ table_close(pg_auth_password_rel, NoLock);
ereport(NOTICE,
(errmsg("MD5 password cleared because of role rename")));
}
diff --git a/src/backend/libpq/auth-sasl.c b/src/backend/libpq/auth-sasl.c
index a1d7dbb6d5..805b3695b7 100644
--- a/src/backend/libpq/auth-sasl.c
+++ b/src/backend/libpq/auth-sasl.c
@@ -33,7 +33,7 @@
* implementation.
*
* shadow_pass is an optional pointer to the stored secret of the role
- * authenticated, from pg_authid.rolpassword. For mechanisms that use
+ * authenticated, from pg_auth_password.password. For mechanisms that use
* shadowed passwords, a NULL pointer here means that an entry could not
* be found for the role (or the user does not exist), and the mechanism
* should fail the authentication exchange.
diff --git a/src/backend/libpq/auth-scram.c b/src/backend/libpq/auth-scram.c
index ee7f52218a..795f1cba55 100644
--- a/src/backend/libpq/auth-scram.c
+++ b/src/backend/libpq/auth-scram.c
@@ -220,7 +220,7 @@ 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.
+ * 'shadow_pass' is the role's stored secret, from pg_auth_password.password.
* 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
@@ -454,7 +454,7 @@ scram_exchange(void *opaq, const char *input, int inputlen,
}
/*
- * Construct a SCRAM secret, for storing in pg_authid.rolpassword.
+ * Construct a SCRAM secret, for storing in pg_auth_password.password.
*
* The result is palloc'd, so caller is responsible for freeing it.
*/
diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c
index efc53f3135..269cf286ad 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -592,10 +592,26 @@ ClientAuthentication(Port *port)
case uaMD5:
case uaSCRAM:
+ /*
+ * check to be sure we are not past rolvaliduntil
+ */
+ if (!is_role_valid(port->user_name, &logdetail)) {
+ status = STATUS_ERROR;
+ break;
+ }
+
status = CheckPWChallengeAuth(port, &logdetail);
break;
case uaPassword:
+ /*
+ * check to be sure we are not past rolvaliduntil
+ */
+ if (!is_role_valid(port->user_name, &logdetail)) {
+ status = STATUS_ERROR;
+ break;
+ }
+
status = CheckPasswordAuth(port, &logdetail);
break;
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 1ff8b0507d..745e61034c 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -2,7 +2,7 @@
*
* crypt.c
* Functions for dealing with encrypted passwords stored in
- * pg_authid.rolpassword.
+ * pg_auth_password.password.
*
* Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
@@ -16,6 +16,7 @@
#include <unistd.h>
#include "catalog/pg_authid.h"
+#include "catalog/pg_auth_password.h"
#include "common/md5.h"
#include "common/scram-common.h"
#include "libpq/crypt.h"
@@ -36,8 +37,7 @@
char *
get_role_password(const char *role, const char **logdetail)
{
- TimestampTz vuntil = 0;
- HeapTuple roleTup;
+ HeapTuple roleTup, passTup;
Datum datum;
bool isnull;
char *shadow_pass;
@@ -51,33 +51,29 @@ get_role_password(const char *role, const char **logdetail)
return NULL; /* no such user */
}
- datum = SysCacheGetAttr(AUTHNAME, roleTup,
- Anum_pg_authid_rolpassword, &isnull);
- if (isnull)
+ datum = SysCacheGetAttr(AUTHNAME, roleTup, Anum_pg_authid_oid, &isnull);
+
+ passTup = SearchSysCache1(AUTHPASSWORD, datum);
+
+ if (!HeapTupleIsValid(passTup))
{
- ReleaseSysCache(roleTup);
*logdetail = psprintf(_("User \"%s\" has no password assigned."),
role);
return NULL; /* user has no password */
}
- shadow_pass = TextDatumGetCString(datum);
-
- datum = SysCacheGetAttr(AUTHNAME, roleTup,
- Anum_pg_authid_rolvaliduntil, &isnull);
- if (!isnull)
- vuntil = DatumGetTimestampTz(datum);
+ datum = SysCacheGetAttr(AUTHPASSWORD, passTup,
+ Anum_pg_auth_password_password, &isnull);
ReleaseSysCache(roleTup);
-
- /*
- * Password OK, but check to be sure we are not past rolvaliduntil
- */
- if (!isnull && vuntil < GetCurrentTimestamp())
+ if (isnull) /* this should not happen any more but just in case */
{
- *logdetail = psprintf(_("User \"%s\" has an expired password."),
+ ReleaseSysCache(passTup);
+ *logdetail = psprintf(_("User \"%s\" has no password assigned."),
role);
- return NULL;
+ return NULL; /* user has no password */
}
+ shadow_pass = TextDatumGetCString(datum);
+ ReleaseSysCache(passTup);
return shadow_pass;
}
@@ -156,7 +152,7 @@ encrypt_password(PasswordType target_type, const char *role,
* Check MD5 authentication response, and return STATUS_OK or STATUS_ERROR.
*
* 'shadow_pass' is the user's correct password or password hash, as stored
- * in pg_authid.rolpassword.
+ * in pg_auth_password.password.
* 'client_pass' is the response given by the remote user to the MD5 challenge.
* 'md5_salt' is the salt used in the MD5 authentication challenge.
*
@@ -211,7 +207,7 @@ md5_crypt_verify(const char *role, const char *shadow_pass,
* Check given password for given user, and return STATUS_OK or STATUS_ERROR.
*
* 'shadow_pass' is the user's correct password hash, as stored in
- * pg_authid.rolpassword.
+ * pg_auth_password.password.
* 'client_pass' is the password given by the remote user.
*
* In the error case, store a string at *logdetail that will be sent to the
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index 6ae7c1f50b..7c8f864a80 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -1104,6 +1104,7 @@ IndexScanOK(CatCache *cache, ScanKey cur_skey)
case AUTHNAME:
case AUTHOID:
+ case AUTHPASSWORD:
case AUTHMEMMEMROLE:
case DATABASEOID:
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index f502df91dc..b2ca82d1ab 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -50,6 +50,7 @@
#include "catalog/pg_attrdef.h"
#include "catalog/pg_auth_members.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_auth_password.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_database.h"
#include "catalog/pg_namespace.h"
@@ -113,6 +114,7 @@ static const FormData_pg_attribute Desc_pg_proc[Natts_pg_proc] = {Schema_pg_proc
static const FormData_pg_attribute Desc_pg_type[Natts_pg_type] = {Schema_pg_type};
static const FormData_pg_attribute Desc_pg_database[Natts_pg_database] = {Schema_pg_database};
static const FormData_pg_attribute Desc_pg_authid[Natts_pg_authid] = {Schema_pg_authid};
+static const FormData_pg_attribute Desc_pg_auth_password[Natts_pg_auth_password] = {Schema_pg_auth_password};
static const FormData_pg_attribute Desc_pg_auth_members[Natts_pg_auth_members] = {Schema_pg_auth_members};
static const FormData_pg_attribute Desc_pg_index[Natts_pg_index] = {Schema_pg_index};
static const FormData_pg_attribute Desc_pg_shseclabel[Natts_pg_shseclabel] = {Schema_pg_shseclabel};
@@ -3485,6 +3487,7 @@ RelationBuildLocalRelation(const char *relname,
{
case DatabaseRelationId:
case AuthIdRelationId:
+ case AuthPasswordRelationId:
case AuthMemRelationId:
case RelationRelationId:
case AttributeRelationId:
@@ -3905,7 +3908,7 @@ RelationCacheInitialize(void)
* RelationCacheInitializePhase2
*
* This is called to prepare for access to shared catalogs during startup.
- * We must at least set up nailed reldescs for pg_database, pg_authid,
+ * We must at least set up nailed reldescs for pg_database, pg_authid, pg_auth_password,
* pg_auth_members, and pg_shseclabel. Ideally we'd like to have reldescs
* for their indexes, too. We attempt to load this information from the
* shared relcache init file. If that's missing or broken, just make
@@ -3950,8 +3953,10 @@ RelationCacheInitializePhase2(void)
Natts_pg_shseclabel, Desc_pg_shseclabel);
formrdesc("pg_subscription", SubscriptionRelation_Rowtype_Id, true,
Natts_pg_subscription, Desc_pg_subscription);
+ formrdesc("pg_auth_password", AuthPasswordRelation_Rowtype_Id, true,
+ Natts_pg_auth_password, Desc_pg_auth_password);
-#define NUM_CRITICAL_SHARED_RELS 5 /* fix if you change list above */
+#define NUM_CRITICAL_SHARED_RELS 6 /* fix if you change list above */
}
MemoryContextSwitchTo(oldcxt);
@@ -4090,8 +4095,10 @@ RelationCacheInitializePhase3(void)
AuthMemRelationId);
load_critical_index(SharedSecLabelObjectIndexId,
SharedSecLabelRelationId);
+ load_critical_index(AuthPasswordRoleOidIndexId,
+ AuthPasswordRelationId);
-#define NUM_CRITICAL_SHARED_INDEXES 6 /* fix if you change list above */
+#define NUM_CRITICAL_SHARED_INDEXES 7 /* fix if you change list above */
criticalSharedRelcachesBuilt = true;
}
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index 1912b12146..de2b3ec1eb 100644
--- a/src/backend/utils/cache/syscache.c
+++ b/src/backend/utils/cache/syscache.c
@@ -27,6 +27,7 @@
#include "catalog/pg_amop.h"
#include "catalog/pg_amproc.h"
#include "catalog/pg_auth_members.h"
+#include "catalog/pg_auth_password.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_cast.h"
#include "catalog/pg_collation.h"
@@ -255,6 +256,17 @@ static const struct cachedesc cacheinfo[] = {
},
8
},
+ {AuthPasswordRelationId, /* AUTHPASSWORD */
+ AuthPasswordRoleOidIndexId,
+ 1,
+ {
+ Anum_pg_auth_password_roleid,
+ 0,
+ 0,
+ 0
+ },
+ 8
+ },
{
CastRelationId, /* CASTSOURCETARGET */
CastSourceTargetIndexId,
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index ed6de7ca94..7a39980e05 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1402,7 +1402,7 @@ setup_auth(FILE *cmdfd)
* The authid table shouldn't be readable except through views, to
* ensure passwords are not publicly visible.
*/
- "REVOKE ALL ON pg_authid FROM public;\n\n",
+ "REVOKE ALL ON pg_auth_password FROM public;\n\n",
NULL
};
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index ae41a652d7..64f27aa3f2 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -737,7 +737,18 @@ dumpRoles(PGconn *conn)
int i;
/* note: rolconfig is dumped later */
- if (server_version >= 90600)
+ if (server_version >= 150000)
+ printfPQExpBuffer(buf,
+ "SELECT oid, rolname, rolsuper, rolinherit, "
+ "rolcreaterole, rolcreatedb, "
+ "rolcanlogin, rolconnlimit, p.password as rolpassword, "
+ "rolvaliduntil, rolreplication, rolbypassrls, "
+ "pg_catalog.shobj_description(oid, '%s') as rolcomment, "
+ "rolname = current_user AS is_current_user "
+ "FROM %s LEFT JOIN pg_auth_password p ON %s.oid = p.roleid "
+ "WHERE rolname !~ '^pg_' "
+ "ORDER BY 2", role_catalog, role_catalog, role_catalog);
+ else if (server_version >= 90600)
printfPQExpBuffer(buf,
"SELECT oid, rolname, rolsuper, rolinherit, "
"rolcreaterole, rolcreatedb, "
diff --git a/src/common/scram-common.c b/src/common/scram-common.c
index 1268625929..d9d0cba737 100644
--- a/src/common/scram-common.c
+++ b/src/common/scram-common.c
@@ -181,7 +181,7 @@ scram_ServerKey(const uint8 *salted_password, uint8 *result,
/*
- * Construct a SCRAM secret, for storing in pg_authid.rolpassword.
+ * Construct a SCRAM secret, for storing in pg_auth_password.password.
*
* The password should already have been processed with SASLprep, if necessary!
*
diff --git a/src/include/catalog/pg_auth_password.h b/src/include/catalog/pg_auth_password.h
new file mode 100644
index 0000000000..beaa2d40b9
--- /dev/null
+++ b/src/include/catalog/pg_auth_password.h
@@ -0,0 +1,50 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_auth_password.h
+ * definition of the "authorization identifier" system catalog (pg_auth_password)
+ *
+ * Portions Copyright (c) 2021, PostgreSQL Global Development Group
+ *
+ * src/include/catalog/pg_auth_password.h
+ *
+ * NOTES
+ * The Catalog.pm module reads this file and derives schema
+ * information.
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PG_AUTH_PASSWORD_H
+#define PG_AUTH_PASSWORD_H
+
+#include "catalog/genbki.h"
+#include "catalog/pg_auth_password_d.h"
+
+/* ----------------
+ * pg_auth_password definition. cpp turns this into
+ * typedef struct FormData_pg_auth_password
+ * ----------------
+ */
+CATALOG(pg_auth_password,4548,AuthPasswordRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(4549,AuthPasswordRelation_Rowtype_Id) BKI_SCHEMA_MACRO
+{
+ Oid roleid BKI_LOOKUP(pg_authid); /* ID of a role */
+#ifdef CATALOG_VARLEN /* variable-length fields start here */
+ text password; /* password */
+#endif
+} FormData_pg_auth_password;
+
+/* ----------------
+ * Form_pg_auth_password corresponds to a pointer to a tuple with
+ * the format of pg_auth_password relation.
+ * ----------------
+ */
+
+DECLARE_TOAST(pg_auth_password, 4175, 4176);
+#define PgAuthPasswordToastTable 4175
+#define PgAuthPasswordToastIndex 4176
+
+typedef FormData_pg_auth_password *Form_pg_auth_password;
+
+DECLARE_UNIQUE_INDEX_PKEY(pg_auth_password_roleoid_index, 4550, AuthPasswordRoleOidIndexId, on pg_auth_password using btree(roleid oid_ops));
+
+
+#endif /* PG_AUTH_PASSWORD_H */
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 6c28119fa1..da36375a58 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -23,66 +23,66 @@
rolname => 'POSTGRES', rolsuper => 't', rolinherit => 't',
rolcreaterole => 't', rolcreatedb => 't', rolcanlogin => 't',
rolreplication => 't', rolbypassrls => 't', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolvaliduntil => '_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_' },
+ rolvaliduntil => '_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_' },
+ rolvaliduntil => '_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_' },
+ rolvaliduntil => '_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_' },
+ rolvaliduntil => '_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_' },
+ rolvaliduntil => '_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_' },
+ rolvaliduntil => '_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_' },
+ rolvaliduntil => '_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_' },
+ rolvaliduntil => '_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_' },
+ rolvaliduntil => '_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_' },
+ rolvaliduntil => '_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_' },
+ rolvaliduntil => '_null_' },
{ oid => '4544', oid_symbol => 'ROLE_PG_CHECKPOINTER',
rolname => 'pg_checkpointer', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
- rolpassword => '_null_', rolvaliduntil => '_null_' },
+ rolvaliduntil => '_null_' },
]
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index 3512601c80..0cb5675a33 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -42,9 +42,8 @@ CATALOG(pg_authid,1260,AuthIdRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(284
int32 rolconnlimit; /* max connections allowed (-1=no limit) */
/* remaining fields may be null; use heap_getattr to read them! */
-#ifdef CATALOG_VARLEN /* variable-length fields start here */
- text rolpassword; /* password, if any */
- timestamptz rolvaliduntil; /* password expiration time, if any */
+#ifdef CATALOG_VARLEN
+ timestamptz rolvaliduntil BKI_FORCE_NULL; /* role expiration time, if any */
#endif
} FormData_pg_authid;
@@ -55,8 +54,6 @@ CATALOG(pg_authid,1260,AuthIdRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(284
*/
typedef FormData_pg_authid *Form_pg_authid;
-DECLARE_TOAST_WITH_MACRO(pg_authid, 4175, 4176, PgAuthidToastTable, PgAuthidToastIndex);
-
DECLARE_UNIQUE_INDEX(pg_authid_rolname_index, 2676, AuthIdRolnameIndexId, on pg_authid using btree(rolname name_ops));
DECLARE_UNIQUE_INDEX_PKEY(pg_authid_oid_index, 2677, AuthIdOidIndexId, on pg_authid using btree(oid oid_ops));
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index d3dd8303d2..3e1aae5785 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -24,6 +24,7 @@ typedef void (*check_password_hook_type) (const char *username, const char *shad
extern PGDLLIMPORT check_password_hook_type check_password_hook;
+extern bool is_role_valid(const char *rolename, char **logdetail);
extern Oid CreateRole(ParseState *pstate, CreateRoleStmt *stmt);
extern Oid AlterRole(ParseState *pstate, AlterRoleStmt *stmt);
extern Oid AlterRoleSet(AlterRoleSetStmt *stmt);
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index 3238cf66d3..b8ff8ccb41 100644
--- a/src/include/libpq/crypt.h
+++ b/src/include/libpq/crypt.h
@@ -21,7 +21,7 @@
* Plaintext passwords can be passed in by the user, in a CREATE/ALTER USER
* command. They will be encrypted to MD5 or SCRAM-SHA-256 format, before
* storing on-disk, so only MD5 and SCRAM-SHA-256 passwords should appear
- * in pg_authid.rolpassword. They are also the allowed values for the
+ * in pg_auth_password.password. They are also the allowed values for the
* password_encryption GUC.
*/
typedef enum PasswordType
diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h
index 4463ea66be..c7bc648f46 100644
--- a/src/include/utils/syscache.h
+++ b/src/include/utils/syscache.h
@@ -43,6 +43,7 @@ enum SysCacheIdentifier
AUTHMEMROLEMEM,
AUTHNAME,
AUTHOID,
+ AUTHPASSWORD,
CASTSOURCETARGET,
CLAAMNAMENSP,
CLAOID,
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index d55aec3a1d..9875b277f2 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2515,10 +2515,10 @@ REINDEX TABLE CONCURRENTLY pg_class; -- no catalog relation
ERROR: cannot reindex system catalogs concurrently
REINDEX INDEX CONCURRENTLY pg_class_oid_index; -- no catalog index
ERROR: cannot reindex system catalogs concurrently
--- These are the toast table and index of pg_authid.
-REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1260; -- no catalog toast table
+-- These are the toast table and index of pg_database.
+REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1262; -- no catalog toast table
ERROR: cannot reindex system catalogs concurrently
-REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -- no catalog toast index
+REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1262_index; -- no catalog toast index
ERROR: cannot reindex system catalogs concurrently
REINDEX SYSTEM CONCURRENTLY postgres; -- not allowed for SYSTEM
ERROR: cannot reindex system catalogs concurrently
@@ -2817,10 +2817,10 @@ ERROR: must be owner of schema schema_to_reindex
RESET ROLE;
GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser;
SET SESSION ROLE regress_reindexuser;
-REINDEX TABLE pg_toast.pg_toast_1260;
-ERROR: must be owner of table pg_toast_1260
-REINDEX INDEX pg_toast.pg_toast_1260_index;
-ERROR: must be owner of index pg_toast_1260_index
+REINDEX TABLE pg_toast.pg_toast_1262;
+ERROR: must be owner of table pg_toast_1262
+REINDEX INDEX pg_toast.pg_toast_1262_index;
+ERROR: must be owner of index pg_toast_1262_index
-- Clean up
RESET ROLE;
REVOKE USAGE ON SCHEMA pg_toast FROM regress_reindexuser;
diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out
index 215eb899be..b69a2a72a7 100644
--- a/src/test/regress/expected/oidjoins.out
+++ b/src/test/regress/expected/oidjoins.out
@@ -197,6 +197,7 @@ NOTICE: checking pg_tablespace {spcowner} => pg_authid {oid}
NOTICE: checking pg_auth_members {roleid} => pg_authid {oid}
NOTICE: checking pg_auth_members {member} => pg_authid {oid}
NOTICE: checking pg_auth_members {grantor} => pg_authid {oid}
+NOTICE: checking pg_auth_password {roleid} => pg_authid {oid}
NOTICE: checking pg_shdepend {dbid} => pg_database {oid}
NOTICE: checking pg_shdepend {classid} => pg_class {oid}
NOTICE: checking pg_shdepend {refclassid} => pg_class {oid}
diff --git a/src/test/regress/expected/password.out b/src/test/regress/expected/password.out
index 7c84c9da33..4ffc41a545 100644
--- a/src/test/regress/expected/password.out
+++ b/src/test/regress/expected/password.out
@@ -24,10 +24,12 @@ CREATE ROLE regress_passwd4 PASSWORD NULL;
--
-- Since the salt is random, the exact value stored will be different on every test
-- run. Use a regular expression to mask the changing parts.
-SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
+SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
FROM pg_authid
+ LEFT JOIN pg_auth_password p
+ ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd%'
- ORDER BY rolname, rolpassword;
+ ORDER BY rolname, password;
rolname | rolpassword_masked
-----------------+---------------------------------------------------
regress_passwd1 | md5783277baca28003b33453252be4dbb34
@@ -40,12 +42,14 @@ SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+
ALTER ROLE regress_passwd2 RENAME TO regress_passwd2_new;
NOTICE: MD5 password cleared because of role rename
-- md5 entry should have been removed
-SELECT rolname, rolpassword
+SELECT rolname, password
FROM pg_authid
+ LEFT JOIN pg_auth_password p
+ ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd2_new'
- ORDER BY rolname, rolpassword;
- rolname | rolpassword
----------------------+-------------
+ ORDER BY rolname, password;
+ rolname | password
+---------------------+----------
regress_passwd2_new |
(1 row)
@@ -72,10 +76,12 @@ CREATE ROLE regress_passwd6 PASSWORD 'SCRAM-SHA-256$1234';
CREATE ROLE regress_passwd7 PASSWORD 'md5012345678901234567890123456789zz';
-- invalid length
CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz';
-SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
+SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
FROM pg_authid
+ LEFT JOIN pg_auth_password p
+ ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd%'
- ORDER BY rolname, rolpassword;
+ ORDER BY rolname, password;
rolname | rolpassword_masked
-----------------+---------------------------------------------------
regress_passwd1 | md5cd3578025fe2c3d7ed1b9a9b26238b70
@@ -95,9 +101,11 @@ ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659e0a';
NOTICE: empty string is not a valid password, clearing password
ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4=';
NOTICE: empty string is not a valid password, clearing password
-SELECT rolpassword FROM pg_authid WHERE rolname='regress_passwd_empty';
- rolpassword
--------------
+SELECT password FROM pg_authid
+LEFT JOIN pg_auth_password p
+ON pg_authid.oid = p.roleid WHERE rolname='regress_passwd_empty';
+ password
+----------
(1 row)
@@ -110,8 +118,10 @@ CREATE ROLE regress_passwd_sha_len1 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941
CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=';
-- Check that the invalid secrets were re-hashed. A re-hashed secret
-- should not contain the original salt.
-SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed
+SELECT rolname, password not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed
FROM pg_authid
+ LEFT JOIN pg_auth_password p
+ ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd_sha_len%'
ORDER BY rolname;
rolname | is_rolpassword_rehashed
@@ -134,11 +144,13 @@ DROP ROLE regress_passwd_sha_len0;
DROP ROLE regress_passwd_sha_len1;
DROP ROLE regress_passwd_sha_len2;
-- all entries should have been removed
-SELECT rolname, rolpassword
+SELECT rolname, password
FROM pg_authid
+ LEFT JOIN pg_auth_password p
+ ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd%'
- ORDER BY rolname, rolpassword;
- rolname | rolpassword
----------+-------------
+ ORDER BY rolname, password;
+ rolname | password
+---------+----------
(0 rows)
diff --git a/src/test/regress/expected/roleattributes.out b/src/test/regress/expected/roleattributes.out
index 5e6969b173..18a9dacadd 100644
--- a/src/test/regress/expected/roleattributes.out
+++ b/src/test/regress/expected/roleattributes.out
@@ -1,233 +1,233 @@
-- default for superuser is false
CREATE ROLE regress_test_def_superuser;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_superuser | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_superuser | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE regress_test_superuser WITH SUPERUSER;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_superuser | t | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_superuser | t | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE regress_test_superuser WITH NOSUPERUSER;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_superuser | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_superuser | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE regress_test_superuser WITH SUPERUSER;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_superuser | t | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_superuser | t | t | f | f | f | f | f | -1 |
(1 row)
-- default for inherit is true
CREATE ROLE regress_test_def_inherit;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_inherit | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_inherit | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE regress_test_inherit WITH NOINHERIT;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_inherit | f | f | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_inherit | f | f | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE regress_test_inherit WITH INHERIT;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_inherit | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_inherit | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE regress_test_inherit WITH NOINHERIT;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_inherit | f | f | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_inherit | f | f | f | f | f | f | f | -1 |
(1 row)
-- default for create role is false
CREATE ROLE regress_test_def_createrole;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_createrole | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_createrole | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE regress_test_createrole WITH CREATEROLE;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_createrole | f | t | t | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_createrole | f | t | t | f | f | f | f | -1 |
(1 row)
ALTER ROLE regress_test_createrole WITH NOCREATEROLE;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_createrole | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_createrole | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE regress_test_createrole WITH CREATEROLE;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_createrole | f | t | t | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_createrole | f | t | t | f | f | f | f | -1 |
(1 row)
-- default for create database is false
CREATE ROLE regress_test_def_createdb;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_createdb | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_createdb | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE regress_test_createdb WITH CREATEDB;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_createdb | f | t | f | t | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_createdb | f | t | f | t | f | f | f | -1 |
(1 row)
ALTER ROLE regress_test_createdb WITH NOCREATEDB;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_createdb | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_createdb | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE regress_test_createdb WITH CREATEDB;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_createdb | f | t | f | t | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_createdb | f | t | f | t | f | f | f | -1 |
(1 row)
-- default for can login is false for role
CREATE ROLE regress_test_def_role_canlogin;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_role_canlogin | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_role_canlogin | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE regress_test_role_canlogin WITH LOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_role_canlogin | f | t | f | f | t | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_role_canlogin | f | t | f | f | t | f | f | -1 |
(1 row)
ALTER ROLE regress_test_role_canlogin WITH NOLOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_role_canlogin | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_role_canlogin | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE regress_test_role_canlogin WITH LOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_role_canlogin | f | t | f | f | t | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_role_canlogin | f | t | f | f | t | f | f | -1 |
(1 row)
-- default for can login is true for user
CREATE USER regress_test_def_user_canlogin;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_user_canlogin | f | t | f | f | t | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_user_canlogin | f | t | f | f | t | f | f | -1 |
(1 row)
CREATE USER regress_test_user_canlogin WITH NOLOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_user_canlogin | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_user_canlogin | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER USER regress_test_user_canlogin WITH LOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_user_canlogin | f | t | f | f | t | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_user_canlogin | f | t | f | f | t | f | f | -1 |
(1 row)
ALTER USER regress_test_user_canlogin WITH NOLOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_user_canlogin | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_user_canlogin | f | t | f | f | f | f | f | -1 |
(1 row)
-- default for replication is false
CREATE ROLE regress_test_def_replication;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_replication | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_replication | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE regress_test_replication WITH REPLICATION;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_replication | f | t | f | f | f | t | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_replication | f | t | f | f | f | t | f | -1 |
(1 row)
ALTER ROLE regress_test_replication WITH NOREPLICATION;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_replication | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_replication | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE regress_test_replication WITH REPLICATION;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_replication | f | t | f | f | f | t | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_replication | f | t | f | f | f | t | f | -1 |
(1 row)
-- default for bypassrls is false
CREATE ROLE regress_test_def_bypassrls;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_def_bypassrls | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_def_bypassrls | f | t | f | f | f | f | f | -1 |
(1 row)
CREATE ROLE regress_test_bypassrls WITH BYPASSRLS;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_bypassrls | f | t | f | f | f | f | t | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_bypassrls | f | t | f | f | f | f | t | -1 |
(1 row)
ALTER ROLE regress_test_bypassrls WITH NOBYPASSRLS;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_bypassrls | f | t | f | f | f | f | f | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_bypassrls | f | t | f | f | f | f | f | -1 |
(1 row)
ALTER ROLE regress_test_bypassrls WITH BYPASSRLS;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
- rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
- regress_test_bypassrls | f | t | f | f | f | f | t | -1 | |
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+---------------
+ regress_test_bypassrls | f | t | f | f | f | f | t | -1 |
(1 row)
-- clean up roles
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index fc3cde3226..231fec7fda 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1725,11 +1725,12 @@ pg_shadow| SELECT pg_authid.rolname AS usename,
pg_authid.rolsuper AS usesuper,
pg_authid.rolreplication AS userepl,
pg_authid.rolbypassrls AS usebypassrls,
- pg_authid.rolpassword AS passwd,
+ p.password AS passwd,
pg_authid.rolvaliduntil AS valuntil,
s.setconfig AS useconfig
- FROM (pg_authid
+ FROM ((pg_authid
LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))))
+ LEFT JOIN pg_auth_password p ON ((p.roleid = pg_authid.oid)))
WHERE pg_authid.rolcanlogin;
pg_shmem_allocations| SELECT pg_get_shmem_allocations.name,
pg_get_shmem_allocations.off,
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index c52cf1cfcf..2ca2cd181f 100644
--- a/src/test/regress/expected/tablespace.out
+++ b/src/test/regress/expected/tablespace.out
@@ -53,13 +53,13 @@ ERROR: cannot move system relation "pg_authid_rolname_index"
REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_authid;
ERROR: cannot reindex system catalogs concurrently
-- toast relations, fail
-REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1260_index;
-ERROR: cannot move system relation "pg_toast_1260_index"
-REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1260_index;
+REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1262_index;
+ERROR: cannot move system relation "pg_toast_1262_index"
+REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1262_index;
ERROR: cannot reindex system catalogs concurrently
-REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1260;
-ERROR: cannot move system relation "pg_toast_1260_index"
-REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1260;
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1262;
+ERROR: cannot move system relation "pg_toast_1262_index"
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1262;
ERROR: cannot reindex system catalogs concurrently
-- system catalog, fail
REINDEX (TABLESPACE pg_global) TABLE pg_authid;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index d8fded3d93..8d649f2355 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -1068,9 +1068,9 @@ REINDEX TABLE CONCURRENTLY concur_reindex_tab;
COMMIT;
REINDEX TABLE CONCURRENTLY pg_class; -- no catalog relation
REINDEX INDEX CONCURRENTLY pg_class_oid_index; -- no catalog index
--- These are the toast table and index of pg_authid.
-REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1260; -- no catalog toast table
-REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -- no catalog toast index
+-- These are the toast table and index of pg_database.
+REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1262; -- no catalog toast table
+REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1262_index; -- no catalog toast index
REINDEX SYSTEM CONCURRENTLY postgres; -- not allowed for SYSTEM
-- Warns about catalog relations
REINDEX SCHEMA CONCURRENTLY pg_catalog;
@@ -1243,8 +1243,8 @@ REINDEX SCHEMA schema_to_reindex;
RESET ROLE;
GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser;
SET SESSION ROLE regress_reindexuser;
-REINDEX TABLE pg_toast.pg_toast_1260;
-REINDEX INDEX pg_toast.pg_toast_1260_index;
+REINDEX TABLE pg_toast.pg_toast_1262;
+REINDEX INDEX pg_toast.pg_toast_1262_index;
-- Clean up
RESET ROLE;
diff --git a/src/test/regress/sql/password.sql b/src/test/regress/sql/password.sql
index 98f49916e5..f41d308fbb 100644
--- a/src/test/regress/sql/password.sql
+++ b/src/test/regress/sql/password.sql
@@ -23,18 +23,22 @@ CREATE ROLE regress_passwd4 PASSWORD NULL;
--
-- Since the salt is random, the exact value stored will be different on every test
-- run. Use a regular expression to mask the changing parts.
-SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
+SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
FROM pg_authid
+ LEFT JOIN pg_auth_password p
+ ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd%'
- ORDER BY rolname, rolpassword;
+ ORDER BY rolname, password;
-- Rename a role
ALTER ROLE regress_passwd2 RENAME TO regress_passwd2_new;
-- md5 entry should have been removed
-SELECT rolname, rolpassword
+SELECT rolname, password
FROM pg_authid
+ LEFT JOIN pg_auth_password p
+ ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd2_new'
- ORDER BY rolname, rolpassword;
+ ORDER BY rolname, password;
ALTER ROLE regress_passwd2_new RENAME TO regress_passwd2;
-- Change passwords with ALTER USER. With plaintext or already-encrypted
@@ -63,16 +67,20 @@ CREATE ROLE regress_passwd7 PASSWORD 'md5012345678901234567890123456789zz';
-- invalid length
CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz';
-SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
+SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
FROM pg_authid
+ LEFT JOIN pg_auth_password p
+ ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd%'
- ORDER BY rolname, rolpassword;
+ ORDER BY rolname, password;
-- An empty password is not allowed, in any form
CREATE ROLE regress_passwd_empty PASSWORD '';
ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659e0a';
ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4=';
-SELECT rolpassword FROM pg_authid WHERE rolname='regress_passwd_empty';
+SELECT password FROM pg_authid
+LEFT JOIN pg_auth_password p
+ON pg_authid.oid = p.roleid WHERE rolname='regress_passwd_empty';
-- Test with invalid stored and server keys.
--
@@ -84,8 +92,10 @@ CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941
-- Check that the invalid secrets were re-hashed. A re-hashed secret
-- should not contain the original salt.
-SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed
+SELECT rolname, password not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed
FROM pg_authid
+ LEFT JOIN pg_auth_password p
+ ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd_sha_len%'
ORDER BY rolname;
@@ -103,7 +113,9 @@ DROP ROLE regress_passwd_sha_len1;
DROP ROLE regress_passwd_sha_len2;
-- all entries should have been removed
-SELECT rolname, rolpassword
+SELECT rolname, password
FROM pg_authid
+ LEFT JOIN pg_auth_password p
+ ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd%'
- ORDER BY rolname, rolpassword;
+ ORDER BY rolname, password;
diff --git a/src/test/regress/sql/roleattributes.sql b/src/test/regress/sql/roleattributes.sql
index c961b2d730..09505c6a3b 100644
--- a/src/test/regress/sql/roleattributes.sql
+++ b/src/test/regress/sql/roleattributes.sql
@@ -1,83 +1,83 @@
-- default for superuser is false
CREATE ROLE regress_test_def_superuser;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser';
CREATE ROLE regress_test_superuser WITH SUPERUSER;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
ALTER ROLE regress_test_superuser WITH NOSUPERUSER;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
ALTER ROLE regress_test_superuser WITH SUPERUSER;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser';
-- default for inherit is true
CREATE ROLE regress_test_def_inherit;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit';
CREATE ROLE regress_test_inherit WITH NOINHERIT;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
ALTER ROLE regress_test_inherit WITH INHERIT;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
ALTER ROLE regress_test_inherit WITH NOINHERIT;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit';
-- default for create role is false
CREATE ROLE regress_test_def_createrole;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole';
CREATE ROLE regress_test_createrole WITH CREATEROLE;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
ALTER ROLE regress_test_createrole WITH NOCREATEROLE;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
ALTER ROLE regress_test_createrole WITH CREATEROLE;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole';
-- default for create database is false
CREATE ROLE regress_test_def_createdb;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb';
CREATE ROLE regress_test_createdb WITH CREATEDB;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
ALTER ROLE regress_test_createdb WITH NOCREATEDB;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
ALTER ROLE regress_test_createdb WITH CREATEDB;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb';
-- default for can login is false for role
CREATE ROLE regress_test_def_role_canlogin;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin';
CREATE ROLE regress_test_role_canlogin WITH LOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
ALTER ROLE regress_test_role_canlogin WITH NOLOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
ALTER ROLE regress_test_role_canlogin WITH LOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
-- default for can login is true for user
CREATE USER regress_test_def_user_canlogin;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin';
CREATE USER regress_test_user_canlogin WITH NOLOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
ALTER USER regress_test_user_canlogin WITH LOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
ALTER USER regress_test_user_canlogin WITH NOLOGIN;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
-- default for replication is false
CREATE ROLE regress_test_def_replication;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication';
CREATE ROLE regress_test_replication WITH REPLICATION;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
ALTER ROLE regress_test_replication WITH NOREPLICATION;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
ALTER ROLE regress_test_replication WITH REPLICATION;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication';
-- default for bypassrls is false
CREATE ROLE regress_test_def_bypassrls;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls';
CREATE ROLE regress_test_bypassrls WITH BYPASSRLS;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
ALTER ROLE regress_test_bypassrls WITH NOBYPASSRLS;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
ALTER ROLE regress_test_bypassrls WITH BYPASSRLS;
-SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
+SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
-- clean up roles
DROP ROLE regress_test_def_superuser;
diff --git a/src/test/regress/sql/tablespace.sql b/src/test/regress/sql/tablespace.sql
index 21db433f2a..d92135173f 100644
--- a/src/test/regress/sql/tablespace.sql
+++ b/src/test/regress/sql/tablespace.sql
@@ -42,10 +42,10 @@ REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am;
REINDEX (TABLESPACE regress_tblspace) TABLE pg_authid;
REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_authid;
-- toast relations, fail
-REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1260_index;
-REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1260_index;
-REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1260;
-REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1260;
+REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1262_index;
+REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1262_index;
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1262;
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1262;
-- system catalog, fail
REINDEX (TABLESPACE pg_global) TABLE pg_authid;
REINDEX (TABLESPACE pg_global) TABLE CONCURRENTLY pg_authid;
--
2.34.1
From bfb15c69fa4367e92cabeef1a97eb6fb394b788f Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@snowman.net>
Date: Thu, 30 Jun 2022 18:16:52 -0400
Subject: [PATCH 2/3] multiple passwords work with scram and md5
also renaming roles, dropping roles, switching between
password_encryption
Author: Joshua Brindle
---
src/backend/commands/user.c | 198 ++++++++++++++++++++-----
src/backend/libpq/auth-sasl.c | 4 +-
src/backend/libpq/auth-scram.c | 181 ++++++++++++----------
src/backend/libpq/auth.c | 103 ++++++-------
src/backend/libpq/crypt.c | 51 ++++---
src/backend/parser/gram.y | 15 +-
src/backend/utils/cache/catcache.c | 2 +-
src/backend/utils/cache/syscache.c | 6 +-
src/include/catalog/pg_auth_password.h | 10 +-
src/include/commands/user.h | 2 +-
src/include/libpq/crypt.h | 2 +-
src/include/libpq/sasl.h | 4 +-
src/include/libpq/scram.h | 2 +-
src/include/parser/kwlist.h | 1 +
src/include/utils/syscache.h | 2 +-
src/test/regress/expected/password.out | 12 +-
16 files changed, 385 insertions(+), 210 deletions(-)
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index b224b4a2e9..94747d7c3c 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -31,11 +31,14 @@
#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"
#include "utils/builtins.h"
+#include "utils/catcache.h"
#include "utils/fmgroids.h"
#include "utils/syscache.h"
#include "utils/timestamp.h"
@@ -47,6 +50,9 @@ Oid binary_upgrade_next_pg_authid_oid = InvalidOid;
/* GUC parameter */
int Password_encryption = PASSWORD_TYPE_SCRAM_SHA_256;
+/* default password name */
+const char* default_passname = "__def__";
+
/* Hook to check passwords in CreateRole() and AlterRole() */
check_password_hook_type check_password_hook = NULL;
@@ -69,7 +75,7 @@ have_createrole_privilege(void)
* Is the role able to log in
*/
bool
-is_role_valid(const char *rolename, char **logdetail)
+is_role_valid(const char *rolename, const char **logdetail)
{
HeapTuple roleTup;
Datum datum;
@@ -102,6 +108,64 @@ is_role_valid(const char *rolename, char **logdetail)
}
+static
+bool
+validate_and_get_salt(char *rolename, char **salt, const char **logdetail)
+{
+ char **current_secrets;
+ char *salt1, *salt2 = NULL;
+ int i, num;
+ 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);
+ if (num == 0)
+ {
+ *salt = NULL; /* No existing passwords, allow one to be generated */
+ return true;
+ }
+ for (i = 0; i < num; 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;
+ uint8 stored_key[SCRAM_KEY_LEN];
+ uint8 server_key[SCRAM_KEY_LEN];
+
+ parse_scram_secret(current_secrets[i], &iterations, &salt1,
+ stored_key, server_key);
+
+ if (salt2 != NULL) {
+ if (strcmp(salt1, salt2)) {
+ *logdetail = psprintf(_("inconsistent salts, clearing password"));
+ *salt = NULL;
+ return false;
+ }
+ }
+ else
+ salt2 = salt1;
+ }
+
+ }
+ for (i = 0; i < num; i++)
+ pfree(current_secrets[i]);
+ pfree(current_secrets);
+ if (salt2)
+ *salt = pstrdup(salt2);
+ return true;
+}
+
/*
* CREATE ROLE
*/
@@ -117,6 +181,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
ListCell *item;
ListCell *option;
char *password = NULL; /* user password */
+ char *passname = NULL; /* name of the password for managing multiple passwords */
bool issuper = false; /* Make the user a superuser? */
bool inherit = true; /* Auto inherit privileges? */
bool createrole = false; /* Can this user create roles? */
@@ -144,6 +209,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
DefElem *dadminmembers = NULL;
DefElem *dvalidUntil = NULL;
DefElem *dbypassRLS = NULL;
+ DefElem *dpassName = NULL;
+
/* The defaults can vary depending on the original statement type */
switch (stmt->stmt_type)
@@ -246,6 +313,13 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
errorConflictingDefElem(defel, pstate);
dbypassRLS = defel;
}
+ else if (strcmp(defel->defname, "passname") == 0)
+ {
+ if (dpassName)
+ errorConflictingDefElem(defel, pstate);
+ dpassName = defel;
+ }
+
else
elog(ERROR, "option \"%s\" not recognized",
defel->defname);
@@ -283,6 +357,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
validUntil = strVal(dvalidUntil->arg);
if (dbypassRLS)
bypassrls = boolVal(dbypassRLS->arg);
+ if (dpassName)
+ passname = strVal(dpassName->arg);
/* Check some permissions first */
if (issuper)
@@ -424,8 +500,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
if (password)
{
- char *shadow_pass;
- char *logdetail;
+ char *shadow_pass, *salt;
+ const char *logdetail;
Datum new_password_record[Natts_pg_auth_password];
bool new_password_record_nulls[Natts_pg_auth_password];
Relation pg_auth_password_rel;
@@ -453,12 +529,18 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
else
{
/* Encrypt the password to the requested format. */
- shadow_pass = encrypt_password(Password_encryption, stmt->role,
+ validate_and_get_salt(stmt->role, &salt, &logdetail);
+ shadow_pass = encrypt_password(Password_encryption, salt,
password);
MemSet(new_password_record, 0, sizeof(new_password_record));
MemSet(new_password_record_nulls, false, sizeof(new_password_record_nulls));
-
+ if (passname != NULL)
+ new_password_record[Anum_pg_auth_password_name - 1] =
+ DirectFunctionCall1(namein, CStringGetDatum(passname));
+ else
+ new_password_record[Anum_pg_auth_password_name - 1] =
+ DirectFunctionCall1(namein, CStringGetDatum(default_passname));
/* open password table and insert it. */
pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
pg_auth_password_dsc = RelationGetDescr(pg_auth_password_rel);
@@ -560,6 +642,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
ListCell *option;
char *rolename;
char *password = NULL; /* user password */
+ char *passname = NULL; /* password name for managing multiple passwords */
int connlimit = -1; /* maximum connections allowed */
char *validUntil = NULL; /* time the login is valid until */
Datum validUntil_datum; /* same, as timestamptz Datum */
@@ -575,6 +658,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
DefElem *drolemembers = NULL;
DefElem *dvalidUntil = NULL;
DefElem *dbypassRLS = NULL;
+ DefElem *dpassName = NULL;
Oid roleid;
check_rolespec_name(stmt->role,
@@ -652,6 +736,12 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
errorConflictingDefElem(defel, pstate);
dbypassRLS = defel;
}
+ else if (strcmp(defel->defname, "passname") == 0)
+ {
+ if (dpassName)
+ errorConflictingDefElem(defel, pstate);
+ dpassName = defel;
+ }
else
elog(ERROR, "option \"%s\" not recognized",
defel->defname);
@@ -669,6 +759,8 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
}
if (dvalidUntil)
validUntil = strVal(dvalidUntil->arg);
+ if (dpassName)
+ passname = strVal(dpassName->arg);
/*
* Scan the pg_authid relation to be certain the user exists.
@@ -805,8 +897,9 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
/* password */
if (password)
{
- char *shadow_pass;
- const char *logdetail = NULL;
+ char *salt = NULL;
+ const char *logdetail = NULL;
+ char *shadow_pass;
/* Like in CREATE USER, don't allow an empty password. */
if (password[0] == '\0' ||
@@ -818,13 +911,26 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
}
else
{
- /* Encrypt the password to the requested format. */
- shadow_pass = encrypt_password(Password_encryption, rolename,
- password);
- new_password_record[Anum_pg_auth_password_password - 1] =
- CStringGetTextDatum(shadow_pass);
+ if (!validate_and_get_salt(rolename, &salt, &logdetail))
+ new_password_record_nulls[Anum_pg_auth_password_password - 1] = true;
+ else
+ {
+ /* Encrypt the password to the requested format. */
+ shadow_pass = encrypt_password(Password_encryption, salt,
+ password);
+ new_password_record[Anum_pg_auth_password_password - 1] =
+ CStringGetTextDatum(shadow_pass);
+
+ new_password_record_repl[Anum_pg_auth_password_password - 1] = true;
+
+ if (passname)
+ new_password_record[Anum_pg_auth_password_name - 1] =
+ DirectFunctionCall1(namein, CStringGetDatum(passname));
+ else
+ new_password_record[Anum_pg_auth_password_name - 1] =
+ DirectFunctionCall1(namein, CStringGetDatum(default_passname));
+ }
}
- new_password_record_repl[Anum_pg_auth_password_password - 1] = true;
}
/* unset password */
@@ -859,7 +965,10 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
pg_auth_password_dsc = RelationGetDescr(pg_auth_password_rel);
- password_tuple = SearchSysCache1(AUTHPASSWORD, ObjectIdGetDatum(roleid));
+ if (dpassName)
+ password_tuple = SearchSysCache2(AUTHPASSWORDNAME, ObjectIdGetDatum(roleid), CStringGetDatum(passname));
+ else
+ password_tuple = SearchSysCache2(AUTHPASSWORDNAME, ObjectIdGetDatum(roleid), CStringGetDatum(default_passname));
if (new_password_record_nulls[Anum_pg_auth_password_password - 1] == true) /* delete existing password */
{
@@ -1157,14 +1266,23 @@ DropRole(DropRoleStmt *stmt)
DeleteSharedSecurityLabel(roleid, AuthIdRelationId);
/*
- * Drop password
+ * Drop password(s)
*/
- tuple = SearchSysCache1(AUTHPASSWORD, roleid);
- if (HeapTupleIsValid(tuple)) {
- CatalogTupleDelete(pg_auth_password_rel, &tuple->t_self);
- ReleaseSysCache(tuple);
+ ScanKeyInit(&scankey,
+ Anum_pg_auth_password_roleid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(roleid));
+
+ sscan = systable_beginscan(pg_auth_password_rel, AuthPasswordRoleOidIndexId,
+ true, NULL, 1, &scankey);
+
+ while (HeapTupleIsValid(tmp_tuple = systable_getnext(sscan)))
+ {
+ CatalogTupleDelete(pg_auth_password_rel, &tmp_tuple->t_self);
}
+ systable_endscan(sscan);
+
/*
* Remove settings for this role.
*/
@@ -1201,6 +1319,9 @@ RenameRole(const char *oldname, const char *newname)
newtuple,
passtuple;
TupleDesc dsc;
+ ScanKeyData scankey;
+ SysScanDesc sscan;
+
Relation rel;
Datum datum;
bool isnull = true;
@@ -1211,6 +1332,7 @@ RenameRole(const char *oldname, const char *newname)
Oid roleid;
ObjectAddress address;
Form_pg_authid authform;
+ Relation pg_auth_password_rel;
rel = table_open(AuthIdRelationId, RowExclusiveLock);
dsc = RelationGetDescr(rel);
@@ -1301,28 +1423,38 @@ RenameRole(const char *oldname, const char *newname)
CStringGetDatum(newname));
repl_null[Anum_pg_authid_rolname - 1] = false;
- passtuple = SearchSysCache1(AUTHPASSWORD, roleid);
+ ScanKeyInit(&scankey,
+ Anum_pg_auth_password_roleid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(roleid));
+
+ pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
- if (HeapTupleIsValid(passtuple))
- datum = SysCacheGetAttr(AUTHPASSWORD, passtuple,
- Anum_pg_auth_password_password, &isnull);
+ sscan = systable_beginscan(pg_auth_password_rel, AuthPasswordRoleOidIndexId,
+ true, NULL, 1, &scankey);
- if (!isnull && get_password_type(TextDatumGetCString(datum)) == PASSWORD_TYPE_MD5)
+ while (HeapTupleIsValid(passtuple = systable_getnext(sscan)))
{
- Relation pg_auth_password_rel;
+ datum = SysCacheGetAttr(AUTHPASSWORDNAME, passtuple,
+ Anum_pg_auth_password_password, &isnull);
- /* MD5 uses the username as salt, so just clear it on a rename */
- pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
+ if (!isnull && get_password_type(TextDatumGetCString(datum)) == PASSWORD_TYPE_MD5)
+ {
- if (HeapTupleIsValid(passtuple)) {
- CatalogTupleDelete(pg_auth_password_rel, &passtuple->t_self);
- ReleaseSysCache(passtuple);
+ /* MD5 uses the username as salt, so just clear it on a rename */
+
+ if (HeapTupleIsValid(passtuple)) {
+ CatalogTupleDelete(pg_auth_password_rel, &passtuple->t_self);
+ ereport(NOTICE,
+ (errmsg("MD5 password cleared because of role rename")));
+
+ }
}
- table_close(pg_auth_password_rel, NoLock);
- ereport(NOTICE,
- (errmsg("MD5 password cleared because of role rename")));
}
+ systable_endscan(sscan);
+ table_close(pg_auth_password_rel, NoLock);
+
newtuple = heap_modify_tuple(oldtuple, dsc, repl_val, repl_null, repl_repl);
CatalogTupleUpdate(rel, &oldtuple->t_self, newtuple);
diff --git a/src/backend/libpq/auth-sasl.c b/src/backend/libpq/auth-sasl.c
index 805b3695b7..652c18fa94 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,
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);
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 795f1cba55..7dfc66b64b 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,13 @@ typedef enum
SCRAM_AUTH_FINISHED
} scram_state_enum;
+typedef struct
+{
+ uint8 StoredKey[SCRAM_KEY_LEN];
+ uint8 ServerKey[SCRAM_KEY_LEN];
+} scram_secret;
+
+
typedef struct
{
scram_state_enum state;
@@ -141,10 +148,16 @@ typedef struct
Port *port;
bool channel_binding_in_use;
+ /*
+ * 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_KEY_LEN];
- uint8 ServerKey[SCRAM_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;
@@ -220,17 +233,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_auth_password.password.
+ * 'passwords' are the role's stored secrets, from pg_auth_password.password.
* 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;
@@ -260,46 +276,47 @@ 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->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->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 */
+ 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;
}
/*
@@ -310,8 +327,10 @@ scram_init(Port *port, const char *selected_mech, const char *shadow_pass)
*/
if (!got_secret)
{
+ state->secrets = palloc0(sizeof(scram_secret));
+
mock_scram_secret(state->port->user_name, &state->iterations,
- &state->salt, state->StoredKey, state->ServerKey);
+ &state->salt, state->secrets[0].StoredKey, state->secrets[0].ServerKey);
state->doomed = true;
}
@@ -459,7 +478,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;
@@ -476,11 +495,13 @@ 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)
+ pg_b64_decode(salt, strlen(salt), saltbuf, SCRAM_DEFAULT_SALT_LEN);
result = scram_build_secret(saltbuf, SCRAM_DEFAULT_SALT_LEN,
SCRAM_DEFAULT_ITERATIONS, password,
@@ -1114,47 +1135,57 @@ verify_client_proof(scram_state *state)
uint8 ClientSignature[SCRAM_KEY_LEN];
uint8 ClientKey[SCRAM_KEY_LEN];
uint8 client_StoredKey[SCRAM_KEY_LEN];
- pg_hmac_ctx *ctx = pg_hmac_create(PG_SHA256);
- 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, SCRAM_KEY_LEN) < 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, sizeof(ClientSignature)) < 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(PG_SHA256);
+ elog(LOG, "Trying to verify password %d", j);
+
+ if (pg_hmac_init(ctx, state->secrets[j].StoredKey, SCRAM_KEY_LEN) < 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, sizeof(ClientSignature)) < 0)
+ {
+ elog(LOG, "could not calculate client signature");
+ pg_hmac_free(ctx);
+ continue;
+ }
- pg_hmac_free(ctx);
+ elog(LOG, "success on %d", j);
- /* Extract the ClientKey that the client calculated from the proof */
- for (i = 0; i < SCRAM_KEY_LEN; i++)
- ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
+ pg_hmac_free(ctx);
- /* Hash it one more time, and compare with StoredKey */
- if (scram_H(ClientKey, SCRAM_KEY_LEN, client_StoredKey, &errstr) < 0)
- elog(ERROR, "could not hash stored key: %s", errstr);
+ for (i = 0; i < SCRAM_KEY_LEN; i++)
+ ClientKey[i] = state->ClientProof[i] ^ ClientSignature[i];
- if (memcmp(client_StoredKey, state->StoredKey, SCRAM_KEY_LEN) != 0)
- return false;
+ /* Hash it one more time, and compare with StoredKey */
+ if (scram_H(ClientKey, SCRAM_KEY_LEN, client_StoredKey, &errstr) < 0)
+ elog(ERROR, "could not hash stored key: %s", errstr);
- return true;
+ if (memcmp(client_StoredKey, state->secrets[j].StoredKey, SCRAM_KEY_LEN) == 0) {
+ elog(LOG, "Moving forward with Password %d", j);
+ state->chosen_secret = j;
+ return true;
+ }
+ }
+
+ return false;
}
/*
@@ -1380,7 +1411,7 @@ build_server_final_message(scram_state *state)
pg_hmac_ctx *ctx = pg_hmac_create(PG_SHA256);
/* calculate ServerSignature */
- if (pg_hmac_init(ctx, state->ServerKey, SCRAM_KEY_LEN) < 0 ||
+ if (pg_hmac_init(ctx, state->secrets[state->chosen_secret].ServerKey, SCRAM_KEY_LEN) < 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 269cf286ad..4cc59d39ce 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -58,8 +58,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, const char **logdetail);
/*----------------------------------------------------------------
@@ -790,8 +789,9 @@ static int
CheckPasswordAuth(Port *port, const char **logdetail)
{
char *passwd;
- int result;
- char *shadow_pass;
+ int result = STATUS_ERROR;
+ int i, num;
+ char **passwords;
sendAuthRequest(port, AUTH_REQ_PASSWORD, NULL, 0);
@@ -799,17 +799,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);
+ passwords = get_role_passwords(port->user_name, logdetail, &num);
+ if (passwords != NULL) {
+ for (i = 0; i < num; 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; i++)
+ pfree(passwords[i]);
+
+ pfree(passwords);
}
- else
- result = STATUS_ERROR;
- if (shadow_pass)
- pfree(shadow_pass);
pfree(passwd);
if (result == STATUS_OK)
@@ -824,57 +828,42 @@ 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;
+ char **passwords;
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);
-
- /*
- * 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
- * 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)
- pwtype = Password_encryption;
- else
- pwtype = get_password_type(shadow_pass);
+ /* First look up the user's passwords. */
+ passwords = get_role_passwords(port->user_name, logdetail, &num);
/*
* 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 PW 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);
- else
- auth_result = CheckSASLAuth(&pg_be_scram_mech, port, shadow_pass,
- logdetail);
+ if (passwords != NULL) {
+ for (i = 0; i < num; i++)
+ if (get_password_type(passwords[i]) == PASSWORD_TYPE_SCRAM_SHA_256)
+ scram_pw_avail = true;
+
+ if (port->hba->auth_method == uaMD5 && !scram_pw_avail)
+ auth_result = CheckMD5Auth(port, (const char **) passwords, num, logdetail);
+ else
+ auth_result = CheckSASLAuth(&pg_be_scram_mech, port, (const char **) passwords, num,
+ logdetail);
- if (shadow_pass)
- pfree(shadow_pass);
+ for (i = 0; i < num; i++)
+ pfree(passwords[i]);
- /*
- * If get_role_password() returned error, return error, even if the
- * authentication succeeded.
- */
- if (!shadow_pass)
- {
- Assert(auth_result != STATUS_OK);
- return STATUS_ERROR;
+ pfree(passwords);
}
if (auth_result == STATUS_OK)
@@ -884,11 +873,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, const char **logdetail)
{
char md5Salt[4]; /* Password salt */
char *passwd;
- int result;
+ int result = STATUS_ERROR;
+ int i;
if (Db_user_namespace)
ereport(FATAL,
@@ -909,12 +899,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; 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 745e61034c..09b6bebf40 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -23,6 +23,7 @@
#include "libpq/scram.h"
#include "miscadmin.h"
#include "utils/builtins.h"
+#include "utils/catcache.h"
#include "utils/syscache.h"
#include "utils/timestamp.h"
@@ -34,13 +35,15 @@
* 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)
{
- HeapTuple roleTup, passTup;
+ HeapTuple roleTup;
Datum datum;
bool isnull;
- char *shadow_pass;
+ char **passwords;
+ CatCList *passlist;
+ int i;
/* Get role info from pg_authid */
roleTup = SearchSysCache1(AUTHNAME, PointerGetDatum(role));
@@ -52,30 +55,34 @@ get_role_password(const char *role, const char **logdetail)
}
datum = SysCacheGetAttr(AUTHNAME, roleTup, Anum_pg_authid_oid, &isnull);
-
- passTup = SearchSysCache1(AUTHPASSWORD, datum);
+ ReleaseSysCache(roleTup);
+ /* Find any existing password that is not the one being updated to get the salt */
+ passlist = SearchSysCacheList1(AUTHPASSWORDNAME, datum);
+ *num = passlist->n_members;
- if (!HeapTupleIsValid(passTup))
+ if (passlist->n_members == 0)
{
+ *num = 0;
*logdetail = psprintf(_("User \"%s\" has no password assigned."),
role);
+ ReleaseCatCacheList(passlist);
return NULL; /* user has no password */
}
- datum = SysCacheGetAttr(AUTHPASSWORD, passTup,
- Anum_pg_auth_password_password, &isnull);
- ReleaseSysCache(roleTup);
- if (isnull) /* this should not happen any more but just in case */
+ passwords = palloc0(sizeof(char *) * passlist->n_members);
+
+ for (i = 0; i < passlist->n_members; i++)
{
- ReleaseSysCache(passTup);
- *logdetail = psprintf(_("User \"%s\" has no password assigned."),
- role);
- return NULL; /* user has no password */
+ HeapTuple tup = &passlist->members[i]->tuple;
+
+ datum = SysCacheGetAttr(AUTHPASSWORDNAME, tup,
+ Anum_pg_auth_password_password, &isnull);
+ passwords[i] = TextDatumGetCString(datum);
}
- shadow_pass = TextDatumGetCString(datum);
- ReleaseSysCache(passTup);
- return shadow_pass;
+ ReleaseCatCacheList(passlist);
+
+ return passwords;
}
/*
@@ -107,7 +114,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);
@@ -128,13 +135,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);
+ 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/parser/gram.y b/src/backend/parser/gram.y
index 969c9c158f..7c1d023b7e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -826,8 +826,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
ORDER ORDINALITY OTHERS OUT_P OUTER_P
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
- PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD PATH
- PLACING PLAN PLANS POLICY
+ PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSNAME PASSWORD
+ PATH PLACING PLAN PLANS POLICY
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
@@ -1261,6 +1261,15 @@ AlterOptRoleElem:
errhint("Remove UNENCRYPTED to store the password in encrypted form instead."),
parser_errposition(@1)));
}
+ | PASSNAME Sconst
+ {
+ $$ = makeDefElem("passname",
+ (Node *)makeString($2), @1);
+ }
+ | VALID FOR Sconst
+ {
+ $$ = makeDefElem("validFor", (Node *)makeString($3), @1);
+ }
| INHERIT
{
$$ = makeDefElem("inherit", (Node *) makeBoolean(true), @1);
@@ -17863,6 +17872,7 @@ unreserved_keyword:
| PARTIAL
| PARTITION
| PASSING
+ | PASSNAME
| PASSWORD
| PATH
| PLAN
@@ -18481,6 +18491,7 @@ bare_label_keyword:
| PARTIAL
| PARTITION
| PASSING
+ | PASSNAME
| PASSWORD
| PATH
| PLACING
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index 7c8f864a80..e487cef5b3 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -1104,7 +1104,7 @@ IndexScanOK(CatCache *cache, ScanKey cur_skey)
case AUTHNAME:
case AUTHOID:
- case AUTHPASSWORD:
+ case AUTHPASSWORDNAME:
case AUTHMEMMEMROLE:
case DATABASEOID:
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index de2b3ec1eb..9879120bf7 100644
--- a/src/backend/utils/cache/syscache.c
+++ b/src/backend/utils/cache/syscache.c
@@ -256,12 +256,12 @@ static const struct cachedesc cacheinfo[] = {
},
8
},
- {AuthPasswordRelationId, /* AUTHPASSWORD */
+ {AuthPasswordRelationId, /* AUTHPASSWORDNAME */
AuthPasswordRoleOidIndexId,
- 1,
+ 2,
{
Anum_pg_auth_password_roleid,
- 0,
+ Anum_pg_auth_password_name,
0,
0
},
diff --git a/src/include/catalog/pg_auth_password.h b/src/include/catalog/pg_auth_password.h
index beaa2d40b9..2100cda758 100644
--- a/src/include/catalog/pg_auth_password.h
+++ b/src/include/catalog/pg_auth_password.h
@@ -24,11 +24,14 @@
* typedef struct FormData_pg_auth_password
* ----------------
*/
-CATALOG(pg_auth_password,4548,AuthPasswordRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(4549,AuthPasswordRelation_Rowtype_Id) BKI_SCHEMA_MACRO
+CATALOG(pg_auth_password,4551,AuthPasswordRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(4552,AuthPasswordRelation_Rowtype_Id) BKI_SCHEMA_MACRO
{
Oid roleid BKI_LOOKUP(pg_authid); /* ID of a role */
+ NameData name; /* name of password for multiple password support */
+
#ifdef CATALOG_VARLEN /* variable-length fields start here */
- text password; /* password */
+ text password BKI_FORCE_NOT_NULL; /* password */
+ timestamptz expiration BKI_FORCE_NULL; /* password expiration time, if any */
#endif
} FormData_pg_auth_password;
@@ -44,7 +47,6 @@ DECLARE_TOAST(pg_auth_password, 4175, 4176);
typedef FormData_pg_auth_password *Form_pg_auth_password;
-DECLARE_UNIQUE_INDEX_PKEY(pg_auth_password_roleoid_index, 4550, AuthPasswordRoleOidIndexId, on pg_auth_password using btree(roleid oid_ops));
-
+DECLARE_UNIQUE_INDEX_PKEY(pg_auth_password_roleoid_index, 4553, AuthPasswordRoleOidIndexId, on pg_auth_password using btree(roleid oid_ops, name name_ops));
#endif /* PG_AUTH_PASSWORD_H */
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index 3e1aae5785..1c14f60a05 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -24,7 +24,7 @@ typedef void (*check_password_hook_type) (const char *username, const char *shad
extern PGDLLIMPORT check_password_hook_type check_password_hook;
-extern bool is_role_valid(const char *rolename, char **logdetail);
+extern bool is_role_valid(const char *rolename, const char **logdetail);
extern Oid CreateRole(ParseState *pstate, CreateRoleStmt *stmt);
extern Oid AlterRole(ParseState *pstate, AlterRoleStmt *stmt);
extern Oid AlterRoleSet(AlterRoleSetStmt *stmt);
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index b8ff8ccb41..84c9c4b4f4 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 39ccf8f0e3..27b4e57033 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, const char **logdetail);
#endif /* PG_SASL_H */
diff --git a/src/include/libpq/scram.h b/src/include/libpq/scram.h
index c51e848c24..70b01b511f 100644
--- a/src/include/libpq/scram.h
+++ b/src/include/libpq/scram.h
@@ -21,7 +21,7 @@
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, char **salt,
uint8 *stored_key, uint8 *server_key);
extern bool scram_verify_plain_password(const char *username,
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index ae35f03251..ec79fafabf 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -334,6 +334,7 @@ PG_KEYWORD("parser", PARSER, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("passname", PASSNAME, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("path", PATH, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h
index c7bc648f46..a543c18188 100644
--- a/src/include/utils/syscache.h
+++ b/src/include/utils/syscache.h
@@ -43,7 +43,7 @@ enum SysCacheIdentifier
AUTHMEMROLEMEM,
AUTHNAME,
AUTHOID,
- AUTHPASSWORD,
+ AUTHPASSWORDNAME,
CASTSOURCETARGET,
CLAAMNAMENSP,
CLAOID,
diff --git a/src/test/regress/expected/password.out b/src/test/regress/expected/password.out
index 4ffc41a545..11fb244021 100644
--- a/src/test/regress/expected/password.out
+++ b/src/test/regress/expected/password.out
@@ -26,7 +26,7 @@ CREATE ROLE regress_passwd4 PASSWORD NULL;
-- run. Use a regular expression to mask the changing parts.
SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
FROM pg_authid
- LEFT JOIN pg_auth_password p
+ LEFT JOIN pg_auth_password p
ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd%'
ORDER BY rolname, password;
@@ -44,7 +44,7 @@ NOTICE: MD5 password cleared because of role rename
-- md5 entry should have been removed
SELECT rolname, password
FROM pg_authid
- LEFT JOIN pg_auth_password p
+ LEFT JOIN pg_auth_password p
ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd2_new'
ORDER BY rolname, password;
@@ -78,7 +78,7 @@ CREATE ROLE regress_passwd7 PASSWORD 'md5012345678901234567890123456789zz';
CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz';
SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked
FROM pg_authid
- LEFT JOIN pg_auth_password p
+ LEFT JOIN pg_auth_password p
ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd%'
ORDER BY rolname, password;
@@ -102,7 +102,7 @@ NOTICE: empty string is not a valid password, clearing password
ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4=';
NOTICE: empty string is not a valid password, clearing password
SELECT password FROM pg_authid
-LEFT JOIN pg_auth_password p
+LEFT JOIN pg_auth_password p
ON pg_authid.oid = p.roleid WHERE rolname='regress_passwd_empty';
password
----------
@@ -120,7 +120,7 @@ CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941
-- should not contain the original salt.
SELECT rolname, password not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed
FROM pg_authid
- LEFT JOIN pg_auth_password p
+ LEFT JOIN pg_auth_password p
ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd_sha_len%'
ORDER BY rolname;
@@ -146,7 +146,7 @@ DROP ROLE regress_passwd_sha_len2;
-- all entries should have been removed
SELECT rolname, password
FROM pg_authid
- LEFT JOIN pg_auth_password p
+ LEFT JOIN pg_auth_password p
ON pg_authid.oid = p.roleid
WHERE rolname LIKE 'regress_passwd%'
ORDER BY rolname, password;
--
2.34.1
From 80f2b1948c299cbc799901d36a23d44bee36e00b Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@snowman.net>
Date: Thu, 30 Jun 2022 18:17:50 -0400
Subject: [PATCH 3/3] Per-password expiration
To build on the multi-password support this
adds per-password expiration, either passed in via ALTER ROLE/CREATE
ROLE with the grammar EXPIRES IN, or via a system-wide setting
called password_valid_duration
Author: Joshua Brindle
---
src/backend/commands/user.c | 121 ++++++++++++++++++++++++++++++--
src/backend/commands/variable.c | 105 +++++++++++++++++++++++++++
src/backend/libpq/auth.c | 9 ++-
src/backend/libpq/crypt.c | 38 ++++++++--
src/backend/parser/gram.y | 10 ++-
src/backend/utils/misc/guc.c | 12 ++++
src/include/commands/user.h | 3 +
src/include/commands/variable.h | 4 ++
src/include/parser/kwlist.h | 1 +
9 files changed, 290 insertions(+), 13 deletions(-)
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 94747d7c3c..9c46af5123 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -47,8 +47,9 @@
Oid binary_upgrade_next_pg_authid_oid = InvalidOid;
-/* GUC parameter */
+/* GUC parameters */
int Password_encryption = PASSWORD_TYPE_SCRAM_SHA_256;
+Interval *default_password_duration = NULL;
/* default password name */
const char* default_passname = "__def__";
@@ -166,6 +167,84 @@ validate_and_get_salt(char *rolename, char **salt, const char **logdetail)
return true;
}
+static
+Datum
+expires_in_datum(DefElem *passExpiresIn)
+{
+ Interval *interval;
+ Node *arg;
+ A_Const *con;
+ TimestampTz now = GetCurrentTimestamp();
+ Datum passExpiresIn_datum;
+ char *dateout;
+
+ if (default_password_duration != NULL)
+ {
+ /* The default duration GUC is set, use it if nothing came from SQL
+ * or if something came from SQL, reject it if not from a superuser
+ */
+
+ if (passExpiresIn != NULL)
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to override password_validity_duration GUC")));
+ else
+ goto bypass;
+ else
+ {
+ passExpiresIn_datum = DirectFunctionCall2(timestamptz_pl_interval,
+ TimestampGetDatum(now),
+ PointerGetDatum(default_password_duration));
+
+ dateout =
+ DatumGetCString(DirectFunctionCall1(timestamp_out,
+ passExpiresIn_datum));
+
+ ereport(NOTICE,
+ (errmsg("Password will expire at: \"%s\" (from GUC)", dateout)));
+
+ return passExpiresIn_datum;
+ }
+ }
+
+ if (passExpiresIn == NULL) /* No duration requested via SQL and no system default, no expiration */
+ return PointerGetDatum(NULL);
+
+bypass:
+ arg = (Node *)passExpiresIn->arg;
+ if (IsA(arg, TypeCast))
+ {
+ TypeCast *tc = (TypeCast *) passExpiresIn->arg;
+ arg = tc->arg;
+ }
+
+ if (!IsA(arg, A_Const))
+ {
+ elog(ERROR, "unrecognized node type: %d", (int) nodeTag(arg));
+ return PointerGetDatum(NULL);
+ }
+ con = (A_Const *) arg;
+
+ interval = DatumGetIntervalP(DirectFunctionCall3(interval_in,
+ CStringGetDatum(strVal(&con->val)),
+ ObjectIdGetDatum(InvalidOid),
+ Int32GetDatum(-1)));
+
+ passExpiresIn_datum = DirectFunctionCall2(timestamptz_pl_interval,
+ TimestampGetDatum(now),
+ PointerGetDatum(interval));
+
+ dateout =
+ DatumGetCString(DirectFunctionCall1(timestamp_out,
+ passExpiresIn_datum));
+
+ ereport(NOTICE,
+ (errmsg("Password will expire at: \"%s\" (from SQL)", dateout)));
+
+ return passExpiresIn_datum;
+}
+
/*
* CREATE ROLE
*/
@@ -210,7 +289,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
DefElem *dvalidUntil = NULL;
DefElem *dbypassRLS = NULL;
DefElem *dpassName = NULL;
-
+ DefElem *dpassExpiresIn = NULL;
/* The defaults can vary depending on the original statement type */
switch (stmt->stmt_type)
@@ -319,6 +398,12 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
errorConflictingDefElem(defel, pstate);
dpassName = defel;
}
+ else if (strcmp(defel->defname, "expiresin") == 0)
+ {
+ if (dpassExpiresIn)
+ errorConflictingDefElem(defel, pstate);
+ dpassExpiresIn = defel;
+ }
else
elog(ERROR, "option \"%s\" not recognized",
@@ -507,6 +592,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
Relation pg_auth_password_rel;
TupleDesc pg_auth_password_dsc;
HeapTuple new_tuple;
+ Datum passExpiresIn_datum;
/*
* Don't allow an empty password. Libpq treats an empty password the
@@ -528,19 +614,27 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
}
else
{
+ MemSet(new_password_record, 0, sizeof(new_password_record));
+ MemSet(new_password_record_nulls, false, sizeof(new_password_record_nulls));
+
+ passExpiresIn_datum = expires_in_datum(dpassExpiresIn);
+ if (passExpiresIn_datum != PointerGetDatum(NULL))
+ new_password_record[Anum_pg_auth_password_expiration - 1] = passExpiresIn_datum;
+ else
+ new_password_record_nulls[Anum_pg_auth_password_expiration - 1] = true;
+
/* Encrypt the password to the requested format. */
validate_and_get_salt(stmt->role, &salt, &logdetail);
shadow_pass = encrypt_password(Password_encryption, salt,
password);
- MemSet(new_password_record, 0, sizeof(new_password_record));
- MemSet(new_password_record_nulls, false, sizeof(new_password_record_nulls));
if (passname != NULL)
new_password_record[Anum_pg_auth_password_name - 1] =
DirectFunctionCall1(namein, CStringGetDatum(passname));
else
new_password_record[Anum_pg_auth_password_name - 1] =
DirectFunctionCall1(namein, CStringGetDatum(default_passname));
+
/* open password table and insert it. */
pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock);
pg_auth_password_dsc = RelationGetDescr(pg_auth_password_rel);
@@ -647,6 +741,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
char *validUntil = NULL; /* time the login is valid until */
Datum validUntil_datum; /* same, as timestamptz Datum */
bool validUntil_null;
+ Datum passExpiresIn_datum; /* Time period until password expires */
DefElem *dpassword = NULL;
DefElem *dissuper = NULL;
DefElem *dinherit = NULL;
@@ -659,6 +754,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
DefElem *dvalidUntil = NULL;
DefElem *dbypassRLS = NULL;
DefElem *dpassName = NULL;
+ DefElem *dpassExpiresIn = NULL;
Oid roleid;
check_rolespec_name(stmt->role,
@@ -742,6 +838,12 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
errorConflictingDefElem(defel, pstate);
dpassName = defel;
}
+ else if (strcmp(defel->defname, "expiresin") == 0)
+ {
+ if (dpassExpiresIn)
+ errorConflictingDefElem(defel, pstate);
+ dpassExpiresIn = defel;
+ }
else
elog(ERROR, "option \"%s\" not recognized",
defel->defname);
@@ -762,6 +864,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
if (dpassName)
passname = strVal(dpassName->arg);
+
/*
* Scan the pg_authid relation to be certain the user exists.
*/
@@ -848,6 +951,13 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
MemSet(new_password_record_nulls, false, sizeof(new_password_record_nulls));
MemSet(new_password_record_repl, false, sizeof(new_password_record_repl));
+ passExpiresIn_datum = expires_in_datum(dpassExpiresIn);
+ if (passExpiresIn_datum != PointerGetDatum(NULL)) {
+ new_password_record[Anum_pg_auth_password_expiration - 1] = passExpiresIn_datum;
+ new_password_record_repl[Anum_pg_auth_password_expiration - 1] = true;
+ }
+ else
+ new_password_record_nulls[Anum_pg_auth_password_expiration - 1] = true;
/*
* issuper/createrole/etc
@@ -957,7 +1067,8 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
ReleaseSysCache(tuple);
heap_freetuple(new_tuple);
- if (new_password_record_repl[Anum_pg_auth_password_password - 1] == true)
+ if (new_password_record_repl[Anum_pg_auth_password_password - 1] == true
+ || new_password_record_repl[Anum_pg_auth_password_expiration - 1] == true)
{
Relation pg_auth_password_rel;
TupleDesc pg_auth_password_dsc;
diff --git a/src/backend/commands/variable.c b/src/backend/commands/variable.c
index e5ddcda0b4..ca85a10b80 100644
--- a/src/backend/commands/variable.c
+++ b/src/backend/commands/variable.c
@@ -24,6 +24,7 @@
#include "access/xlog.h"
#include "catalog/pg_authid.h"
#include "commands/variable.h"
+#include "commands/user.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
#include "utils/acl.h"
@@ -933,3 +934,107 @@ show_role(void)
/* Otherwise we can just use the GUC string */
return role_string ? role_string : "none";
}
+
+
+/*
+ * check_password_duration: GUC check_hook for password_duration
+ */
+bool
+check_password_duration(char **newval, void **extra, GucSource source)
+{
+ Interval *new_interval;
+ char *endptr;
+
+ const char *valueptr = *newval;
+ char *val;
+ Interval *interval;
+
+ if (newval == NULL || *newval == NULL) {
+ extra = NULL;
+ return true;
+ }
+
+ elog(NOTICE,"Setting password duration to \"%s\"",
+ *newval);
+
+ while (isspace((unsigned char) *valueptr))
+ valueptr++;
+ if (*valueptr != '\'') {
+ val = pstrdup(valueptr);
+ }
+ else
+ {
+ valueptr++;
+ val = pstrdup(valueptr);
+ /* Check and remove trailing quote */
+ endptr = strchr(val, '\'');
+ if (!endptr || endptr[1] != '\0')
+ {
+ pfree(val);
+ return false;
+ }
+ *endptr = '\0';
+ }
+
+ /*
+ * Try to parse it. XXX an invalid interval format will result in
+ * ereport(ERROR), which is not desirable for GUC. We did what we
+ * could to guard against this in flatten_set_variable_args, but a
+ * string coming in from postgresql.conf might contain anything.
+ */
+ interval = DatumGetIntervalP(DirectFunctionCall3(interval_in,
+ CStringGetDatum(val),
+ ObjectIdGetDatum(InvalidOid),
+ Int32GetDatum(-1)));
+
+ pfree(val);
+
+ if (!interval) {
+ return false;
+ }
+
+ new_interval = malloc(sizeof(Interval));
+ memcpy(new_interval, interval, sizeof(Interval));
+ pfree(interval);
+
+ /*
+ * Pass back data for assign_password_validity to use
+ */
+ *extra = malloc(sizeof(Interval *));
+ if (!*extra)
+ return false;
+ *((Interval **) *extra) = new_interval;
+
+ return true;
+}
+
+/*
+ * assign_password_validity: GUC assign_hook for timezone
+ */
+void
+assign_password_duration(const char *newval, void *extra)
+{
+ if (extra == NULL)
+ default_password_duration = NULL;
+ else
+ default_password_duration = *((Interval **) extra);
+}
+
+/*
+ * show_password_validity: GUC show_hook for timezone
+ */
+const char *
+show_password_duration(void)
+{
+ const char *intervalout;
+ if (default_password_duration == NULL) {
+ return "";
+ }
+ intervalout = DatumGetCString(DirectFunctionCall1(interval_out,
+ PointerGetDatum(default_password_duration)));
+
+ if (intervalout != NULL)
+ return intervalout;
+
+ return "";
+}
\ No newline at end of file
diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c
index 4cc59d39ce..857c58027c 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -860,9 +860,14 @@ CheckPWChallengeAuth(Port *port, const char **logdetail)
auth_result = CheckSASLAuth(&pg_be_scram_mech, port, (const char **) passwords, num,
logdetail);
- for (i = 0; i < num; i++)
- pfree(passwords[i]);
+ for (i = 0; i < num; i++) {
+ if (passwords[i] != NULL)
+ pfree(passwords[i]);
+ else
+ ereport(LOG,
+ (errmsg("Password %d was null", i)));
+ }
pfree(passwords);
}
diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 09b6bebf40..ae3ca1175f 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -40,10 +40,12 @@ get_role_passwords(const char *role, const char **logdetail, int *num)
{
HeapTuple roleTup;
Datum datum;
+ TimestampTz current, vuntil = 0;
+
bool isnull;
char **passwords;
CatCList *passlist;
- int i;
+ int i, j = 0, num_valid_passwords = 0;
/* Get role info from pg_authid */
roleTup = SearchSysCache1(AUTHNAME, PointerGetDatum(role));
@@ -51,6 +53,7 @@ get_role_passwords(const char *role, const char **logdetail, int *num)
{
*logdetail = psprintf(_("Role \"%s\" does not exist."),
role);
+ *num = 0;
return NULL; /* no such user */
}
@@ -58,7 +61,6 @@ get_role_passwords(const char *role, const char **logdetail, int *num)
ReleaseSysCache(roleTup);
/* Find any existing password that is not the one being updated to get the salt */
passlist = SearchSysCacheList1(AUTHPASSWORDNAME, datum);
- *num = passlist->n_members;
if (passlist->n_members == 0)
{
@@ -69,15 +71,41 @@ get_role_passwords(const char *role, const char **logdetail, int *num)
return NULL; /* user has no password */
}
- passwords = palloc0(sizeof(char *) * passlist->n_members);
+ current = GetCurrentTimestamp();
+
+ for (i = 0; i < passlist->n_members; i++)
+ {
+ HeapTuple tup = &passlist->members[i]->tuple;
+
+ datum = SysCacheGetAttr(AUTHPASSWORDNAME, tup,
+ Anum_pg_auth_password_expiration, &isnull);
+
+ if (!isnull)
+ vuntil = DatumGetTimestampTz(datum);
+
+ if (isnull || vuntil > current)
+ num_valid_passwords++;
+ }
+
+ passwords = palloc0(sizeof(char *) * num_valid_passwords);
+ *num = num_valid_passwords;
for (i = 0; i < passlist->n_members; i++)
{
HeapTuple tup = &passlist->members[i]->tuple;
datum = SysCacheGetAttr(AUTHPASSWORDNAME, tup,
- Anum_pg_auth_password_password, &isnull);
- passwords[i] = TextDatumGetCString(datum);
+ Anum_pg_auth_password_expiration, &isnull);
+
+ if (!isnull)
+ vuntil = DatumGetTimestampTz(datum);
+
+ if (isnull || vuntil > current)
+ {
+ datum = SysCacheGetAttr(AUTHPASSWORDNAME, tup,
+ Anum_pg_auth_password_password, &isnull);
+ passwords[j++] = pstrdup(TextDatumGetCString(datum));
+ }
}
ReleaseCatCacheList(passlist);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7c1d023b7e..1f00a0aeaf 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -790,7 +790,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
DOUBLE_P DROP
EACH ELSE EMPTY_P ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ERROR_P ESCAPE
- EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
+ EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPIRES EXPLAIN EXPRESSION
EXTENSION EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FILTER FINALIZE FIRST_P FLOAT_P FOLLOWING FOR
@@ -1266,6 +1266,12 @@ AlterOptRoleElem:
$$ = makeDefElem("passname",
(Node *)makeString($2), @1);
}
+ | EXPIRES IN_P Sconst opt_interval
+ {
+ TypeName *t = SystemTypeName("interval");
+ t->typmods = $4;
+ $$ = makeDefElem("expiresin", (Node *)makeStringConstCast($3, @3, t), @1);
+ }
| VALID FOR Sconst
{
$$ = makeDefElem("validFor", (Node *)makeString($3), @1);
@@ -17766,6 +17772,7 @@ unreserved_keyword:
| EXCLUDING
| EXCLUSIVE
| EXECUTE
+ | EXPIRES
| EXPLAIN
| EXPRESSION
| EXTENSION
@@ -18340,6 +18347,7 @@ bare_label_keyword:
| EXCLUSIVE
| EXECUTE
| EXISTS
+ | EXPIRES
| EXPLAIN
| EXPRESSION
| EXTENSION
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index a7cc49898b..0d4c2c5d79 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -716,6 +716,7 @@ static char *recovery_target_string;
static char *recovery_target_xid_string;
static char *recovery_target_name_string;
static char *recovery_target_lsn_string;
+static char *password_duration_string;
/* should be static, but commands/variable.c needs to get at this */
@@ -4721,6 +4722,17 @@ static struct config_string ConfigureNamesString[] =
check_backtrace_functions, assign_backtrace_functions, NULL
},
+ {
+ {"password_valid_duration", PGC_SUSET, CONN_AUTH_AUTH,
+ gettext_noop("Specifies the default validity duration of new passwords."),
+ NULL,
+ GUC_SUPERUSER_ONLY | GUC_NOT_IN_SAMPLE
+ },
+ &password_duration_string,
+ NULL,
+ check_password_duration, assign_password_duration, show_password_duration
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, NULL, NULL, NULL, NULL
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index 1c14f60a05..13d3479fac 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -19,6 +19,9 @@
/* GUC. Is actually of type PasswordType. */
extern PGDLLIMPORT int Password_encryption;
+/* GUC. system-wide password validity duration */
+extern Interval *default_password_duration;
+
/* 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/commands/variable.h b/src/include/commands/variable.h
index 0e5ddcbcf3..3b941c554c 100644
--- a/src/include/commands/variable.h
+++ b/src/include/commands/variable.h
@@ -34,5 +34,9 @@ extern void assign_session_authorization(const char *newval, void *extra);
extern bool check_role(char **newval, void **extra, GucSource source);
extern void assign_role(const char *newval, void *extra);
extern const char *show_role(void);
+extern bool check_password_duration(char **newval, void **extra, GucSource source);
+extern void assign_password_duration(const char *newval, void *extra);
+extern const char *show_password_duration(void);
+
#endif /* VARIABLE_H */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index ec79fafabf..cebf01840b 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -163,6 +163,7 @@ PG_KEYWORD("excluding", EXCLUDING, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("exclusive", EXCLUSIVE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("execute", EXECUTE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("exists", EXISTS, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("expires", EXPIRES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("explain", EXPLAIN, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("expression", EXPRESSION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("extension", EXTENSION, UNRESERVED_KEYWORD, BARE_LABEL)
--
2.34.1