From 82d235b39b32ca0cd0b94d47a54ee6806645a365 Mon Sep 17 00:00:00 2001 From: Mark Dilger Date: Fri, 28 Jan 2022 07:57:57 -0800 Subject: [PATCH v8] Adding admin options for role attributes When creating roles, attributes such as BYPASSRLS can be optionally specified WITH ADMIN OPTION or WITHOUT ADMIN OPTION. If these optional clauses are unspecified, they all default to WITHOUT unless the role being created is given CREATEROLE, in which case they default to WITHOUT for SUPERUSER, REPLICATION, and BYPASSRLS and true for all others. This preserves backwards compatible behavior. The CREATEROLE attribute no longer makes up for lacking the ADMIN option on a role. The creator of a role only has the ADMIN-like right to grant other roles into the new role during the creation statement itself. After that, the creator may only do so if the creator has ADMIN on the role. Note that creators may add themselves to the list of ADMINs on the new role during creation time. SUPERUSER can still only be granted by superusers. --- doc/src/sgml/ref/create_role.sgml | 50 ++-- src/backend/catalog/aclchk.c | 179 ++++++++++++-- src/backend/commands/user.c | 278 +++++++++++++++++----- src/backend/parser/gram.y | 161 ++++++++++--- src/include/catalog/pg_authid.dat | 52 +++- src/include/catalog/pg_authid.h | 10 + src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 11 +- src/include/utils/acl.h | 12 + src/test/regress/expected/create_role.out | 188 ++++++++++++++- src/test/regress/expected/privileges.out | 4 + src/test/regress/sql/create_role.sql | 153 +++++++++++- src/test/regress/sql/privileges.sql | 3 + src/tools/pgindent/typedefs.list | 1 + 14 files changed, 936 insertions(+), 167 deletions(-) diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index b6a4ea1f72..7163779e0a 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -26,15 +26,22 @@ CREATE ROLE name [ [ WITH ] where option can be: SUPERUSER | NOSUPERUSER - | CREATEDB | NOCREATEDB - | CREATEROLE | NOCREATEROLE - | INHERIT | NOINHERIT - | LOGIN | NOLOGIN - | REPLICATION | NOREPLICATION - | BYPASSRLS | NOBYPASSRLS - | CONNECTION LIMIT connlimit - | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL - | VALID UNTIL 'timestamp' + | INHERIT [ { WITH | WITHOUT } GRANT OPTION ] + | NOINHERIT [ { WITH | WITHOUT } GRANT OPTION ] + | CREATEDB [ { WITH | WITHOUT } GRANT OPTION ] + | NOCREATEDB [ { WITH | WITHOUT } GRANT OPTION ] + | CREATEROLE [ { WITH | WITHOUT } GRANT OPTION ] + | NOCREATEROLE [ { WITH | WITHOUT } GRANT OPTION ] + | LOGIN [ { WITH | WITHOUT } GRANT OPTION ] + | NOLOGIN [ { WITH | WITHOUT } GRANT OPTION ] + | REPLICATION [ { WITH | WITHOUT } GRANT OPTION ] + | NOREPLICATION [ { WITH | WITHOUT } GRANT OPTION ] + | BYPASSRLS [ { WITH | WITHOUT } GRANT OPTION ] + | NOBYPASSRLS [ { WITH | WITHOUT } GRANT OPTION ] + | CONNECTION LIMIT [ connlimit | NONE ] [ { WITH | WITHOUT } GRANT OPTION ] + | [ ENCRYPTED ] PASSWORD 'password' [ { WITH | WITHOUT } GRANT OPTION ] + | PASSWORD NULL [ { WITH | WITHOUT } GRANT OPTION ] + | VALID [ UNTIL 'timestamp' | ALWAYS ] [ { WITH | WITHOUT } GRANT OPTION ] | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] @@ -356,6 +363,18 @@ in sync when changing the above synopsis! REVOKE. + + Some parameters allow the WITH ADMIN OPTION or + WITHOUT ADMIN OPTION clause to be specified. For roles + with the CREATEROLE attribute, these clauses govern + whether new roles may be created with the attribute. If not given, for + reasons of backwards compatibility, WITHOUT ADMIN OPTION + is the default for REPLICATION and + BYPASSRLS, but WITH ADMIN OPTION is + the default for CREATEDB, CREATEROLE, + and LOGIN. + + The VALID UNTIL clause defines an expiration time for a password only, not for the role per se. In @@ -383,19 +402,6 @@ in sync when changing the above synopsis! specified in the SQL standard. - - Be careful with the CREATEROLE privilege. There is no concept of - inheritance for the privileges of a CREATEROLE-role. That - means that even if a role does not have a certain privilege but is allowed - to create other roles, it can easily create another role with different - privileges than its own (except for creating roles with superuser - privileges). For example, if the role user has the - CREATEROLE privilege but not the CREATEDB privilege, - nonetheless it can create a new role with the CREATEDB - privilege. Therefore, regard roles that have the CREATEROLE - privilege as almost-superuser-roles. - - PostgreSQL includes a program that has diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index 1dd03a8e51..c66f545f36 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -5430,6 +5430,91 @@ pg_statistics_object_ownercheck(Oid stat_oid, Oid roleid) return has_privs_of_role(roleid, ownerId); } +typedef enum ROLPRIV +{ + CREATEROLE, + CREATEDB, + CANLOGIN, + REPLICATION, + BYPASSRLS, + INHERIT, + CONNLIMIT, + VALIDUNTIL, + PASSWORD +} ROLPRIV; + +static bool +has_privilege(Oid roleid, ROLPRIV priv, bool self, bool grant) +{ + bool result = false; + HeapTuple utup; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); + if (HeapTupleIsValid(utup)) + { + Form_pg_authid uform = (Form_pg_authid) GETSTRUCT(utup); + + result = true; + switch (priv) + { + case CREATEROLE: + if (self && !uform->rolcreaterole) + result = false; + if (grant && !uform->admcreaterole) + result = false; + break; + case CREATEDB: + if (self && !uform->rolcreatedb) + result = false; + if (grant && !uform->admcreatedb) + result = false; + break; + case CANLOGIN: + if (self && !uform->rolcanlogin) + result = false; + if (grant && !uform->admcanlogin) + result = false; + break; + case REPLICATION: + if (self && !uform->rolreplication) + result = false; + if (grant && !uform->admreplication) + result = false; + break; + case BYPASSRLS: + if (self && !uform->rolbypassrls) + result = false; + if (grant && !uform->admbypassrls) + result = false; + break; + case INHERIT: + if (grant && !uform->adminherit) + result = false; + break; + case CONNLIMIT: + if (grant && !uform->admconnlimit) + result = false; + break; + case VALIDUNTIL: + if (grant && !uform->admvaliduntil) + result = false; + break; + case PASSWORD: + if (grant && !uform->admpassword) + result = false; + break; + + /* Intentionally no default here */ + } + ReleaseSysCache(utup); + } + return result; +} + /* * Check whether specified role has CREATEROLE privilege (or is a superuser) * @@ -5444,39 +5529,85 @@ pg_statistics_object_ownercheck(Oid stat_oid, Oid roleid) bool has_createrole_privilege(Oid roleid) { - bool result = false; - HeapTuple utup; + return has_privilege(roleid, CREATEROLE, true, false); +} - /* Superusers bypass all permission checking. */ - if (superuser_arg(roleid)) - return true; +bool +has_createdb_privilege(Oid roleid) +{ + return has_privilege(roleid, CREATEDB, true, false); +} - utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); - if (HeapTupleIsValid(utup)) - { - result = ((Form_pg_authid) GETSTRUCT(utup))->rolcreaterole; - ReleaseSysCache(utup); - } - return result; +bool +has_canlogin_privilege(Oid roleid) +{ + return has_privilege(roleid, CANLOGIN, true, false); +} + +bool +has_replication_privilege(Oid roleid) +{ + return has_privilege(roleid, REPLICATION, true, false); } bool has_bypassrls_privilege(Oid roleid) { - bool result = false; - HeapTuple utup; + return has_privilege(roleid, BYPASSRLS, true, false); +} - /* Superusers bypass all permission checking. */ - if (superuser_arg(roleid)) - return true; +bool +may_admin_createrole_privilege(Oid roleid) +{ + return has_privilege(roleid, CREATEROLE, false, true); +} - utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); - if (HeapTupleIsValid(utup)) - { - result = ((Form_pg_authid) GETSTRUCT(utup))->rolbypassrls; - ReleaseSysCache(utup); - } - return result; +bool +may_admin_createdb_privilege(Oid roleid) +{ + return has_privilege(roleid, CREATEDB, false, true); +} + +bool +may_admin_canlogin_privilege(Oid roleid) +{ + return has_privilege(roleid, CANLOGIN, false, true); +} + +bool +may_admin_replication_privilege(Oid roleid) +{ + return has_privilege(roleid, REPLICATION, false, true); +} + +bool +may_admin_bypassrls_privilege(Oid roleid) +{ + return has_privilege(roleid, BYPASSRLS, false, true); +} + +bool +may_admin_inherit_privilege(Oid roleid) +{ + return has_privilege(roleid, INHERIT, false, true); +} + +bool +may_admin_connlimit_privilege(Oid roleid) +{ + return has_privilege(roleid, CONNLIMIT, false, true); +} + +bool +may_admin_validuntil_privilege(Oid roleid) +{ + return has_privilege(roleid, VALIDUNTIL, false, true); +} + +bool +may_admin_password_privilege(Oid roleid) +{ + return has_privilege(roleid, PASSWORD, false, true); } /* diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index f9d3c1246b..501613a840 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -51,7 +51,7 @@ check_password_hook_type check_password_hook = NULL; static void AddRoleMems(const char *rolename, Oid roleid, List *memberSpecs, List *memberIds, - Oid grantorId, bool admin_opt); + Oid grantorId, bool admin_opt, bool creating); static void DelRoleMems(const char *rolename, Oid roleid, List *memberSpecs, List *memberIds, bool admin_opt); @@ -107,6 +107,24 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) DefElem *dadminmembers = NULL; DefElem *dvalidUntil = NULL; DefElem *dbypassRLS = NULL; + int inherit_spec = 0; + int createrole_spec = 0; + int createdb_spec = 0; + int canlogin_spec = 0; + int replication_spec = 0; + int bypassrls_spec = 0; + int connlimit_spec = 0; + int password_spec = 0; + int validuntil_spec = 0; + bool admin_inherit = false; + bool admin_createrole = false; + bool admin_createdb = false; + bool admin_canlogin = false; + bool admin_replication = false; + bool admin_bypassrls = false; + bool admin_connlimit = false; + bool admin_password = false; + bool admin_validuntil = false; /* The defaults can vary depending on the original statement type */ switch (stmt->stmt_type) @@ -124,13 +142,15 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) /* Extract options from the statement node tree */ foreach(option, stmt->options) { - DefElem *defel = (DefElem *) lfirst(option); + RoleElem *relem = (RoleElem *) lfirst(option); + DefElem *defel = relem->elem; if (strcmp(defel->defname, "password") == 0) { if (dpassword) errorConflictingDefElem(defel, pstate); dpassword = defel; + password_spec = relem->admin_spec; } else if (strcmp(defel->defname, "sysid") == 0) { @@ -148,36 +168,42 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) if (dinherit) errorConflictingDefElem(defel, pstate); dinherit = defel; + inherit_spec = relem->admin_spec; } else if (strcmp(defel->defname, "createrole") == 0) { if (dcreaterole) errorConflictingDefElem(defel, pstate); dcreaterole = defel; + createrole_spec = relem->admin_spec; } else if (strcmp(defel->defname, "createdb") == 0) { if (dcreatedb) errorConflictingDefElem(defel, pstate); dcreatedb = defel; + createdb_spec = relem->admin_spec; } else if (strcmp(defel->defname, "canlogin") == 0) { if (dcanlogin) errorConflictingDefElem(defel, pstate); dcanlogin = defel; + canlogin_spec = relem->admin_spec; } else if (strcmp(defel->defname, "isreplication") == 0) { if (disreplication) errorConflictingDefElem(defel, pstate); disreplication = defel; + replication_spec = relem->admin_spec; } else if (strcmp(defel->defname, "connectionlimit") == 0) { if (dconnlimit) errorConflictingDefElem(defel, pstate); dconnlimit = defel; + connlimit_spec = relem->admin_spec; } else if (strcmp(defel->defname, "addroleto") == 0) { @@ -202,12 +228,14 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) if (dvalidUntil) errorConflictingDefElem(defel, pstate); dvalidUntil = defel; + validuntil_spec = relem->admin_spec; } else if (strcmp(defel->defname, "bypassrls") == 0) { if (dbypassRLS) errorConflictingDefElem(defel, pstate); dbypassRLS = defel; + bypassrls_spec = relem->admin_spec; } else elog(ERROR, "option \"%s\" not recognized", @@ -247,6 +275,28 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) if (dbypassRLS) bypassrls = boolVal(dbypassRLS->arg); + /* + * For backward-compatibility, we allow CREATEROLE to imply the ability to + * grant all privileges except SUPERUSER, REPLICATION, and BYPASSRLS when + * neither WITH ADMIN OPTION nor WITHOUT ADMIN OPTION is specified. + */ + admin_inherit = ((inherit_spec == 1) || + (createrole && inherit_spec != -1)); + admin_createrole = ((createrole_spec == 1) || + (createrole && createrole_spec != -1)); + admin_createdb = ((createdb_spec == 1) || + (createrole && createdb_spec != -1)); + admin_canlogin = ((canlogin_spec == 1) || + (createrole && canlogin_spec != -1)); + admin_replication = (replication_spec == 1); + admin_bypassrls = (bypassrls_spec == 1); + admin_connlimit = ((connlimit_spec == 1) || + (createrole && connlimit_spec != -1)); + admin_password = ((password_spec == 1) || + (createrole && password_spec != -1)); + admin_validuntil = ((validuntil_spec == 1) || + (createrole && validuntil_spec != -1)); + /* Check some permissions first */ if (issuper) { @@ -255,27 +305,36 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to create superusers"))); } - else if (isreplication) - { - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to create replication users"))); - } - else if (bypassrls) - { - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to create bypassrls users"))); - } - else - { - if (!have_createrole_privilege()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to create role"))); - } + + if (createrole && !may_admin_createrole_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have grant option on CREATEROLE privilege to create createrole users"))); + + if (createdb && !may_admin_createdb_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have grant option on CREATEDB privilege to create createdb users"))); + + if (canlogin && !may_admin_canlogin_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have grant option on LOGIN privilege to create login users"))); + + if (isreplication && !may_admin_replication_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have grant option on REPLICATION privilege to create replication users"))); + + if (bypassrls && !may_admin_bypassrls_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have grant option on BYPASSRLS privilege to create bypassrls users"))); + + if (!have_createrole_privilege()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to create role"))); /* * Check that the user is not trying to create a role in the reserved @@ -311,7 +370,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) stmt->role))); /* Convert validuntil to internal form */ - if (validUntil) + if (validUntil && strcmp(validUntil, "always") != 0) { validUntil_datum = DirectFunctionCall3(timestamptz_in, CStringGetDatum(validUntil), @@ -393,6 +452,16 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) new_record[Anum_pg_authid_rolbypassrls - 1] = BoolGetDatum(bypassrls); + new_record[Anum_pg_authid_adminherit - 1] = BoolGetDatum(admin_inherit); + new_record[Anum_pg_authid_admcreaterole - 1] = BoolGetDatum(admin_createrole); + new_record[Anum_pg_authid_admcreatedb - 1] = BoolGetDatum(admin_createdb); + new_record[Anum_pg_authid_admcanlogin - 1] = BoolGetDatum(admin_canlogin); + new_record[Anum_pg_authid_admreplication - 1] = BoolGetDatum(admin_replication); + new_record[Anum_pg_authid_admbypassrls - 1] = BoolGetDatum(admin_bypassrls); + new_record[Anum_pg_authid_admconnlimit - 1] = BoolGetDatum(admin_connlimit); + new_record[Anum_pg_authid_admpassword - 1] = BoolGetDatum(admin_password); + new_record[Anum_pg_authid_admvaliduntil - 1] = BoolGetDatum(admin_validuntil); + /* * pg_largeobject_metadata contains pg_authid.oid's, so we use the * binary-upgrade override. @@ -453,7 +522,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) AddRoleMems(oldrolename, oldroleid, thisrole_list, thisrole_oidlist, - GetUserId(), false); + GetUserId(), false, false); ReleaseSysCache(oldroletup); } @@ -465,10 +534,10 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) */ AddRoleMems(stmt->role, roleid, adminmembers, roleSpecsToIds(adminmembers), - GetUserId(), true); + GetUserId(), true, true); AddRoleMems(stmt->role, roleid, rolemembers, roleSpecsToIds(rolemembers), - GetUserId(), false); + GetUserId(), false, true); /* Post creation hook for new role */ InvokeObjectPostCreateHook(AuthIdRelationId, roleid, 0); @@ -518,6 +587,15 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) DefElem *drolemembers = NULL; DefElem *dvalidUntil = NULL; DefElem *dbypassRLS = NULL; + int inherit_spec = 0; + int createrole_spec = 0; + int createdb_spec = 0; + int canlogin_spec = 0; + int replication_spec = 0; + int bypassrls_spec = 0; + int connlimit_spec = 0; + int password_spec = 0; + int validuntil_spec = 0; Oid roleid; check_rolespec_name(stmt->role, @@ -526,13 +604,15 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) /* Extract options from the statement node tree */ foreach(option, stmt->options) { - DefElem *defel = (DefElem *) lfirst(option); + RoleElem *relem = (RoleElem *) lfirst(option); + DefElem *defel = relem->elem; if (strcmp(defel->defname, "password") == 0) { if (dpassword) errorConflictingDefElem(defel, pstate); dpassword = defel; + password_spec = relem->admin_spec; } else if (strcmp(defel->defname, "superuser") == 0) { @@ -545,36 +625,42 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (dinherit) errorConflictingDefElem(defel, pstate); dinherit = defel; + inherit_spec = relem->admin_spec; } else if (strcmp(defel->defname, "createrole") == 0) { if (dcreaterole) errorConflictingDefElem(defel, pstate); dcreaterole = defel; + createrole_spec = relem->admin_spec; } else if (strcmp(defel->defname, "createdb") == 0) { if (dcreatedb) errorConflictingDefElem(defel, pstate); dcreatedb = defel; + createdb_spec = relem->admin_spec; } else if (strcmp(defel->defname, "canlogin") == 0) { if (dcanlogin) errorConflictingDefElem(defel, pstate); dcanlogin = defel; + canlogin_spec = relem->admin_spec; } else if (strcmp(defel->defname, "isreplication") == 0) { if (disreplication) errorConflictingDefElem(defel, pstate); disreplication = defel; + replication_spec = relem->admin_spec; } else if (strcmp(defel->defname, "connectionlimit") == 0) { if (dconnlimit) errorConflictingDefElem(defel, pstate); dconnlimit = defel; + connlimit_spec = relem->admin_spec; } else if (strcmp(defel->defname, "rolemembers") == 0 && stmt->action != 0) @@ -588,12 +674,14 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (dvalidUntil) errorConflictingDefElem(defel, pstate); dvalidUntil = defel; + validuntil_spec = relem->admin_spec; } else if (strcmp(defel->defname, "bypassrls") == 0) { if (dbypassRLS) errorConflictingDefElem(defel, pstate); dbypassRLS = defel; + bypassrls_spec = relem->admin_spec; } else elog(ERROR, "option \"%s\" not recognized", @@ -630,6 +718,8 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) * property. Otherwise, if you don't have createrole, you're only allowed * to change your own password. */ + + /* To mess with a superuser in any way you gotta be superuser. */ if (authform->rolsuper || dissuper) { if (!superuser()) @@ -637,32 +727,57 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to alter superuser roles or change superuser attribute"))); } - else if (authform->rolreplication || disreplication) - { - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter replication roles or change replication attribute"))); - } - else if (dbypassRLS) - { - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to change bypassrls attribute"))); - } - else if (!have_createrole_privilege()) - { - /* check the rest */ - if (dinherit || dcreaterole || dcreatedb || dcanlogin || dconnlimit || - drolemembers || dvalidUntil || !dpassword || roleid != GetUserId()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied"))); - } + + /* To mess with replication roles, must have admin on REPLICATION */ + if ((authform->rolreplication || disreplication) && + !may_admin_replication_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have admin on replication to alter replication roles or change replication attribute"))); + + /* For other privileges, must have admin on the privilege being altered */ + if (dbypassRLS && !may_admin_bypassrls_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have admin on bypassrls to change bypassrls attribute"))); + + if (dinherit && !may_admin_inherit_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have admin on inherit to change inherit attribute"))); + + if (dcreaterole && !may_admin_createrole_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have admin on createrole to change createrole attribute"))); + + if (dcreatedb && !may_admin_createdb_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have admin on createdb to change createdb attribute"))); + + if (dcanlogin && !may_admin_canlogin_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have admin on login to change login attribute"))); + + if (dconnlimit && !may_admin_connlimit_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have admin on connection limit to change connection limit attribute"))); + + if (dvalidUntil && !may_admin_validuntil_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have admin on valid until to change valid until attribute"))); + + if (dpassword && roleid != GetUserId() && !may_admin_password_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have admin on password to change password attribute"))); /* Convert validuntil to internal form */ - if (dvalidUntil) + if (validUntil && strcmp(validUntil, "always") != 0) { validUntil_datum = DirectFunctionCall3(timestamptz_in, CStringGetDatum(validUntil), @@ -670,6 +785,10 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) Int32GetDatum(-1)); validUntil_null = false; } + else if (validUntil && strcmp(validUntil, "always") == 0) + { + validUntil_null = true; + } else { /* fetch existing setting in case hook needs it */ @@ -709,36 +828,66 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) new_record[Anum_pg_authid_rolinherit - 1] = BoolGetDatum(boolVal(dinherit->arg)); new_record_repl[Anum_pg_authid_rolinherit - 1] = true; } + if (inherit_spec != 0) + { + new_record[Anum_pg_authid_adminherit - 1] = BoolGetDatum(inherit_spec == 1); + new_record_repl[Anum_pg_authid_adminherit - 1] = true; + } if (dcreaterole) { new_record[Anum_pg_authid_rolcreaterole - 1] = BoolGetDatum(boolVal(dcreaterole->arg)); new_record_repl[Anum_pg_authid_rolcreaterole - 1] = true; } + if (createrole_spec != 0) + { + new_record[Anum_pg_authid_admcreaterole - 1] = BoolGetDatum(createrole_spec == 1); + new_record_repl[Anum_pg_authid_admcreaterole - 1] = true; + } if (dcreatedb) { new_record[Anum_pg_authid_rolcreatedb - 1] = BoolGetDatum(boolVal(dcreatedb->arg)); new_record_repl[Anum_pg_authid_rolcreatedb - 1] = true; } + if (createdb_spec != 0) + { + new_record[Anum_pg_authid_admcreatedb - 1] = BoolGetDatum(createdb_spec == 1); + new_record_repl[Anum_pg_authid_admcreatedb - 1] = true; + } if (dcanlogin) { new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(boolVal(dcanlogin->arg)); new_record_repl[Anum_pg_authid_rolcanlogin - 1] = true; } + if (canlogin_spec != 0) + { + new_record[Anum_pg_authid_admcanlogin - 1] = BoolGetDatum(canlogin_spec == 1); + new_record_repl[Anum_pg_authid_admcanlogin - 1] = true; + } if (disreplication) { new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(boolVal(disreplication->arg)); new_record_repl[Anum_pg_authid_rolreplication - 1] = true; } + if (replication_spec != 0) + { + new_record[Anum_pg_authid_admreplication - 1] = BoolGetDatum(replication_spec == 1); + new_record_repl[Anum_pg_authid_admreplication - 1] = true; + } if (dconnlimit) { new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit); new_record_repl[Anum_pg_authid_rolconnlimit - 1] = true; } + if (connlimit_spec != 0) + { + new_record[Anum_pg_authid_admconnlimit - 1] = BoolGetDatum(connlimit_spec == 1); + new_record_repl[Anum_pg_authid_admconnlimit - 1] = true; + } /* password */ if (password) @@ -771,17 +920,32 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) new_record_repl[Anum_pg_authid_rolpassword - 1] = true; new_record_nulls[Anum_pg_authid_rolpassword - 1] = true; } + if (password_spec != 0) + { + new_record[Anum_pg_authid_admpassword - 1] = BoolGetDatum(password_spec == 1); + new_record_repl[Anum_pg_authid_admpassword - 1] = true; + } /* valid until */ new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum; new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null; new_record_repl[Anum_pg_authid_rolvaliduntil - 1] = true; + if (validuntil_spec != 0) + { + new_record[Anum_pg_authid_admvaliduntil - 1] = BoolGetDatum(validuntil_spec == 1); + new_record_repl[Anum_pg_authid_admvaliduntil - 1] = true; + } if (dbypassRLS) { new_record[Anum_pg_authid_rolbypassrls - 1] = BoolGetDatum(boolVal(dbypassRLS->arg)); new_record_repl[Anum_pg_authid_rolbypassrls - 1] = true; } + if (bypassrls_spec != 0) + { + new_record[Anum_pg_authid_admbypassrls - 1] = BoolGetDatum(bypassrls_spec == 1); + new_record_repl[Anum_pg_authid_admbypassrls - 1] = true; + } new_tuple = heap_modify_tuple(tuple, pg_authid_dsc, new_record, new_record_nulls, new_record_repl); @@ -805,7 +969,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (stmt->action == +1) /* add members to role */ AddRoleMems(rolename, roleid, rolemembers, roleSpecsToIds(rolemembers), - GetUserId(), false); + GetUserId(), false, false); else if (stmt->action == -1) /* drop members from role */ DelRoleMems(rolename, roleid, rolemembers, roleSpecsToIds(rolemembers), @@ -1264,7 +1428,7 @@ GrantRole(GrantRoleStmt *stmt) if (stmt->is_grant) AddRoleMems(rolename, roleid, stmt->grantee_roles, grantee_ids, - grantor, stmt->admin_opt); + grantor, stmt->admin_opt, false); else DelRoleMems(rolename, roleid, stmt->grantee_roles, grantee_ids, @@ -1371,11 +1535,12 @@ roleSpecsToIds(List *memberNames) * memberIds: OIDs of roles to add * grantorId: who is granting the membership * admin_opt: granting admin option? + * creating: is roleid presently being created? */ static void AddRoleMems(const char *rolename, Oid roleid, List *memberSpecs, List *memberIds, - Oid grantorId, bool admin_opt) + Oid grantorId, bool admin_opt, bool creating) { Relation pg_authmem_rel; TupleDesc pg_authmem_dsc; @@ -1401,8 +1566,7 @@ AddRoleMems(const char *rolename, Oid roleid, } else { - if (!have_createrole_privilege() && - !is_admin_of_role(grantorId, roleid)) + if (!creating && !is_admin_of_role(grantorId, roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must have admin option on role \"%s\"", diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index b5966712ce..7503d3ead6 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -327,7 +327,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); PLpgSQL_Expr PLAssignStmt %type alter_column_default opclass_item opclass_drop alter_using -%type add_drop opt_asc_desc opt_nulls_order +%type add_drop opt_asc_desc opt_nulls_order opt_admin_spec %type alter_table_cmd alter_type_cmd opt_collate_clause replica_identity partition_cmd index_partition_cmd @@ -356,8 +356,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); opt_transaction_chain %type opt_nowait_or_skip -%type OptRoleList AlterOptRoleList -%type CreateOptRoleElem AlterOptRoleElem +%type OptRoleList AlterOptRoleList user_role_list +%type CreateOptRoleElem AlterOptRoleElem %type opt_type %type foreign_server_version opt_foreign_server_version @@ -1104,24 +1104,33 @@ AlterOptRoleList: ; AlterOptRoleElem: - PASSWORD Sconst + PASSWORD Sconst opt_admin_spec { - $$ = makeDefElem("password", + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("password", (Node *)makeString($2), @1); + n->admin_spec = $3; + $$ = (Node *)n; } - | PASSWORD NULL_P + | PASSWORD NULL_P opt_admin_spec { - $$ = makeDefElem("password", NULL, @1); + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("password", NULL, @1); + n->admin_spec = $3; + $$ = (Node *)n; } - | ENCRYPTED PASSWORD Sconst + | ENCRYPTED PASSWORD Sconst opt_admin_spec { /* * These days, passwords are always stored in encrypted * form, so there is no difference between PASSWORD and * ENCRYPTED PASSWORD. */ - $$ = makeDefElem("password", + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("password", (Node *)makeString($3), @1); + n->admin_spec = $4; + $$ = (Node *)n; } | UNENCRYPTED PASSWORD Sconst { @@ -1131,67 +1140,111 @@ AlterOptRoleElem: errhint("Remove UNENCRYPTED to store the password in encrypted form instead."), parser_errposition(@1))); } - | INHERIT + | INHERIT opt_admin_spec + { + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("inherit", (Node *)makeBoolean(true), @1); + n->admin_spec = $2; + $$ = (Node *)n; + } + | CONNECTION LIMIT SignedIconst opt_admin_spec { - $$ = makeDefElem("inherit", (Node *)makeBoolean(true), @1); + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("connectionlimit", (Node *)makeInteger($3), @1); + n->admin_spec = $4; + $$ = (Node *)n; } - | CONNECTION LIMIT SignedIconst + | CONNECTION LIMIT NONE opt_admin_spec { - $$ = makeDefElem("connectionlimit", (Node *)makeInteger($3), @1); + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("connectionlimit", (Node *)makeInteger(-1), @1); + n->admin_spec = $4; + $$ = (Node *)n; } - | VALID UNTIL Sconst + | VALID UNTIL Sconst opt_admin_spec { - $$ = makeDefElem("validUntil", (Node *)makeString($3), @1); + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("validUntil", (Node *)makeString($3), @1); + n->admin_spec = $4; + $$ = (Node *)n; + } + | VALID ALWAYS opt_admin_spec + { + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("validUntil", (Node *)makeString("always"), @1); + n->admin_spec = $3; + $$ = (Node *)n; } /* Supported but not documented for roles, for use by ALTER GROUP. */ - | USER role_list + | USER role_list opt_admin_spec { - $$ = makeDefElem("rolemembers", (Node *)$2, @1); + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("rolemembers", (Node *)$2, @1); + n->admin_spec = $3; + $$ = (Node *)n; } - | IDENT + | IDENT opt_admin_spec { /* * We handle identifiers that aren't parser keywords with * the following special-case codes, to avoid bloating the * size of the main parser. */ + RoleElem *n = makeNode(RoleElem); + + /* + * Record whether the user specified WITH GRANT OPTION. + * Note that for some privileges this is always implied, + * such as SUPERUSER, but we don't reflect that here. + */ + n->admin_spec = $2; + if (strcmp($1, "superuser") == 0) - $$ = makeDefElem("superuser", (Node *)makeBoolean(true), @1); + n->elem = makeDefElem("superuser", (Node *)makeBoolean(true), @1); else if (strcmp($1, "nosuperuser") == 0) - $$ = makeDefElem("superuser", (Node *)makeBoolean(false), @1); + { + n->elem = makeDefElem("superuser", (Node *)makeBoolean(false), @1); + if (n->admin_spec == 1) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("only superusers may create new superusers"), + parser_errposition(@2)); + } else if (strcmp($1, "createrole") == 0) - $$ = makeDefElem("createrole", (Node *)makeBoolean(true), @1); + n->elem = makeDefElem("createrole", (Node *)makeBoolean(true), @1); else if (strcmp($1, "nocreaterole") == 0) - $$ = makeDefElem("createrole", (Node *)makeBoolean(false), @1); + n->elem = makeDefElem("createrole", (Node *)makeBoolean(false), @1); else if (strcmp($1, "replication") == 0) - $$ = makeDefElem("isreplication", (Node *)makeBoolean(true), @1); + n->elem = makeDefElem("isreplication", (Node *)makeBoolean(true), @1); else if (strcmp($1, "noreplication") == 0) - $$ = makeDefElem("isreplication", (Node *)makeBoolean(false), @1); + n->elem = makeDefElem("isreplication", (Node *)makeBoolean(false), @1); else if (strcmp($1, "createdb") == 0) - $$ = makeDefElem("createdb", (Node *)makeBoolean(true), @1); + n->elem = makeDefElem("createdb", (Node *)makeBoolean(true), @1); else if (strcmp($1, "nocreatedb") == 0) - $$ = makeDefElem("createdb", (Node *)makeBoolean(false), @1); + n->elem = makeDefElem("createdb", (Node *)makeBoolean(false), @1); else if (strcmp($1, "login") == 0) - $$ = makeDefElem("canlogin", (Node *)makeBoolean(true), @1); + n->elem = makeDefElem("canlogin", (Node *)makeBoolean(true), @1); else if (strcmp($1, "nologin") == 0) - $$ = makeDefElem("canlogin", (Node *)makeBoolean(false), @1); + n->elem = makeDefElem("canlogin", (Node *)makeBoolean(false), @1); else if (strcmp($1, "bypassrls") == 0) - $$ = makeDefElem("bypassrls", (Node *)makeBoolean(true), @1); + n->elem = makeDefElem("bypassrls", (Node *)makeBoolean(true), @1); else if (strcmp($1, "nobypassrls") == 0) - $$ = makeDefElem("bypassrls", (Node *)makeBoolean(false), @1); + n->elem = makeDefElem("bypassrls", (Node *)makeBoolean(false), @1); else if (strcmp($1, "noinherit") == 0) { /* * Note that INHERIT is a keyword, so it's handled by main parser, but * NOINHERIT is handled here. */ - $$ = makeDefElem("inherit", (Node *)makeBoolean(false), @1); + n->elem = makeDefElem("inherit", (Node *)makeBoolean(false), @1); } else ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("unrecognized role option \"%s\"", $1), parser_errposition(@1))); + + $$ = (Node *)n; } ; @@ -1200,23 +1253,38 @@ CreateOptRoleElem: /* The following are not supported by ALTER ROLE/USER/GROUP */ | SYSID Iconst { - $$ = makeDefElem("sysid", (Node *)makeInteger($2), @1); + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("sysid", (Node *)makeInteger($2), @1); + n->admin_spec = 0; + $$ = (Node *)n; } | ADMIN role_list { - $$ = makeDefElem("adminmembers", (Node *)$2, @1); + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("adminmembers", (Node *)$2, @1); + n->admin_spec = 0; + $$ = (Node *)n; } | ROLE role_list { - $$ = makeDefElem("rolemembers", (Node *)$2, @1); + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("rolemembers", (Node *)$2, @1); + n->admin_spec = 0; + $$ = (Node *)n; } | IN_P ROLE role_list { - $$ = makeDefElem("addroleto", (Node *)$3, @1); + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("addroleto", (Node *)$3, @1); + n->admin_spec = 0; + $$ = (Node *)n; } | IN_P GROUP_P role_list { - $$ = makeDefElem("addroleto", (Node *)$3, @1); + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("addroleto", (Node *)$3, @1); + n->admin_spec = 0; + $$ = (Node *)n; } ; @@ -1385,13 +1453,12 @@ CreateGroupStmt: *****************************************************************************/ AlterGroupStmt: - ALTER GROUP_P RoleSpec add_drop USER role_list + ALTER GROUP_P RoleSpec add_drop user_role_list { AlterRoleStmt *n = makeNode(AlterRoleStmt); n->role = $3; n->action = $4; - n->options = list_make1(makeDefElem("rolemembers", - (Node *)$6, @6)); + n->options = $5; $$ = (Node *)n; } ; @@ -1400,6 +1467,16 @@ add_drop: ADD_P { $$ = +1; } | DROP { $$ = -1; } ; +user_role_list: + USER role_list + { + RoleElem *n = makeNode(RoleElem); + n->elem = makeDefElem("rolemembers", (Node *)$2, @2); + n->admin_spec = 0; + $$ = list_make1(n); + } + ; + /***************************************************************************** * @@ -7257,6 +7334,12 @@ opt_grant_grant_option: | /*EMPTY*/ { $$ = false; } ; +opt_admin_spec: + WITH ADMIN OPTION { $$ = 1; } + | WITHOUT ADMIN OPTION { $$ = -1; } + | /* EMPTY */ { $$ = 0; } + ; + /***************************************************************************** * * GRANT and REVOKE ROLE statements diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat index 6c28119fa1..4829a6dbd2 100644 --- a/src/include/catalog/pg_authid.dat +++ b/src/include/catalog/pg_authid.dat @@ -22,67 +22,93 @@ { oid => '10', oid_symbol => 'BOOTSTRAP_SUPERUSERID', rolname => 'POSTGRES', rolsuper => 't', rolinherit => 't', rolcreaterole => 't', rolcreatedb => 't', rolcanlogin => 't', - rolreplication => 't', rolbypassrls => 't', rolconnlimit => '-1', + rolreplication => 't', rolbypassrls => 't', adminherit => 't', admcreaterole => 't', + admcreatedb => 't', admcanlogin => 't', admreplication => 't', admbypassrls => 't', + admconnlimit => 't', admpassword => 't', admvaliduntil => 't', rolconnlimit => '-1', rolpassword => '_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', + rolreplication => 'f', rolbypassrls => 'f', adminherit => 'f', admcreaterole => 'f', + admcreatedb => 'f', admcanlogin => 'f', admreplication => 'f', admbypassrls => 'f', + admconnlimit => 'f', admpassword => 'f', admvaliduntil => 'f', rolconnlimit => '-1', rolpassword => '_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', + rolreplication => 'f', rolbypassrls => 'f', adminherit => 'f', admcreaterole => 'f', + admcreatedb => 'f', admcanlogin => 'f', admreplication => 'f', admbypassrls => 'f', + admconnlimit => 'f', admpassword => 'f', admvaliduntil => 'f', rolconnlimit => '-1', rolpassword => '_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', + rolreplication => 'f', rolbypassrls => 'f', adminherit => 'f', admcreaterole => 'f', + admcreatedb => 'f', admcanlogin => 'f', admreplication => 'f', admbypassrls => 'f', + admconnlimit => 'f', admpassword => 'f', admvaliduntil => 'f', rolconnlimit => '-1', rolpassword => '_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', + rolreplication => 'f', rolbypassrls => 'f', adminherit => 'f', admcreaterole => 'f', + admcreatedb => 'f', admcanlogin => 'f', admreplication => 'f', admbypassrls => 'f', + admconnlimit => 'f', admpassword => 'f', admvaliduntil => 'f', rolconnlimit => '-1', rolpassword => '_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', + rolreplication => 'f', rolbypassrls => 'f', adminherit => 'f', admcreaterole => 'f', + admcreatedb => 'f', admcanlogin => 'f', admreplication => 'f', admbypassrls => 'f', + admconnlimit => 'f', admpassword => 'f', admvaliduntil => 'f', rolconnlimit => '-1', rolpassword => '_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', + rolreplication => 'f', rolbypassrls => 'f', adminherit => 'f', admcreaterole => 'f', + admcreatedb => 'f', admcanlogin => 'f', admreplication => 'f', admbypassrls => 'f', + admconnlimit => 'f', admpassword => 'f', admvaliduntil => 'f', rolconnlimit => '-1', rolpassword => '_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', + rolreplication => 'f', rolbypassrls => 'f', adminherit => 'f', admcreaterole => 'f', + admcreatedb => 'f', admcanlogin => 'f', admreplication => 'f', admbypassrls => 'f', + admconnlimit => 'f', admpassword => 'f', admvaliduntil => 'f', rolconnlimit => '-1', rolpassword => '_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', + rolreplication => 'f', rolbypassrls => 'f', adminherit => 'f', admcreaterole => 'f', + admcreatedb => 'f', admcanlogin => 'f', admreplication => 'f', admbypassrls => 'f', + admconnlimit => 'f', admpassword => 'f', admvaliduntil => 'f', rolconnlimit => '-1', rolpassword => '_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', + rolreplication => 'f', rolbypassrls => 'f', adminherit => 'f', admcreaterole => 'f', + admcreatedb => 'f', admcanlogin => 'f', admreplication => 'f', admbypassrls => 'f', + admconnlimit => 'f', admpassword => 'f', admvaliduntil => 'f', rolconnlimit => '-1', rolpassword => '_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', + rolreplication => 'f', rolbypassrls => 'f', adminherit => 'f', admcreaterole => 'f', + admcreatedb => 'f', admcanlogin => 'f', admreplication => 'f', admbypassrls => 'f', + admconnlimit => 'f', admpassword => 'f', admvaliduntil => 'f', rolconnlimit => '-1', rolpassword => '_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', + rolreplication => 'f', rolbypassrls => 'f', adminherit => 'f', admcreaterole => 'f', + admcreatedb => 'f', admcanlogin => 'f', admreplication => 'f', admbypassrls => 'f', + admconnlimit => 'f', admpassword => 'f', admvaliduntil => 'f', rolconnlimit => '-1', rolpassword => '_null_', rolvaliduntil => '_null_' }, { oid => '4544', oid_symbol => 'ROLE_PG_CHECKPOINTER', rolname => 'pg_checkpointer', rolsuper => 'f', rolinherit => 't', rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', - rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', + rolreplication => 'f', rolbypassrls => 'f', adminherit => 'f', admcreaterole => 'f', + admcreatedb => 'f', admcanlogin => 'f', admreplication => 'f', admbypassrls => 'f', + admconnlimit => 'f', admpassword => 'f', admvaliduntil => 'f', rolconnlimit => '-1', rolpassword => '_null_', rolvaliduntil => '_null_' }, ] diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h index 4b65e39a1f..4acdcaa685 100644 --- a/src/include/catalog/pg_authid.h +++ b/src/include/catalog/pg_authid.h @@ -39,6 +39,16 @@ CATALOG(pg_authid,1260,AuthIdRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(284 bool rolcanlogin; /* allowed to log in as session user? */ bool rolreplication; /* role used for streaming replication */ bool rolbypassrls; /* bypasses row-level security? */ + + bool adminherit; /* allowed to administer inherit? */ + bool admcreaterole; /* allowed to administer createrole? */ + bool admcreatedb; /* allowed to administer createdb?? */ + bool admcanlogin; /* allowed to administer login? */ + bool admreplication; /* allowed to administer replication? */ + bool admbypassrls; /* allowed to administer bypassesrls? */ + bool admconnlimit; /* allowed to administer connlimit? */ + bool admpassword; /* allowed to administer password? */ + bool admvaliduntil; /* allowed to administer validuntil? */ int32 rolconnlimit; /* max connections allowed (-1=no limit) */ /* remaining fields may be null; use heap_getattr to read them! */ diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index da35f2c272..065f58c485 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -361,6 +361,7 @@ typedef enum NodeTag T_DiscardStmt, T_CreateTrigStmt, T_CreatePLangStmt, + T_RoleElem, T_CreateRoleStmt, T_AlterRoleStmt, T_DropRoleStmt, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 3e9bdc781f..e19980d5ff 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2618,19 +2618,26 @@ typedef enum RoleStmtType ROLESTMT_GROUP } RoleStmtType; +typedef struct RoleElem +{ + NodeTag type; + DefElem *elem; + int admin_spec; +} RoleElem; + typedef struct CreateRoleStmt { NodeTag type; RoleStmtType stmt_type; /* ROLE/USER/GROUP */ char *role; /* role name */ - List *options; /* List of DefElem nodes */ + List *options; /* List of RoleElem nodes */ } CreateRoleStmt; typedef struct AlterRoleStmt { NodeTag type; RoleSpec *role; /* role */ - List *options; /* List of DefElem nodes */ + List *options; /* List of RoleElem nodes */ int action; /* +1 = add members, -1 = drop members */ } AlterRoleStmt; diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h index 1ce4c5556e..7cdc454869 100644 --- a/src/include/utils/acl.h +++ b/src/include/utils/acl.h @@ -317,6 +317,18 @@ extern bool pg_publication_ownercheck(Oid pub_oid, Oid roleid); extern bool pg_subscription_ownercheck(Oid sub_oid, Oid roleid); extern bool pg_statistics_object_ownercheck(Oid stat_oid, Oid roleid); extern bool has_createrole_privilege(Oid roleid); +extern bool has_createdb_privilege(Oid roleid); +extern bool has_canlogin_privilege(Oid roleid); +extern bool has_replication_privilege(Oid roleid); extern bool has_bypassrls_privilege(Oid roleid); +extern bool may_admin_createrole_privilege(Oid roleid); +extern bool may_admin_createdb_privilege(Oid roleid); +extern bool may_admin_canlogin_privilege(Oid roleid); +extern bool may_admin_replication_privilege(Oid roleid); +extern bool may_admin_bypassrls_privilege(Oid roleid); +extern bool may_admin_inherit_privilege(Oid roleid); +extern bool may_admin_connlimit_privilege(Oid roleid); +extern bool may_admin_validuntil_privilege(Oid roleid); +extern bool may_admin_password_privilege(Oid roleid); #endif /* ACL_H */ diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out index 4e67d72760..20da6644b7 100644 --- a/src/test/regress/expected/create_role.out +++ b/src/test/regress/expected/create_role.out @@ -1,17 +1,72 @@ -- ok, superuser can create users with any set of privileges CREATE ROLE regress_role_super SUPERUSER; CREATE ROLE regress_role_admin CREATEDB CREATEROLE REPLICATION BYPASSRLS; --- fail, only superusers can create users with these privileges +-- ok, superuser can create a role that can create login replication users, but +-- cannot itself login, nor perform replication +CREATE ROLE regress_role_repladmin + CREATEROLE WITHOUT ADMIN OPTION -- can create roles, but cannot give it away + NOCREATEDB WITHOUT ADMIN OPTION -- cannot create db, nor give it away + NOLOGIN WITH ADMIN OPTION -- cannot log in, but can give it away + NOREPLICATION WITH ADMIN OPTION -- cannot replicate, but can give it away + NOBYPASSRLS WITHOUT ADMIN OPTION; -- cannot bypassrls, nor give it away +-- ok, superuser can create a role with CREATEROLE but restrict give-aways +CREATE ROLE regress_role_minoradmin + NOSUPERUSER -- WITHOUT ADMIN OPTION is implied + CREATEROLE WITHOUT ADMIN OPTION + NOCREATEDB WITHOUT ADMIN OPTION + NOLOGIN WITHOUT ADMIN OPTION + NOREPLICATION -- WITHOUT ADMIN OPTION is implied + NOBYPASSRLS -- WITHOUT ADMIN OPTION is implied + NOINHERIT WITHOUT ADMIN OPTION + CONNECTION LIMIT NONE WITHOUT ADMIN OPTION + VALID ALWAYS WITHOUT ADMIN OPTION + PASSWORD NULL WITHOUT ADMIN OPTION; +-- fail, not granted privilege to create these users +SET SESSION AUTHORIZATION regress_role_repladmin; +CREATE ROLE regress_nosuch_superuser SUPERUSER; +ERROR: must be superuser to create superusers +CREATE ROLE regress_nosuch_createrole CREATEROLE; +ERROR: must have grant option on CREATEROLE privilege to create createrole users +CREATE ROLE regress_nosuch_createdb CREATEDB; +ERROR: must have grant option on CREATEDB privilege to create createdb users +CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS; +ERROR: must have grant option on BYPASSRLS privilege to create bypassrls users +CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; +ERROR: must have grant option on BYPASSRLS privilege to create bypassrls users +-- ok, can create login and replication users +CREATE ROLE regress_role_login LOGIN; +CREATE ROLE regress_role_replication REPLICATION; +CREATE ROLE regress_role_login_replication LOGIN REPLICATION; +-- fail, cannot create privileged users +SET SESSION AUTHORIZATION regress_role_minoradmin; +CREATE ROLE regress_nosuch_superuser SUPERUSER; +ERROR: must be superuser to create superusers +CREATE ROLE regress_nosuch_createrole CREATEROLE; +ERROR: must have grant option on CREATEROLE privilege to create createrole users +CREATE ROLE regress_nosuch_createdb CREATEDB; +ERROR: must have grant option on CREATEDB privilege to create createdb users +CREATE ROLE regress_nosuch_replication REPLICATION; +ERROR: must have grant option on REPLICATION privilege to create replication users +CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; +ERROR: must have grant option on BYPASSRLS privilege to create bypassrls users +-- ok, can create unprivileged roles +CREATE ROLE regress_role_unprivileged; +-- fail, CREATEROLE does not imply ADMIN on roles +CREATE ROLE regress_nosuch_inrole IN ROLE regress_role_unprivileged; +ERROR: must have admin option on role "regress_role_unprivileged" +CREATE ROLE regress_nosuch_inrole IN GROUP regress_role_unprivileged; +ERROR: must have admin option on role "regress_role_unprivileged" +-- fail, having CREATEROLE does not by default give ADMIN OPTION on these SET SESSION AUTHORIZATION regress_role_admin; CREATE ROLE regress_nosuch_superuser SUPERUSER; ERROR: must be superuser to create superusers CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS; -ERROR: must be superuser to create replication users +ERROR: must have grant option on REPLICATION privilege to create replication users CREATE ROLE regress_nosuch_replication REPLICATION; -ERROR: must be superuser to create replication users +ERROR: must have grant option on REPLICATION privilege to create replication users CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; -ERROR: must be superuser to create bypassrls users --- ok, having CREATEROLE is enough to create users with these privileges +ERROR: must have grant option on BYPASSRLS privilege to create bypassrls users +-- ok, having CREATEROLE does by default give ADMIN OPTION on these CREATE ROLE regress_createdb CREATEDB; CREATE ROLE regress_createrole CREATEROLE; CREATE ROLE regress_login LOGIN; @@ -25,9 +80,16 @@ NOTICE: SYSID can no longer be specified -- fail, cannot grant membership in superuser role CREATE ROLE regress_nosuch_super IN ROLE regress_role_super; ERROR: must be superuser to alter superusers --- fail, database owner cannot have members +-- fail, lack ADMIN privilege on role pg_database_owner; CREATE ROLE regress_nosuch_dbowner IN ROLE pg_database_owner; +ERROR: must have admin option on role "pg_database_owner" +-- fail, database owner cannot have members +RESET SESSION AUTHORIZATION; +GRANT pg_database_owner TO regress_role_admin WITH ADMIN OPTION; ERROR: role "pg_database_owner" cannot have explicit members +SET SESSION AUTHORIZATION regress_role_admin; +CREATE ROLE regress_nosuch_dbowner IN ROLE pg_database_owner; +ERROR: must have admin option on role "pg_database_owner" -- ok, can grant other users into a role CREATE ROLE regress_inroles ROLE regress_role_super, regress_createdb, regress_createrole, regress_login, @@ -73,7 +135,35 @@ ERROR: must be owner of view tenant_view -- fail, cannot take ownership of these objects from regress_tenant REASSIGN OWNED BY regress_tenant TO regress_createrole; ERROR: permission denied to reassign objects --- ok, having CREATEROLE is enough to create roles in privileged roles +-- fail, having CREATEROLE is not enough to create roles in privileged roles +CREATE ROLE regress_nosuch_read_all_data IN ROLE pg_read_all_data; +ERROR: must have admin option on role "pg_read_all_data" +CREATE ROLE regress_nosuch_write_all_data IN ROLE pg_write_all_data; +ERROR: must have admin option on role "pg_write_all_data" +CREATE ROLE regress_nosuch_monitor IN ROLE pg_monitor; +ERROR: must have admin option on role "pg_monitor" +CREATE ROLE regress_nosuch_read_all_settings IN ROLE pg_read_all_settings; +ERROR: must have admin option on role "pg_read_all_settings" +CREATE ROLE regress_nosuch_read_all_stats IN ROLE pg_read_all_stats; +ERROR: must have admin option on role "pg_read_all_stats" +CREATE ROLE regress_nosuch_stat_scan_tables IN ROLE pg_stat_scan_tables; +ERROR: must have admin option on role "pg_stat_scan_tables" +CREATE ROLE regress_nosuch_read_server_files IN ROLE pg_read_server_files; +ERROR: must have admin option on role "pg_read_server_files" +CREATE ROLE regress_nosuch_write_server_files IN ROLE pg_write_server_files; +ERROR: must have admin option on role "pg_write_server_files" +CREATE ROLE regress_nosuch_execute_server_program IN ROLE pg_execute_server_program; +ERROR: must have admin option on role "pg_execute_server_program" +CREATE ROLE regress_nosuch_signal_backend IN ROLE pg_signal_backend; +ERROR: must have admin option on role "pg_signal_backend" +-- ok, superuser can grant ADMIN on privileged roles +SET SESSION AUTHORIZATION regress_role_super; +GRANT pg_read_all_data, pg_write_all_data, pg_monitor, pg_read_all_settings, + pg_read_all_stats, pg_stat_scan_tables, pg_read_server_files, + pg_write_server_files, pg_execute_server_program, pg_signal_backend +TO regress_createrole WITH ADMIN OPTION; +-- ok, having CREATEROLE plus ADMIN is enough to create roles in privileged roles +SET SESSION AUTHORIZATION regress_createrole; CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data; CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data; CREATE ROLE regress_monitor IN ROLE pg_monitor; @@ -103,7 +193,89 @@ ERROR: role "regress_nosuch_recursive" does not exist DROP ROLE regress_nosuch_admin_recursive; ERROR: role "regress_nosuch_admin_recursive" does not exist DROP ROLE regress_plainrole; +-- fail, cannot change attributes without ADMIN for them +SET SESSION AUTHORIZATION regress_role_minoradmin; +ALTER ROLE regress_role_login LOGIN; +ERROR: must have admin on login to change login attribute +ALTER ROLE regress_role_login NOLOGIN; +ERROR: must have admin on login to change login attribute +ALTER ROLE regress_role_login REPLICATION; +ERROR: must have admin on replication to alter replication roles or change replication attribute +ALTER ROLE regress_role_login NOREPLICATION; +ERROR: must have admin on replication to alter replication roles or change replication attribute +ALTER ROLE regress_role_login CREATEDB; +ERROR: must have admin on createdb to change createdb attribute +ALTER ROLE regress_role_login NOCREATEDB; +ERROR: must have admin on createdb to change createdb attribute +ALTER ROLE regress_role_login CREATEROLE; +ERROR: must have admin on createrole to change createrole attribute +ALTER ROLE regress_role_login NOCREATEROLE; +ERROR: must have admin on createrole to change createrole attribute +ALTER ROLE regress_role_login INHERIT; +ERROR: must have admin on inherit to change inherit attribute +ALTER ROLE regress_role_login NOINHERIT; +ERROR: must have admin on inherit to change inherit attribute +ALTER ROLE regress_role_login CONNECTION LIMIT 5; +ERROR: must have admin on connection limit to change connection limit attribute +ALTER ROLE regress_role_login VALID ALWAYS; +ERROR: must have admin on valid until to change valid until attribute +ALTER ROLE regress_role_login PASSWORD 'foobar'; +ERROR: must have admin on password to change password attribute +-- ok, regress_role_admin got ADMIN on attributes by way of having CREATEROLE +SET SESSION AUTHORIZATION regress_role_admin; +ALTER ROLE regress_role_login LOGIN; +ALTER ROLE regress_role_login NOLOGIN; +ALTER ROLE regress_role_login CREATEDB; +ALTER ROLE regress_role_login NOCREATEDB; +ALTER ROLE regress_role_login CREATEROLE; +ALTER ROLE regress_role_login NOCREATEROLE; +ALTER ROLE regress_role_login INHERIT; +ALTER ROLE regress_role_login NOINHERIT; +ALTER ROLE regress_role_login CONNECTION LIMIT 5; +ALTER ROLE regress_role_login VALID ALWAYS; +ALTER ROLE regress_role_login PASSWORD 'foobar'; +-- fail, regress_role_admin did not get ADMIN on these +ALTER ROLE regress_role_login SUPERUSER; +ERROR: must be superuser to alter superuser roles or change superuser attribute +ALTER ROLE regress_role_login NOSUPERUSER; +ERROR: must be superuser to alter superuser roles or change superuser attribute +ALTER ROLE regress_role_login REPLICATION; +ERROR: must have admin on replication to alter replication roles or change replication attribute +ALTER ROLE regress_role_login NOREPLICATION; +ERROR: must have admin on replication to alter replication roles or change replication attribute +ALTER ROLE regress_role_login BYPASSRLS; +ERROR: must have admin on bypassrls to change bypassrls attribute +ALTER ROLE regress_role_login NOBYPASSRLS; +ERROR: must have admin on bypassrls to change bypassrls attribute +-- superuser can grant them now, though +RESET SESSION AUTHORIZATION; +ALTER ROLE regress_role_admin + NOREPLICATION WITH ADMIN OPTION + NOBYPASSRLS WITH ADMIN OPTION; +-- ok, regress_role_admin can now grant these +SET SESSION AUTHORIZATION regress_role_admin; +ALTER ROLE regress_role_login REPLICATION; +ALTER ROLE regress_role_login NOREPLICATION; +ALTER ROLE regress_role_login BYPASSRLS; +ALTER ROLE regress_role_login NOBYPASSRLS; +-- fail, but regress_role_admin still cannot grant this +ALTER ROLE regress_role_login SUPERUSER; +ERROR: must be superuser to alter superuser roles or change superuser attribute +ALTER ROLE regress_role_login NOSUPERUSER; +ERROR: must be superuser to alter superuser roles or change superuser attribute +-- ok, regress_role_admin can grant attributes with ADMIN +ALTER ROLE regress_role_unprivileged LOGIN WITH ADMIN OPTION; +ALTER ROLE regress_role_unprivileged CREATEDB WITH ADMIN OPTION; +ALTER ROLE regress_role_unprivileged CREATEROLE WITH ADMIN OPTION; +ALTER ROLE regress_role_unprivileged INHERIT WITH ADMIN OPTION; +ALTER ROLE regress_role_unprivileged CONNECTION LIMIT 5 WITH ADMIN OPTION; +ALTER ROLE regress_role_unprivileged VALID ALWAYS WITH ADMIN OPTION; +ALTER ROLE regress_role_unprivileged PASSWORD 'foobar' WITH ADMIN OPTION; -- ok, should be able to drop non-superuser roles we created +DROP ROLE regress_role_login; +DROP ROLE regress_role_replication; +DROP ROLE regress_role_login_replication; +DROP ROLE regress_role_unprivileged; DROP ROLE regress_createdb; DROP ROLE regress_createrole; DROP ROLE regress_login; @@ -141,5 +313,7 @@ DROP INDEX tenant_idx; DROP TABLE tenant_table; DROP VIEW tenant_view; DROP ROLE regress_tenant; +DROP ROLE regress_role_minoradmin; +DROP ROLE regress_role_repladmin; DROP ROLE regress_role_admin; DROP ROLE regress_role_super; diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 291e21d7a6..60370626f4 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -59,6 +59,10 @@ CREATE GROUP regress_priv_group2 WITH USER regress_priv_user1, regress_priv_user ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4; ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- duplicate NOTICE: role "regress_priv_user2" is already a member of role "regress_priv_group2" +ALTER GROUP regress_priv_group2 ADD USER regress_priv_user4, regress_priv_user2; -- duplicates +NOTICE: role "regress_priv_user2" is already a member of role "regress_priv_group2" +ALTER GROUP regress_priv_group2 ADD USER regress_priv_user5, regress_priv_user6, regress_priv_user7; +ALTER GROUP regress_priv_group2 DROP USER regress_priv_user7, regress_priv_user6, regress_priv_user5; ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; GRANT regress_priv_group2 TO regress_priv_user4 WITH ADMIN OPTION; -- prepare non-leakproof function for later diff --git a/src/test/regress/sql/create_role.sql b/src/test/regress/sql/create_role.sql index 292dc08797..c15fc33be3 100644 --- a/src/test/regress/sql/create_role.sql +++ b/src/test/regress/sql/create_role.sql @@ -2,14 +2,64 @@ CREATE ROLE regress_role_super SUPERUSER; CREATE ROLE regress_role_admin CREATEDB CREATEROLE REPLICATION BYPASSRLS; --- fail, only superusers can create users with these privileges +-- ok, superuser can create a role that can create login replication users, but +-- cannot itself login, nor perform replication +CREATE ROLE regress_role_repladmin + CREATEROLE WITHOUT ADMIN OPTION -- can create roles, but cannot give it away + NOCREATEDB WITHOUT ADMIN OPTION -- cannot create db, nor give it away + NOLOGIN WITH ADMIN OPTION -- cannot log in, but can give it away + NOREPLICATION WITH ADMIN OPTION -- cannot replicate, but can give it away + NOBYPASSRLS WITHOUT ADMIN OPTION; -- cannot bypassrls, nor give it away + +-- ok, superuser can create a role with CREATEROLE but restrict give-aways +CREATE ROLE regress_role_minoradmin + NOSUPERUSER -- WITHOUT ADMIN OPTION is implied + CREATEROLE WITHOUT ADMIN OPTION + NOCREATEDB WITHOUT ADMIN OPTION + NOLOGIN WITHOUT ADMIN OPTION + NOREPLICATION -- WITHOUT ADMIN OPTION is implied + NOBYPASSRLS -- WITHOUT ADMIN OPTION is implied + NOINHERIT WITHOUT ADMIN OPTION + CONNECTION LIMIT NONE WITHOUT ADMIN OPTION + VALID ALWAYS WITHOUT ADMIN OPTION + PASSWORD NULL WITHOUT ADMIN OPTION; + +-- fail, not granted privilege to create these users +SET SESSION AUTHORIZATION regress_role_repladmin; +CREATE ROLE regress_nosuch_superuser SUPERUSER; +CREATE ROLE regress_nosuch_createrole CREATEROLE; +CREATE ROLE regress_nosuch_createdb CREATEDB; +CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS; +CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; + +-- ok, can create login and replication users +CREATE ROLE regress_role_login LOGIN; +CREATE ROLE regress_role_replication REPLICATION; +CREATE ROLE regress_role_login_replication LOGIN REPLICATION; + +-- fail, cannot create privileged users +SET SESSION AUTHORIZATION regress_role_minoradmin; +CREATE ROLE regress_nosuch_superuser SUPERUSER; +CREATE ROLE regress_nosuch_createrole CREATEROLE; +CREATE ROLE regress_nosuch_createdb CREATEDB; +CREATE ROLE regress_nosuch_replication REPLICATION; +CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; + +-- ok, can create unprivileged roles +CREATE ROLE regress_role_unprivileged; + +-- fail, CREATEROLE does not imply ADMIN on roles +CREATE ROLE regress_nosuch_inrole IN ROLE regress_role_unprivileged; +CREATE ROLE regress_nosuch_inrole IN GROUP regress_role_unprivileged; + +-- fail, having CREATEROLE does not by default give ADMIN OPTION on these SET SESSION AUTHORIZATION regress_role_admin; CREATE ROLE regress_nosuch_superuser SUPERUSER; CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS; CREATE ROLE regress_nosuch_replication REPLICATION; CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; --- ok, having CREATEROLE is enough to create users with these privileges +-- ok, having CREATEROLE does by default give ADMIN OPTION on these CREATE ROLE regress_createdb CREATEDB; CREATE ROLE regress_createrole CREATEROLE; CREATE ROLE regress_login LOGIN; @@ -24,7 +74,14 @@ CREATE ROLE regress_noiseword SYSID 12345; -- fail, cannot grant membership in superuser role CREATE ROLE regress_nosuch_super IN ROLE regress_role_super; +-- fail, lack ADMIN privilege on role pg_database_owner; +CREATE ROLE regress_nosuch_dbowner IN ROLE pg_database_owner; + -- fail, database owner cannot have members +RESET SESSION AUTHORIZATION; +GRANT pg_database_owner TO regress_role_admin WITH ADMIN OPTION; + +SET SESSION AUTHORIZATION regress_role_admin; CREATE ROLE regress_nosuch_dbowner IN ROLE pg_database_owner; -- ok, can grant other users into a role @@ -74,7 +131,27 @@ DROP VIEW tenant_view; -- fail, cannot take ownership of these objects from regress_tenant REASSIGN OWNED BY regress_tenant TO regress_createrole; --- ok, having CREATEROLE is enough to create roles in privileged roles +-- fail, having CREATEROLE is not enough to create roles in privileged roles +CREATE ROLE regress_nosuch_read_all_data IN ROLE pg_read_all_data; +CREATE ROLE regress_nosuch_write_all_data IN ROLE pg_write_all_data; +CREATE ROLE regress_nosuch_monitor IN ROLE pg_monitor; +CREATE ROLE regress_nosuch_read_all_settings IN ROLE pg_read_all_settings; +CREATE ROLE regress_nosuch_read_all_stats IN ROLE pg_read_all_stats; +CREATE ROLE regress_nosuch_stat_scan_tables IN ROLE pg_stat_scan_tables; +CREATE ROLE regress_nosuch_read_server_files IN ROLE pg_read_server_files; +CREATE ROLE regress_nosuch_write_server_files IN ROLE pg_write_server_files; +CREATE ROLE regress_nosuch_execute_server_program IN ROLE pg_execute_server_program; +CREATE ROLE regress_nosuch_signal_backend IN ROLE pg_signal_backend; + +-- ok, superuser can grant ADMIN on privileged roles +SET SESSION AUTHORIZATION regress_role_super; +GRANT pg_read_all_data, pg_write_all_data, pg_monitor, pg_read_all_settings, + pg_read_all_stats, pg_stat_scan_tables, pg_read_server_files, + pg_write_server_files, pg_execute_server_program, pg_signal_backend +TO regress_createrole WITH ADMIN OPTION; + +-- ok, having CREATEROLE plus ADMIN is enough to create roles in privileged roles +SET SESSION AUTHORIZATION regress_createrole; CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data; CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data; CREATE ROLE regress_monitor IN ROLE pg_monitor; @@ -98,7 +175,75 @@ DROP ROLE regress_nosuch_recursive; DROP ROLE regress_nosuch_admin_recursive; DROP ROLE regress_plainrole; +-- fail, cannot change attributes without ADMIN for them +SET SESSION AUTHORIZATION regress_role_minoradmin; +ALTER ROLE regress_role_login LOGIN; +ALTER ROLE regress_role_login NOLOGIN; +ALTER ROLE regress_role_login REPLICATION; +ALTER ROLE regress_role_login NOREPLICATION; +ALTER ROLE regress_role_login CREATEDB; +ALTER ROLE regress_role_login NOCREATEDB; +ALTER ROLE regress_role_login CREATEROLE; +ALTER ROLE regress_role_login NOCREATEROLE; +ALTER ROLE regress_role_login INHERIT; +ALTER ROLE regress_role_login NOINHERIT; +ALTER ROLE regress_role_login CONNECTION LIMIT 5; +ALTER ROLE regress_role_login VALID ALWAYS; +ALTER ROLE regress_role_login PASSWORD 'foobar'; + +-- ok, regress_role_admin got ADMIN on attributes by way of having CREATEROLE +SET SESSION AUTHORIZATION regress_role_admin; +ALTER ROLE regress_role_login LOGIN; +ALTER ROLE regress_role_login NOLOGIN; +ALTER ROLE regress_role_login CREATEDB; +ALTER ROLE regress_role_login NOCREATEDB; +ALTER ROLE regress_role_login CREATEROLE; +ALTER ROLE regress_role_login NOCREATEROLE; +ALTER ROLE regress_role_login INHERIT; +ALTER ROLE regress_role_login NOINHERIT; +ALTER ROLE regress_role_login CONNECTION LIMIT 5; +ALTER ROLE regress_role_login VALID ALWAYS; +ALTER ROLE regress_role_login PASSWORD 'foobar'; + +-- fail, regress_role_admin did not get ADMIN on these +ALTER ROLE regress_role_login SUPERUSER; +ALTER ROLE regress_role_login NOSUPERUSER; +ALTER ROLE regress_role_login REPLICATION; +ALTER ROLE regress_role_login NOREPLICATION; +ALTER ROLE regress_role_login BYPASSRLS; +ALTER ROLE regress_role_login NOBYPASSRLS; + +-- superuser can grant them now, though +RESET SESSION AUTHORIZATION; +ALTER ROLE regress_role_admin + NOREPLICATION WITH ADMIN OPTION + NOBYPASSRLS WITH ADMIN OPTION; + +-- ok, regress_role_admin can now grant these +SET SESSION AUTHORIZATION regress_role_admin; +ALTER ROLE regress_role_login REPLICATION; +ALTER ROLE regress_role_login NOREPLICATION; +ALTER ROLE regress_role_login BYPASSRLS; +ALTER ROLE regress_role_login NOBYPASSRLS; + +-- fail, but regress_role_admin still cannot grant this +ALTER ROLE regress_role_login SUPERUSER; +ALTER ROLE regress_role_login NOSUPERUSER; + +-- ok, regress_role_admin can grant attributes with ADMIN +ALTER ROLE regress_role_unprivileged LOGIN WITH ADMIN OPTION; +ALTER ROLE regress_role_unprivileged CREATEDB WITH ADMIN OPTION; +ALTER ROLE regress_role_unprivileged CREATEROLE WITH ADMIN OPTION; +ALTER ROLE regress_role_unprivileged INHERIT WITH ADMIN OPTION; +ALTER ROLE regress_role_unprivileged CONNECTION LIMIT 5 WITH ADMIN OPTION; +ALTER ROLE regress_role_unprivileged VALID ALWAYS WITH ADMIN OPTION; +ALTER ROLE regress_role_unprivileged PASSWORD 'foobar' WITH ADMIN OPTION; + -- ok, should be able to drop non-superuser roles we created +DROP ROLE regress_role_login; +DROP ROLE regress_role_replication; +DROP ROLE regress_role_login_replication; +DROP ROLE regress_role_unprivileged; DROP ROLE regress_createdb; DROP ROLE regress_createrole; DROP ROLE regress_login; @@ -134,5 +279,7 @@ DROP INDEX tenant_idx; DROP TABLE tenant_table; DROP VIEW tenant_view; DROP ROLE regress_tenant; +DROP ROLE regress_role_minoradmin; +DROP ROLE regress_role_repladmin; DROP ROLE regress_role_admin; DROP ROLE regress_role_super; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index c8c545b64c..8658b6e83c 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -72,6 +72,9 @@ CREATE GROUP regress_priv_group2 WITH USER regress_priv_user1, regress_priv_user ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4; ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- duplicate +ALTER GROUP regress_priv_group2 ADD USER regress_priv_user4, regress_priv_user2; -- duplicates +ALTER GROUP regress_priv_group2 ADD USER regress_priv_user5, regress_priv_user6, regress_priv_user7; +ALTER GROUP regress_priv_group2 DROP USER regress_priv_user7, regress_priv_user6, regress_priv_user5; ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; GRANT regress_priv_group2 TO regress_priv_user4 WITH ADMIN OPTION; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 89249ecc97..2bb87b3b45 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2260,6 +2260,7 @@ RmgrData RmgrDescData RmgrId RmgrIds +RoleElem RoleSpec RoleSpecType RoleStmtType -- 2.21.1 (Apple Git-122.3)