Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1o74Oq-0002P6-IM for pgsql-hackers@arkaria.postgresql.org; Fri, 01 Jul 2022 00:20:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1o74Oo-00089P-OZ for pgsql-hackers@arkaria.postgresql.org; Fri, 01 Jul 2022 00:20:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1o74Oo-00089G-71 for pgsql-hackers@lists.postgresql.org; Fri, 01 Jul 2022 00:20:42 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1o74Oh-0008C1-RM for pgsql-hackers@postgresql.org; Fri, 01 Jul 2022 00:20:41 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 62E875F79F; Thu, 30 Jun 2022 20:20:34 -0400 (EDT) Date: Thu, 30 Jun 2022 20:20:34 -0400 From: Stephen Frost To: Gurjeet Singh Cc: Jacob Champion , PostgreSQL-development Subject: Re: [PoC/RFC] Multiple passwords, interval expirations Message-ID: <20220701002034.GA9030@tamriel.snowman.net> References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="mB/CYmFGQzL2EujR" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --mB/CYmFGQzL2EujR Content-Type: multipart/mixed; boundary="oFGQ2VdWvpciDCpb" Content-Disposition: inline --oFGQ2VdWvpciDCpb Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Greetings, * Gurjeet Singh (gurjeet@singh.im) wrote: > I am planning on picking it up next week; right now picking up steam, > and reviewing a different, smaller patch. Great! Glad that others are interested in this. > At his behest, I had a conversation with Joshua (OP), and have his > support to pick up and continue working on this patch. I have a some > ideas of my own, on what this patch should do, but since I haven't > fully reviewed the (bulky) patch, I'll reserve my proposals until I > wrap my head around it. I'd be curious as to your thought as to what the patch should be doing. Joshua and I had discussed it at some length as he was working on it. > Please expect some activity on this patch towards the end of next week. I've gone ahead and updated it, cleaned up a couple things, and make it so that check-world actually passes with it. Attached is an updated version and I'll add it to the July commitfest. Thanks! Stephen --oFGQ2VdWvpciDCpb Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="multipass-v3.patch" Content-Transfer-Encoding: quoted-printable =46rom 1203b8a92e4e6c51c7e5aa244d8ee9bbd2f74e66 Mon Sep 17 00:00:00 2001 =46rom: Stephen Frost 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 :=3D \ 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_shdescr= iption.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 =3D=3D AuthIdRelationId || + relationId =3D=3D AuthPasswordRelationId || relationId =3D=3D AuthMemRelationId || relationId =3D=3D DatabaseRelationId || relationId =3D=3D DbRoleSettingRelationId || @@ -260,6 +262,8 @@ IsSharedRelation(Oid relationId) /* These are their indexes */ if (relationId =3D=3D AuthIdOidIndexId || relationId =3D=3D AuthIdRolnameIndexId || + relationId =3D=3D AuthPasswordRoleOidIndexId || + relationId =3D=3D AuthMemRoleMemIndexId || relationId =3D=3D AuthMemMemRoleIndexId || relationId =3D=3D AuthMemRoleMemIndexId || relationId =3D=3D DatabaseNameIndexId || @@ -279,8 +283,8 @@ IsSharedRelation(Oid relationId) relationId =3D=3D TablespaceOidIndexId) return true; /* These are their toast tables and toast indexes */ - if (relationId =3D=3D PgAuthidToastTable || - relationId =3D=3D PgAuthidToastIndex || + if (relationId =3D=3D PgAuthPasswordToastTable || + relationId =3D=3D PgAuthPasswordToastIndex || relationId =3D=3D PgDatabaseToastTable || relationId =3D=3D PgDatabaseToastIndex || relationId =3D=3D PgDbRoleSettingToastTable || diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/sys= tem_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 =3D setrole AND setdatabase =3D 0) + LEFT JOIN pg_auth_password p + ON p.roleid =3D pg_authid.oid + WHERE rolcanlogin; =20 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()); } =20 +/* + * Is the role able to log in +*/ +bool +is_role_valid(const char *rolename, char **logdetail) +{ + HeapTuple roleTup; + Datum datum; + bool isnull; + TimestampTz vuntil =3D 0; + + /* Get role info from pg_authid */ + roleTup =3D SearchSysCache1(AUTHNAME, PointerGetDatum(rolename)); + if (!HeapTupleIsValid(roleTup)) + { + *logdetail =3D psprintf(_("Role \"%s\" does not exist."), + rolename); + return false; /* no such user */ + } + + datum =3D SysCacheGetAttr(AUTHNAME, roleTup, + Anum_pg_authid_rolvaliduntil, &isnull); + ReleaseSysCache(roleTup); + + if (!isnull) + vuntil =3D DatumGetTimestampTz(datum); + + if (!isnull && vuntil < GetCurrentTimestamp()) + { + *logdetail =3D psprintf(_("User \"%s\" has an expired password."), rolen= ame); + return false; + } + + return true; +} + =20 /* * CREATE ROLE @@ -351,43 +388,6 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) new_record[Anum_pg_authid_rolcanlogin - 1] =3D BoolGetDatum(canlogin); new_record[Anum_pg_authid_rolreplication - 1] =3D BoolGetDatum(isreplicat= ion); new_record[Anum_pg_authid_rolconnlimit - 1] =3D Int32GetDatum(connlimit); - - if (password) - { - char *shadow_pass; - const char *logdetail =3D 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] =3D=3D '\0' || - plain_crypt_verify(stmt->role, password, "", &logdetail) =3D=3D STATUS_= OK) - { - ereport(NOTICE, - (errmsg("empty string is not a valid password, clearing password"))); - new_record_nulls[Anum_pg_authid_rolpassword - 1] =3D true; - } - else - { - /* Encrypt the password to the requested format. */ - shadow_pass =3D encrypt_password(Password_encryption, stmt->role, - password); - new_record[Anum_pg_authid_rolpassword - 1] =3D - CStringGetTextDatum(shadow_pass); - } - } - else - new_record_nulls[Anum_pg_authid_rolpassword - 1] =3D true; - new_record[Anum_pg_authid_rolvaliduntil - 1] =3D validUntil_datum; new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] =3D validUntil_null; =20 @@ -422,6 +422,60 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) */ CatalogTupleInsert(pg_authid_rel, tuple); =20 + 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] =3D=3D '\0' || + plain_crypt_verify(stmt->role, password, "", &logdetail) =3D=3D STATUS_= OK) + { + ereport(NOTICE, + (errmsg("empty string is not a valid password, clearing password"))); + } + else + { + /* Encrypt the password to the requested format. */ + shadow_pass =3D 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_nul= ls)); + + /* open password table and insert it. */ + pg_auth_password_rel =3D table_open(AuthPasswordRelationId, RowExclusiv= eLock); + pg_auth_password_dsc =3D RelationGetDescr(pg_auth_password_rel); + + new_password_record[Anum_pg_auth_password_password - 1] =3D + CStringGetTextDatum(shadow_pass); + new_password_record[Anum_pg_auth_password_roleid - 1] =3D roleid; + + new_tuple =3D 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 =3D pstrdup(NameStr(authform->rolname)); roleid =3D authform->oid; =20 + /* * 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)); + =20 /* * 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] =3D true; + new_password_record_nulls[Anum_pg_auth_password_password - 1] =3D true; } else { /* Encrypt the password to the requested format. */ shadow_pass =3D encrypt_password(Password_encryption, rolename, password); - new_record[Anum_pg_authid_rolpassword - 1] =3D + new_password_record[Anum_pg_auth_password_password - 1] =3D CStringGetTextDatum(shadow_pass); } - new_record_repl[Anum_pg_authid_rolpassword - 1] =3D true; + new_password_record_repl[Anum_pg_auth_password_password - 1] =3D true; } =20 /* unset password */ if (dpassword && dpassword->arg =3D=3D NULL) { - new_record_repl[Anum_pg_authid_rolpassword - 1] =3D true; - new_record_nulls[Anum_pg_authid_rolpassword - 1] =3D true; + new_password_record_repl[Anum_pg_auth_password_password - 1] =3D true; + new_password_record_nulls[Anum_pg_auth_password_password - 1] =3D true; } =20 /* valid until */ @@ -786,12 +848,48 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) new_tuple =3D 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); =20 + if (new_password_record_repl[Anum_pg_auth_password_password - 1] =3D=3D t= rue) + { + Relation pg_auth_password_rel; + TupleDesc pg_auth_password_dsc; + HeapTuple password_tuple; + + pg_auth_password_rel =3D table_open(AuthPasswordRelationId, RowExclusive= Lock); + pg_auth_password_dsc =3D RelationGetDescr(pg_auth_password_rel); + password_tuple =3D SearchSysCache1(AUTHPASSWORD, ObjectIdGetDatum(roleid= )); + + if (new_password_record_nulls[Anum_pg_auth_password_password - 1] =3D=3D= 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 =3D heap_modify_tuple(password_tuple, pg_auth_password_dsc, n= ew_password_record, + new_password_record_nulls, new_password_record_repl); + CatalogTupleUpdate(pg_auth_password_rel, &password_tuple->t_self, new_t= uple); + ReleaseSysCache(password_tuple); + heap_freetuple(new_tuple); + } + else /* insert new password */ + { + new_password_record[Anum_pg_auth_password_roleid - 1] =3D roleid; + + new_tuple =3D 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; } =20 - /* * 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; =20 if (!have_createrole_privilege()) @@ -923,6 +1021,8 @@ DropRole(DropRoleStmt *stmt) */ pg_authid_rel =3D table_open(AuthIdRelationId, RowExclusiveLock); pg_auth_members_rel =3D table_open(AuthMemRelationId, RowExclusiveLock); + pg_auth_password_rel =3D table_open(AuthPasswordRelationId, RowExclusiveL= ock); + =20 foreach(item, stmt->roles) { @@ -1056,6 +1156,15 @@ DropRole(DropRoleStmt *stmt) DeleteSharedComments(roleid, AuthIdRelationId); DeleteSharedSecurityLabel(roleid, AuthIdRelationId); =20 + /* + * Drop password + */ + tuple =3D 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); + } =20 /* @@ -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 =3D 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] =3D false; =20 - datum =3D heap_getattr(oldtuple, Anum_pg_authid_rolpassword, dsc, &isnull= ); + passtuple =3D SearchSysCache1(AUTHPASSWORD, roleid); + + if (HeapTupleIsValid(passtuple)) + datum =3D SysCacheGetAttr(AUTHPASSWORD, passtuple, + Anum_pg_auth_password_password, &isnull); =20 if (!isnull && get_password_type(TextDatumGetCString(datum)) =3D=3D PASSW= ORD_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] =3D true; - repl_null[Anum_pg_authid_rolpassword - 1] =3D true; + pg_auth_password_rel =3D table_open(AuthPasswordRelationId, RowExclusive= Lock); =20 + 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.passwo= rd. * 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 perfo= rm * an authentication exchange, but it will fail, as if an incorrect passwo= rd @@ -454,7 +454,7 @@ scram_exchange(void *opaq, const char *input, int input= len, } =20 /* - * 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) =20 case uaMD5: case uaSCRAM: + /* + * check to be sure we are not past rolvaliduntil + */ + if (!is_role_valid(port->user_name, &logdetail)) { + status =3D STATUS_ERROR; + break; + } + status =3D CheckPWChallengeAuth(port, &logdetail); break; =20 case uaPassword: + /* + * check to be sure we are not past rolvaliduntil + */ + if (!is_role_valid(port->user_name, &logdetail)) { + status =3D STATUS_ERROR; + break; + } + status =3D CheckPasswordAuth(port, &logdetail); break; =20 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 =20 #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 =3D 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 **logdet= ail) return NULL; /* no such user */ } =20 - datum =3D SysCacheGetAttr(AUTHNAME, roleTup, - Anum_pg_authid_rolpassword, &isnull); - if (isnull) + datum =3D SysCacheGetAttr(AUTHNAME, roleTup, Anum_pg_authid_oid, &isnull); +=09 + passTup =3D SearchSysCache1(AUTHPASSWORD, datum); + + if (!HeapTupleIsValid(passTup)) { - ReleaseSysCache(roleTup); *logdetail =3D psprintf(_("User \"%s\" has no password assigned."), role); return NULL; /* user has no password */ } - shadow_pass =3D TextDatumGetCString(datum); - - datum =3D SysCacheGetAttr(AUTHNAME, roleTup, - Anum_pg_authid_rolvaliduntil, &isnull); - if (!isnull) - vuntil =3D DatumGetTimestampTz(datum); + datum =3D SysCacheGetAttr(AUTHPASSWORD, passTup, + Anum_pg_auth_password_password, &isnull); =20 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 =3D psprintf(_("User \"%s\" has an expired password."), + ReleaseSysCache(passTup); + *logdetail =3D psprintf(_("User \"%s\" has no password assigned."), role); - return NULL; + return NULL; /* user has no password */ } + shadow_pass =3D TextDatumGetCString(datum); + ReleaseSysCache(passTup); =20 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 chall= enge. * 'md5_salt' is the salt used in the MD5 authentication challenge. * @@ -211,7 +207,7 @@ md5_crypt_verify(const char *role, const char *shadow_p= ass, * Check given password for given user, and return STATUS_OK or STATUS_ERR= OR. * * '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/c= atcache.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) =20 case AUTHNAME: case AUTHOID: + case AUTHPASSWORD: case AUTHMEMMEMROLE: case DATABASEOID: =20 diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/r= elcache.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_p= g_proc] =3D {Schema_pg_proc static const FormData_pg_attribute Desc_pg_type[Natts_pg_type] =3D {Schema= _pg_type}; static const FormData_pg_attribute Desc_pg_database[Natts_pg_database] =3D= {Schema_pg_database}; static const FormData_pg_attribute Desc_pg_authid[Natts_pg_authid] =3D {Sc= hema_pg_authid}; +static const FormData_pg_attribute Desc_pg_auth_password[Natts_pg_auth_pas= sword] =3D {Schema_pg_auth_password}; static const FormData_pg_attribute Desc_pg_auth_members[Natts_pg_auth_memb= ers] =3D {Schema_pg_auth_members}; static const FormData_pg_attribute Desc_pg_index[Natts_pg_index] =3D {Sche= ma_pg_index}; static const FormData_pg_attribute Desc_pg_shseclabel[Natts_pg_shseclabel]= =3D {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); =20 -#define NUM_CRITICAL_SHARED_RELS 5 /* fix if you change list above */ +#define NUM_CRITICAL_SHARED_RELS 6 /* fix if you change list above */ } =20 MemoryContextSwitchTo(oldcxt); @@ -4090,8 +4095,10 @@ RelationCacheInitializePhase3(void) AuthMemRelationId); load_critical_index(SharedSecLabelObjectIndexId, SharedSecLabelRelationId); + load_critical_index(AuthPasswordRoleOidIndexId, + AuthPasswordRelationId); =20 -#define NUM_CRITICAL_SHARED_INDEXES 6 /* fix if you change list above */ +#define NUM_CRITICAL_SHARED_INDEXES 7 /* fix if you change list above */ =20 criticalSharedRelcachesBuilt =3D true; } diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/s= yscache.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[] =3D { }, 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 }; =20 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; =20 /* note: rolconfig is dumped later */ - if (server_version >=3D 90600) + if (server_version >=3D 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 =3D current_user AS is_current_user " + "FROM %s LEFT JOIN pg_auth_password p ON %s.oid =3D p.roleid " + "WHERE rolname !~ '^pg_' " + "ORDER BY 2", role_catalog, role_catalog, role_catalog); + else if (server_version >=3D 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 *re= sult, =20 =20 /* - * 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 neces= sary! * diff --git a/src/include/catalog/pg_auth_password.h b/src/include/catalog/p= g_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, AuthPasswo= rdRoleOidIndexId, 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_aut= hid.dat index 6c28119fa1..da36375a58 100644 --- a/src/include/catalog/pg_authid.dat +++ b/src/include/catalog/pg_authid.dat @@ -23,66 +23,66 @@ rolname =3D> 'POSTGRES', rolsuper =3D> 't', rolinherit =3D> 't', rolcreaterole =3D> 't', rolcreatedb =3D> 't', rolcanlogin =3D> 't', rolreplication =3D> 't', rolbypassrls =3D> 't', rolconnlimit =3D> '-1', - rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' }, + rolvaliduntil =3D> '_null_' }, { oid =3D> '6171', oid_symbol =3D> 'ROLE_PG_DATABASE_OWNER', rolname =3D> 'pg_database_owner', rolsuper =3D> 'f', rolinherit =3D> 't', rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f', rolreplication =3D> 'f', rolbypassrls =3D> 'f', rolconnlimit =3D> '-1', - rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' }, + rolvaliduntil =3D> '_null_' }, { oid =3D> '6181', oid_symbol =3D> 'ROLE_PG_READ_ALL_DATA', rolname =3D> 'pg_read_all_data', rolsuper =3D> 'f', rolinherit =3D> 't', rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f', rolreplication =3D> 'f', rolbypassrls =3D> 'f', rolconnlimit =3D> '-1', - rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' }, + rolvaliduntil =3D> '_null_' }, { oid =3D> '6182', oid_symbol =3D> 'ROLE_PG_WRITE_ALL_DATA', rolname =3D> 'pg_write_all_data', rolsuper =3D> 'f', rolinherit =3D> 't', rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f', rolreplication =3D> 'f', rolbypassrls =3D> 'f', rolconnlimit =3D> '-1', - rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' }, + rolvaliduntil =3D> '_null_' }, { oid =3D> '3373', oid_symbol =3D> 'ROLE_PG_MONITOR', rolname =3D> 'pg_monitor', rolsuper =3D> 'f', rolinherit =3D> 't', rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f', rolreplication =3D> 'f', rolbypassrls =3D> 'f', rolconnlimit =3D> '-1', - rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' }, + rolvaliduntil =3D> '_null_' }, { oid =3D> '3374', oid_symbol =3D> 'ROLE_PG_READ_ALL_SETTINGS', rolname =3D> 'pg_read_all_settings', rolsuper =3D> 'f', rolinherit =3D> = 't', rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f', rolreplication =3D> 'f', rolbypassrls =3D> 'f', rolconnlimit =3D> '-1', - rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' }, + rolvaliduntil =3D> '_null_' }, { oid =3D> '3375', oid_symbol =3D> 'ROLE_PG_READ_ALL_STATS', rolname =3D> 'pg_read_all_stats', rolsuper =3D> 'f', rolinherit =3D> 't', rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f', rolreplication =3D> 'f', rolbypassrls =3D> 'f', rolconnlimit =3D> '-1', - rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' }, + rolvaliduntil =3D> '_null_' }, { oid =3D> '3377', oid_symbol =3D> 'ROLE_PG_STAT_SCAN_TABLES', rolname =3D> 'pg_stat_scan_tables', rolsuper =3D> 'f', rolinherit =3D> '= t', rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f', rolreplication =3D> 'f', rolbypassrls =3D> 'f', rolconnlimit =3D> '-1', - rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' }, + rolvaliduntil =3D> '_null_' }, { oid =3D> '4569', oid_symbol =3D> 'ROLE_PG_READ_SERVER_FILES', rolname =3D> 'pg_read_server_files', rolsuper =3D> 'f', rolinherit =3D> = 't', rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f', rolreplication =3D> 'f', rolbypassrls =3D> 'f', rolconnlimit =3D> '-1', - rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' }, + rolvaliduntil =3D> '_null_' }, { oid =3D> '4570', oid_symbol =3D> 'ROLE_PG_WRITE_SERVER_FILES', rolname =3D> 'pg_write_server_files', rolsuper =3D> 'f', rolinherit =3D>= 't', rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f', rolreplication =3D> 'f', rolbypassrls =3D> 'f', rolconnlimit =3D> '-1', - rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' }, + rolvaliduntil =3D> '_null_' }, { oid =3D> '4571', oid_symbol =3D> 'ROLE_PG_EXECUTE_SERVER_PROGRAM', rolname =3D> 'pg_execute_server_program', rolsuper =3D> 'f', rolinherit = =3D> 't', rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f', rolreplication =3D> 'f', rolbypassrls =3D> 'f', rolconnlimit =3D> '-1', - rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' }, + rolvaliduntil =3D> '_null_' }, { oid =3D> '4200', oid_symbol =3D> 'ROLE_PG_SIGNAL_BACKEND', rolname =3D> 'pg_signal_backend', rolsuper =3D> 'f', rolinherit =3D> 't', rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f', rolreplication =3D> 'f', rolbypassrls =3D> 'f', rolconnlimit =3D> '-1', - rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' }, + rolvaliduntil =3D> '_null_' }, { oid =3D> '4544', oid_symbol =3D> 'ROLE_PG_CHECKPOINTER', rolname =3D> 'pg_checkpointer', rolsuper =3D> 'f', rolinherit =3D> 't', rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f', rolreplication =3D> 'f', rolbypassrls =3D> 'f', rolconnlimit =3D> '-1', - rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' }, + rolvaliduntil =3D> '_null_' }, =20 ] diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authi= d.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_RELAT= ION BKI_ROWTYPE_OID(284 int32 rolconnlimit; /* max connections allowed (-1=3Dno limit) */ =20 /* 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; =20 @@ -55,8 +54,6 @@ CATALOG(pg_authid,1260,AuthIdRelationId) BKI_SHARED_RELAT= ION BKI_ROWTYPE_OID(284 */ typedef FormData_pg_authid *Form_pg_authid; =20 -DECLARE_TOAST_WITH_MACRO(pg_authid, 4175, 4176, PgAuthidToastTable, PgAuth= idToastIndex); - 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)); =20 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 *use= rname, const char *shad =20 extern PGDLLIMPORT check_password_hook_type check_password_hook; =20 +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 tab= le +-- These are the toast table and index of pg_database. +REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1262; -- no catalog toast tab= le ERROR: cannot reindex system catalogs concurrently -REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -- no catalog toa= st index +REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1262_index; -- no catalog toa= st 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/expe= cted/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} =3D> pg_auth= id {oid} NOTICE: checking pg_auth_members {roleid} =3D> pg_authid {oid} NOTICE: checking pg_auth_members {member} =3D> pg_authid {oid} NOTICE: checking pg_auth_members {grantor} =3D> pg_authid {oid} +NOTICE: checking pg_auth_password {roleid} =3D> pg_authid {oid} NOTICE: checking pg_shdepend {dbid} =3D> pg_database {oid} NOTICE: checking pg_shdepend {classid} =3D> pg_class {oid} NOTICE: checking pg_shdepend {refclassid} =3D> pg_class {oid} diff --git a/src/test/regress/expected/password.out b/src/test/regress/expe= cted/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 e= very test -- run. Use a regular expression to mask the changing parts. -SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA= -Z0-9+/=3D]+)\$([a-zA-Z0-9+=3D/]+):([a-zA-Z0-9+/=3D]+)', '\1$\2:$:') as rolpassword_masked +SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0= -9+/=3D]+)\$([a-zA-Z0-9+=3D/]+):([a-zA-Z0-9+/=3D]+)', '\1$\2:$:') as rolpassword_masked FROM pg_authid + LEFT JOIN pg_auth_password p=20 + ON pg_authid.oid =3D p.roleid WHERE rolname LIKE 'regress_passwd%' - ORDER BY rolname, rolpassword; + ORDER BY rolname, password; rolname | rolpassword_masked =20 -----------------+--------------------------------------------------- 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=20 + ON pg_authid.oid =3D p.roleid WHERE rolname LIKE 'regress_passwd2_new' - ORDER BY rolname, rolpassword; - rolname | rolpassword=20 ----------------------+------------- + ORDER BY rolname, password; + rolname | password=20 +---------------------+---------- regress_passwd2_new |=20 (1 row) =20 @@ -72,10 +76,12 @@ CREATE ROLE regress_passwd6 PASSWORD 'SCRAM-SHA-256$123= 4'; CREATE ROLE regress_passwd7 PASSWORD 'md5012345678901234567890123456789zz'; -- invalid length CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901z= z'; -SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA= -Z0-9+/=3D]+)\$([a-zA-Z0-9+=3D/]+):([a-zA-Z0-9+/=3D]+)', '\1$\2:$:') as rolpassword_masked +SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0= -9+/=3D]+)\$([a-zA-Z0-9+=3D/]+):([a-zA-Z0-9+/=3D]+)', '\1$\2:$:') as rolpassword_masked FROM pg_authid + LEFT JOIN pg_auth_password p=20 + ON pg_authid.oid =3D p.roleid WHERE rolname LIKE 'regress_passwd%' - ORDER BY rolname, rolpassword; + ORDER BY rolname, password; rolname | rolpassword_masked =20 -----------------+--------------------------------------------------- regress_passwd1 | md5cd3578025fe2c3d7ed1b9a9b26238b70 @@ -95,9 +101,11 @@ ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce8= 45f1a1b620742e3c659e0a'; NOTICE: empty string is not a valid password, clearing password ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7= O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=3D:qVV5NeluNfUPkwm7Vqat25Rj= SPLkGeoZBQs6wVv+um4=3D'; NOTICE: empty string is not a valid password, clearing password -SELECT rolpassword FROM pg_authid WHERE rolname=3D'regress_passwd_empty'; - rolpassword=20 -------------- +SELECT password FROM pg_authid +LEFT JOIN pg_auth_password p=20 +ON pg_authid.oid =3D p.roleid WHERE rolname=3D'regress_passwd_empty'; + password=20 +---------- =20 (1 row) =20 @@ -110,8 +118,10 @@ CREATE ROLE regress_passwd_sha_len1 PASSWORD 'SCRAM-SH= A-256$4096:A6xHKoH/494E941 CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/4= 94E941doaPOYg=3D=3D$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=3D:COPdlNiI= krsacU5QoxydEuOH6e/KfiipeETb/bPw8ZIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA= AAAAAAAAAAAAAAAAAAAAAAAAAAAA=3D'; -- 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=3D=3D%' as i= s_rolpassword_rehashed +SELECT rolname, password not like '%A6xHKoH/494E941doaPOYg=3D=3D%' as is_r= olpassword_rehashed FROM pg_authid + LEFT JOIN pg_auth_password p=20 + ON pg_authid.oid =3D p.roleid WHERE rolname LIKE 'regress_passwd_sha_len%' ORDER BY rolname; rolname | is_rolpassword_rehashed=20 @@ -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=20 + ON pg_authid.oid =3D p.roleid WHERE rolname LIKE 'regress_passwd%' - ORDER BY rolname, rolpassword; - rolname | rolpassword=20 ----------+------------- + ORDER BY rolname, password; + rolname | password=20 +---------+---------- (0 rows) =20 diff --git a/src/test/regress/expected/roleattributes.out b/src/test/regres= s/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, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_superuser'; - rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolp= assword | rolvaliduntil=20 -----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= --------+--------------- - regress_test_def_superuser | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_superuser'; + rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolv= aliduntil=20 +----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= ---------- + regress_test_def_superuser | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 CREATE ROLE regress_test_superuser WITH SUPERUSER; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_superuser'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreat= edb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassw= ord | rolvaliduntil=20 -------------------------+----------+------------+---------------+---------= ----+-------------+----------------+--------------+--------------+---------= ----+--------------- - regress_test_superuser | t | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_superuser'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreat= edb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvalid= until=20 +------------------------+----------+------------+---------------+---------= ----+-------------+----------------+--------------+--------------+---------= ------ + regress_test_superuser | t | t | f | f = | f | f | f | -1 |=20 (1 row) =20 ALTER ROLE regress_test_superuser WITH NOSUPERUSER; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_superuser'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreat= edb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassw= ord | rolvaliduntil=20 -------------------------+----------+------------+---------------+---------= ----+-------------+----------------+--------------+--------------+---------= ----+--------------- - regress_test_superuser | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_superuser'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreat= edb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvalid= until=20 +------------------------+----------+------------+---------------+---------= ----+-------------+----------------+--------------+--------------+---------= ------ + regress_test_superuser | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 ALTER ROLE regress_test_superuser WITH SUPERUSER; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_superuser'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreat= edb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassw= ord | rolvaliduntil=20 -------------------------+----------+------------+---------------+---------= ----+-------------+----------------+--------------+--------------+---------= ----+--------------- - regress_test_superuser | t | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_superuser'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreat= edb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvalid= until=20 +------------------------+----------+------------+---------------+---------= ----+-------------+----------------+--------------+--------------+---------= ------ + regress_test_superuser | t | t | f | f = | f | f | f | -1 |=20 (1 row) =20 -- default for inherit is true CREATE ROLE regress_test_def_inherit; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_inherit'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcre= atedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpas= sword | rolvaliduntil=20 ---------------------------+----------+------------+---------------+-------= ------+-------------+----------------+--------------+--------------+-------= ------+--------------- - regress_test_def_inherit | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_inherit'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcre= atedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolval= iduntil=20 +--------------------------+----------+------------+---------------+-------= ------+-------------+----------------+--------------+--------------+-------= -------- + regress_test_def_inherit | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 CREATE ROLE regress_test_inherit WITH NOINHERIT; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_inherit'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreated= b | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpasswor= d | rolvaliduntil=20 -----------------------+----------+------------+---------------+-----------= --+-------------+----------------+--------------+--------------+-----------= --+--------------- - regress_test_inherit | f | f | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_inherit'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreated= b | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvalidun= til=20 +----------------------+----------+------------+---------------+-----------= --+-------------+----------------+--------------+--------------+-----------= ---- + regress_test_inherit | f | f | f | f = | f | f | f | -1 |=20 (1 row) =20 ALTER ROLE regress_test_inherit WITH INHERIT; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_inherit'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreated= b | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpasswor= d | rolvaliduntil=20 -----------------------+----------+------------+---------------+-----------= --+-------------+----------------+--------------+--------------+-----------= --+--------------- - regress_test_inherit | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_inherit'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreated= b | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvalidun= til=20 +----------------------+----------+------------+---------------+-----------= --+-------------+----------------+--------------+--------------+-----------= ---- + regress_test_inherit | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 ALTER ROLE regress_test_inherit WITH NOINHERIT; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_inherit'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreated= b | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpasswor= d | rolvaliduntil=20 -----------------------+----------+------------+---------------+-----------= --+-------------+----------------+--------------+--------------+-----------= --+--------------- - regress_test_inherit | f | f | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_inherit'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreated= b | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvalidun= til=20 +----------------------+----------+------------+---------------+-----------= --+-------------+----------------+--------------+--------------+-----------= ---- + regress_test_inherit | f | f | f | f = | f | f | f | -1 |=20 (1 row) =20 -- default for create role is false CREATE ROLE regress_test_def_createrole; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_createrole'; - rolname | rolsuper | rolinherit | rolcreaterole | rol= createdb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rol= password | rolvaliduntil=20 ------------------------------+----------+------------+---------------+----= ---------+-------------+----------------+--------------+--------------+----= ---------+--------------- - regress_test_def_createrole | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_createrole'; + rolname | rolsuper | rolinherit | rolcreaterole | rol= createdb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rol= validuntil=20 +-----------------------------+----------+------------+---------------+----= ---------+-------------+----------------+--------------+--------------+----= ----------- + regress_test_def_createrole | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 CREATE ROLE regress_test_createrole WITH CREATEROLE; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_createrole'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcrea= tedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpass= word | rolvaliduntil=20 --------------------------+----------+------------+---------------+--------= -----+-------------+----------------+--------------+--------------+--------= -----+--------------- - regress_test_createrole | f | t | t | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_createrole'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcrea= tedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvali= duntil=20 +-------------------------+----------+------------+---------------+--------= -----+-------------+----------------+--------------+--------------+--------= ------- + regress_test_createrole | f | t | t | f = | f | f | f | -1 |=20 (1 row) =20 ALTER ROLE regress_test_createrole WITH NOCREATEROLE; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_createrole'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcrea= tedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpass= word | rolvaliduntil=20 --------------------------+----------+------------+---------------+--------= -----+-------------+----------------+--------------+--------------+--------= -----+--------------- - regress_test_createrole | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_createrole'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcrea= tedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvali= duntil=20 +-------------------------+----------+------------+---------------+--------= -----+-------------+----------------+--------------+--------------+--------= ------- + regress_test_createrole | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 ALTER ROLE regress_test_createrole WITH CREATEROLE; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_createrole'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcrea= tedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpass= word | rolvaliduntil=20 --------------------------+----------+------------+---------------+--------= -----+-------------+----------------+--------------+--------------+--------= -----+--------------- - regress_test_createrole | f | t | t | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_createrole'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcrea= tedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvali= duntil=20 +-------------------------+----------+------------+---------------+--------= -----+-------------+----------------+--------------+--------------+--------= ------- + regress_test_createrole | f | t | t | f = | f | f | f | -1 |=20 (1 row) =20 -- default for create database is false CREATE ROLE regress_test_def_createdb; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_createdb'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcr= eatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpa= ssword | rolvaliduntil=20 ----------------------------+----------+------------+---------------+------= -------+-------------+----------------+--------------+--------------+------= -------+--------------- - regress_test_def_createdb | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_createdb'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcr= eatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolva= liduntil=20 +---------------------------+----------+------------+---------------+------= -------+-------------+----------------+--------------+--------------+------= --------- + regress_test_def_createdb | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 CREATE ROLE regress_test_createdb WITH CREATEDB; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_createdb'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreate= db | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpasswo= rd | rolvaliduntil=20 ------------------------+----------+------------+---------------+----------= ---+-------------+----------------+--------------+--------------+----------= ---+--------------- - regress_test_createdb | f | t | f | t = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_createdb'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreate= db | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvalidu= ntil=20 +-----------------------+----------+------------+---------------+----------= ---+-------------+----------------+--------------+--------------+----------= ----- + regress_test_createdb | f | t | f | t = | f | f | f | -1 |=20 (1 row) =20 ALTER ROLE regress_test_createdb WITH NOCREATEDB; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_createdb'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreate= db | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpasswo= rd | rolvaliduntil=20 ------------------------+----------+------------+---------------+----------= ---+-------------+----------------+--------------+--------------+----------= ---+--------------- - regress_test_createdb | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_createdb'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreate= db | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvalidu= ntil=20 +-----------------------+----------+------------+---------------+----------= ---+-------------+----------------+--------------+--------------+----------= ----- + regress_test_createdb | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 ALTER ROLE regress_test_createdb WITH CREATEDB; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_createdb'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreate= db | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpasswo= rd | rolvaliduntil=20 ------------------------+----------+------------+---------------+----------= ---+-------------+----------------+--------------+--------------+----------= ---+--------------- - regress_test_createdb | f | t | f | t = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_createdb'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreate= db | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvalidu= ntil=20 +-----------------------+----------+------------+---------------+----------= ---+-------------+----------------+--------------+--------------+----------= ----- + regress_test_createdb | f | t | f | t = | f | f | f | -1 |=20 (1 row) =20 -- default for can login is false for role CREATE ROLE regress_test_def_role_canlogin; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_role_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | = rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | = rolpassword | rolvaliduntil=20 ---------------------------------+----------+------------+---------------+-= ------------+-------------+----------------+--------------+--------------+-= ------------+--------------- - regress_test_def_role_canlogin | f | t | f | = f | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_role_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | = rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | = rolvaliduntil=20 +--------------------------------+----------+------------+---------------+-= ------------+-------------+----------------+--------------+--------------+-= -------------- + regress_test_def_role_canlogin | f | t | f | = f | f | f | f | -1 |= =20 (1 row) =20 CREATE ROLE regress_test_role_canlogin WITH LOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_role_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolp= assword | rolvaliduntil=20 -----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= --------+--------------- - regress_test_role_canlogin | f | t | f | f = | t | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_role_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolv= aliduntil=20 +----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= ---------- + regress_test_role_canlogin | f | t | f | f = | t | f | f | -1 |=20 (1 row) =20 ALTER ROLE regress_test_role_canlogin WITH NOLOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_role_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolp= assword | rolvaliduntil=20 -----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= --------+--------------- - regress_test_role_canlogin | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_role_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolv= aliduntil=20 +----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= ---------- + regress_test_role_canlogin | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 ALTER ROLE regress_test_role_canlogin WITH LOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_role_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolp= assword | rolvaliduntil=20 -----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= --------+--------------- - regress_test_role_canlogin | f | t | f | f = | t | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_role_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolv= aliduntil=20 +----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= ---------- + regress_test_role_canlogin | f | t | f | f = | t | f | f | -1 |=20 (1 row) =20 -- default for can login is true for user CREATE USER regress_test_def_user_canlogin; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_user_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | = rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | = rolpassword | rolvaliduntil=20 ---------------------------------+----------+------------+---------------+-= ------------+-------------+----------------+--------------+--------------+-= ------------+--------------- - regress_test_def_user_canlogin | f | t | f | = f | t | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_user_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | = rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | = rolvaliduntil=20 +--------------------------------+----------+------------+---------------+-= ------------+-------------+----------------+--------------+--------------+-= -------------- + regress_test_def_user_canlogin | f | t | f | = f | t | f | f | -1 |= =20 (1 row) =20 CREATE USER regress_test_user_canlogin WITH NOLOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_user_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolp= assword | rolvaliduntil=20 -----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= --------+--------------- - regress_test_user_canlogin | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_user_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolv= aliduntil=20 +----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= ---------- + regress_test_user_canlogin | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 ALTER USER regress_test_user_canlogin WITH LOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_user_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolp= assword | rolvaliduntil=20 -----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= --------+--------------- - regress_test_user_canlogin | f | t | f | f = | t | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_user_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolv= aliduntil=20 +----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= ---------- + regress_test_user_canlogin | f | t | f | f = | t | f | f | -1 |=20 (1 row) =20 ALTER USER regress_test_user_canlogin WITH NOLOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_user_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolp= assword | rolvaliduntil=20 -----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= --------+--------------- - regress_test_user_canlogin | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_user_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolv= aliduntil=20 +----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= ---------- + regress_test_user_canlogin | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 -- default for replication is false CREATE ROLE regress_test_def_replication; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_replication'; - rolname | rolsuper | rolinherit | rolcreaterole | ro= lcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | ro= lpassword | rolvaliduntil=20 -------------------------------+----------+------------+---------------+---= ----------+-------------+----------------+--------------+--------------+---= ----------+--------------- - regress_test_def_replication | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_replication'; + rolname | rolsuper | rolinherit | rolcreaterole | ro= lcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | ro= lvaliduntil=20 +------------------------------+----------+------------+---------------+---= ----------+-------------+----------------+--------------+--------------+---= ------------ + regress_test_def_replication | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 CREATE ROLE regress_test_replication WITH REPLICATION; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_replication'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcre= atedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpas= sword | rolvaliduntil=20 ---------------------------+----------+------------+---------------+-------= ------+-------------+----------------+--------------+--------------+-------= ------+--------------- - regress_test_replication | f | t | f | f = | f | t | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_replication'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcre= atedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolval= iduntil=20 +--------------------------+----------+------------+---------------+-------= ------+-------------+----------------+--------------+--------------+-------= -------- + regress_test_replication | f | t | f | f = | f | t | f | -1 |=20 (1 row) =20 ALTER ROLE regress_test_replication WITH NOREPLICATION; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_replication'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcre= atedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpas= sword | rolvaliduntil=20 ---------------------------+----------+------------+---------------+-------= ------+-------------+----------------+--------------+--------------+-------= ------+--------------- - regress_test_replication | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_replication'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcre= atedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolval= iduntil=20 +--------------------------+----------+------------+---------------+-------= ------+-------------+----------------+--------------+--------------+-------= -------- + regress_test_replication | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 ALTER ROLE regress_test_replication WITH REPLICATION; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_replication'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcre= atedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpas= sword | rolvaliduntil=20 ---------------------------+----------+------------+---------------+-------= ------+-------------+----------------+--------------+--------------+-------= ------+--------------- - regress_test_replication | f | t | f | f = | f | t | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_replication'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcre= atedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolval= iduntil=20 +--------------------------+----------+------------+---------------+-------= ------+-------------+----------------+--------------+--------------+-------= -------- + regress_test_replication | f | t | f | f = | f | t | f | -1 |=20 (1 row) =20 -- default for bypassrls is false CREATE ROLE regress_test_def_bypassrls; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_bypassrls'; - rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolp= assword | rolvaliduntil=20 -----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= --------+--------------- - regress_test_def_bypassrls | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_bypassrls'; + rolname | rolsuper | rolinherit | rolcreaterole | rolc= reatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolv= aliduntil=20 +----------------------------+----------+------------+---------------+-----= --------+-------------+----------------+--------------+--------------+-----= ---------- + regress_test_def_bypassrls | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 CREATE ROLE regress_test_bypassrls WITH BYPASSRLS; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_bypassrls'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreat= edb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassw= ord | rolvaliduntil=20 -------------------------+----------+------------+---------------+---------= ----+-------------+----------------+--------------+--------------+---------= ----+--------------- - regress_test_bypassrls | f | t | f | f = | f | f | t | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_bypassrls'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreat= edb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvalid= until=20 +------------------------+----------+------------+---------------+---------= ----+-------------+----------------+--------------+--------------+---------= ------ + regress_test_bypassrls | f | t | f | f = | f | f | t | -1 |=20 (1 row) =20 ALTER ROLE regress_test_bypassrls WITH NOBYPASSRLS; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_bypassrls'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreat= edb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassw= ord | rolvaliduntil=20 -------------------------+----------+------------+---------------+---------= ----+-------------+----------------+--------------+--------------+---------= ----+--------------- - regress_test_bypassrls | f | t | f | f = | f | f | f | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_bypassrls'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreat= edb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvalid= until=20 +------------------------+----------+------------+---------------+---------= ----+-------------+----------------+--------------+--------------+---------= ------ + regress_test_bypassrls | f | t | f | f = | f | f | f | -1 |=20 (1 row) =20 ALTER ROLE regress_test_bypassrls WITH BYPASSRLS; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_bypassrls'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreat= edb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassw= ord | rolvaliduntil=20 -------------------------+----------+------------+---------------+---------= ----+-------------+----------------+--------------+--------------+---------= ----+--------------- - regress_test_bypassrls | f | t | f | f = | f | f | t | -1 | = |=20 +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_bypassrls'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreat= edb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvalid= until=20 +------------------------+----------+------------+---------------+---------= ----+-------------+----------------+--------------+--------------+---------= ------ + regress_test_bypassrls | f | t | f | f = | f | f | t | -1 |=20 (1 row) =20 -- clean up roles diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expecte= d/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 =3D s.setrole) AND= (s.setdatabase =3D (0)::oid)))) + LEFT JOIN pg_auth_password p ON ((p.roleid =3D 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/ex= pected/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/c= reate_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 tab= le -REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -- no catalog toa= st index +-- These are the toast table and index of pg_database. +REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1262; -- no catalog toast tab= le +REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1262_index; -- no catalog toa= st 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; =20 -- Clean up RESET ROLE; diff --git a/src/test/regress/sql/password.sql b/src/test/regress/sql/passw= ord.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 e= very test -- run. Use a regular expression to mask the changing parts. -SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA= -Z0-9+/=3D]+)\$([a-zA-Z0-9+=3D/]+):([a-zA-Z0-9+/=3D]+)', '\1$\2:$:') as rolpassword_masked +SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0= -9+/=3D]+)\$([a-zA-Z0-9+=3D/]+):([a-zA-Z0-9+/=3D]+)', '\1$\2:$:') as rolpassword_masked FROM pg_authid + LEFT JOIN pg_auth_password p + ON pg_authid.oid =3D p.roleid WHERE rolname LIKE 'regress_passwd%' - ORDER BY rolname, rolpassword; + ORDER BY rolname, password; =20 -- 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 =3D 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; =20 -- Change passwords with ALTER USER. With plaintext or already-encrypted @@ -63,16 +67,20 @@ CREATE ROLE regress_passwd7 PASSWORD 'md501234567890123= 4567890123456789zz'; -- invalid length CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901z= z'; =20 -SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA= -Z0-9+/=3D]+)\$([a-zA-Z0-9+=3D/]+):([a-zA-Z0-9+/=3D]+)', '\1$\2:$:') as rolpassword_masked +SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0= -9+/=3D]+)\$([a-zA-Z0-9+=3D/]+):([a-zA-Z0-9+/=3D]+)', '\1$\2:$:') as rolpassword_masked FROM pg_authid + LEFT JOIN pg_auth_password p + ON pg_authid.oid =3D p.roleid WHERE rolname LIKE 'regress_passwd%' - ORDER BY rolname, rolpassword; + ORDER BY rolname, password; =20 -- An empty password is not allowed, in any form CREATE ROLE regress_passwd_empty PASSWORD ''; ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659= e0a'; ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7= O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=3D:qVV5NeluNfUPkwm7Vqat25Rj= SPLkGeoZBQs6wVv+um4=3D'; -SELECT rolpassword FROM pg_authid WHERE rolname=3D'regress_passwd_empty'; +SELECT password FROM pg_authid +LEFT JOIN pg_auth_password p +ON pg_authid.oid =3D p.roleid WHERE rolname=3D'regress_passwd_empty'; =20 -- Test with invalid stored and server keys. -- @@ -84,8 +92,10 @@ CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-= 256$4096:A6xHKoH/494E941 =20 -- 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=3D=3D%' as i= s_rolpassword_rehashed +SELECT rolname, password not like '%A6xHKoH/494E941doaPOYg=3D=3D%' as is_r= olpassword_rehashed FROM pg_authid + LEFT JOIN pg_auth_password p + ON pg_authid.oid =3D p.roleid WHERE rolname LIKE 'regress_passwd_sha_len%' ORDER BY rolname; =20 @@ -103,7 +113,9 @@ DROP ROLE regress_passwd_sha_len1; DROP ROLE regress_passwd_sha_len2; =20 -- 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 =3D 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; =20 -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_superuser'; CREATE ROLE regress_test_superuser WITH SUPERUSER; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_superuser'; ALTER ROLE regress_test_superuser WITH NOSUPERUSER; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_superuser'; ALTER ROLE regress_test_superuser WITH SUPERUSER; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_superuser'; =20 -- default for inherit is true CREATE ROLE regress_test_def_inherit; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_inherit'; CREATE ROLE regress_test_inherit WITH NOINHERIT; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_inherit'; ALTER ROLE regress_test_inherit WITH INHERIT; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_inherit'; ALTER ROLE regress_test_inherit WITH NOINHERIT; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_inherit'; =20 -- default for create role is false CREATE ROLE regress_test_def_createrole; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_createrole'; CREATE ROLE regress_test_createrole WITH CREATEROLE; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_createrole'; ALTER ROLE regress_test_createrole WITH NOCREATEROLE; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_createrole'; ALTER ROLE regress_test_createrole WITH CREATEROLE; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_createrole'; =20 -- default for create database is false CREATE ROLE regress_test_def_createdb; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_createdb'; CREATE ROLE regress_test_createdb WITH CREATEDB; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_createdb'; ALTER ROLE regress_test_createdb WITH NOCREATEDB; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_createdb'; ALTER ROLE regress_test_createdb WITH CREATEDB; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_createdb'; =20 -- default for can login is false for role CREATE ROLE regress_test_def_role_canlogin; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_role_canlogin'; CREATE ROLE regress_test_role_canlogin WITH LOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_role_canlogin'; ALTER ROLE regress_test_role_canlogin WITH NOLOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_role_canlogin'; ALTER ROLE regress_test_role_canlogin WITH LOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_role_canlogin'; =20 -- default for can login is true for user CREATE USER regress_test_def_user_canlogin; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_user_canlogin'; CREATE USER regress_test_user_canlogin WITH NOLOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_user_canlogin'; ALTER USER regress_test_user_canlogin WITH LOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_user_canlogin'; ALTER USER regress_test_user_canlogin WITH NOLOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_user_canlogin'; =20 -- default for replication is false CREATE ROLE regress_test_def_replication; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_replication'; CREATE ROLE regress_test_replication WITH REPLICATION; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_replication'; ALTER ROLE regress_test_replication WITH NOREPLICATION; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_replication'; ALTER ROLE regress_test_replication WITH REPLICATION; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_replication'; =20 -- default for bypassrls is false CREATE ROLE regress_test_def_bypassrls; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_def_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_def_bypassrls'; CREATE ROLE regress_test_bypassrls WITH BYPASSRLS; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_bypassrls'; ALTER ROLE regress_test_bypassrls WITH NOBYPASSRLS; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_bypassrls'; ALTER ROLE regress_test_bypassrls WITH BYPASSRLS; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil= FROM pg_authid WHERE rolname =3D 'regress_test_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlo= gin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_auth= id WHERE rolname =3D 'regress_test_bypassrls'; =20 -- clean up roles DROP ROLE regress_test_def_superuser; diff --git a/src/test/regress/sql/tablespace.sql b/src/test/regress/sql/tab= lespace.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 CONCURRENTL= Y 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; --=20 2.34.1 =46rom bfb15c69fa4367e92cabeef1a97eb6fb394b788f Mon Sep 17 00:00:00 2001 =46rom: Stephen Frost 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 =3D InvalidOid; /* GUC parameter */ int Password_encryption =3D PASSWORD_TYPE_SCRAM_SHA_256; =20 +/* default password name */ +const char* default_passname =3D "__def__"; + /* Hook to check passwords in CreateRole() and AlterRole() */ check_password_hook_type check_password_hook =3D NULL; =20 @@ -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) } =20 =20 +static +bool +validate_and_get_salt(char *rolename, char **salt, const char **logdetail) +{ + char **current_secrets; + char *salt1, *salt2 =3D NULL; + int i, num; + PasswordType passtype; + + if (Password_encryption =3D=3D PASSWORD_TYPE_MD5) + { + *salt =3D rolename; /* md5 always uses role name, no need to look throug= h the passwords */ + return true; + } + else if (Password_encryption =3D=3D PASSWORD_TYPE_PLAINTEXT) + { + *salt =3D NULL; /* Plaintext does not have a salt */ + return true; + } + + current_secrets =3D get_role_passwords(rolename, logdetail, &num); + if (num =3D=3D 0) + { + *salt =3D NULL; /* No existing passwords, allow one to be generated */ + return true; + } + for (i =3D 0; i < num; i++) { + passtype =3D get_password_type(current_secrets[i]); + if (passtype =3D=3D PASSWORD_TYPE_MD5 || passtype =3D=3D PASSWORD_TYPE_P= LAINTEXT) + continue; /* md5 uses rolename as salt so it is always the same and pla= intext has no salt */ + else if (passtype =3D=3D 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 !=3D NULL) { + if (strcmp(salt1, salt2)) { + *logdetail =3D psprintf(_("inconsistent salts, clearing password")); + *salt =3D NULL; + return false; + } + } + else + salt2 =3D salt1; + } + + } + for (i =3D 0; i < num; i++) + pfree(current_secrets[i]); + pfree(current_secrets); + if (salt2) + *salt =3D pstrdup(salt2); + return true; +} + /* * CREATE ROLE */ @@ -117,6 +181,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) ListCell *item; ListCell *option; char *password =3D NULL; /* user password */ + char *passname =3D NULL; /* name of the password for managing mu= ltiple passwords */ bool issuper =3D false; /* Make the user a superuser? */ bool inherit =3D true; /* Auto inherit privileges? */ bool createrole =3D false; /* Can this user create roles? */ @@ -144,6 +209,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) DefElem *dadminmembers =3D NULL; DefElem *dvalidUntil =3D NULL; DefElem *dbypassRLS =3D NULL; + DefElem *dpassName =3D NULL; + =20 /* 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 =3D defel; } + else if (strcmp(defel->defname, "passname") =3D=3D 0) + { + if (dpassName) + errorConflictingDefElem(defel, pstate); + dpassName =3D defel; + } + else elog(ERROR, "option \"%s\" not recognized", defel->defname); @@ -283,6 +357,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) validUntil =3D strVal(dvalidUntil->arg); if (dbypassRLS) bypassrls =3D boolVal(dbypassRLS->arg); + if (dpassName) + passname =3D strVal(dpassName->arg); =20 /* Check some permissions first */ if (issuper) @@ -424,8 +500,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) =20 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 =3D encrypt_password(Password_encryption, stmt->role, + validate_and_get_salt(stmt->role, &salt, &logdetail); + shadow_pass =3D encrypt_password(Password_encryption, salt, password); =20 MemSet(new_password_record, 0, sizeof(new_password_record)); MemSet(new_password_record_nulls, false, sizeof(new_password_record_nul= ls)); - + if (passname !=3D NULL) + new_password_record[Anum_pg_auth_password_name - 1] =3D + DirectFunctionCall1(namein, CStringGetDatum(passname)); + else + new_password_record[Anum_pg_auth_password_name - 1] =3D + DirectFunctionCall1(namein, CStringGetDatum(default_passname)); /* open password table and insert it. */ pg_auth_password_rel =3D table_open(AuthPasswordRelationId, RowExclusiv= eLock); pg_auth_password_dsc =3D RelationGetDescr(pg_auth_password_rel); @@ -560,6 +642,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) ListCell *option; char *rolename; char *password =3D NULL; /* user password */ + char *passname =3D NULL; /* password name for managing multiple = passwords */ int connlimit =3D -1; /* maximum connections allowed */ char *validUntil =3D 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 =3D NULL; DefElem *dvalidUntil =3D NULL; DefElem *dbypassRLS =3D NULL; + DefElem *dpassName =3D NULL; Oid roleid; =20 check_rolespec_name(stmt->role, @@ -652,6 +736,12 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) errorConflictingDefElem(defel, pstate); dbypassRLS =3D defel; } + else if (strcmp(defel->defname, "passname") =3D=3D 0) + { + if (dpassName) + errorConflictingDefElem(defel, pstate); + dpassName =3D defel; + } else elog(ERROR, "option \"%s\" not recognized", defel->defname); @@ -669,6 +759,8 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) } if (dvalidUntil) validUntil =3D strVal(dvalidUntil->arg); + if (dpassName) + passname =3D strVal(dpassName->arg); =20 /* * 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 =3D NULL; + char *salt =3D NULL; + const char *logdetail =3D NULL; + char *shadow_pass; =20 /* Like in CREATE USER, don't allow an empty password. */ if (password[0] =3D=3D '\0' || @@ -818,13 +911,26 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) } else { - /* Encrypt the password to the requested format. */ - shadow_pass =3D encrypt_password(Password_encryption, rolename, - password); - new_password_record[Anum_pg_auth_password_password - 1] =3D - CStringGetTextDatum(shadow_pass); + if (!validate_and_get_salt(rolename, &salt, &logdetail)) + new_password_record_nulls[Anum_pg_auth_password_password - 1] =3D true; + else + { + /* Encrypt the password to the requested format. */ + shadow_pass =3D encrypt_password(Password_encryption, salt, + password); + new_password_record[Anum_pg_auth_password_password - 1] =3D + CStringGetTextDatum(shadow_pass); + + new_password_record_repl[Anum_pg_auth_password_password - 1] =3D true; + + if (passname) + new_password_record[Anum_pg_auth_password_name - 1] =3D + DirectFunctionCall1(namein, CStringGetDatum(passname)); + else + new_password_record[Anum_pg_auth_password_name - 1] =3D + DirectFunctionCall1(namein, CStringGetDatum(default_passname)); + } } - new_password_record_repl[Anum_pg_auth_password_password - 1] =3D true; } =20 /* unset password */ @@ -859,7 +965,10 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) =20 pg_auth_password_rel =3D table_open(AuthPasswordRelationId, RowExclusive= Lock); pg_auth_password_dsc =3D RelationGetDescr(pg_auth_password_rel); - password_tuple =3D SearchSysCache1(AUTHPASSWORD, ObjectIdGetDatum(roleid= )); + if (dpassName) + password_tuple =3D SearchSysCache2(AUTHPASSWORDNAME, ObjectIdGetDatum(r= oleid), CStringGetDatum(passname)); + else + password_tuple =3D SearchSysCache2(AUTHPASSWORDNAME, ObjectIdGetDatum(r= oleid), CStringGetDatum(default_passname)); =20 if (new_password_record_nulls[Anum_pg_auth_password_password - 1] =3D=3D= true) /* delete existing password */ { @@ -1157,14 +1266,23 @@ DropRole(DropRoleStmt *stmt) DeleteSharedSecurityLabel(roleid, AuthIdRelationId); =20 /* - * Drop password + * Drop password(s) */ - tuple =3D 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 =3D systable_beginscan(pg_auth_password_rel, AuthPasswordRoleOidIn= dexId, + true, NULL, 1, &scankey); + + while (HeapTupleIsValid(tmp_tuple =3D systable_getnext(sscan))) + { + CatalogTupleDelete(pg_auth_password_rel, &tmp_tuple->t_self); } =20 + 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 =3D 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; =20 rel =3D table_open(AuthIdRelationId, RowExclusiveLock); dsc =3D RelationGetDescr(rel); @@ -1301,28 +1423,38 @@ RenameRole(const char *oldname, const char *newname) CStringGetDatum(newname)); repl_null[Anum_pg_authid_rolname - 1] =3D false; =20 - passtuple =3D SearchSysCache1(AUTHPASSWORD, roleid); + ScanKeyInit(&scankey, + Anum_pg_auth_password_roleid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(roleid)); + + pg_auth_password_rel =3D table_open(AuthPasswordRelationId, RowExclusiveL= ock); =20 - if (HeapTupleIsValid(passtuple)) - datum =3D SysCacheGetAttr(AUTHPASSWORD, passtuple, - Anum_pg_auth_password_password, &isnull); + sscan =3D systable_beginscan(pg_auth_password_rel, AuthPasswordRoleOidInd= exId, + true, NULL, 1, &scankey); =20 - if (!isnull && get_password_type(TextDatumGetCString(datum)) =3D=3D PASSW= ORD_TYPE_MD5) + while (HeapTupleIsValid(passtuple =3D systable_getnext(sscan))) { - Relation pg_auth_password_rel; + datum =3D SysCacheGetAttr(AUTHPASSWORDNAME, passtuple, + Anum_pg_auth_password_password, &isnull); =20 - /* MD5 uses the username as salt, so just clear it on a rename */ - pg_auth_password_rel =3D table_open(AuthPasswordRelationId, RowExclusive= Lock); + if (!isnull && get_password_type(TextDatumGetCString(datum)) =3D=3D PASS= WORD_TYPE_MD5) + { =20 - 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"))); } =20 + systable_endscan(sscan); + table_close(pg_auth_password_rel, NoLock); + newtuple =3D heap_modify_tuple(oldtuple, dsc, repl_val, repl_null, repl_r= epl); CatalogTupleUpdate(rel, &oldtuple->t_self, newtuple); =20 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 **passwo= rds, 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 =3D mech->init(port, selected_mech, shadow_pass); + opaq =3D mech->init(port, selected_mech, passwords, num); =20 inputlen =3D pq_getmsgint(&buf, 4); if (inputlen =3D=3D -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 @@ =20 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; =20 +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; =20 + /* + * 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 */ =20 /* 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.passwo= rd. + * 'passwords' are the role's stored secrets, from pg_auth_password.passwo= rd. * 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 perfo= rm * an authentication exchange, but it will fail, as if an incorrect passwo= rd * 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, co= nst int num_secrets) { scram_state *state; - bool got_secret; + bool got_secret =3D false; + int i; + int iterations; + char *salt =3D NULL; /* base64-encoded */ =20 state =3D (scram_state *) palloc0(sizeof(scram_state)); state->port =3D port; @@ -260,46 +276,47 @@ scram_init(Port *port, const char *selected_mech, con= st char *shadow_pass) /* * Parse the stored secret. */ - if (shadow_pass) + if (secrets) { - int password_type =3D get_password_type(shadow_pass); - - if (password_type =3D=3D PASSWORD_TYPE_SCRAM_SHA_256) + state->secrets =3D palloc0(sizeof(scram_secret) * num_secrets); + state->num_secrets =3D num_secrets; + for (i =3D 0; i < num_secrets; i++) { - if (parse_scram_secret(shadow_pass, &state->iterations, &state->salt, - state->StoredKey, state->ServerKey)) - got_secret =3D true; - else + int password_type =3D get_password_type(secrets[i]); + + if (password_type =3D=3D 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 =3D 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, a= llow failure via mock */ + if (strcmp(salt, state->salt) || iterations !=3D state->iterations) { + ereport(WARNING, (errmsg("inconsistent salt or iterations for user = \"%s\"", + state->port->user_name))); + got_secret =3D false; /* fail and allow mock creditials to be creat= ed */ + break; + } + } + else + { + salt =3D state->salt; + iterations =3D state->iterations; + got_secret =3D 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 =3D psprintf(_("User \"%s\" does not have a valid SCRA= M secret."), - state->port->user_name); - got_secret =3D 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 =3D false; } =20 /* @@ -310,8 +327,10 @@ scram_init(Port *port, const char *selected_mech, cons= t char *shadow_pass) */ if (!got_secret) { + state->secrets =3D 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].Serve= rKey); state->doomed =3D true; } =20 @@ -459,7 +478,7 @@ scram_exchange(void *opaq, const char *input, int input= len, * 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 =3D=3D SASLPREP_SUCCESS) password =3D (const char *) prep_password; =20 - /* 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); =20 result =3D 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 =3D pg_hmac_create(PG_SHA256); - int i; + pg_hmac_ctx *ctx; + int i, j; const char *errstr =3D 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 =3D 0; j < state->num_secrets; j++) { - elog(ERROR, "could not calculate client signature: %s", - pg_hmac_error(ctx)); - } + ctx =3D 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; + } =20 - pg_hmac_free(ctx); + elog(LOG, "success on %d", j); =20 - /* Extract the ClientKey that the client calculated from the proof */ - for (i =3D 0; i < SCRAM_KEY_LEN; i++) - ClientKey[i] =3D state->ClientProof[i] ^ ClientSignature[i]; + pg_hmac_free(ctx); =20 - /* 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 =3D 0; i < SCRAM_KEY_LEN; i++) + ClientKey[i] =3D state->ClientProof[i] ^ ClientSignature[i]; =20 - if (memcmp(client_StoredKey, state->StoredKey, SCRAM_KEY_LEN) !=3D 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); =20 - return true; + if (memcmp(client_StoredKey, state->secrets[j].StoredKey, SCRAM_KEY_LEN)= =3D=3D 0) { + elog(LOG, "Moving forward with Password %d", j); + state->chosen_secret =3D j; + return true; + } + } + + return false; } =20 /* @@ -1380,7 +1411,7 @@ build_server_final_message(scram_state *state) pg_hmac_ctx *ctx =3D pg_hmac_create(PG_SHA256); =20 /* calculate ServerSignature */ - if (pg_hmac_init(ctx, state->ServerKey, SCRAM_KEY_LEN) < 0 || + if (pg_hmac_init(ctx, state->secrets[state->chosen_secret].ServerKey, SCR= AM_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); =20 -static int CheckMD5Auth(Port *port, char *shadow_pass, - const char **logdetail); +static int CheckMD5Auth(Port *port, const char **passwords, int num, const= char **logdetail); =20 =20 /*---------------------------------------------------------------- @@ -790,8 +789,9 @@ static int CheckPasswordAuth(Port *port, const char **logdetail) { char *passwd; - int result; - char *shadow_pass; + int result =3D STATUS_ERROR; + int i, num; + char **passwords; =20 sendAuthRequest(port, AUTH_REQ_PASSWORD, NULL, 0); =20 @@ -799,17 +799,21 @@ CheckPasswordAuth(Port *port, const char **logdetail) if (passwd =3D=3D NULL) return STATUS_EOF; /* client wouldn't send password */ =20 - shadow_pass =3D get_role_password(port->user_name, logdetail); - if (shadow_pass) - { - result =3D plain_crypt_verify(port->user_name, shadow_pass, passwd, - logdetail); + passwords =3D get_role_passwords(port->user_name, logdetail, &num); + if (passwords !=3D NULL) { + for (i =3D 0; i < num; i++) + { + result =3D plain_crypt_verify(port->user_name, passwords[i], passwd, + logdetail); + if (result =3D=3D STATUS_OK) + break; /* Found a matching password, no need to try any others */ + } + for (i =3D 0; i < num; i++) + pfree(passwords[i]); + + pfree(passwords); } - else - result =3D STATUS_ERROR; =20 - if (shadow_pass) - pfree(shadow_pass); pfree(passwd); =20 if (result =3D=3D 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 =3D false; + int auth_result =3D STATUS_ERROR; + int i, num; + char **passwords; =20 Assert(port->hba->auth_method =3D=3D uaSCRAM || port->hba->auth_method =3D=3D uaMD5); =20 - /* First look up the user's password. */ - shadow_pass =3D 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 =3D Password_encryption; - else - pwtype =3D get_password_type(shadow_pass); + /* First look up the user's passwords. */ + passwords =3D get_role_passwords(port->user_name, logdetail, &num); =20 /* * 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 =3D=3D uaMD5 && pwtype =3D=3D PASSWORD_TYPE_MD= 5) - auth_result =3D CheckMD5Auth(port, shadow_pass, logdetail); - else - auth_result =3D CheckSASLAuth(&pg_be_scram_mech, port, shadow_pass, - logdetail); + if (passwords !=3D NULL) { + for (i =3D 0; i < num; i++) + if (get_password_type(passwords[i]) =3D=3D PASSWORD_TYPE_SCRAM_SHA_256) + scram_pw_avail =3D true; + + if (port->hba->auth_method =3D=3D uaMD5 && !scram_pw_avail) + auth_result =3D CheckMD5Auth(port, (const char **) passwords, num, logd= etail); + else + auth_result =3D CheckSASLAuth(&pg_be_scram_mech, port, (const char **) = passwords, num, + logdetail); =20 - if (shadow_pass) - pfree(shadow_pass); + for (i =3D 0; i < num; i++)=20 + pfree(passwords[i]); =20 - /* - * If get_role_password() returned error, return error, even if the - * authentication succeeded. - */ - if (!shadow_pass) - { - Assert(auth_result !=3D STATUS_OK); - return STATUS_ERROR; + pfree(passwords); } =20 if (auth_result =3D=3D STATUS_OK) @@ -884,11 +873,12 @@ CheckPWChallengeAuth(Port *port, const char **logdeta= il) } =20 static int -CheckMD5Auth(Port *port, char *shadow_pass, const char **logdetail) +CheckMD5Auth(Port *port, const char **passwords, int num, const char **log= detail) { char md5Salt[4]; /* Password salt */ char *passwd; - int result; + int result =3D STATUS_ERROR; + int i; =20 if (Db_user_namespace) ereport(FATAL, @@ -909,12 +899,13 @@ CheckMD5Auth(Port *port, char *shadow_pass, const cha= r **logdetail) if (passwd =3D=3D NULL) return STATUS_EOF; /* client wouldn't send password */ =20 - if (shadow_pass) - result =3D md5_crypt_verify(port->user_name, shadow_pass, passwd, + for (i =3D 0; i < num; i++) + { + result =3D md5_crypt_verify(port->user_name, passwords[i], passwd, md5Salt, 4, logdetail); - else - result =3D STATUS_ERROR; - + if (result =3D=3D STATUS_OK) + break; + } pfree(passwd); =20 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" =20 @@ -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; =20 /* Get role info from pg_authid */ roleTup =3D SearchSysCache1(AUTHNAME, PointerGetDatum(role)); @@ -52,30 +55,34 @@ get_role_password(const char *role, const char **logdet= ail) } =20 datum =3D SysCacheGetAttr(AUTHNAME, roleTup, Anum_pg_authid_oid, &isnull); -=09 - passTup =3D SearchSysCache1(AUTHPASSWORD, datum); + ReleaseSysCache(roleTup); + /* Find any existing password that is not the one being updated to get th= e salt */ + passlist =3D SearchSysCacheList1(AUTHPASSWORDNAME, datum); + *num =3D passlist->n_members; =20 - if (!HeapTupleIsValid(passTup)) + if (passlist->n_members =3D=3D 0) { + *num =3D 0; *logdetail =3D psprintf(_("User \"%s\" has no password assigned."), role); + ReleaseCatCacheList(passlist); return NULL; /* user has no password */ } - datum =3D SysCacheGetAttr(AUTHPASSWORD, passTup, - Anum_pg_auth_password_password, &isnull); =20 - ReleaseSysCache(roleTup); - if (isnull) /* this should not happen any more but just in case */ + passwords =3D palloc0(sizeof(char *) * passlist->n_members);=20 + + for (i =3D 0; i < passlist->n_members; i++) { - ReleaseSysCache(passTup); - *logdetail =3D psprintf(_("User \"%s\" has no password assigned."), - role); - return NULL; /* user has no password */ + HeapTuple tup =3D &passlist->members[i]->tuple; + + datum =3D SysCacheGetAttr(AUTHPASSWORDNAME, tup, + Anum_pg_auth_password_password, &isnull); + passwords[i] =3D TextDatumGetCString(datum); } - shadow_pass =3D TextDatumGetCString(datum); - ReleaseSysCache(passTup); =20 - return shadow_pass; + ReleaseCatCacheList(passlist); + + return passwords; } =20 /* @@ -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 =3D get_password_type(password); @@ -128,13 +135,13 @@ encrypt_password(PasswordType target_type, const char= *role, case PASSWORD_TYPE_MD5: encrypted_password =3D palloc(MD5_PASSWD_LEN + 1); =20 - 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; =20 case PASSWORD_TYPE_SCRAM_SHA_256: - return pg_be_scram_build_secret(password); + return pg_be_scram_build_secret(password, salt); =20 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, Lis= t *aliases, Node *query); ORDER ORDINALITY OTHERS OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER =20 - 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 =20 @@ -1261,6 +1261,15 @@ AlterOptRoleElem: errhint("Remove UNENCRYPTED to store the password in encrypted for= m instead."), parser_errposition(@1))); } + | PASSNAME Sconst + { + $$ =3D makeDefElem("passname", + (Node *)makeString($2), @1); + } + | VALID FOR Sconst + { + $$ =3D makeDefElem("validFor", (Node *)makeString($3), @1); + } | INHERIT { $$ =3D 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/c= atcache.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) =20 case AUTHNAME: case AUTHOID: - case AUTHPASSWORD: + case AUTHPASSWORDNAME: case AUTHMEMMEMROLE: case DATABASEOID: =20 diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/s= yscache.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[] =3D { }, 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/p= g_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 su= pport */ + #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; =20 @@ -44,7 +47,6 @@ DECLARE_TOAST(pg_auth_password, 4175, 4176); =20 typedef FormData_pg_auth_password *Form_pg_auth_password; =20 -DECLARE_UNIQUE_INDEX_PKEY(pg_auth_password_roleoid_index, 4550, AuthPasswo= rdRoleOidIndexId, on pg_auth_password using btree(roleid oid_ops)); - +DECLARE_UNIQUE_INDEX_PKEY(pg_auth_password_roleoid_index, 4553, AuthPasswo= rdRoleOidIndexId, on pg_auth_password using btree(roleid oid_ops, name name= _ops)); =20 #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 *use= rname, const char *shad =20 extern PGDLLIMPORT check_password_hook_type check_password_hook; =20 -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); =20 -extern char *get_role_password(const char *role, const char **logdetail); +extern char **get_role_passwords(const char *role, const char **logdetail,= int *num); =20 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, con= st int num_secrets); =20 /*--------- * exchange() @@ -131,6 +131,6 @@ typedef struct pg_be_sasl_mech =20 /* 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); =20 #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; =20 /* 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 *sa= lt); 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_L= ABEL) 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/expe= cted/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+/=3D]+)\$([a-zA-Z0-9+=3D/]+):([a-zA-Z0-9+/=3D]+)', '\1$\2:$:') as rolpassword_masked FROM pg_authid - LEFT JOIN pg_auth_password p=20 + LEFT JOIN pg_auth_password p ON pg_authid.oid =3D 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=20 + LEFT JOIN pg_auth_password p ON pg_authid.oid =3D p.roleid WHERE rolname LIKE 'regress_passwd2_new' ORDER BY rolname, password; @@ -78,7 +78,7 @@ CREATE ROLE regress_passwd7 PASSWORD 'md50123456789012345= 67890123456789zz'; CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901z= z'; SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0= -9+/=3D]+)\$([a-zA-Z0-9+=3D/]+):([a-zA-Z0-9+/=3D]+)', '\1$\2:$:') as rolpassword_masked FROM pg_authid - LEFT JOIN pg_auth_password p=20 + LEFT JOIN pg_auth_password p ON pg_authid.oid =3D 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:hpFyHTUsSWcR7= O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=3D:qVV5NeluNfUPkwm7Vqat25Rj= SPLkGeoZBQs6wVv+um4=3D'; NOTICE: empty string is not a valid password, clearing password SELECT password FROM pg_authid -LEFT JOIN pg_auth_password p=20 +LEFT JOIN pg_auth_password p ON pg_authid.oid =3D p.roleid WHERE rolname=3D'regress_passwd_empty'; password=20 ---------- @@ -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=3D=3D%' as is_r= olpassword_rehashed FROM pg_authid - LEFT JOIN pg_auth_password p=20 + LEFT JOIN pg_auth_password p ON pg_authid.oid =3D 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=20 + LEFT JOIN pg_auth_password p ON pg_authid.oid =3D p.roleid WHERE rolname LIKE 'regress_passwd%' ORDER BY rolname, password; --=20 2.34.1 =46rom 80f2b1948c299cbc799901d36a23d44bee36e00b Mon Sep 17 00:00:00 2001 =46rom: Stephen Frost 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 =3D InvalidOid; =20 =20 -/* GUC parameter */ +/* GUC parameters */ int Password_encryption =3D PASSWORD_TYPE_SCRAM_SHA_256; +Interval *default_password_duration =3D NULL; =20 /* default password name */ const char* default_passname =3D "__def__"; @@ -166,6 +167,84 @@ validate_and_get_salt(char *rolename, char **salt, con= st char **logdetail) return true; } =20 +static +Datum +expires_in_datum(DefElem *passExpiresIn) +{ + Interval *interval; + Node *arg; + A_Const *con; + TimestampTz now =3D GetCurrentTimestamp(); + Datum passExpiresIn_datum; + char *dateout; + + if (default_password_duration !=3D 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 !=3D NULL) + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to override password_validity_duration GU= C"))); + else + goto bypass; + else + { + passExpiresIn_datum =3D DirectFunctionCall2(timestamptz_pl_interval, + TimestampGetDatum(now), + PointerGetDatum(default_password_duration)); + + dateout =3D + DatumGetCString(DirectFunctionCall1(timestamp_out, + passExpiresIn_datum)); + + ereport(NOTICE, + (errmsg("Password will expire at: \"%s\" (from GUC)", dateout))); + + return passExpiresIn_datum; + } + } + + if (passExpiresIn =3D=3D NULL) /* No duration requested via SQL and no s= ystem default, no expiration */ + return PointerGetDatum(NULL); + +bypass: + arg =3D (Node *)passExpiresIn->arg; + if (IsA(arg, TypeCast)) + { + TypeCast *tc =3D (TypeCast *) passExpiresIn->arg; + arg =3D tc->arg; + } + + if (!IsA(arg, A_Const)) + { + elog(ERROR, "unrecognized node type: %d", (int) nodeTag(arg)); + return PointerGetDatum(NULL); + } + con =3D (A_Const *) arg; + + interval =3D DatumGetIntervalP(DirectFunctionCall3(interval_in, + CStringGetDatum(strVal(&con->val)), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1))); + + passExpiresIn_datum =3D DirectFunctionCall2(timestamptz_pl_interval, + TimestampGetDatum(now), + PointerGetDatum(interval)); + + dateout =3D + 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 =3D NULL; DefElem *dbypassRLS =3D NULL; DefElem *dpassName =3D NULL; - + DefElem *dpassExpiresIn =3D NULL; =20 /* 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 =3D defel; } + else if (strcmp(defel->defname, "expiresin") =3D=3D 0) + { + if (dpassExpiresIn) + errorConflictingDefElem(defel, pstate); + dpassExpiresIn =3D defel; + } =20 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; =20 /* * 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_nul= ls)); + + passExpiresIn_datum =3D expires_in_datum(dpassExpiresIn); + if (passExpiresIn_datum !=3D PointerGetDatum(NULL)) + new_password_record[Anum_pg_auth_password_expiration - 1] =3D passExpi= resIn_datum; + else + new_password_record_nulls[Anum_pg_auth_password_expiration - 1] =3D tr= ue; + /* Encrypt the password to the requested format. */ validate_and_get_salt(stmt->role, &salt, &logdetail); shadow_pass =3D encrypt_password(Password_encryption, salt, password); =20 - MemSet(new_password_record, 0, sizeof(new_password_record)); - MemSet(new_password_record_nulls, false, sizeof(new_password_record_nul= ls)); if (passname !=3D NULL) new_password_record[Anum_pg_auth_password_name - 1] =3D DirectFunctionCall1(namein, CStringGetDatum(passname)); else new_password_record[Anum_pg_auth_password_name - 1] =3D DirectFunctionCall1(namein, CStringGetDatum(default_passname)); + /* open password table and insert it. */ pg_auth_password_rel =3D table_open(AuthPasswordRelationId, RowExclusiv= eLock); pg_auth_password_dsc =3D RelationGetDescr(pg_auth_password_rel); @@ -647,6 +741,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) char *validUntil =3D 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 =3D NULL; DefElem *dissuper =3D NULL; DefElem *dinherit =3D NULL; @@ -659,6 +754,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) DefElem *dvalidUntil =3D NULL; DefElem *dbypassRLS =3D NULL; DefElem *dpassName =3D NULL; + DefElem *dpassExpiresIn =3D NULL; Oid roleid; =20 check_rolespec_name(stmt->role, @@ -742,6 +838,12 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) errorConflictingDefElem(defel, pstate); dpassName =3D defel; } + else if (strcmp(defel->defname, "expiresin") =3D=3D 0) + { + if (dpassExpiresIn) + errorConflictingDefElem(defel, pstate); + dpassExpiresIn =3D defel; + } else elog(ERROR, "option \"%s\" not recognized", defel->defname); @@ -762,6 +864,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (dpassName) passname =3D strVal(dpassName->arg); =20 + /* * 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)); =20 + passExpiresIn_datum =3D expires_in_datum(dpassExpiresIn); + if (passExpiresIn_datum !=3D PointerGetDatum(NULL)) { + new_password_record[Anum_pg_auth_password_expiration - 1] =3D passExpire= sIn_datum; + new_password_record_repl[Anum_pg_auth_password_expiration - 1] =3D true; + } + else + new_password_record_nulls[Anum_pg_auth_password_expiration - 1] =3D true; =20 /* * issuper/createrole/etc @@ -957,7 +1067,8 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) ReleaseSysCache(tuple); heap_freetuple(new_tuple); =20 - if (new_password_record_repl[Anum_pg_auth_password_password - 1] =3D=3D t= rue) + if (new_password_record_repl[Anum_pg_auth_password_password - 1] =3D=3D t= rue + || new_password_record_repl[Anum_pg_auth_password_expiration - 1] =3D=3D= true) { Relation pg_auth_password_rel; TupleDesc pg_auth_password_dsc; diff --git a/src/backend/commands/variable.c b/src/backend/commands/variabl= e.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 =3D *newval; + char *val; + Interval *interval; + + if (newval =3D=3D NULL || *newval =3D=3D NULL) { + extra =3D NULL; + return true; + } + + elog(NOTICE,"Setting password duration to \"%s\"", + *newval); + + while (isspace((unsigned char) *valueptr)) + valueptr++; + if (*valueptr !=3D '\'') { + val =3D pstrdup(valueptr); + } + else + { + valueptr++; + val =3D pstrdup(valueptr); + /* Check and remove trailing quote */ + endptr =3D strchr(val, '\''); + if (!endptr || endptr[1] !=3D '\0') + { + pfree(val); + return false; + } + *endptr =3D '\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 =3D DatumGetIntervalP(DirectFunctionCall3(interval_in, + CStringGetDatum(val), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1))); + + pfree(val); + + if (!interval) { + return false; + } + + new_interval =3D malloc(sizeof(Interval)); + memcpy(new_interval, interval, sizeof(Interval)); + pfree(interval); + + /* + * Pass back data for assign_password_validity to use + */ + *extra =3D malloc(sizeof(Interval *)); + if (!*extra) + return false; + *((Interval **) *extra) =3D new_interval; + + return true; +} + +/* + * assign_password_validity: GUC assign_hook for timezone + */ +void +assign_password_duration(const char *newval, void *extra) +{ + if (extra =3D=3D NULL) + default_password_duration =3D NULL; + else + default_password_duration =3D *((Interval **) extra); +} + +/* + * show_password_validity: GUC show_hook for timezone + */ +const char * +show_password_duration(void) +{ + const char *intervalout; + if (default_password_duration =3D=3D NULL) { + return ""; + } + intervalout =3D DatumGetCString(DirectFunctionCall1(interval_out, + PointerGetDatum(default_password_duration))); + + if (intervalout !=3D 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 **logdetai= l) auth_result =3D CheckSASLAuth(&pg_be_scram_mech, port, (const char **) = passwords, num, logdetail); =20 - for (i =3D 0; i < num; i++)=20 - pfree(passwords[i]); + for (i =3D 0; i < num; i++) { =20 + if (passwords[i] !=3D NULL) + pfree(passwords[i]); + else + ereport(LOG, + (errmsg("Password %d was null", i))); + } pfree(passwords); } =20 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 **logde= tail, int *num) { HeapTuple roleTup; Datum datum; + TimestampTz current, vuntil =3D 0; + bool isnull; char **passwords; CatCList *passlist; - int i; + int i, j =3D 0, num_valid_passwords =3D 0; =20 /* Get role info from pg_authid */ roleTup =3D SearchSysCache1(AUTHNAME, PointerGetDatum(role)); @@ -51,6 +53,7 @@ get_role_passwords(const char *role, const char **logdeta= il, int *num) { *logdetail =3D psprintf(_("Role \"%s\" does not exist."), role); + *num =3D 0; return NULL; /* no such user */ } =20 @@ -58,7 +61,6 @@ get_role_passwords(const char *role, const char **logdeta= il, int *num) ReleaseSysCache(roleTup); /* Find any existing password that is not the one being updated to get th= e salt */ passlist =3D SearchSysCacheList1(AUTHPASSWORDNAME, datum); - *num =3D passlist->n_members; =20 if (passlist->n_members =3D=3D 0) { @@ -69,15 +71,41 @@ get_role_passwords(const char *role, const char **logde= tail, int *num) return NULL; /* user has no password */ } =20 - passwords =3D palloc0(sizeof(char *) * passlist->n_members);=20 + current =3D GetCurrentTimestamp(); + + for (i =3D 0; i < passlist->n_members; i++) + { + HeapTuple tup =3D &passlist->members[i]->tuple; + + datum =3D SysCacheGetAttr(AUTHPASSWORDNAME, tup, + Anum_pg_auth_password_expiration, &isnull); + + if (!isnull) + vuntil =3D DatumGetTimestampTz(datum); + + if (isnull || vuntil > current) + num_valid_passwords++; + } + + passwords =3D palloc0(sizeof(char *) * num_valid_passwords); + *num =3D num_valid_passwords; =20 for (i =3D 0; i < passlist->n_members; i++) { HeapTuple tup =3D &passlist->members[i]->tuple; =20 datum =3D SysCacheGetAttr(AUTHPASSWORDNAME, tup, - Anum_pg_auth_password_password, &isnull); - passwords[i] =3D TextDatumGetCString(datum); + Anum_pg_auth_password_expiration, &isnull); + + if (!isnull) + vuntil =3D DatumGetTimestampTz(datum); + + if (isnull || vuntil > current) + { + datum =3D SysCacheGetAttr(AUTHPASSWORDNAME, tup, + Anum_pg_auth_password_password, &isnull); + passwords[j++] =3D pstrdup(TextDatumGetCString(datum)); + } } =20 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, Lis= t *aliases, Node *query); DOUBLE_P DROP =20 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 E= XPRESSION EXTENSION EXTERNAL EXTRACT =20 FALSE_P FAMILY FETCH FILTER FINALIZE FIRST_P FLOAT_P FOLLOWING FOR @@ -1266,6 +1266,12 @@ AlterOptRoleElem: $$ =3D makeDefElem("passname", (Node *)makeString($2), @1); } + | EXPIRES IN_P Sconst opt_interval + { + TypeName *t =3D SystemTypeName("interval"); + t->typmods =3D $4; + $$ =3D makeDefElem("expiresin", (Node *)makeStringConstCast($3, @3, t= ), @1); + } | VALID FOR Sconst { $$ =3D 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; =20 =20 /* should be static, but commands/variable.c needs to get at this */ @@ -4721,6 +4722,17 @@ static struct config_string ConfigureNamesString[] = =3D check_backtrace_functions, assign_backtrace_functions, NULL }, =20 + { + {"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; =20 +/* 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 vali= duntil_null); =20 diff --git a/src/include/commands/variable.h b/src/include/commands/variabl= e.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 *newv= al, 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); + =20 #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) --=20 2.34.1 --oFGQ2VdWvpciDCpb-- --mB/CYmFGQzL2EujR Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJivj3RAAoJEO1sijiDR2RVkdsQAKAdjPv0JLut11ciGqQC+N7U dAtV8aRiuCXjBETbYOJxPiPZFHpdI84cb9Cy3dFJzBQ+caMaRgQ9Doh8Ut05MVmX ZHHDTzmezTGPy8iT9p2qZm84bU56DhAWGBV0HBbIbEy4fhOiQjZ4n2O7X3xVpBAN lkgv3zW3hxmvyC0wFon1mjUECdsjDm++9pcBErmThJelZOgi/uh1xG+U9N69l4xc ZqesNhvLqRgjTcqa/VYno5rDKrgiPkIvBG5Gij+p7KUFjL8wb+92sMr7Pa+cFkR1 18HmZBd6//xX11xWwJkpxqHnszBPsBLpcK93NIIn/DRHg776UYUjgLnBvEN28bIx 3bIRzXtuL66lRKVq8IsLcZn1xbu319wN9Kk7l6Ro874Td4XzTaHiAOX0TxsUO2sQ 1ditFPihjG3LOvn+WTR4cWgGGKmN0mjOGtiIkl15u5Emh5qHIk8jNw/M/iQ1gq0E rpGxLF/8+EsbH+nIAnbAYp039zV17ZBBb5uFQmlQpHMszw8vBKwcsYpW2OIv88mt RseYn3oqDJVE5aYDzv9SffXb+f0B0EvDRaz+LO5NVHxfDpH8LpEMt9gaHLSnZpYm vGFq1djSOvaoBYGhbQmScahednPqVHq3C1kcQtgkU05tesZ7dn0vs0ofgpfT3tSc j8lHWHsBLkZSubzq5NYY =rCWx -----END PGP SIGNATURE----- --mB/CYmFGQzL2EujR--