diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index a72f80f033..babaae69e4 100644 --- a/doc/src/sgml/client-auth.sgml +++ b/doc/src/sgml/client-auth.sgml @@ -1281,7 +1281,7 @@ omicron bryanh guest1 PostgreSQL database passwords are separate from operating system user passwords. The password for - each database user is stored in the pg_authid system + each database user is stored in the pg_passwords system catalog. Passwords can be managed with the SQL commands and , diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile index 3e9994793d..8a377d3f23 100644 --- a/src/backend/catalog/Makefile +++ b/src/backend/catalog/Makefile @@ -118,7 +118,8 @@ CATALOG_HEADERS := \ pg_publication_namespace.h \ pg_publication_rel.h \ pg_subscription.h \ - pg_subscription_rel.h + pg_subscription_rel.h \ + pg_auth_password.h GENERATED_HEADERS := $(CATALOG_HEADERS:%.h=%_d.h) schemapg.h system_fk_info.h diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c index 1bf6c5633c..2e8e37b59b 100644 --- a/src/backend/catalog/catalog.c +++ b/src/backend/catalog/catalog.c @@ -28,6 +28,7 @@ #include "catalog/catalog.h" #include "catalog/namespace.h" #include "catalog/pg_auth_members.h" +#include "catalog/pg_auth_password.h" #include "catalog/pg_authid.h" #include "catalog/pg_database.h" #include "catalog/pg_db_role_setting.h" @@ -246,6 +247,7 @@ IsSharedRelation(Oid relationId) { /* These are the shared catalogs (look for BKI_SHARED_RELATION) */ if (relationId == AuthIdRelationId || + relationId == AuthPasswordRelationId || relationId == AuthMemRelationId || relationId == DatabaseRelationId || relationId == DbRoleSettingRelationId || @@ -260,6 +262,8 @@ IsSharedRelation(Oid relationId) /* These are their indexes */ if (relationId == AuthIdOidIndexId || relationId == AuthIdRolnameIndexId || + relationId == AuthPasswordRoleOidIndexId || + relationId == AuthMemRoleMemIndexId || relationId == AuthMemMemRoleIndexId || relationId == AuthMemRoleMemIndexId || relationId == AuthMemOidIndexId || @@ -281,8 +285,8 @@ IsSharedRelation(Oid relationId) relationId == TablespaceOidIndexId) return true; /* These are their toast tables and toast indexes */ - if (relationId == PgAuthidToastTable || - relationId == PgAuthidToastIndex || + if (relationId == PgAuthPasswordToastTable || + relationId == PgAuthPasswordToastIndex || relationId == PgDatabaseToastTable || relationId == PgDatabaseToastIndex || relationId == PgDbRoleSettingToastTable || diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 77b06e2a7a..2c9e074607 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -40,11 +40,13 @@ CREATE VIEW pg_shadow AS rolsuper AS usesuper, rolreplication AS userepl, rolbypassrls AS usebypassrls, - rolpassword AS passwd, + p.password AS passwd, rolvaliduntil AS valuntil, setconfig AS useconfig FROM pg_authid LEFT JOIN pg_db_role_setting s ON (pg_authid.oid = setrole AND setdatabase = 0) + LEFT JOIN pg_auth_password p + ON (p.roleid = pg_authid.oid) WHERE rolcanlogin; REVOKE ALL ON pg_shadow FROM public; diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index ce77a055e5..8bdd0eb592 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" @@ -30,7 +31,9 @@ #include "commands/defrem.h" #include "commands/seclabel.h" #include "commands/user.h" +#include "common/scram-common.h" #include "libpq/crypt.h" +#include "libpq/scram.h" #include "miscadmin.h" #include "storage/lmgr.h" #include "utils/acl.h" @@ -88,6 +91,11 @@ char *createrole_self_grant = ""; bool createrole_self_grant_enabled = false; GrantRoleOptions createrole_self_grant_options; +Interval *default_password_duration = NULL; + +/* default password name */ +static const char* default_password_name = "__def__"; + /* Hook to check passwords in CreateRole() and AlterRole() */ check_password_hook_type check_password_hook = NULL; @@ -125,6 +133,164 @@ have_createrole_privilege(void) return has_createrole_privilege(GetUserId()); } +static bool +validate_and_get_salt(char *rolename, char **salt, const char **logdetail) +{ + char **current_secrets; + int i, num_secrets; + char *salt1, *salt2 = NULL; + PasswordType passtype; + + if (Password_encryption == PASSWORD_TYPE_MD5) + { + *salt = rolename; /* md5 always uses role name, no need to look through the passwords */ + return true; + } + else if (Password_encryption == PASSWORD_TYPE_PLAINTEXT) + { + *salt = NULL; /* Plaintext does not have a salt */ + return true; + } + + current_secrets = get_role_passwords(rolename, logdetail, &num_secrets); + if (num_secrets == 0) + { + *salt = NULL; /* No existing passwords, allow one to be generated */ + return true; + } + + for (i = 0; i < num_secrets; i++) + { + passtype = get_password_type(current_secrets[i]); + + if (passtype == PASSWORD_TYPE_MD5 || passtype == PASSWORD_TYPE_PLAINTEXT) + continue; /* md5 uses rolename as salt so it is always the same and plaintext has no salt */ + else if (passtype == PASSWORD_TYPE_SCRAM_SHA_256) + { + int iterations; + int key_length = 0; + pg_cryptohash_type hash_type; + uint8 stored_key[SCRAM_MAX_KEY_LEN]; + uint8 server_key[SCRAM_MAX_KEY_LEN]; + + if (!parse_scram_secret(current_secrets[i], &iterations, &hash_type, &key_length, + &salt1, stored_key, server_key)) + { + *logdetail = psprintf(_("could not parse SCRAM secret")); + *salt = NULL; + return false; + } + + if (salt2 != NULL) + { + if (strcmp(salt1, salt2)) + { + *logdetail = psprintf(_("inconsistent salts, clearing password")); // TODO: Better message + *salt = NULL; + return false; + } + } + else + salt2 = salt1; + } + } + + for (i = 0; i < num_secrets; i++) + pfree(current_secrets[i]); + pfree(current_secrets); + + if (salt2) + *salt = pstrdup(salt2); + + return true; +} + +static Datum +expires_in_datum(DefElem *passExpiresIn) +{ + Interval *interval; + Node *arg; + A_Const *con; + TimestampTz now = GetCurrentTimestamp(); + Datum passExpiresIn_datum; + char *dateout; + + if (default_password_duration != NULL) + { + /* + * The default duration GUC is set, use it if nothing came from SQL. + * If something came from SQL, reject it unless it is from a superuser. + */ + if (passExpiresIn != NULL) + { + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to override password_validity_duration GUC"))); + else + goto bypass; + } + else + { + passExpiresIn_datum = DirectFunctionCall2(timestamptz_pl_interval, + TimestampGetDatum(now), + PointerGetDatum(default_password_duration)); + + dateout = + DatumGetCString(DirectFunctionCall1(timestamp_out, + passExpiresIn_datum)); + + /* TODO: convert to DEBUG2 */ + ereport(NOTICE, + (errmsg("Password will expire at: \"%s\" (from GUC)", dateout))); + + return passExpiresIn_datum; + } + } + + if (passExpiresIn == NULL) /* No duration requested via SQL and no system default, no expiration */ + return PointerGetDatum(NULL); + +bypass: + arg = (Node *)passExpiresIn->arg; + if (IsA(arg, TypeCast)) // TODO: Do we really need to do this? Ensure a test-case covers or obviates it. + { + TypeCast *tc = (TypeCast *) passExpiresIn->arg; + arg = tc->arg; + } + + if (!IsA(arg, A_Const)) + { + elog(ERROR, "unrecognized node type: %d", (int) nodeTag(arg)); + /* Keep compiler happy */ + return PointerGetDatum(NULL); + } + + con = (A_Const *) arg; + if (con->isnull || !IsA(&(con->val), String)) + { + elog(ERROR, "'expires in' value must be a string"); + /* Keep compiler happy */ + return PointerGetDatum(NULL); + } + + interval = DatumGetIntervalP(DirectFunctionCall3(interval_in, + CStringGetDatum(strVal(&con->val)), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1))); + + passExpiresIn_datum = DirectFunctionCall2(timestamptz_pl_interval, + TimestampGetDatum(now), + PointerGetDatum(interval)); + + dateout = DatumGetCString(DirectFunctionCall1(timestamp_out, + passExpiresIn_datum)); + + ereport(NOTICE, + (errmsg("Password will expire at: \"%s\" (from SQL)", dateout))); + + return passExpiresIn_datum; +} /* * CREATE ROLE @@ -142,6 +308,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) ListCell *item; ListCell *option; char *password = NULL; /* user password */ + char *passname = NULL; /* name of the password for managing multiple passwords */ bool issuper = false; /* Make the user a superuser? */ bool inherit = true; /* Auto inherit privileges? */ bool createrole = false; /* Can this user create roles? */ @@ -169,6 +336,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) DefElem *dadminmembers = NULL; DefElem *dvalidUntil = NULL; DefElem *dbypassRLS = NULL; + DefElem *dpassName = NULL; + DefElem *dpassExpiresIn = NULL; GrantRoleOptions popt; /* The defaults can vary depending on the original statement type */ @@ -272,6 +441,19 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) errorConflictingDefElem(defel, pstate); dbypassRLS = defel; } + else if (strcmp(defel->defname, "passname") == 0) + { + if (dpassName) + errorConflictingDefElem(defel, pstate); + dpassName = defel; + } + else if (strcmp(defel->defname, "expiresin") == 0) + { + if (dpassExpiresIn) + errorConflictingDefElem(defel, pstate); + dpassExpiresIn = defel; + } + else elog(ERROR, "option \"%s\" not recognized", defel->defname); @@ -309,6 +491,8 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) validUntil = strVal(dvalidUntil->arg); if (dbypassRLS) bypassrls = boolVal(dbypassRLS->arg); + if (dpassName) + passname = strVal(dpassName->arg); /* Check some permissions first */ if (!superuser_arg(currentUserId)) @@ -415,43 +599,6 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin); new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication); new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit); - - if (password) - { - char *shadow_pass; - const char *logdetail = NULL; - - /* - * Don't allow an empty password. Libpq treats an empty password the - * same as no password at all, and won't even try to authenticate. But - * other clients might, so allowing it would be confusing. By clearing - * the password when an empty string is specified, the account is - * consistently locked for all clients. - * - * Note that this only covers passwords stored in the database itself. - * There are also checks in the authentication code, to forbid an - * empty password from being used with authentication methods that - * fetch the password from an external system, like LDAP or PAM. - */ - if (password[0] == '\0' || - plain_crypt_verify(stmt->role, password, "", &logdetail) == STATUS_OK) - { - ereport(NOTICE, - (errmsg("empty string is not a valid password, clearing password"))); - new_record_nulls[Anum_pg_authid_rolpassword - 1] = true; - } - else - { - /* Encrypt the password to the requested format. */ - shadow_pass = encrypt_password(Password_encryption, stmt->role, - password); - new_record[Anum_pg_authid_rolpassword - 1] = - CStringGetTextDatum(shadow_pass); - } - } - else - new_record_nulls[Anum_pg_authid_rolpassword - 1] = true; - new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum; new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null; @@ -486,6 +633,74 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) */ CatalogTupleInsert(pg_authid_rel, tuple); + if (password) + { + char *shadow_pass, *salt; + const char *logdetail; + Datum new_password_record[Natts_pg_auth_password] = {0}; + bool new_password_record_nulls[Natts_pg_auth_password] = {0}; + Relation pg_auth_password_rel; + TupleDesc pg_auth_password_dsc; + HeapTuple new_tuple; + Datum passExpiresIn_datum; + + /* + * Don't allow an empty password. Libpq treats an empty password the + * same as no password at all, and won't even try to authenticate. But + * other clients might, so allowing it would be confusing. By clearing + * the password when an empty string is specified, the account is + * consistently locked for all clients. + * + * Note that this only covers passwords stored in the database itself. + * There are also checks in the authentication code, to forbid an + * empty password from being used with authentication methods that + * fetch the password from an external system, like LDAP or PAM. + */ + if (password[0] == '\0' || + plain_crypt_verify(stmt->role, password, "", &logdetail) == STATUS_OK) + { + ereport(NOTICE, + (errmsg("empty string is not a valid password, clearing password"))); + } + else + { + passExpiresIn_datum = expires_in_datum(dpassExpiresIn); + if (passExpiresIn_datum != PointerGetDatum(NULL)) + new_password_record[Anum_pg_auth_password_expiration - 1] = passExpiresIn_datum; + else + new_password_record_nulls[Anum_pg_auth_password_expiration - 1] = true; + + /* Encrypt the password to the requested format. */ + if (!validate_and_get_salt(stmt->role, &salt, &logdetail)) + ereport(ERROR, + (errmsg("could not get a valid salt for password"), + errdetail("%s", logdetail))); + + shadow_pass = encrypt_password(Password_encryption, salt, password); + + if (passname != NULL) + new_password_record[Anum_pg_auth_password_name - 1] = + DirectFunctionCall1(namein, CStringGetDatum(passname)); + else + new_password_record[Anum_pg_auth_password_name - 1] = + DirectFunctionCall1(namein, CStringGetDatum(default_password_name)); + + /* open password table and insert it. */ + pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock); + pg_auth_password_dsc = RelationGetDescr(pg_auth_password_rel); + + new_password_record[Anum_pg_auth_password_password - 1] = + CStringGetTextDatum(shadow_pass); + new_password_record[Anum_pg_auth_password_roleid - 1] = roleid; + + new_tuple = heap_form_tuple(pg_auth_password_dsc, + new_password_record, new_password_record_nulls); + CatalogTupleInsert(pg_auth_password_rel, new_tuple); + heap_freetuple(new_tuple); + table_close(pg_auth_password_rel, NoLock); + } + } + /* * Advance command counter so we can see new record; else tests in * AddRoleMems may fail. @@ -622,6 +837,9 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) Datum new_record[Natts_pg_authid] = {0}; bool new_record_nulls[Natts_pg_authid] = {0}; bool new_record_repl[Natts_pg_authid] = {0}; + Datum new_password_record[Natts_pg_auth_password] = {0}; + bool new_password_record_nulls[Natts_pg_auth_password] = {0}; + bool new_password_record_repl[Natts_pg_auth_password] = {0}; Relation pg_authid_rel; TupleDesc pg_authid_dsc; HeapTuple tuple, @@ -630,10 +848,12 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) ListCell *option; char *rolename; char *password = NULL; /* user password */ + char *passname = NULL; /* password name for managing multiple passwords */ int connlimit = -1; /* maximum connections allowed */ char *validUntil = NULL; /* time the login is valid until */ Datum validUntil_datum; /* same, as timestamptz Datum */ bool validUntil_null; + Datum passExpiresIn_datum; /* Time period until password expires */ DefElem *dpassword = NULL; DefElem *dissuper = NULL; DefElem *dinherit = NULL; @@ -645,6 +865,8 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) DefElem *drolemembers = NULL; DefElem *dvalidUntil = NULL; DefElem *dbypassRLS = NULL; + DefElem *dpassName = NULL; + DefElem *dpassExpiresIn = NULL; Oid roleid; Oid currentUserId = GetUserId(); GrantRoleOptions popt; @@ -724,6 +946,18 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) errorConflictingDefElem(defel, pstate); dbypassRLS = defel; } + else if (strcmp(defel->defname, "passname") == 0) + { + if (dpassName) + errorConflictingDefElem(defel, pstate); + dpassName = defel; + } + else if (strcmp(defel->defname, "expiresin") == 0) + { + if (dpassExpiresIn) + errorConflictingDefElem(defel, pstate); + dpassExpiresIn = defel; + } else elog(ERROR, "option \"%s\" not recognized", defel->defname); @@ -741,6 +975,8 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) } if (dvalidUntil) validUntil = strVal(dvalidUntil->arg); + if (dpassName) + passname = strVal(dpassName->arg); /* * Scan the pg_authid relation to be certain the user exists. @@ -857,6 +1093,15 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) * Build an updated tuple, perusing the information just obtained */ + passExpiresIn_datum = expires_in_datum(dpassExpiresIn); + if (passExpiresIn_datum != PointerGetDatum(NULL)) + { + new_password_record[Anum_pg_auth_password_expiration - 1] = passExpiresIn_datum; + new_password_record_repl[Anum_pg_auth_password_expiration - 1] = true; + } + else + new_password_record_nulls[Anum_pg_auth_password_expiration - 1] = true; + /* * issuper/createrole/etc */ @@ -915,6 +1160,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (password) { char *shadow_pass; + char *salt = NULL; const char *logdetail = NULL; /* Like in CREATE USER, don't allow an empty password. */ @@ -923,24 +1169,41 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) { ereport(NOTICE, (errmsg("empty string is not a valid password, clearing password"))); - new_record_nulls[Anum_pg_authid_rolpassword - 1] = true; + new_password_record_nulls[Anum_pg_auth_password_password - 1] = true; } else + { + if (!validate_and_get_salt(rolename, &salt, &logdetail)) + ereport(ERROR, + (errmsg("could not get a valid salt for password"), + errdetail("%s", logdetail))); + + if (salt == NULL) + new_password_record_nulls[Anum_pg_auth_password_password - 1] = true; + else { /* Encrypt the password to the requested format. */ - shadow_pass = encrypt_password(Password_encryption, rolename, - password); - new_record[Anum_pg_authid_rolpassword - 1] = + shadow_pass = encrypt_password(Password_encryption, salt, password); + new_password_record[Anum_pg_auth_password_password - 1] = CStringGetTextDatum(shadow_pass); + + new_password_record_repl[Anum_pg_auth_password_password - 1] = true; + + if (passname) + new_password_record[Anum_pg_auth_password_name - 1] = + DirectFunctionCall1(namein, CStringGetDatum(passname)); + else + new_password_record[Anum_pg_auth_password_name - 1] = + DirectFunctionCall1(namein, CStringGetDatum(default_password_name)); + } } - new_record_repl[Anum_pg_authid_rolpassword - 1] = true; } /* unset password */ if (dpassword && dpassword->arg == NULL) { - new_record_repl[Anum_pg_authid_rolpassword - 1] = true; - new_record_nulls[Anum_pg_authid_rolpassword - 1] = true; + new_password_record_repl[Anum_pg_auth_password_password - 1] = true; + new_password_record_nulls[Anum_pg_auth_password_password - 1] = true; } /* valid until */ @@ -957,11 +1220,52 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) new_tuple = heap_modify_tuple(tuple, pg_authid_dsc, new_record, new_record_nulls, new_record_repl); CatalogTupleUpdate(pg_authid_rel, &tuple->t_self, new_tuple); + ReleaseSysCache(tuple); + heap_freetuple(new_tuple); - InvokeObjectPostAlterHook(AuthIdRelationId, roleid, 0); + if (new_password_record_repl[Anum_pg_auth_password_password - 1] == true + || new_password_record_repl[Anum_pg_auth_password_expiration - 1] == true) + { + Relation pg_auth_password_rel; + TupleDesc pg_auth_password_dsc; + HeapTuple password_tuple; - ReleaseSysCache(tuple); + pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock); + pg_auth_password_dsc = RelationGetDescr(pg_auth_password_rel); + if (dpassName) + password_tuple = SearchSysCache2(AUTHPASSWORDNAME, ObjectIdGetDatum(roleid), CStringGetDatum(passname)); + else + password_tuple = SearchSysCache2(AUTHPASSWORDNAME, ObjectIdGetDatum(roleid), CStringGetDatum(default_password_name)); + + if (new_password_record_nulls[Anum_pg_auth_password_password - 1] == true) /* delete existing password */ + { + if (HeapTupleIsValid(password_tuple)) + { + CatalogTupleDelete(pg_auth_password_rel, &password_tuple->t_self); + ReleaseSysCache(password_tuple); + } + } + else if (HeapTupleIsValid(password_tuple)) /* update existing password */ + { + new_tuple = heap_modify_tuple(password_tuple, pg_auth_password_dsc, new_password_record, + new_password_record_nulls, new_password_record_repl); + CatalogTupleUpdate(pg_auth_password_rel, &password_tuple->t_self, new_tuple); + ReleaseSysCache(password_tuple); heap_freetuple(new_tuple); + } + else /* insert new password */ + { + new_password_record[Anum_pg_auth_password_roleid - 1] = roleid; + + new_tuple = heap_form_tuple(pg_auth_password_dsc, + new_password_record, new_password_record_nulls); + CatalogTupleInsert(pg_auth_password_rel, new_tuple); + heap_freetuple(new_tuple); + } + + table_close(pg_auth_password_rel, NoLock); + } + InvokeObjectPostAlterHook(AuthIdRelationId, roleid, 0); InitGrantRoleOptions(&popt); @@ -1083,7 +1387,6 @@ AlterRoleSet(AlterRoleSetStmt *stmt) return roleid; } - /* * DROP ROLE */ @@ -1091,7 +1394,8 @@ void DropRole(DropRoleStmt *stmt) { Relation pg_authid_rel, - pg_auth_members_rel; + pg_auth_members_rel, + pg_auth_password_rel; ListCell *item; List *role_addresses = NIL; @@ -1108,6 +1412,7 @@ DropRole(DropRoleStmt *stmt) */ pg_authid_rel = table_open(AuthIdRelationId, RowExclusiveLock); pg_auth_members_rel = table_open(AuthMemRelationId, RowExclusiveLock); + pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock); foreach(item, stmt->roles) { @@ -1249,6 +1554,29 @@ DropRole(DropRoleStmt *stmt) systable_endscan(sscan); + /* + * Drop password(s) + */ + ScanKeyInit(&scankey, + Anum_pg_auth_password_roleid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(roleid)); + + sscan = systable_beginscan(pg_auth_password_rel, AuthPasswordRoleOidIndexId, + true, NULL, 1, &scankey); + + while (HeapTupleIsValid(tmp_tuple = systable_getnext(sscan))) + { + CatalogTupleDelete(pg_auth_password_rel, &tmp_tuple->t_self); + } + + systable_endscan(sscan); + + /* + * Remove settings for this role. + */ + DropSetting(InvalidOid, roleid); + /* * Advance command counter so that later iterations of this loop will * see the changes already made. This is essential if, for example, @@ -1331,6 +1659,7 @@ 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); } @@ -1341,11 +1670,15 @@ ObjectAddress RenameRole(const char *oldname, const char *newname) { HeapTuple oldtuple, - newtuple; + newtuple, + passtuple; TupleDesc dsc; + ScanKeyData scankey; + SysScanDesc sscan; + Relation rel; Datum datum; - bool isnull; + bool isnull = true; Datum repl_val[Natts_pg_authid]; bool repl_null[Natts_pg_authid]; bool repl_repl[Natts_pg_authid]; @@ -1353,6 +1686,7 @@ RenameRole(const char *oldname, const char *newname) Oid roleid; ObjectAddress address; Form_pg_authid authform; + Relation pg_auth_password_rel; rel = table_open(AuthIdRelationId, RowExclusiveLock); dsc = RelationGetDescr(rel); @@ -1449,17 +1783,38 @@ RenameRole(const char *oldname, const char *newname) CStringGetDatum(newname)); repl_null[Anum_pg_authid_rolname - 1] = false; - datum = heap_getattr(oldtuple, Anum_pg_authid_rolpassword, dsc, &isnull); + ScanKeyInit(&scankey, + Anum_pg_auth_password_roleid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(roleid)); + + pg_auth_password_rel = table_open(AuthPasswordRelationId, RowExclusiveLock); + + sscan = systable_beginscan(pg_auth_password_rel, AuthPasswordRoleOidIndexId, + true, NULL, 1, &scankey); + + while (HeapTupleIsValid(passtuple = systable_getnext(sscan))) + { + datum = SysCacheGetAttr(AUTHPASSWORDNAME, passtuple, + Anum_pg_auth_password_password, &isnull); if (!isnull && get_password_type(TextDatumGetCString(datum)) == PASSWORD_TYPE_MD5) { + /* MD5 uses the username as salt, so just clear it on a rename */ - repl_repl[Anum_pg_authid_rolpassword - 1] = true; - repl_null[Anum_pg_authid_rolpassword - 1] = true; + if (HeapTupleIsValid(passtuple)) + { + CatalogTupleDelete(pg_auth_password_rel, &passtuple->t_self); ereport(NOTICE, (errmsg("MD5 password cleared because of role rename"))); + + } } + } + + systable_endscan(sscan); + table_close(pg_auth_password_rel, NoLock); newtuple = heap_modify_tuple(oldtuple, dsc, repl_val, repl_null, repl_repl); CatalogTupleUpdate(rel, &oldtuple->t_self, newtuple); diff --git a/src/backend/commands/variable.c b/src/backend/commands/variable.c index a88cf5f118..c9b9bda5fd 100644 --- a/src/backend/commands/variable.c +++ b/src/backend/commands/variable.c @@ -24,6 +24,7 @@ #include "access/xlog.h" #include "access/xlogprefetcher.h" #include "catalog/pg_authid.h" +#include "commands/user.h" #include "common/string.h" #include "mb/pg_wchar.h" #include "miscadmin.h" @@ -1234,3 +1235,100 @@ check_ssl(bool *newval, void **extra, GucSource source) #endif return true; } + +/* + * check_password_duration: GUC check_hook for password_duration + */ +bool +check_password_duration(char **newval, void **extra, GucSource source) +{ + Interval *new_interval; + char *endptr; + + const char *valueptr = *newval; + char *val; + Interval *interval; + + if (*newval == NULL) + return true; + + /* TODO: Change to DEBUG2 */ + elog(NOTICE,"Setting password duration to \"%s\"", *newval); + + while (isspace((unsigned char) *valueptr)) + valueptr++; + + if (*valueptr != '\'') + { + val = pstrdup(valueptr); + } + else + { + valueptr++; + val = pstrdup(valueptr); + /* Check and remove trailing quote */ + endptr = strchr(val, '\''); + if (!endptr || endptr[1] != '\0') + { + pfree(val); + return false; + } + *endptr = '\0'; + } + + /* + * Try to parse it. XXX an invalid interval format will result in + * ereport(ERROR), which is not desirable for GUC. We did what we + * could to guard against this in flatten_set_variable_args, but a + * string coming in from postgresql.conf might contain anything. + */ + interval = DatumGetIntervalP(DirectFunctionCall3(interval_in, + CStringGetDatum(val), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1))); + + pfree(val); + + if (!interval) + return false; + + new_interval = guc_malloc(LOG, sizeof(Interval)); + memcpy(new_interval, interval, sizeof(Interval)); + pfree(interval); + + *extra = (void*) new_interval; + + return true; +} + +/* + * assign_password_validity: GUC assign_hook for password_duration + */ +void +assign_password_duration(const char *newval, void *extra) +{ + if (extra == NULL) + default_password_duration = NULL; + else + default_password_duration = (Interval *) extra; +} + +/* + * show_password_validity: GUC show_hook for password_duration + */ +const char * +show_password_duration(void) +{ + const char *intervalout; + + if (default_password_duration == NULL) + return ""; + + intervalout = DatumGetCString(DirectFunctionCall1(interval_out, + PointerGetDatum(default_password_duration))); + + if (intervalout != NULL) + return intervalout; + + return ""; +} diff --git a/src/backend/libpq/auth-sasl.c b/src/backend/libpq/auth-sasl.c index c535bc5383..fa03615696 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. @@ -49,7 +49,7 @@ * should just pass NULL. */ int -CheckSASLAuth(const pg_be_sasl_mech *mech, Port *port, char *shadow_pass, +CheckSASLAuth(const pg_be_sasl_mech *mech, Port *port, const char **passwords, int num_passwords, const char **logdetail) { StringInfoData sasl_mechs; @@ -136,7 +136,7 @@ CheckSASLAuth(const pg_be_sasl_mech *mech, Port *port, char *shadow_pass, * This is because we don't want to reveal to an attacker what * usernames are valid, nor which users have a valid password. */ - opaq = mech->init(port, selected_mech, shadow_pass); + opaq = mech->init(port, selected_mech, passwords, num_passwords); inputlen = pq_getmsgint(&buf, 4); if (inputlen == -1) diff --git a/src/backend/libpq/auth-scram.c b/src/backend/libpq/auth-scram.c index 118d15b1a1..9c66d09206 100644 --- a/src/backend/libpq/auth-scram.c +++ b/src/backend/libpq/auth-scram.c @@ -109,7 +109,7 @@ static void scram_get_mechanisms(Port *port, StringInfo buf); static void *scram_init(Port *port, const char *selected_mech, - const char *shadow_pass); + const char **secrets, const int num_secrets); static int scram_exchange(void *opaq, const char *input, int inputlen, char **output, int *outputlen, const char **logdetail); @@ -132,6 +132,12 @@ typedef enum SCRAM_AUTH_FINISHED } scram_state_enum; +typedef struct +{ + uint8 StoredKey[SCRAM_MAX_KEY_LEN]; + uint8 ServerKey[SCRAM_MAX_KEY_LEN]; +} scram_secret; + typedef struct { scram_state_enum state; @@ -145,10 +151,16 @@ typedef struct pg_cryptohash_type hash_type; int key_length; + /* + * The salt and iterations must be the same for all + * secrets since they are sent as part of the initial message + */ int iterations; char *salt; /* base64-encoded */ - uint8 StoredKey[SCRAM_MAX_KEY_LEN]; - uint8 ServerKey[SCRAM_MAX_KEY_LEN]; + /* Array of possible secrets */ + scram_secret *secrets; + int num_secrets; + int chosen_secret; /* secret chosen during final client message */ /* Fields of the first message from client */ char cbind_flag; @@ -231,17 +243,20 @@ scram_get_mechanisms(Port *port, StringInfo buf) * It should be one of the mechanisms that we support, as returned by * scram_get_mechanisms(). * - * 'shadow_pass' is the role's stored secret, from pg_authid.rolpassword. + * 'passwords' are the role's stored secrets, from pg_auth_password.password. * The username was provided by the client in the startup message, and is * available in port->user_name. If 'shadow_pass' is NULL, we still perform * an authentication exchange, but it will fail, as if an incorrect password * was given. */ static void * -scram_init(Port *port, const char *selected_mech, const char *shadow_pass) +scram_init(Port *port, const char *selected_mech, const char **secrets, const int num_secrets) { scram_state *state; - bool got_secret; + bool got_secret = false; + int i; + int iterations; + char *salt = NULL; /* base64-encoded */ state = (scram_state *) palloc0(sizeof(scram_state)); state->port = port; @@ -270,49 +285,54 @@ scram_init(Port *port, const char *selected_mech, const char *shadow_pass) /* * Parse the stored secret. */ - if (shadow_pass) + if (secrets) { - int password_type = get_password_type(shadow_pass); + state->secrets = palloc0(sizeof(scram_secret) * num_secrets); + state->num_secrets = num_secrets; + for (i = 0; i < num_secrets; i++) + { + int password_type = get_password_type(secrets[i]); if (password_type == PASSWORD_TYPE_SCRAM_SHA_256) { - if (parse_scram_secret(shadow_pass, &state->iterations, + if (parse_scram_secret(secrets[i], &state->iterations, &state->hash_type, &state->key_length, &state->salt, - state->StoredKey, - state->ServerKey)) - got_secret = true; - else + state->secrets[i].StoredKey, + state->secrets[i].ServerKey)) { - /* - * The password looked like a SCRAM secret, but could not be - * parsed. - */ - ereport(LOG, - (errmsg("invalid SCRAM secret for user \"%s\"", + if (salt) + { + /* The stored iterations and salt must match or we cannot proceed, allow failure via mock */ + if (strcmp(salt, state->salt) || iterations != state->iterations) + { + ereport(WARNING, (errmsg("inconsistent salt or iterations for user \"%s\"", state->port->user_name))); - got_secret = false; + got_secret = false; /* fail and allow mock creditials to be created */ + pfree(state->secrets); + state->num_secrets = 0; + break; } } else { - /* - * The user doesn't have SCRAM secret. (You cannot do SCRAM - * authentication with an MD5 hash.) - */ - state->logdetail = psprintf(_("User \"%s\" does not have a valid SCRAM secret."), - state->port->user_name); - got_secret = false; + salt = state->salt; + iterations = state->iterations; + got_secret = true; /* We got at least one good SCRAM secret */ } } 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. + * The password looked like a SCRAM secret, but could not be + * parsed. */ - got_secret = false; + ereport(LOG, + (errmsg("invalid SCRAM secret for user \"%s\"", + state->port->user_name))); + } + } + } } /* @@ -323,10 +343,13 @@ scram_init(Port *port, const char *selected_mech, const char *shadow_pass) */ if (!got_secret) { + state->secrets = palloc0(sizeof(scram_secret)); + state->num_secrets = 1; + mock_scram_secret(state->port->user_name, &state->hash_type, &state->iterations, &state->key_length, &state->salt, - state->StoredKey, state->ServerKey); + state->secrets[0].StoredKey, state->secrets[0].ServerKey); state->doomed = true; } @@ -469,12 +492,12 @@ scram_exchange(void *opaq, const char *input, int inputlen, } /* - * Construct a SCRAM secret, for storing in pg_authid.rolpassword. + * Construct a SCRAM secret, for storing in pg_auth_password.password. * * The result is palloc'd, so caller is responsible for freeing it. */ char * -pg_be_scram_build_secret(const char *password) +pg_be_scram_build_secret(const char *password, const char *salt) { char *prep_password; pg_saslprep_rc rc; @@ -491,11 +514,20 @@ pg_be_scram_build_secret(const char *password) if (rc == SASLPREP_SUCCESS) password = (const char *) prep_password; - /* Generate random salt */ - if (!pg_strong_random(saltbuf, SCRAM_DEFAULT_SALT_LEN)) + /* Use passed-in salt, or generate random salt */ + if (!salt && !pg_strong_random(saltbuf, SCRAM_DEFAULT_SALT_LEN)) + { ereport(ERROR, (errcode(ERRCODE_INTERNAL_ERROR), errmsg("could not generate random salt"))); + } + else if (salt) + { + if (pg_b64_decode(salt, strlen(salt), saltbuf, SCRAM_DEFAULT_SALT_LEN) == -1) + ereport(ERROR, + (errcode(ERRCODE_INTERNAL_ERROR), + errmsg("could not decode SCRAM salt"))); + } result = scram_build_secret(PG_SHA256, SCRAM_SHA_256_KEY_LEN, saltbuf, SCRAM_DEFAULT_SALT_LEN, @@ -1142,16 +1174,20 @@ verify_client_proof(scram_state *state) uint8 ClientSignature[SCRAM_MAX_KEY_LEN]; uint8 ClientKey[SCRAM_MAX_KEY_LEN]; uint8 client_StoredKey[SCRAM_MAX_KEY_LEN]; - pg_hmac_ctx *ctx = pg_hmac_create(state->hash_type); - int i; + pg_hmac_ctx *ctx; + int i, j; const char *errstr = NULL; - /* * Calculate ClientSignature. Note that we don't log directly a failure * here even when processing the calculations as this could involve a mock * authentication. */ - if (pg_hmac_init(ctx, state->StoredKey, state->key_length) < 0 || + for (j = 0; j < state->num_secrets; j++) + { + ctx = pg_hmac_create(state->hash_type); + elog(LOG, "Trying to verify password %d", j); // TODO: Convert to DEBUG2 + + if (pg_hmac_init(ctx, state->secrets[j].StoredKey, state->key_length) < 0 || pg_hmac_update(ctx, (uint8 *) state->client_first_message_bare, strlen(state->client_first_message_bare)) < 0 || @@ -1165,10 +1201,15 @@ verify_client_proof(scram_state *state) strlen(state->client_final_message_without_proof)) < 0 || pg_hmac_final(ctx, ClientSignature, state->key_length) < 0) { - elog(ERROR, "could not calculate client signature: %s", - pg_hmac_error(ctx)); + // TODO: Convert to DEBUG2 + elog(LOG, "could not calculate client signature for secret %d", j); + pg_hmac_free(ctx); + continue; } + // TODO: Convert to DEBUG2 + elog(LOG, "succeeded on %d password", j); + pg_hmac_free(ctx); /* Extract the ClientKey that the client calculated from the proof */ @@ -1180,10 +1221,15 @@ verify_client_proof(scram_state *state) client_StoredKey, &errstr) < 0) elog(ERROR, "could not hash stored key: %s", errstr); - if (memcmp(client_StoredKey, state->StoredKey, state->key_length) != 0) - return false; - + if (memcmp(client_StoredKey, state->secrets[j].StoredKey, state->key_length) == 0) { + // TODO: Convert to DEBUG2 + elog(LOG, "Moving forward with Password %d", j); + state->chosen_secret = j; return true; + } + } + + return false; } /* @@ -1409,7 +1455,7 @@ build_server_final_message(scram_state *state) pg_hmac_ctx *ctx = pg_hmac_create(state->hash_type); /* calculate ServerSignature */ - if (pg_hmac_init(ctx, state->ServerKey, state->key_length) < 0 || + if (pg_hmac_init(ctx, state->secrets[state->chosen_secret].ServerKey, state->key_length) < 0 || pg_hmac_update(ctx, (uint8 *) state->client_first_message_bare, strlen(state->client_first_message_bare)) < 0 || diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c index 81dabb9c27..3c18c8f616 100644 --- a/src/backend/libpq/auth.c +++ b/src/backend/libpq/auth.c @@ -57,8 +57,7 @@ static void set_authn_id(Port *port, const char *id); static int CheckPasswordAuth(Port *port, const char **logdetail); static int CheckPWChallengeAuth(Port *port, const char **logdetail); -static int CheckMD5Auth(Port *port, char *shadow_pass, - const char **logdetail); +static int CheckMD5Auth(Port *port, const char **passwords, int num_passwords, const char **logdetail); /*---------------------------------------------------------------- @@ -789,8 +788,9 @@ static int CheckPasswordAuth(Port *port, const char **logdetail) { char *passwd; - int result; - char *shadow_pass; + int result = STATUS_ERROR; + int i, num_passwords; + char **passwords; sendAuthRequest(port, AUTH_REQ_PASSWORD, NULL, 0); @@ -798,17 +798,21 @@ CheckPasswordAuth(Port *port, const char **logdetail) if (passwd == NULL) return STATUS_EOF; /* client wouldn't send password */ - shadow_pass = get_role_password(port->user_name, logdetail); - if (shadow_pass) + passwords = get_role_passwords(port->user_name, logdetail, &num_passwords); + if (passwords != NULL) { + for (i = 0; i < num_passwords; i++) { - result = plain_crypt_verify(port->user_name, shadow_pass, passwd, + result = plain_crypt_verify(port->user_name, passwords[i], passwd, logdetail); + if (result == STATUS_OK) + break; /* Found a matching password, no need to try any others */ + } + for (i = 0; i < num_passwords; i++) + pfree(passwords[i]); + + pfree(passwords); } - else - result = STATUS_ERROR; - if (shadow_pass) - pfree(shadow_pass); pfree(passwd); if (result == STATUS_OK) @@ -823,57 +827,51 @@ CheckPasswordAuth(Port *port, const char **logdetail) static int CheckPWChallengeAuth(Port *port, const char **logdetail) { - int auth_result; - char *shadow_pass; - PasswordType pwtype; + bool scram_pw_avail = false; + int auth_result = STATUS_ERROR; + int i, num_passwords; + char **passwords; Assert(port->hba->auth_method == uaSCRAM || port->hba->auth_method == uaMD5); - /* First look up the user's password. */ - shadow_pass = get_role_password(port->user_name, logdetail); - - /* - * If the user does not exist, or has no password or it's expired, we - * still go through the motions of authentication, to avoid revealing to - * the client that the user didn't exist. If 'md5' is allowed, we choose - * whether to use 'md5' or 'scram-sha-256' authentication based on current - * password_encryption setting. The idea is that most genuine users - * probably have a password of that type, and if we pretend that this user - * had a password of that type, too, it "blends in" best. - */ - if (!shadow_pass) - pwtype = Password_encryption; - else - pwtype = get_password_type(shadow_pass); + /* First look up the user's passwords. */ + passwords = get_role_passwords(port->user_name, logdetail, &num_passwords); /* * If 'md5' authentication is allowed, decide whether to perform 'md5' or * 'scram-sha-256' authentication based on the type of password the user - * has. If it's an MD5 hash, we must do MD5 authentication, and if it's a - * SCRAM secret, we must do SCRAM authentication. + * has. If there's a SCRAM PW available then we'll do SCRAM, otherwise we + * will fall back to trying to use MD5. * * If MD5 authentication is not allowed, always use SCRAM. If the user * had an MD5 password, CheckSASLAuth() with the SCRAM mechanism will * fail. */ - if (port->hba->auth_method == uaMD5 && pwtype == PASSWORD_TYPE_MD5) - auth_result = CheckMD5Auth(port, shadow_pass, logdetail); + if (passwords != NULL) + { + for (i = 0; i < num_passwords; i++) + if (get_password_type(passwords[i]) == PASSWORD_TYPE_SCRAM_SHA_256) + { + scram_pw_avail = true; + break; + } + + if (port->hba->auth_method == uaMD5 && !scram_pw_avail) + auth_result = CheckMD5Auth(port, (const char **) passwords, num_passwords, logdetail); else - auth_result = CheckSASLAuth(&pg_be_scram_mech, port, shadow_pass, + auth_result = CheckSASLAuth(&pg_be_scram_mech, port, (const char **) passwords, num_passwords, logdetail); - if (shadow_pass) - pfree(shadow_pass); - - /* - * If get_role_password() returned error, return error, even if the - * authentication succeeded. - */ - if (!shadow_pass) + for (i = 0; i < num_passwords; i++) { - Assert(auth_result != STATUS_OK); - return STATUS_ERROR; + if (passwords[i] != NULL) + pfree(passwords[i]); + else + ereport(DEBUG2, + (errmsg("Password %d was null", i))); + } + pfree(passwords); } if (auth_result == STATUS_OK) @@ -883,11 +881,12 @@ CheckPWChallengeAuth(Port *port, const char **logdetail) } static int -CheckMD5Auth(Port *port, char *shadow_pass, const char **logdetail) +CheckMD5Auth(Port *port, const char **passwords, int num_passwords, const char **logdetail) { char md5Salt[4]; /* Password salt */ char *passwd; - int result; + int result = STATUS_ERROR; + int i; /* include the salt to use for computing the response */ if (!pg_strong_random(md5Salt, 4)) @@ -903,12 +902,13 @@ CheckMD5Auth(Port *port, char *shadow_pass, const char **logdetail) if (passwd == NULL) return STATUS_EOF; /* client wouldn't send password */ - if (shadow_pass) - result = md5_crypt_verify(port->user_name, shadow_pass, passwd, + for (i = 0; i < num_passwords; i++) + { + result = md5_crypt_verify(port->user_name, passwords[i], passwd, md5Salt, 4, logdetail); - else - result = STATUS_ERROR; - + if (result == STATUS_OK) + break; + } pfree(passwd); return result; diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c index ef496a0bea..5880033f0c 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-2023, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California @@ -16,12 +16,14 @@ #include #include "catalog/pg_authid.h" +#include "catalog/pg_auth_password.h" #include "common/md5.h" #include "common/scram-common.h" #include "libpq/crypt.h" #include "libpq/scram.h" #include "miscadmin.h" #include "utils/builtins.h" +#include "utils/catcache.h" #include "utils/syscache.h" #include "utils/timestamp.h" @@ -33,53 +35,108 @@ * 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) { - TimestampTz vuntil = 0; HeapTuple roleTup; Datum datum; + TimestampTz current, vuntil = 0; + bool isnull; - char *shadow_pass; + char **passwords; + CatCList *passlist; + int i, j = 0, num_valid_passwords = 0; + + *num = 0; /* Get role info from pg_authid */ roleTup = SearchSysCache1(AUTHNAME, PointerGetDatum(role)); if (!HeapTupleIsValid(roleTup)) { - *logdetail = psprintf(_("Role \"%s\" does not exist."), - role); + *logdetail = psprintf(_("Role \"%s\" does not exist."), role); return NULL; /* no such user */ } datum = SysCacheGetAttr(AUTHNAME, roleTup, - Anum_pg_authid_rolpassword, &isnull); - if (isnull) + Anum_pg_authid_rolvaliduntil, &isnull); + if (!isnull) + vuntil = DatumGetTimestampTz(datum); + + current = GetCurrentTimestamp(); + + /* + * Check to be sure we are not past rolvaliduntil + */ + if (!isnull && vuntil < current) { + /* + * Although it is the role that has expired, not one of its passwords, + * below we complain that the password has expired. This is to maintain + * compatibility with external tools that may have come to depend on + * this message when a role expires. + */ + *logdetail = psprintf(_("User \"%s\" has an expired password."), + role); + return NULL; + } + + datum = SysCacheGetAttr(AUTHNAME, roleTup, Anum_pg_authid_oid, &isnull); ReleaseSysCache(roleTup); + + /* Find any existing password that is not the one being updated, to get the salt */ + passlist = SearchSysCacheList1(AUTHPASSWORDNAME, datum); + + if (passlist->n_members == 0) + { *logdetail = psprintf(_("User \"%s\" has no password assigned."), role); + ReleaseCatCacheList(passlist); return NULL; /* user has no password */ } - shadow_pass = TextDatumGetCString(datum); - datum = SysCacheGetAttr(AUTHNAME, roleTup, - Anum_pg_authid_rolvaliduntil, &isnull); + /* + * TODO: Merge this and the following loop into one; allocate + * valid_passwords array as long as n_members, then use repalloc() to shrink + * the valid_passwords array. + */ + for (i = 0; i < passlist->n_members; i++) + { + HeapTuple tup = &passlist->members[i]->tuple; + + datum = SysCacheGetAttr(AUTHPASSWORDNAME, tup, + Anum_pg_auth_password_expiration, &isnull); + if (!isnull) vuntil = DatumGetTimestampTz(datum); - ReleaseSysCache(roleTup); + if (isnull || vuntil > current) + num_valid_passwords++; + } - /* - * Password OK, but check to be sure we are not past rolvaliduntil - */ - if (!isnull && vuntil < GetCurrentTimestamp()) + passwords = palloc0(sizeof(char *) * num_valid_passwords); + *num = num_valid_passwords; + + for (i = 0; i < passlist->n_members; i++) { - *logdetail = psprintf(_("User \"%s\" has an expired password."), - role); - return NULL; + HeapTuple tup = &passlist->members[i]->tuple; + + datum = SysCacheGetAttr(AUTHPASSWORDNAME, tup, + Anum_pg_auth_password_expiration, &isnull); + + if (!isnull) + vuntil = DatumGetTimestampTz(datum); + + if (isnull || vuntil > current) + { + datum = SysCacheGetAttr(AUTHPASSWORDNAME, tup, + Anum_pg_auth_password_password, &isnull); + passwords[j++] = pstrdup(TextDatumGetCString(datum)); } + } + + ReleaseCatCacheList(passlist); - return shadow_pass; + return passwords; } /* @@ -113,7 +170,7 @@ get_password_type(const char *shadow_pass) * hash, so it is stored as it is regardless of the requested type. */ char * -encrypt_password(PasswordType target_type, const char *role, +encrypt_password(PasswordType target_type, const char *salt, const char *password) { PasswordType guessed_type = get_password_type(password); @@ -134,13 +191,13 @@ encrypt_password(PasswordType target_type, const char *role, case PASSWORD_TYPE_MD5: encrypted_password = palloc(MD5_PASSWD_LEN + 1); - if (!pg_md5_encrypt(password, role, strlen(role), + if (!pg_md5_encrypt(password, salt, strlen(salt), encrypted_password, &errstr)) elog(ERROR, "password encryption failed: %s", errstr); return encrypted_password; case PASSWORD_TYPE_SCRAM_SHA_256: - return pg_be_scram_build_secret(password); + return pg_be_scram_build_secret(password, salt); case PASSWORD_TYPE_PLAINTEXT: elog(ERROR, "cannot encrypt password with 'plaintext'"); @@ -158,7 +215,7 @@ encrypt_password(PasswordType target_type, const char *role, * Check MD5 authentication response, and return STATUS_OK or STATUS_ERROR. * * 'shadow_pass' is the user's correct password or password hash, as stored - * in pg_authid.rolpassword. + * in pg_auth_password.password. * 'client_pass' is the response given by the remote user to the MD5 challenge. * 'md5_salt' is the salt used in the MD5 authentication challenge. * @@ -213,7 +270,7 @@ md5_crypt_verify(const char *role, const char *shadow_pass, * Check given password for given user, and return STATUS_OK or STATUS_ERROR. * * 'shadow_pass' is the user's correct password hash, as stored in - * pg_authid.rolpassword. + * pg_auth_password.password. * 'client_pass' is the password given by the remote user. * * In the error case, store a string at *logdetail that will be sent to the diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 7d2032885e..a8f48efcca 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -707,7 +707,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); DOUBLE_P DROP EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT - EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION + EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPIRES EXPLAIN EXPRESSION EXTENSION EXTERNAL EXTRACT FALSE_P FAMILY FETCH FILTER FINALIZE FIRST_P FLOAT_P FOLLOWING FOR @@ -743,7 +743,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); ORDER ORDINALITY OTHERS OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER - PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD + PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSNAME PASSWORD PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION @@ -1199,6 +1199,21 @@ AlterOptRoleElem: errhint("Remove UNENCRYPTED to store the password in encrypted form instead."), parser_errposition(@1))); } + | PASSNAME Sconst + { + $$ = makeDefElem("passname", + (Node *)makeString($2), @1); + } + | EXPIRES IN_P Sconst opt_interval + { + TypeName *t = SystemTypeName("interval"); + t->typmods = $4; + $$ = makeDefElem("expiresin", (Node *)makeStringConstCast($3, @3, t), @1); + } + | VALID FOR Sconst + { + $$ = makeDefElem("validFor", (Node *)makeString($3), @1); + } | INHERIT { $$ = makeDefElem("inherit", (Node *) makeBoolean(true), @1); @@ -17110,6 +17125,7 @@ unreserved_keyword: | EXCLUDING | EXCLUSIVE | EXECUTE + | EXPIRES | EXPLAIN | EXPRESSION | EXTENSION @@ -17214,6 +17230,7 @@ unreserved_keyword: | PARTIAL | PARTITION | PASSING + | PASSNAME | PASSWORD | PLANS | POLICY @@ -17670,6 +17687,7 @@ bare_label_keyword: | EXCLUSIVE | EXECUTE | EXISTS + | EXPIRES | EXPLAIN | EXPRESSION | EXTENSION @@ -17815,6 +17833,7 @@ bare_label_keyword: | PARTIAL | PARTITION | PASSING + | PASSNAME | PASSWORD | PLACING | PLANS diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c index 1aacb736c2..71eb0b7f49 100644 --- a/src/backend/utils/cache/catcache.c +++ b/src/backend/utils/cache/catcache.c @@ -1106,6 +1106,7 @@ IndexScanOK(CatCache *cache, ScanKey cur_skey) case AUTHNAME: case AUTHOID: + case AUTHPASSWORDNAME: case AUTHMEMMEMROLE: case DATABASEOID: diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 7234cb3da6..710470dd46 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -51,6 +51,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" @@ -114,6 +115,7 @@ static const FormData_pg_attribute Desc_pg_proc[Natts_pg_proc] = {Schema_pg_proc static const FormData_pg_attribute Desc_pg_type[Natts_pg_type] = {Schema_pg_type}; static const FormData_pg_attribute Desc_pg_database[Natts_pg_database] = {Schema_pg_database}; static const FormData_pg_attribute Desc_pg_authid[Natts_pg_authid] = {Schema_pg_authid}; +static const FormData_pg_attribute Desc_pg_auth_password[Natts_pg_auth_password] = {Schema_pg_auth_password}; static const FormData_pg_attribute Desc_pg_auth_members[Natts_pg_auth_members] = {Schema_pg_auth_members}; static const FormData_pg_attribute Desc_pg_index[Natts_pg_index] = {Schema_pg_index}; static const FormData_pg_attribute Desc_pg_shseclabel[Natts_pg_shseclabel] = {Schema_pg_shseclabel}; @@ -3495,6 +3497,7 @@ RelationBuildLocalRelation(const char *relname, { case DatabaseRelationId: case AuthIdRelationId: + case AuthPasswordRelationId: case AuthMemRelationId: case RelationRelationId: case AttributeRelationId: @@ -3970,7 +3973,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 @@ -4011,12 +4014,14 @@ RelationCacheInitializePhase2(void) Natts_pg_authid, Desc_pg_authid); formrdesc("pg_auth_members", AuthMemRelation_Rowtype_Id, true, Natts_pg_auth_members, Desc_pg_auth_members); + formrdesc("pg_auth_password", AuthPasswordRelation_Rowtype_Id, true, + Natts_pg_auth_password, Desc_pg_auth_password); formrdesc("pg_shseclabel", SharedSecLabelRelation_Rowtype_Id, true, Natts_pg_shseclabel, Desc_pg_shseclabel); formrdesc("pg_subscription", SubscriptionRelation_Rowtype_Id, true, Natts_pg_subscription, Desc_pg_subscription); -#define NUM_CRITICAL_SHARED_RELS 5 /* fix if you change list above */ +#define NUM_CRITICAL_SHARED_RELS 6 /* fix if you change list above */ } MemoryContextSwitchTo(oldcxt); @@ -4153,10 +4158,12 @@ RelationCacheInitializePhase3(void) AuthIdRelationId); load_critical_index(AuthMemMemRoleIndexId, AuthMemRelationId); + load_critical_index(AuthPasswordRoleOidIndexId, + AuthPasswordRelationId); load_critical_index(SharedSecLabelObjectIndexId, SharedSecLabelRelationId); -#define NUM_CRITICAL_SHARED_INDEXES 6 /* fix if you change list above */ +#define NUM_CRITICAL_SHARED_INDEXES 7 /* fix if you change list above */ criticalSharedRelcachesBuilt = true; } diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c index 8dbda0024f..f9caf4c1b8 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" @@ -213,6 +214,13 @@ static const struct cachedesc cacheinfo[] = { KEY(Anum_pg_authid_oid), 8 }, + [AUTHPASSWORDNAME] = { + AuthPasswordRelationId, + AuthPasswordRoleOidIndexId, + KEY(Anum_pg_auth_password_roleid, + Anum_pg_auth_password_name), + 8 + }, [CASTSOURCETARGET] = { CastRelationId, CastSourceTargetIndexId, diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index bdb26e2b77..4a5c3866d8 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -610,6 +610,8 @@ static char *recovery_target_xid_string; static char *recovery_target_name_string; static char *recovery_target_lsn_string; +static char *password_duration_string; + /* should be static, but commands/variable.c needs to get at this */ char *role_string; @@ -4540,6 +4542,16 @@ struct config_string ConfigureNamesString[] = "", check_debug_io_direct, assign_debug_io_direct, NULL }, + { + {"password_valid_duration", PGC_SUSET, CONN_AUTH_AUTH, + gettext_noop("Specifies the default validity duration of new passwords."), + NULL, + GUC_SUPERUSER_ONLY | GUC_NOT_IN_SAMPLE + }, + &password_duration_string, + NULL, + check_password_duration, assign_password_duration, show_password_duration + }, /* End-of-list marker */ { diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c index bddb30d766..3c3b874580 100644 --- a/src/bin/initdb/initdb.c +++ b/src/bin/initdb/initdb.c @@ -1567,10 +1567,10 @@ static void setup_auth(FILE *cmdfd) { /* - * The authid table shouldn't be readable except through views, to ensure + * The pg_auth_password table shouldn't be readable except through views, to ensure * passwords are not publicly visible. */ - PG_CMD_PUTS("REVOKE ALL ON pg_authid FROM public;\n\n"); + PG_CMD_PUTS("REVOKE ALL ON pg_auth_password FROM public;\n\n"); if (superuser_password) PG_CMD_PRINTF("ALTER USER \"%s\" WITH PASSWORD E'%s';\n\n", diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index e2a9733d34..bfa15e9696 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -762,7 +762,18 @@ dumpRoles(PGconn *conn) int i; /* note: rolconfig is dumped later */ - if (server_version >= 90600) + if (server_version >= 170000) + printfPQExpBuffer(buf, + "SELECT oid, rolname, rolsuper, rolinherit, " + "rolcreaterole, rolcreatedb, " + "rolcanlogin, rolconnlimit, p.password as rolpassword, " + "rolvaliduntil, rolreplication, rolbypassrls, " + "pg_catalog.shobj_description(oid, '%s') as rolcomment, " + "rolname = current_user AS is_current_user " + "FROM %s LEFT JOIN pg_auth_password p ON %s.oid = p.roleid " + "WHERE rolname !~ '^pg_' " + "ORDER BY 2", role_catalog, role_catalog, role_catalog); + else if (server_version >= 90600) printfPQExpBuffer(buf, "SELECT oid, rolname, rolsuper, rolinherit, " "rolcreaterole, rolcreatedb, " diff --git a/src/common/scram-common.c b/src/common/scram-common.c index ef997ef684..df48f74f88 100644 --- a/src/common/scram-common.c +++ b/src/common/scram-common.c @@ -185,7 +185,7 @@ scram_ServerKey(const uint8 *salted_password, /* - * Construct a SCRAM secret, for storing in pg_authid.rolpassword. + * Construct a SCRAM secret, for storing in pg_auth_password.password. * * The password should already have been processed with SASLprep, if necessary! * diff --git a/src/include/catalog/pg_auth_password.h b/src/include/catalog/pg_auth_password.h new file mode 100644 index 0000000000..a1e0741d63 --- /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,4551,AuthPasswordRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(4552,AuthPasswordRelation_Rowtype_Id) BKI_SCHEMA_MACRO +{ + Oid roleid BKI_LOOKUP(pg_authid); /* ID of a role */ + NameData name; /* name of password for multiple password support */ + +#ifdef CATALOG_VARLEN /* variable-length fields start here */ + text password BKI_FORCE_NOT_NULL; /* password */ + timestamptz expiration BKI_FORCE_NULL; /* password expiration time, if any */ +#endif +} FormData_pg_auth_password; + +/* ---------------- + * Form_pg_auth_password corresponds to a pointer to a tuple with + * the format of pg_auth_password relation. + * ---------------- + */ + +typedef FormData_pg_auth_password *Form_pg_auth_password; + +DECLARE_TOAST_WITH_MACRO(pg_auth_password, 9122, 9133, PgAuthPasswordToastTable, PgAuthPasswordToastIndex); + +DECLARE_UNIQUE_INDEX_PKEY(pg_auth_password_roleoid_index, 9134, AuthPasswordRoleOidIndexId, pg_auth_password, btree(roleid oid_ops, name name_ops)); + +#endif /* PG_AUTH_PASSWORD_H */ diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat index 6b4a0aaaad..1dbf5ca0e0 100644 --- a/src/include/catalog/pg_authid.dat +++ b/src/include/catalog/pg_authid.dat @@ -23,76 +23,76 @@ rolname => 'POSTGRES', rolsuper => 't', rolinherit => 't', rolcreaterole => 't', rolcreatedb => 't', rolcanlogin => 't', rolreplication => 't', rolbypassrls => 't', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '6171', oid_symbol => 'ROLE_PG_DATABASE_OWNER', rolname => 'pg_database_owner', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '6181', oid_symbol => 'ROLE_PG_READ_ALL_DATA', rolname => 'pg_read_all_data', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '6182', oid_symbol => 'ROLE_PG_WRITE_ALL_DATA', rolname => 'pg_write_all_data', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '3373', oid_symbol => 'ROLE_PG_MONITOR', rolname => 'pg_monitor', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '3374', oid_symbol => 'ROLE_PG_READ_ALL_SETTINGS', rolname => 'pg_read_all_settings', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '3375', oid_symbol => 'ROLE_PG_READ_ALL_STATS', rolname => 'pg_read_all_stats', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '3377', oid_symbol => 'ROLE_PG_STAT_SCAN_TABLES', rolname => 'pg_stat_scan_tables', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '4569', oid_symbol => 'ROLE_PG_READ_SERVER_FILES', rolname => 'pg_read_server_files', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '4570', oid_symbol => 'ROLE_PG_WRITE_SERVER_FILES', rolname => 'pg_write_server_files', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '4571', oid_symbol => 'ROLE_PG_EXECUTE_SERVER_PROGRAM', rolname => 'pg_execute_server_program', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '4200', oid_symbol => 'ROLE_PG_SIGNAL_BACKEND', rolname => 'pg_signal_backend', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '4544', oid_symbol => 'ROLE_PG_CHECKPOINT', rolname => 'pg_checkpoint', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '4550', oid_symbol => 'ROLE_PG_USE_RESERVED_CONNECTIONS', rolname => 'pg_use_reserved_connections', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, { oid => '6304', oid_symbol => 'ROLE_PG_CREATE_SUBSCRIPTION', rolname => 'pg_create_subscription', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', - rolpassword => '_null_', rolvaliduntil => '_null_' }, + rolvaliduntil => '_null_' }, ] diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h index 0e7ddc56ea..56190512cf 100644 --- a/src/include/catalog/pg_authid.h +++ b/src/include/catalog/pg_authid.h @@ -42,9 +42,8 @@ CATALOG(pg_authid,1260,AuthIdRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(284 int32 rolconnlimit; /* max connections allowed (-1=no limit) */ /* remaining fields may be null; use heap_getattr to read them! */ -#ifdef CATALOG_VARLEN /* variable-length fields start here */ - text rolpassword; /* password, if any */ - timestamptz rolvaliduntil; /* password expiration time, if any */ +#ifdef CATALOG_VARLEN + timestamptz rolvaliduntil BKI_FORCE_NULL; /* role expiration time, if any */ #endif } FormData_pg_authid; @@ -55,8 +54,6 @@ CATALOG(pg_authid,1260,AuthIdRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(284 */ typedef FormData_pg_authid *Form_pg_authid; -DECLARE_TOAST_WITH_MACRO(pg_authid, 4175, 4176, PgAuthidToastTable, PgAuthidToastIndex); - DECLARE_UNIQUE_INDEX(pg_authid_rolname_index, 2676, AuthIdRolnameIndexId, pg_authid, btree(rolname name_ops)); DECLARE_UNIQUE_INDEX_PKEY(pg_authid_oid_index, 2677, AuthIdOidIndexId, pg_authid, btree(oid oid_ops)); diff --git a/src/include/commands/user.h b/src/include/commands/user.h index 97dcb93791..75bb58eea8 100644 --- a/src/include/commands/user.h +++ b/src/include/commands/user.h @@ -21,6 +21,9 @@ extern PGDLLIMPORT int Password_encryption; /* values from enum PasswordType */ extern PGDLLIMPORT char *createrole_self_grant; +/* GUC. system-wide password validity duration */ +extern Interval *default_password_duration; + /* Hook to check passwords in CreateRole() and AlterRole() */ typedef void (*check_password_hook_type) (const char *username, const char *shadow_pass, PasswordType password_type, Datum validuntil_time, bool validuntil_null); diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h index ddcd27469a..2b66fd909a 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 @@ -35,7 +35,7 @@ extern PasswordType get_password_type(const char *shadow_pass); extern char *encrypt_password(PasswordType target_type, const char *role, const char *password); -extern char *get_role_password(const char *role, const char **logdetail); +extern char **get_role_passwords(const char *role, const char **logdetail, int *num); extern int md5_crypt_verify(const char *role, const char *shadow_pass, const char *client_pass, const char *md5_salt, diff --git a/src/include/libpq/sasl.h b/src/include/libpq/sasl.h index 7a1b1ed0a0..12c5c9602b 100644 --- a/src/include/libpq/sasl.h +++ b/src/include/libpq/sasl.h @@ -77,7 +77,7 @@ typedef struct pg_be_sasl_mech * disclosing valid user names. *--------- */ - void *(*init) (Port *port, const char *mech, const char *shadow_pass); + void *(*init) (Port *port, const char *mech, const char **secrets, const int num_secrets); /*--------- * exchange() @@ -131,6 +131,6 @@ typedef struct pg_be_sasl_mech /* Common implementation for auth.c */ extern int CheckSASLAuth(const pg_be_sasl_mech *mech, Port *port, - char *shadow_pass, const char **logdetail); + const char **passwords, int num_passwords, const char **logdetail); #endif /* PG_SASL_H */ diff --git a/src/include/libpq/scram.h b/src/include/libpq/scram.h index 310bc36517..07d9cc3990 100644 --- a/src/include/libpq/scram.h +++ b/src/include/libpq/scram.h @@ -25,7 +25,7 @@ extern PGDLLIMPORT int scram_sha_256_iterations; extern PGDLLIMPORT const pg_be_sasl_mech pg_be_scram_mech; /* Routines to handle and check SCRAM-SHA-256 secret */ -extern char *pg_be_scram_build_secret(const char *password); +extern char *pg_be_scram_build_secret(const char *password, const char *salt); extern bool parse_scram_secret(const char *secret, int *iterations, pg_cryptohash_type *hash_type, diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 5984dcfa4b..af0bba6244 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -160,6 +160,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) @@ -325,6 +326,7 @@ PG_KEYWORD("parser", PARSER, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("passname", PASSNAME, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index f04b99e3b9..554c3c06b3 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -161,4 +161,8 @@ extern void assign_wal_consistency_checking(const char *newval, void *extra); extern bool check_wal_segment_size(int *newval, void **extra, GucSource source); extern void assign_xlog_sync_method(int new_sync_method, void *extra); +extern bool check_password_duration(char **newval, void **extra, GucSource source); +extern void assign_password_duration(const char *newval, void *extra); +extern const char *show_password_duration(void); + #endif /* GUC_HOOKS_H */ diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h index 67ea6e4945..6015f53d73 100644 --- a/src/include/utils/syscache.h +++ b/src/include/utils/syscache.h @@ -43,6 +43,7 @@ enum SysCacheIdentifier AUTHMEMROLEMEM, AUTHNAME, AUTHOID, + AUTHPASSWORDNAME, CASTSOURCETARGET, CLAAMNAMENSP, CLAOID, diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index acfd9d1f4f..3ba23628a1 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2521,10 +2521,10 @@ REINDEX TABLE CONCURRENTLY pg_class; -- no catalog relation ERROR: cannot reindex system catalogs concurrently REINDEX INDEX CONCURRENTLY pg_class_oid_index; -- no catalog index ERROR: cannot reindex system catalogs concurrently --- These are the toast table and index of pg_authid. -REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1260; -- no catalog toast table +-- These are the toast table and index of pg_database. +REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1262; -- no catalog toast table ERROR: cannot reindex system catalogs concurrently -REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -- no catalog toast index +REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1262_index; -- no catalog toast index ERROR: cannot reindex system catalogs concurrently REINDEX SYSTEM CONCURRENTLY postgres; -- not allowed for SYSTEM ERROR: cannot reindex system catalogs concurrently @@ -2830,10 +2830,10 @@ ERROR: must be owner of schema schema_to_reindex RESET ROLE; GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser; SET SESSION ROLE regress_reindexuser; -REINDEX TABLE pg_toast.pg_toast_1260; -ERROR: must be owner of table pg_toast_1260 -REINDEX INDEX pg_toast.pg_toast_1260_index; -ERROR: must be owner of index pg_toast_1260_index +REINDEX TABLE pg_toast.pg_toast_1262; +ERROR: must be owner of table pg_toast_1262 +REINDEX INDEX pg_toast.pg_toast_1262_index; +ERROR: must be owner of index pg_toast_1262_index -- Clean up RESET ROLE; REVOKE USAGE ON SCHEMA pg_toast FROM regress_reindexuser; diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out index 215eb899be..b69a2a72a7 100644 --- a/src/test/regress/expected/oidjoins.out +++ b/src/test/regress/expected/oidjoins.out @@ -197,6 +197,7 @@ NOTICE: checking pg_tablespace {spcowner} => pg_authid {oid} NOTICE: checking pg_auth_members {roleid} => pg_authid {oid} NOTICE: checking pg_auth_members {member} => pg_authid {oid} NOTICE: checking pg_auth_members {grantor} => pg_authid {oid} +NOTICE: checking pg_auth_password {roleid} => pg_authid {oid} NOTICE: checking pg_shdepend {dbid} => pg_database {oid} NOTICE: checking pg_shdepend {classid} => pg_class {oid} NOTICE: checking pg_shdepend {refclassid} => pg_class {oid} diff --git a/src/test/regress/expected/password.out b/src/test/regress/expected/password.out index 8475231735..0d44ac3915 100644 --- a/src/test/regress/expected/password.out +++ b/src/test/regress/expected/password.out @@ -24,10 +24,12 @@ CREATE ROLE regress_passwd4 PASSWORD NULL; -- -- Since the salt is random, the exact value stored will be different on every test -- run. Use a regular expression to mask the changing parts. -SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:$:') as rolpassword_masked +SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:$:') as rolpassword_masked FROM pg_authid + LEFT JOIN pg_auth_password p + ON pg_authid.oid = p.roleid WHERE rolname LIKE 'regress_passwd%' - ORDER BY rolname, rolpassword; + ORDER BY rolname, password; rolname | rolpassword_masked -----------------+--------------------------------------------------- regress_passwd1 | md5783277baca28003b33453252be4dbb34 @@ -40,12 +42,14 @@ SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+ ALTER ROLE regress_passwd2 RENAME TO regress_passwd2_new; NOTICE: MD5 password cleared because of role rename -- md5 entry should have been removed -SELECT rolname, rolpassword +SELECT rolname, password FROM pg_authid + LEFT JOIN pg_auth_password p + ON pg_authid.oid = p.roleid WHERE rolname LIKE 'regress_passwd2_new' - ORDER BY rolname, rolpassword; - rolname | rolpassword ----------------------+------------- + ORDER BY rolname, password; + rolname | password +---------------------+---------- regress_passwd2_new | (1 row) @@ -75,10 +79,12 @@ CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz'; -- Changing the SCRAM iteration count SET scram_iterations = 1024; CREATE ROLE regress_passwd9 PASSWORD 'alterediterationcount'; -SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:$:') as rolpassword_masked +SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:$:') as rolpassword_masked FROM pg_authid + LEFT JOIN pg_auth_password p + ON pg_authid.oid = p.roleid WHERE rolname LIKE 'regress_passwd%' - ORDER BY rolname, rolpassword; + ORDER BY rolname, password; rolname | rolpassword_masked -----------------+--------------------------------------------------- regress_passwd1 | md5cd3578025fe2c3d7ed1b9a9b26238b70 @@ -99,9 +105,11 @@ ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659e0a'; NOTICE: empty string is not a valid password, clearing password ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4='; NOTICE: empty string is not a valid password, clearing password -SELECT rolpassword FROM pg_authid WHERE rolname='regress_passwd_empty'; - rolpassword -------------- +SELECT password FROM pg_authid +LEFT JOIN pg_auth_password p +ON pg_authid.oid = p.roleid WHERE rolname='regress_passwd_empty'; + password +---------- (1 row) @@ -114,8 +122,10 @@ CREATE ROLE regress_passwd_sha_len1 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941 CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA='; -- Check that the invalid secrets were re-hashed. A re-hashed secret -- should not contain the original salt. -SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed +SELECT rolname, password not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed FROM pg_authid + LEFT JOIN pg_auth_password p + ON pg_authid.oid = p.roleid WHERE rolname LIKE 'regress_passwd_sha_len%' ORDER BY rolname; rolname | is_rolpassword_rehashed @@ -139,11 +149,13 @@ DROP ROLE regress_passwd_sha_len0; DROP ROLE regress_passwd_sha_len1; DROP ROLE regress_passwd_sha_len2; -- all entries should have been removed -SELECT rolname, rolpassword +SELECT rolname, password FROM pg_authid + LEFT JOIN pg_auth_password p + ON pg_authid.oid = p.roleid WHERE rolname LIKE 'regress_passwd%' - ORDER BY rolname, rolpassword; - rolname | rolpassword ----------+------------- + ORDER BY rolname, password; + rolname | password +---------+---------- (0 rows) diff --git a/src/test/regress/expected/roleattributes.out b/src/test/regress/expected/roleattributes.out index 5e6969b173..18a9dacadd 100644 --- a/src/test/regress/expected/roleattributes.out +++ b/src/test/regress/expected/roleattributes.out @@ -1,233 +1,233 @@ -- default for superuser is false CREATE ROLE regress_test_def_superuser; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_def_superuser | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_def_superuser | f | t | f | f | f | f | f | -1 | (1 row) CREATE ROLE regress_test_superuser WITH SUPERUSER; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_superuser | t | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_superuser | t | t | f | f | f | f | f | -1 | (1 row) ALTER ROLE regress_test_superuser WITH NOSUPERUSER; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_superuser | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_superuser | f | t | f | f | f | f | f | -1 | (1 row) ALTER ROLE regress_test_superuser WITH SUPERUSER; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_superuser | t | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_superuser | t | t | f | f | f | f | f | -1 | (1 row) -- default for inherit is true CREATE ROLE regress_test_def_inherit; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_def_inherit | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_def_inherit | f | t | f | f | f | f | f | -1 | (1 row) CREATE ROLE regress_test_inherit WITH NOINHERIT; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_inherit | f | f | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_inherit | f | f | f | f | f | f | f | -1 | (1 row) ALTER ROLE regress_test_inherit WITH INHERIT; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_inherit | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_inherit | f | t | f | f | f | f | f | -1 | (1 row) ALTER ROLE regress_test_inherit WITH NOINHERIT; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_inherit | f | f | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_inherit | f | f | f | f | f | f | f | -1 | (1 row) -- default for create role is false CREATE ROLE regress_test_def_createrole; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_def_createrole | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_def_createrole | f | t | f | f | f | f | f | -1 | (1 row) CREATE ROLE regress_test_createrole WITH CREATEROLE; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil --------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_createrole | f | t | t | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_createrole | f | t | t | f | f | f | f | -1 | (1 row) ALTER ROLE regress_test_createrole WITH NOCREATEROLE; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil --------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_createrole | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_createrole | f | t | f | f | f | f | f | -1 | (1 row) ALTER ROLE regress_test_createrole WITH CREATEROLE; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil --------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_createrole | f | t | t | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_createrole | f | t | t | f | f | f | f | -1 | (1 row) -- default for create database is false CREATE ROLE regress_test_def_createdb; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_def_createdb | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_def_createdb | f | t | f | f | f | f | f | -1 | (1 row) CREATE ROLE regress_test_createdb WITH CREATEDB; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_createdb | f | t | f | t | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_createdb | f | t | f | t | f | f | f | -1 | (1 row) ALTER ROLE regress_test_createdb WITH NOCREATEDB; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_createdb | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_createdb | f | t | f | f | f | f | f | -1 | (1 row) ALTER ROLE regress_test_createdb WITH CREATEDB; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_createdb | f | t | f | t | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_createdb | f | t | f | t | f | f | f | -1 | (1 row) -- default for can login is false for role CREATE ROLE regress_test_def_role_canlogin; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ---------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_def_role_canlogin | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +--------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_def_role_canlogin | f | t | f | f | f | f | f | -1 | (1 row) CREATE ROLE regress_test_role_canlogin WITH LOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_role_canlogin | f | t | f | f | t | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_role_canlogin | f | t | f | f | t | f | f | -1 | (1 row) ALTER ROLE regress_test_role_canlogin WITH NOLOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_role_canlogin | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_role_canlogin | f | t | f | f | f | f | f | -1 | (1 row) ALTER ROLE regress_test_role_canlogin WITH LOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_role_canlogin | f | t | f | f | t | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_role_canlogin | f | t | f | f | t | f | f | -1 | (1 row) -- default for can login is true for user CREATE USER regress_test_def_user_canlogin; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ---------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_def_user_canlogin | f | t | f | f | t | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +--------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_def_user_canlogin | f | t | f | f | t | f | f | -1 | (1 row) CREATE USER regress_test_user_canlogin WITH NOLOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_user_canlogin | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_user_canlogin | f | t | f | f | f | f | f | -1 | (1 row) ALTER USER regress_test_user_canlogin WITH LOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_user_canlogin | f | t | f | f | t | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_user_canlogin | f | t | f | f | t | f | f | -1 | (1 row) ALTER USER regress_test_user_canlogin WITH NOLOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_user_canlogin | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_user_canlogin | f | t | f | f | f | f | f | -1 | (1 row) -- default for replication is false CREATE ROLE regress_test_def_replication; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_def_replication | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_def_replication | f | t | f | f | f | f | f | -1 | (1 row) CREATE ROLE regress_test_replication WITH REPLICATION; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_replication | f | t | f | f | f | t | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_replication | f | t | f | f | f | t | f | -1 | (1 row) ALTER ROLE regress_test_replication WITH NOREPLICATION; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_replication | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_replication | f | t | f | f | f | f | f | -1 | (1 row) ALTER ROLE regress_test_replication WITH REPLICATION; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_replication | f | t | f | f | f | t | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_replication | f | t | f | f | f | t | f | -1 | (1 row) -- default for bypassrls is false CREATE ROLE regress_test_def_bypassrls; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_def_bypassrls | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_def_bypassrls | f | t | f | f | f | f | f | -1 | (1 row) CREATE ROLE regress_test_bypassrls WITH BYPASSRLS; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_bypassrls | f | t | f | f | f | f | t | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_bypassrls | f | t | f | f | f | f | t | -1 | (1 row) ALTER ROLE regress_test_bypassrls WITH NOBYPASSRLS; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_bypassrls | f | t | f | f | f | f | f | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_bypassrls | f | t | f | f | f | f | f | -1 | (1 row) ALTER ROLE regress_test_bypassrls WITH BYPASSRLS; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; - rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- - regress_test_bypassrls | f | t | f | f | f | f | t | -1 | | +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolvaliduntil +------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------- + regress_test_bypassrls | f | t | f | f | f | f | t | -1 | (1 row) -- clean up roles diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 5058be5411..ddbf56b940 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1727,11 +1727,12 @@ pg_shadow| SELECT pg_authid.rolname AS usename, pg_authid.rolsuper AS usesuper, pg_authid.rolreplication AS userepl, pg_authid.rolbypassrls AS usebypassrls, - pg_authid.rolpassword AS passwd, + p.password AS passwd, pg_authid.rolvaliduntil AS valuntil, s.setconfig AS useconfig - FROM (pg_authid + FROM ((pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) + LEFT JOIN pg_auth_password p ON (p.roleid = pg_authid.oid)) WHERE pg_authid.rolcanlogin; pg_shmem_allocations| SELECT name, off, diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out index 9aabb85349..dd535d41a3 100644 --- a/src/test/regress/expected/tablespace.out +++ b/src/test/regress/expected/tablespace.out @@ -51,13 +51,13 @@ ERROR: cannot move system relation "pg_authid_rolname_index" REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_authid; ERROR: cannot reindex system catalogs concurrently -- toast relations, fail -REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1260_index; -ERROR: cannot move system relation "pg_toast_1260_index" -REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; +REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1262_index; +ERROR: cannot move system relation "pg_toast_1262_index" +REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1262_index; ERROR: cannot reindex system catalogs concurrently -REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1260; -ERROR: cannot move system relation "pg_toast_1260_index" -REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1260; +REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1262; +ERROR: cannot move system relation "pg_toast_1262_index" +REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1262; ERROR: cannot reindex system catalogs concurrently -- system catalog, fail REINDEX (TABLESPACE pg_global) TABLE pg_authid; diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index d49ce9f300..d2c63e424c 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -1074,9 +1074,9 @@ REINDEX TABLE CONCURRENTLY concur_reindex_tab; COMMIT; REINDEX TABLE CONCURRENTLY pg_class; -- no catalog relation REINDEX INDEX CONCURRENTLY pg_class_oid_index; -- no catalog index --- These are the toast table and index of pg_authid. -REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1260; -- no catalog toast table -REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -- no catalog toast index +-- These are the toast table and index of pg_database. +REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1262; -- no catalog toast table +REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1262_index; -- no catalog toast index REINDEX SYSTEM CONCURRENTLY postgres; -- not allowed for SYSTEM REINDEX (CONCURRENTLY) SYSTEM postgres; -- ditto REINDEX (CONCURRENTLY) SYSTEM; -- ditto @@ -1253,8 +1253,8 @@ REINDEX SCHEMA schema_to_reindex; RESET ROLE; GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser; SET SESSION ROLE regress_reindexuser; -REINDEX TABLE pg_toast.pg_toast_1260; -REINDEX INDEX pg_toast.pg_toast_1260_index; +REINDEX TABLE pg_toast.pg_toast_1262; +REINDEX INDEX pg_toast.pg_toast_1262_index; -- Clean up RESET ROLE; diff --git a/src/test/regress/sql/password.sql b/src/test/regress/sql/password.sql index 53e86b0b6c..b4b6ab9847 100644 --- a/src/test/regress/sql/password.sql +++ b/src/test/regress/sql/password.sql @@ -23,18 +23,22 @@ CREATE ROLE regress_passwd4 PASSWORD NULL; -- -- Since the salt is random, the exact value stored will be different on every test -- run. Use a regular expression to mask the changing parts. -SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:$:') as rolpassword_masked +SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:$:') as rolpassword_masked FROM pg_authid + LEFT JOIN pg_auth_password p + ON pg_authid.oid = p.roleid WHERE rolname LIKE 'regress_passwd%' - ORDER BY rolname, rolpassword; + ORDER BY rolname, password; -- Rename a role ALTER ROLE regress_passwd2 RENAME TO regress_passwd2_new; -- md5 entry should have been removed -SELECT rolname, rolpassword +SELECT rolname, password FROM pg_authid + LEFT JOIN pg_auth_password p + ON pg_authid.oid = p.roleid WHERE rolname LIKE 'regress_passwd2_new' - ORDER BY rolname, rolpassword; + ORDER BY rolname, password; ALTER ROLE regress_passwd2_new RENAME TO regress_passwd2; -- Change passwords with ALTER USER. With plaintext or already-encrypted @@ -67,16 +71,20 @@ CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz'; SET scram_iterations = 1024; CREATE ROLE regress_passwd9 PASSWORD 'alterediterationcount'; -SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:$:') as rolpassword_masked +SELECT rolname, regexp_replace(password, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:$:') as rolpassword_masked FROM pg_authid + LEFT JOIN pg_auth_password p + ON pg_authid.oid = p.roleid WHERE rolname LIKE 'regress_passwd%' - ORDER BY rolname, rolpassword; + ORDER BY rolname, password; -- An empty password is not allowed, in any form CREATE ROLE regress_passwd_empty PASSWORD ''; ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659e0a'; ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4='; -SELECT rolpassword FROM pg_authid WHERE rolname='regress_passwd_empty'; +SELECT password FROM pg_authid +LEFT JOIN pg_auth_password p +ON pg_authid.oid = p.roleid WHERE rolname='regress_passwd_empty'; -- Test with invalid stored and server keys. -- @@ -88,8 +96,10 @@ CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941 -- Check that the invalid secrets were re-hashed. A re-hashed secret -- should not contain the original salt. -SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed +SELECT rolname, password not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed FROM pg_authid + LEFT JOIN pg_auth_password p + ON pg_authid.oid = p.roleid WHERE rolname LIKE 'regress_passwd_sha_len%' ORDER BY rolname; @@ -108,7 +118,9 @@ DROP ROLE regress_passwd_sha_len1; DROP ROLE regress_passwd_sha_len2; -- all entries should have been removed -SELECT rolname, rolpassword +SELECT rolname, password FROM pg_authid + LEFT JOIN pg_auth_password p + ON pg_authid.oid = p.roleid WHERE rolname LIKE 'regress_passwd%' - ORDER BY rolname, rolpassword; + ORDER BY rolname, password; diff --git a/src/test/regress/sql/roleattributes.sql b/src/test/regress/sql/roleattributes.sql index c961b2d730..09505c6a3b 100644 --- a/src/test/regress/sql/roleattributes.sql +++ b/src/test/regress/sql/roleattributes.sql @@ -1,83 +1,83 @@ -- default for superuser is false CREATE ROLE regress_test_def_superuser; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser'; CREATE ROLE regress_test_superuser WITH SUPERUSER; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; ALTER ROLE regress_test_superuser WITH NOSUPERUSER; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; ALTER ROLE regress_test_superuser WITH SUPERUSER; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; -- default for inherit is true CREATE ROLE regress_test_def_inherit; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit'; CREATE ROLE regress_test_inherit WITH NOINHERIT; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; ALTER ROLE regress_test_inherit WITH INHERIT; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; ALTER ROLE regress_test_inherit WITH NOINHERIT; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; -- default for create role is false CREATE ROLE regress_test_def_createrole; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole'; CREATE ROLE regress_test_createrole WITH CREATEROLE; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; ALTER ROLE regress_test_createrole WITH NOCREATEROLE; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; ALTER ROLE regress_test_createrole WITH CREATEROLE; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; -- default for create database is false CREATE ROLE regress_test_def_createdb; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb'; CREATE ROLE regress_test_createdb WITH CREATEDB; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; ALTER ROLE regress_test_createdb WITH NOCREATEDB; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; ALTER ROLE regress_test_createdb WITH CREATEDB; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; -- default for can login is false for role CREATE ROLE regress_test_def_role_canlogin; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin'; CREATE ROLE regress_test_role_canlogin WITH LOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; ALTER ROLE regress_test_role_canlogin WITH NOLOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; ALTER ROLE regress_test_role_canlogin WITH LOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; -- default for can login is true for user CREATE USER regress_test_def_user_canlogin; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin'; CREATE USER regress_test_user_canlogin WITH NOLOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; ALTER USER regress_test_user_canlogin WITH LOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; ALTER USER regress_test_user_canlogin WITH NOLOGIN; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; -- default for replication is false CREATE ROLE regress_test_def_replication; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication'; CREATE ROLE regress_test_replication WITH REPLICATION; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; ALTER ROLE regress_test_replication WITH NOREPLICATION; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; ALTER ROLE regress_test_replication WITH REPLICATION; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; -- default for bypassrls is false CREATE ROLE regress_test_def_bypassrls; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls'; CREATE ROLE regress_test_bypassrls WITH BYPASSRLS; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; ALTER ROLE regress_test_bypassrls WITH NOBYPASSRLS; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; ALTER ROLE regress_test_bypassrls WITH BYPASSRLS; -SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; -- clean up roles DROP ROLE regress_test_def_superuser; diff --git a/src/test/regress/sql/tablespace.sql b/src/test/regress/sql/tablespace.sql index d274d9615e..c8b83788f0 100644 --- a/src/test/regress/sql/tablespace.sql +++ b/src/test/regress/sql/tablespace.sql @@ -40,10 +40,10 @@ REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am; REINDEX (TABLESPACE regress_tblspace) TABLE pg_authid; REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_authid; -- toast relations, fail -REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1260_index; -REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1260; -REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1260; +REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1262_index; +REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1262_index; +REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1262; +REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1262; -- system catalog, fail REINDEX (TABLESPACE pg_global) TABLE pg_authid; REINDEX (TABLESPACE pg_global) TABLE CONCURRENTLY pg_authid;