agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v27 4/5] Add ALTER INDEX ... ALTER COLLATION ... REFRESH VERSION. 6+ messages / 2 participants [nested] [flat]
* [PATCH v27 4/5] Add ALTER INDEX ... ALTER COLLATION ... REFRESH VERSION. @ 2019-12-11 12:54 Julien Rouhaud <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Julien Rouhaud @ 2019-12-11 12:54 UTC (permalink / raw) Allow privileged users to declare that the currently installed collation version, for a specific collation, is binary compatible with the one that was installed when the index was built. This provides a way to clear warnings about potentially corrupted indexes without having to use REINDEX. Author: Julien Rouhaud <[email protected]> Reviewed-by: Laurenz Albe <[email protected]> Reviewed-by: Thomas Munro <[email protected]> Reviewed-by: Peter Eisentraut <[email protected]> Discussion: https://postgr.es/m/CAEepm%3D0uEQCpfq_%2BLYFBdArCe4Ot98t1aR4eYiYTe%3DyavQygiQ%40mail.gmail.com --- doc/src/sgml/ref/alter_index.sgml | 17 +++++++ src/backend/catalog/index.c | 2 +- src/backend/commands/tablecmds.c | 47 +++++++++++++++++++ src/backend/nodes/copyfuncs.c | 1 + src/backend/parser/gram.y | 8 ++++ src/bin/psql/tab-complete.c | 27 ++++++++++- src/include/catalog/index.h | 3 ++ src/include/nodes/parsenodes.h | 4 +- .../regress/expected/collate.icu.utf8.out | 20 ++++++++ src/test/regress/sql/collate.icu.utf8.sql | 11 +++++ 10 files changed, 137 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml index a5e3b06ee4..03355164b3 100644 --- a/doc/src/sgml/ref/alter_index.sgml +++ b/doc/src/sgml/ref/alter_index.sgml @@ -25,6 +25,7 @@ ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENA ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ALTER INDEX <replaceable class="parameter">name</replaceable> ATTACH PARTITION <replaceable class="parameter">index_name</replaceable> ALTER INDEX <replaceable class="parameter">name</replaceable> DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable> +ALTER INDEX <replaceable class="parameter">name</replaceable> ALTER COLLATION <replaceable class="parameter">collation_name</replaceable> REFRESH VERSION ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RESET ( <replaceable class="parameter">storage_parameter</replaceable> [, ... ] ) ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="parameter">column_number</replaceable> @@ -112,6 +113,22 @@ ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> </listitem> </varlistentry> + <varlistentry> + <term><literal>ALTER COLLATION <replaceable class="parameter">collation_name</replaceable> REFRESH VERSION</literal></term> + <listitem> + <para> + This command declares that the index is compatible with the currently + installed version of a collation that determines its order. It is used + to silence warnings caused by collation version incompatibilities and + should be issued only if the collation ordering is known not to have + changed since the index was last built. Be aware that incorrect use of + this command can hide index corruption. If you don't know whether a + collation's definition has changed, using <xref linkend="sql-reindex"/> + is a safe alternative. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term> <listitem> diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 5cd3af3053..78bac65a7e 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -3130,7 +3130,7 @@ index_build(Relation heapRelation, SetUserIdAndSecContext(save_userid, save_sec_context); } -static char * +char * index_force_collation_version(const ObjectAddress *otherObject, const char *version, void *userdata) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index cd989c95e5..2f931e3ca4 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -93,6 +93,7 @@ #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/partcache.h" +#include "utils/pg_locale.h" #include "utils/relcache.h" #include "utils/ruleutils.h" #include "utils/snapmgr.h" @@ -556,6 +557,7 @@ static void refuseDupeIndexAttach(Relation parentIdx, Relation partIdx, Relation partitionTbl); static List *GetParentedForeignKeyRefs(Relation partition); static void ATDetachCheckNoForeignKeyRefs(Relation partition); +static void ATExecAlterCollationRefreshVersion(Relation rel, List *coll); /* ---------------------------------------------------------------- @@ -3926,6 +3928,10 @@ AlterTableGetLockLevel(List *cmds) cmd_lockmode = AccessShareLock; break; + case AT_AlterCollationRefreshVersion: + cmd_lockmode = AccessExclusiveLock; + break; + default: /* oops */ elog(ERROR, "unrecognized alter table type: %d", (int) cmd->subtype); @@ -4093,6 +4099,12 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, /* This command never recurses */ pass = AT_PASS_MISC; break; + case AT_AlterCollationRefreshVersion: /* ALTER COLLATION ... REFRESH + * VERSION */ + ATSimplePermissions(rel, ATT_INDEX); + /* This command never recurses */ + pass = AT_PASS_MISC; + break; case AT_SetStorage: /* ALTER COLUMN SET STORAGE */ ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW | ATT_FOREIGN_TABLE); ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); @@ -4659,6 +4671,11 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, Assert(rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE); ATExecDetachPartition(rel, ((PartitionCmd *) cmd->def)->name); break; + case AT_AlterCollationRefreshVersion: + /* ATPrepCmd ensured it must be an index */ + Assert(rel->rd_rel->relkind == RELKIND_INDEX); + ATExecAlterCollationRefreshVersion(rel, cmd->object); + break; default: /* oops */ elog(ERROR, "unrecognized alter table type: %d", (int) cmd->subtype); @@ -17426,3 +17443,33 @@ ATDetachCheckNoForeignKeyRefs(Relation partition) table_close(rel, NoLock); } } + +/* + * ALTER INDEX ... ALTER COLLATION ... REFRESH VERSION + * + * This overrides an existing dependency on a specific collation for a specific + * index to depend on the current collation version. + */ +static void +ATExecAlterCollationRefreshVersion(Relation rel, List *coll) +{ + ObjectAddress object; + NewCollationVersionDependency forced_dependency; + + Assert(coll != NIL); + forced_dependency.oid = get_collation_oid(coll, false); + + /* Retrieve the current version for the CURRENT VERSION case. */ + Assert(OidIsValid(forced_dependency.oid)); + forced_dependency.version = + get_collation_version_for_oid(forced_dependency.oid); + + object.classId = RelationRelationId; + object.objectId = rel->rd_id; + object.objectSubId = 0; + visitDependentObjects(&object, &index_force_collation_version, + &forced_dependency); + + /* Invalidate the index relcache */ + CacheInvalidateRelcache(rel); +} diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 2218f6e3db..db35add7bb 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3217,6 +3217,7 @@ _copyAlterTableCmd(const AlterTableCmd *from) COPY_SCALAR_FIELD(subtype); COPY_STRING_FIELD(name); + COPY_NODE_FIELD(object); COPY_SCALAR_FIELD(num); COPY_NODE_FIELD(newowner); COPY_NODE_FIELD(def); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index e432049f8e..3199f9e7e1 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -2594,6 +2594,14 @@ alter_table_cmd: n->subtype = AT_NoForceRowSecurity; $$ = (Node *)n; } + /* ALTER INDEX <name> ALTER COLLATION ... REFRESH VERSION */ + | ALTER COLLATION any_name REFRESH VERSION_P + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_AlterCollationRefreshVersion; + n->object = $3; + $$ = (Node *)n; + } | alter_generic_options { AlterTableCmd *n = makeNode(AlterTableCmd); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index f41785f11c..71e982ffa0 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -45,6 +45,7 @@ #include "catalog/pg_am_d.h" #include "catalog/pg_class_d.h" +#include "catalog/pg_collation_d.h" #include "common.h" #include "libpq-fe.h" #include "pqexpbuffer.h" @@ -820,6 +821,20 @@ static const SchemaQuery Query_for_list_of_statistics = { " (SELECT tgrelid FROM pg_catalog.pg_trigger "\ " WHERE pg_catalog.quote_ident(tgname)='%s')" +/* the silly-looking length condition is just to eat up the current word */ +#define Query_for_list_of_colls_for_one_index \ +" SELECT DISTINCT pg_catalog.quote_ident(coll.collname) " \ +" FROM pg_catalog.pg_depend d, pg_catalog.pg_collation coll, " \ +" pg_catalog.pg_class c" \ +" WHERE (%d = pg_catalog.length('%s'))" \ +" AND d.refclassid = " CppAsString2(CollationRelationId) \ +" AND d.refobjid = coll.oid " \ +" AND d.classid = " CppAsString2(RelationRelationId) \ +" AND d.objid = c.oid " \ +" AND c.relkind = " CppAsString2(RELKIND_INDEX) \ +" AND pg_catalog.pg_table_is_visible(c.oid) " \ +" AND c.relname = '%s'" + #define Query_for_list_of_ts_configurations \ "SELECT pg_catalog.quote_ident(cfgname) FROM pg_catalog.pg_ts_config "\ " WHERE substring(pg_catalog.quote_ident(cfgname),1,%d)='%s'" @@ -1715,7 +1730,8 @@ psql_completion(const char *text, int start, int end) /* ALTER INDEX <name> */ else if (Matches("ALTER", "INDEX", MatchAny)) COMPLETE_WITH("ALTER COLUMN", "OWNER TO", "RENAME TO", "SET", - "RESET", "ATTACH PARTITION", "DEPENDS", "NO DEPENDS"); + "RESET", "ATTACH PARTITION", "DEPENDS", "NO DEPENDS", + "ALTER COLLATION"); else if (Matches("ALTER", "INDEX", MatchAny, "ATTACH")) COMPLETE_WITH("PARTITION"); else if (Matches("ALTER", "INDEX", MatchAny, "ATTACH", "PARTITION")) @@ -1765,6 +1781,15 @@ psql_completion(const char *text, int start, int end) COMPLETE_WITH("ON EXTENSION"); else if (Matches("ALTER", "INDEX", MatchAny, "DEPENDS")) COMPLETE_WITH("ON EXTENSION"); + /* ALTER INDEX <name> ALTER COLLATION */ + else if (Matches("ALTER", "INDEX", MatchAny, "ALTER", "COLLATION")) + { + completion_info_charp = prev4_wd; + COMPLETE_WITH_QUERY(Query_for_list_of_colls_for_one_index); + } + /* ALTER INDEX <name> ALTER COLLATION <name> */ + else if (Matches("ALTER", "INDEX", MatchAny, "ALTER", "COLLATION", MatchAny)) + COMPLETE_WITH("REFRESH VERSION"); /* ALTER LANGUAGE <name> */ else if (Matches("ALTER", "LANGUAGE", MatchAny)) diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 9b4de26514..46d5df1613 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -123,6 +123,9 @@ extern void FormIndexDatum(IndexInfo *indexInfo, extern void index_check_collation_versions(Oid relid); +extern char *index_force_collation_version(const ObjectAddress *otherObject, + const char *version, + void *userdata); extern void index_force_collation_versions(Oid indexid, Oid coll, char *version); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index a8da438d22..0370ad6361 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1847,7 +1847,8 @@ typedef enum AlterTableType AT_DetachPartition, /* DETACH PARTITION */ AT_AddIdentity, /* ADD IDENTITY */ AT_SetIdentity, /* SET identity column options */ - AT_DropIdentity /* DROP IDENTITY */ + AT_DropIdentity, /* DROP IDENTITY */ + AT_AlterCollationRefreshVersion /* ALTER COLLATION ... REFRESH VERSION */ } AlterTableType; typedef struct ReplicaIdentityStmt @@ -1863,6 +1864,7 @@ typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */ AlterTableType subtype; /* Type of table alteration to apply */ char *name; /* column, constraint, or trigger to act on, * or tablespace */ + List *object; /* collation to act on if it's a collation */ int16 num; /* attribute number for columns referenced by * number */ RoleSpec *newowner; diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out index 0075e85072..1843d71a38 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -2054,6 +2054,26 @@ SELECT objid::regclass FROM pg_depend WHERE refobjversion = 'not a version'; ------- (0 rows) +-- Test ALTER INDEX name ALTER COLLATION name REFRESH VERSION +UPDATE pg_depend SET refobjversion = 'not a version' +WHERE refclassid = 'pg_collation'::regclass +AND objid::regclass::text = 'icuidx17_part' +AND refobjversion IS NOT NULL; +SELECT objid::regclass FROM pg_depend WHERE refobjversion = 'not a version'; + objid +--------------- + icuidx17_part +(1 row) + +ALTER INDEX icuidx17_part ALTER COLLATION "en-x-icu" REFRESH VERSION; +SELECT objid::regclass, refobjversion = 'not a version' AS ver FROM pg_depend +WHERE refclassid = 'pg_collation'::regclass +AND objid::regclass::text = 'icuidx17_part'; + objid | ver +---------------+----- + icuidx17_part | f +(1 row) + -- cleanup RESET search_path; SET client_min_messages TO warning; diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql index e93530af55..b3a75b29e6 100644 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@ -823,6 +823,17 @@ VACUUM FULL collate_part_1; SELECT objid::regclass FROM pg_depend WHERE refobjversion = 'not a version'; +-- Test ALTER INDEX name ALTER COLLATION name REFRESH VERSION +UPDATE pg_depend SET refobjversion = 'not a version' +WHERE refclassid = 'pg_collation'::regclass +AND objid::regclass::text = 'icuidx17_part' +AND refobjversion IS NOT NULL; +SELECT objid::regclass FROM pg_depend WHERE refobjversion = 'not a version'; +ALTER INDEX icuidx17_part ALTER COLLATION "en-x-icu" REFRESH VERSION; +SELECT objid::regclass, refobjversion = 'not a version' AS ver FROM pg_depend +WHERE refclassid = 'pg_collation'::regclass +AND objid::regclass::text = 'icuidx17_part'; + -- cleanup RESET search_path; SET client_min_messages TO warning; -- 2.20.1 --o56keubw4jpehgac Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="v27-0005-Doc-Add-Collation-Versions-section.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v5 1/2] Improve user.c error messages. @ 2023-01-26 19:05 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Nathan Bossart @ 2023-01-26 19:05 UTC (permalink / raw) --- src/backend/commands/user.c | 169 ++++++++++++++++------ src/test/regress/expected/create_role.out | 77 ++++++---- src/test/regress/expected/dependency.out | 4 + src/test/regress/expected/privileges.out | 23 ++- 4 files changed, 198 insertions(+), 75 deletions(-) diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index 3a92e930c0..3d0b8b9ea6 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -316,23 +316,33 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) if (!has_createrole_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to create role"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles.", + "CREATEROLE"))); if (issuper) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to create superusers"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "SUPERUSER", "SUPERUSER"))); if (createdb && !have_createdb_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have createdb permission to create createdb users"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "CREATEDB", "CREATEDB"))); if (isreplication && !has_rolreplication(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have replication permission to create replication users"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "REPLICATION", "REPLICATION"))); if (bypassrls && !has_bypassrls_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have bypassrls to create bypassrls users"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "BYPASSRLS", "BYPASSRLS"))); } /* @@ -744,10 +754,18 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) roleid = authform->oid; /* To mess with a superuser in any way you gotta be superuser. */ - if (!superuser() && (authform->rolsuper || dissuper)) + if (!superuser() && authform->rolsuper) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superuser roles or change superuser attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to alter roles with %s.", + "SUPERUSER", "SUPERUSER"))); + if (!superuser() && dissuper) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "SUPERUSER", "SUPERUSER"))); /* * Most changes to a role require that you both have CREATEROLE privileges @@ -761,13 +779,17 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) dvalidUntil || disreplication || dbypassRLS) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATEROLE", "ADMIN OPTION", rolename))); /* an unprivileged user can change their own password */ if (dpassword && roleid != currentUserId) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have CREATEROLE privilege to change another user's password"))); + errmsg("permission denied to alter role"), + errdetail("To change another role's password, you must have %s privilege and %s on the role.", + "CREATEROLE", "ADMIN OPTION"))); } else if (!superuser()) { @@ -779,23 +801,30 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (dcreatedb && !have_createdb_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have createdb privilege to change createdb attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "CREATEDB", "CREATEDB"))); if (disreplication && !has_rolreplication(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have replication privilege to change replication attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "REPLICATION", "REPLICATION"))); if (dbypassRLS && !has_bypassrls_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have bypassrls privilege to change bypassrls attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "BYPASSRLS", "BYPASSRLS"))); } /* To add members to a role, you need ADMIN OPTION. */ if (drolemembers && !is_admin_of_role(currentUserId, roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\" to add members", - rolename))); + errmsg("permission denied to alter role"), + errdetail("You must have %s on role \"%s\" to add members.", + "ADMIN OPTION", rolename))); /* Convert validuntil to internal form */ if (dvalidUntil) @@ -837,8 +866,10 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (!should_be_super && roleid == BOOTSTRAP_SUPERUSERID) ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied: bootstrap user must be superuser"))); + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("permission denied to alter role"), + errdetail("The bootstrap user must have %s privilege.", + "SUPERUSER"))); new_record[Anum_pg_authid_rolsuper - 1] = BoolGetDatum(should_be_super); new_record_repl[Anum_pg_authid_rolsuper - 1] = true; @@ -999,7 +1030,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superusers"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to alter roles with %s.", + "SUPERUSER", "SUPERUSER"))); } else { @@ -1008,7 +1041,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) && roleid != GetUserId()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATROLE", "ADMIN OPTION", NameStr(roleform->rolname)))); } ReleaseSysCache(roletuple); @@ -1038,7 +1073,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter settings globally"))); + errmsg("permission denied to alter setting"), + errdetail("You must have %s privilege to alter settings globally.", + "SUPERUSER"))); } AlterSetting(databaseid, roleid, stmt->setstmt); @@ -1061,7 +1098,9 @@ DropRole(DropRoleStmt *stmt) if (!have_createrole_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to drop role"))); + errmsg("permission denied to drop role"), + errdetail("You must have %s privilege and %s on the target roles.", + "CREATEROLE", "ADMIN OPTION"))); /* * Scan the pg_authid relation to find the Oid of the role(s) to be @@ -1131,12 +1170,15 @@ DropRole(DropRoleStmt *stmt) if (roleform->rolsuper && !superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to drop superusers"))); + errmsg("permission denied to drop role"), + errdetail("You must have %s privilege to drop roles with %s.", + "SUPERUSER", "SUPERUSER"))); if (!is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\"", - role))); + errmsg("permission denied to drop role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATEROLE", "ADMIN OPTION", NameStr(roleform->rolname)))); /* DROP hook for the role being removed */ InvokeObjectDropHook(AuthIdRelationId, roleid, 0); @@ -1378,12 +1420,14 @@ RenameRole(const char *oldname, const char *newname) * Only superusers can mess with superusers. Otherwise, a user with * CREATEROLE can rename a role for which they have ADMIN OPTION. */ - if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper) + if (authform->rolsuper) { if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to rename superusers"))); + errmsg("permission denied to rename role"), + errdetail("You must have %s privilege to rename roles with %s.", + "SUPERUSER", "SUPERUSER"))); } else { @@ -1391,7 +1435,9 @@ RenameRole(const char *oldname, const char *newname) !is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to rename role"))); + errmsg("permission denied to rename role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATEROLE", "ADMIN OPTION", NameStr(authform->rolname)))); } /* OK, construct the modified tuple */ @@ -1554,7 +1600,9 @@ DropOwnedObjects(DropOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to drop objects"))); + errmsg("permission denied to drop objects"), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(roleid, false)))); } /* Ok, do it */ @@ -1581,7 +1629,9 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to reassign objects"))); + errmsg("permission denied to reassign objects"), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(roleid, false)))); } /* Must have privileges on the receiving side too */ @@ -1590,7 +1640,9 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), newrole)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to reassign objects"))); + errmsg("permission denied to reassign objects"), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(newrole, false)))); /* Ok, do it */ shdepReassignOwned(role_ids, newrole); @@ -1738,7 +1790,8 @@ AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid, if (memberid == BOOTSTRAP_SUPERUSERID) ereport(ERROR, (errcode(ERRCODE_INVALID_GRANT_OPERATION), - errmsg("admin option cannot be granted back to your own grantor"))); + errmsg("%s cannot be granted back to your own grantor", + "ADMIN OPTION"))); plan_member_revoke(memlist, actions, memberid); } @@ -1763,7 +1816,8 @@ AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid, if (i >= memlist->n_members) ereport(ERROR, (errcode(ERRCODE_INVALID_GRANT_OPERATION), - errmsg("admin option cannot be granted back to your own grantor"))); + errmsg("%s cannot be granted back to your own grantor", + "ADMIN OPTION"))); ReleaseSysCacheList(memlist); } @@ -2081,9 +2135,22 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid, if (superuser_arg(roleid)) { if (!superuser_arg(currentUserId)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superusers"))); + { + if (is_grant) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to grant role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s privilege to grant roles with %s.", + "SUPERUSER", "SUPERUSER"))); + else + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to revoke role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s privilege to revoke roles with %s.", + "SUPERUSER", "SUPERUSER"))); + } } else { @@ -2091,10 +2158,22 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid, * Otherwise, must have admin option on the role to be changed. */ if (!is_admin_of_role(currentUserId, roleid)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\"", - GetUserNameFromId(roleid, false)))); + { + if (is_grant) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to grant role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s on role \"%s\".", + "ADMIN OPTION", GetUserNameFromId(roleid, false)))); + else + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to revoke role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s on role \"%s\".", + "ADMIN OPTION", GetUserNameFromId(roleid, false)))); + } } } @@ -2173,14 +2252,18 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to grant privileges as role \"%s\"", - GetUserNameFromId(grantorId, false)))); + GetUserNameFromId(grantorId, false)), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(grantorId, false)))); if (grantorId != BOOTSTRAP_SUPERUSERID && select_best_admin(grantorId, roleid) != grantorId) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("grantor must have ADMIN OPTION on \"%s\"", - GetUserNameFromId(roleid, false)))); + errmsg("permission denied to grant privileges as role \"%s\"", + GetUserNameFromId(grantorId, false)), + errdetail("The grantor must have %s on role \"%s\".", + "ADMIN OPTION", GetUserNameFromId(roleid, false)))); } else { @@ -2188,7 +2271,9 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to revoke privileges granted by role \"%s\"", - GetUserNameFromId(grantorId, false)))); + GetUserNameFromId(grantorId, false)), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(grantorId, false)))); } /* diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out index 9f431bd4f5..2a7d14dba9 100644 --- a/src/test/regress/expected/create_role.out +++ b/src/test/regress/expected/create_role.out @@ -7,26 +7,35 @@ CREATE ROLE regress_role_normal; -- fail, CREATEROLE user can't give away role attributes without having them SET SESSION AUTHORIZATION regress_role_limited_admin; CREATE ROLE regress_nosuch_superuser SUPERUSER; -ERROR: must be superuser to create superusers +ERROR: permission denied to create role +DETAIL: You must have SUPERUSER privilege to create roles with SUPERUSER. CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS; -ERROR: must have replication permission to create replication users +ERROR: permission denied to create role +DETAIL: You must have REPLICATION privilege to create roles with REPLICATION. CREATE ROLE regress_nosuch_replication REPLICATION; -ERROR: must have replication permission to create replication users +ERROR: permission denied to create role +DETAIL: You must have REPLICATION privilege to create roles with REPLICATION. CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; -ERROR: must have bypassrls to create bypassrls users +ERROR: permission denied to create role +DETAIL: You must have BYPASSRLS privilege to create roles with BYPASSRLS. CREATE ROLE regress_nosuch_createdb CREATEDB; -ERROR: must have createdb permission to create createdb users +ERROR: permission denied to create role +DETAIL: You must have CREATEDB privilege to create roles with CREATEDB. -- ok, can create a role without any special attributes CREATE ROLE regress_role_limited; -- fail, can't give it in any of the restricted attributes ALTER ROLE regress_role_limited SUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have SUPERUSER privilege to change the SUPERUSER attribute. ALTER ROLE regress_role_limited REPLICATION; -ERROR: must have replication privilege to change replication attribute +ERROR: permission denied to alter role +DETAIL: You must have REPLICATION privilege to change the REPLICATION attribute. ALTER ROLE regress_role_limited CREATEDB; -ERROR: must have createdb privilege to change createdb attribute +ERROR: permission denied to alter role +DETAIL: You must have CREATEDB privilege to change the CREATEDB attribute. ALTER ROLE regress_role_limited BYPASSRLS; -ERROR: must have bypassrls privilege to change bypassrls attribute +ERROR: permission denied to alter role +DETAIL: You must have BYPASSRLS privilege to change the BYPASSRLS attribute. DROP ROLE regress_role_limited; -- ok, can give away these role attributes if you have them SET SESSION AUTHORIZATION regress_role_admin; @@ -43,9 +52,11 @@ ALTER ROLE regress_createdb NOCREATEDB; ALTER ROLE regress_createdb CREATEDB; -- fail, can't toggle SUPERUSER ALTER ROLE regress_createdb SUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have SUPERUSER privilege to change the SUPERUSER attribute. ALTER ROLE regress_createdb NOSUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have SUPERUSER privilege to change the SUPERUSER attribute. -- ok, having CREATEROLE is enough to create users with these privileges CREATE ROLE regress_createrole CREATEROLE NOINHERIT; GRANT CREATE ON DATABASE regression TO regress_createrole WITH GRANT OPTION; @@ -59,7 +70,8 @@ CREATE ROLE regress_noiseword SYSID 12345; 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 +ERROR: permission denied to grant role "regress_role_super" +DETAIL: You must have SUPERUSER privilege to grant roles with SUPERUSER. -- fail, database owner cannot have members CREATE ROLE regress_nosuch_dbowner IN ROLE pg_database_owner; ERROR: role "pg_database_owner" cannot have explicit members @@ -97,8 +109,10 @@ COMMENT ON ROLE regress_role_normal IS 'some comment'; ERROR: must have admin option on role "regress_role_normal" ALTER ROLE regress_role_normal RENAME TO regress_role_abnormal; ERROR: permission denied to rename role +DETAIL: You must have CREATEROLE privilege and ADMIN OPTION on role "regress_role_normal". ALTER ROLE regress_role_normal NOINHERIT NOLOGIN CONNECTION LIMIT 7; -ERROR: permission denied +ERROR: permission denied to alter role +DETAIL: You must have CREATEROLE privilege and ADMIN OPTION on role "regress_role_normal". -- ok, regress_tenant can create objects within the database SET SESSION AUTHORIZATION regress_tenant; CREATE TABLE tenant_table (i integer); @@ -123,6 +137,7 @@ ERROR: must be able to SET ROLE "regress_tenant" -- fail, we don't inherit permissions from regress_tenant REASSIGN OWNED BY regress_tenant TO regress_createrole; ERROR: permission denied to reassign objects +DETAIL: You must have privileges of role "regress_tenant". -- ok, create a role with a value for createrole_self_grant SET createrole_self_grant = 'set, inherit'; CREATE ROLE regress_tenant2; @@ -150,25 +165,35 @@ ERROR: must be able to SET ROLE "regress_tenant2" DROP TABLE tenant2_table; -- fail, CREATEROLE is not enough to create roles in privileged roles CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data; -ERROR: must have admin option on role "pg_read_all_data" +ERROR: permission denied to grant role "pg_read_all_data" +DETAIL: You must have ADMIN OPTION on role "pg_read_all_data". CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data; -ERROR: must have admin option on role "pg_write_all_data" +ERROR: permission denied to grant role "pg_write_all_data" +DETAIL: You must have ADMIN OPTION on role "pg_write_all_data". CREATE ROLE regress_monitor IN ROLE pg_monitor; -ERROR: must have admin option on role "pg_monitor" +ERROR: permission denied to grant role "pg_monitor" +DETAIL: You must have ADMIN OPTION on role "pg_monitor". CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings; -ERROR: must have admin option on role "pg_read_all_settings" +ERROR: permission denied to grant role "pg_read_all_settings" +DETAIL: You must have ADMIN OPTION on role "pg_read_all_settings". CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats; -ERROR: must have admin option on role "pg_read_all_stats" +ERROR: permission denied to grant role "pg_read_all_stats" +DETAIL: You must have ADMIN OPTION on role "pg_read_all_stats". CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables; -ERROR: must have admin option on role "pg_stat_scan_tables" +ERROR: permission denied to grant role "pg_stat_scan_tables" +DETAIL: You must have ADMIN OPTION on role "pg_stat_scan_tables". CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files; -ERROR: must have admin option on role "pg_read_server_files" +ERROR: permission denied to grant role "pg_read_server_files" +DETAIL: You must have ADMIN OPTION on role "pg_read_server_files". CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files; -ERROR: must have admin option on role "pg_write_server_files" +ERROR: permission denied to grant role "pg_write_server_files" +DETAIL: You must have ADMIN OPTION on role "pg_write_server_files". CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program; -ERROR: must have admin option on role "pg_execute_server_program" +ERROR: permission denied to grant role "pg_execute_server_program" +DETAIL: You must have ADMIN OPTION on role "pg_execute_server_program". CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend; -ERROR: must have admin option on role "pg_signal_backend" +ERROR: permission denied to grant role "pg_signal_backend" +DETAIL: You must have ADMIN OPTION on role "pg_signal_backend". -- fail, role still owns database objects DROP ROLE regress_tenant; ERROR: role "regress_tenant" cannot be dropped because some objects depend on it @@ -211,11 +236,13 @@ DROP ROLE regress_inroles; DROP ROLE regress_adminroles; -- fail, cannot drop ourself, nor superusers or roles we lack ADMIN for DROP ROLE regress_role_super; -ERROR: must be superuser to drop superusers +ERROR: permission denied to drop role +DETAIL: You must have SUPERUSER privilege to drop roles with SUPERUSER. DROP ROLE regress_role_admin; ERROR: current user cannot be dropped DROP ROLE regress_rolecreator; -ERROR: must have admin option on role "regress_rolecreator" +ERROR: permission denied to drop role +DETAIL: You must have CREATEROLE privilege and ADMIN OPTION on role "regress_rolecreator". -- ok RESET SESSION AUTHORIZATION; REVOKE CREATE ON DATABASE regression FROM regress_role_admin CASCADE; diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out index 520035f6a0..78ee4d7448 100644 --- a/src/test/regress/expected/dependency.out +++ b/src/test/regress/expected/dependency.out @@ -48,12 +48,16 @@ SET SESSION AUTHORIZATION regress_dep_user0; -- permission denied DROP OWNED BY regress_dep_user1; ERROR: permission denied to drop objects +DETAIL: You must have privileges of role "regress_dep_user1". DROP OWNED BY regress_dep_user0, regress_dep_user2; ERROR: permission denied to drop objects +DETAIL: You must have privileges of role "regress_dep_user2". REASSIGN OWNED BY regress_dep_user0 TO regress_dep_user1; ERROR: permission denied to reassign objects +DETAIL: You must have privileges of role "regress_dep_user1". REASSIGN OWNED BY regress_dep_user1 TO regress_dep_user0; ERROR: permission denied to reassign objects +DETAIL: You must have privileges of role "regress_dep_user1". -- this one is allowed DROP OWNED BY regress_dep_user0; CREATE TABLE deptest1 (f1 int unique); diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 95d1e5515f..6794645e56 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -37,7 +37,7 @@ CREATE ROLE regress_priv_role; GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION; GRANT regress_priv_user1 TO regress_priv_user3 WITH ADMIN OPTION GRANTED BY regress_priv_user2; GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION GRANTED BY regress_priv_user3; -ERROR: admin option cannot be granted back to your own grantor +ERROR: ADMIN OPTION cannot be granted back to your own grantor -- need CASCADE to revoke grant or admin option if dependent grants exist REVOKE ADMIN OPTION FOR regress_priv_user1 FROM regress_priv_user2; -- fail ERROR: dependent privileges exist @@ -156,7 +156,8 @@ ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- duplicate NOTICE: role "regress_priv_user2" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user1" ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; ALTER USER regress_priv_user2 PASSWORD 'verysecret'; -- not permitted -ERROR: must have CREATEROLE privilege to change another user's password +ERROR: permission denied to alter role +DETAIL: To change another role's password, you must have CREATEROLE privilege and ADMIN OPTION on the role. RESET SESSION AUTHORIZATION; ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; REVOKE ADMIN OPTION FOR regress_priv_group2 FROM regress_priv_user1; @@ -168,7 +169,8 @@ CREATE FUNCTION leak(integer,integer) RETURNS boolean ALTER FUNCTION leak(integer,integer) OWNER TO regress_priv_user1; -- test owner privileges GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY regress_priv_role; -- error, doesn't have ADMIN OPTION -ERROR: grantor must have ADMIN OPTION on "regress_priv_role" +ERROR: permission denied to grant privileges as role "regress_priv_role" +DETAIL: The grantor must have ADMIN OPTION on role "regress_priv_role". GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY CURRENT_ROLE; REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY foo; -- error ERROR: role "foo" does not exist @@ -1795,7 +1797,8 @@ REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation CONTEXT: SQL function "mv_action" statement 1 BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". CONTEXT: SQL function "unwanted_grant" statement 1 SQL statement "SELECT unwanted_grant()" PL/pgSQL function sro_trojan() line 1 at PERFORM @@ -1825,10 +1828,12 @@ CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION SET ROLE regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended privilege -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_user1; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN NOTICE: role "regress_priv_user5" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user4" dogrant_ok @@ -1838,10 +1843,12 @@ NOTICE: role "regress_priv_user5" has already been granted membership in role " SET ROLE regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no self-admin -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_user4; DROP FUNCTION dogrant_ok(); REVOKE regress_priv_group2 FROM regress_priv_user5; -- 2.25.1 --Qxx1br4bt0+wmkIi Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v5-0002-Improve-more-insufficient-privileges-error-messag.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v2 1/1] Improve user.c error messages. @ 2023-01-26 19:05 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Nathan Bossart @ 2023-01-26 19:05 UTC (permalink / raw) --- src/backend/commands/user.c | 171 ++++++++++++++++------ src/test/regress/expected/create_role.out | 77 ++++++---- src/test/regress/expected/dependency.out | 4 + src/test/regress/expected/privileges.out | 23 ++- 4 files changed, 195 insertions(+), 80 deletions(-) diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index 3a92e930c0..e2c80f5060 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -316,23 +316,33 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) if (!has_createrole_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to create role"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles.", + "CREATEROLE"))); if (issuper) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to create superusers"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "SUPERUSER", "SUPERUSER"))); if (createdb && !have_createdb_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have createdb permission to create createdb users"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "CREATEDB", "CREATEDB"))); if (isreplication && !has_rolreplication(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have replication permission to create replication users"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "REPLICATION", "REPLICATION"))); if (bypassrls && !has_bypassrls_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have bypassrls to create bypassrls users"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "BYPASSRLS", "BYPASSRLS"))); } /* @@ -744,10 +754,18 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) roleid = authform->oid; /* To mess with a superuser in any way you gotta be superuser. */ - if (!superuser() && (authform->rolsuper || dissuper)) + if (!superuser() && authform->rolsuper) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superuser roles or change superuser attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to alter roles with %s.", + "SUPERUSER", "SUPERUSER"))); + if (!superuser() && dissuper) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "SUPERUSER", "SUPERUSER"))); /* * Most changes to a role require that you both have CREATEROLE privileges @@ -758,16 +776,13 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) { /* things an unprivileged user certainly can't do */ if (dinherit || dcreaterole || dcreatedb || dcanlogin || dconnlimit || - dvalidUntil || disreplication || dbypassRLS) + dvalidUntil || disreplication || dbypassRLS || + (dpassword && roleid != currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied"))); - - /* an unprivileged user can change their own password */ - if (dpassword && roleid != currentUserId) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have CREATEROLE privilege to change another user's password"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATEROLE", "ADMIN OPTION", rolename))); } else if (!superuser()) { @@ -779,23 +794,30 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (dcreatedb && !have_createdb_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have createdb privilege to change createdb attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "CREATEDB", "CREATEDB"))); if (disreplication && !has_rolreplication(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have replication privilege to change replication attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "REPLICATION", "REPLICATION"))); if (dbypassRLS && !has_bypassrls_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have bypassrls privilege to change bypassrls attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "BYPASSRLS", "BYPASSRLS"))); } /* To add members to a role, you need ADMIN OPTION. */ if (drolemembers && !is_admin_of_role(currentUserId, roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\" to add members", - rolename))); + errmsg("permission denied to alter role"), + errdetail("You must have %s on role \"%s\" to add members.", + "ADMIN OPTION", rolename))); /* Convert validuntil to internal form */ if (dvalidUntil) @@ -838,7 +860,8 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (!should_be_super && roleid == BOOTSTRAP_SUPERUSERID) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied: bootstrap user must be superuser"))); + errmsg("permission denied to alter role"), + errdetail("The bootstrap user must be superuser."))); new_record[Anum_pg_authid_rolsuper - 1] = BoolGetDatum(should_be_super); new_record_repl[Anum_pg_authid_rolsuper - 1] = true; @@ -999,7 +1022,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superusers"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to alter roles with %s.", + "SUPERUSER", "SUPERUSER"))); } else { @@ -1008,7 +1033,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) && roleid != GetUserId()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATROLE", "ADMIN OPTION", NameStr(roleform->rolname)))); } ReleaseSysCache(roletuple); @@ -1038,7 +1065,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter settings globally"))); + errmsg("permission denied to alter setting"), + errdetail("You must have %s privilege to alter settings globally.", + "SUPERUSER"))); } AlterSetting(databaseid, roleid, stmt->setstmt); @@ -1061,7 +1090,9 @@ DropRole(DropRoleStmt *stmt) if (!have_createrole_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to drop role"))); + errmsg("permission denied to drop role"), + errdetail("You must have %s privilege and %s on the target roles.", + "CREATEROLE", "ADMIN OPTION"))); /* * Scan the pg_authid relation to find the Oid of the role(s) to be @@ -1131,12 +1162,15 @@ DropRole(DropRoleStmt *stmt) if (roleform->rolsuper && !superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to drop superusers"))); + errmsg("permission denied to drop role"), + errdetail("You must have %s privilege to drop roles with %s.", + "SUPERUSER", "SUPERUSER"))); if (!is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\"", - role))); + errmsg("permission denied to drop role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATEROLE", "ADMIN OPTION", NameStr(roleform->rolname)))); /* DROP hook for the role being removed */ InvokeObjectDropHook(AuthIdRelationId, roleid, 0); @@ -1378,12 +1412,14 @@ RenameRole(const char *oldname, const char *newname) * Only superusers can mess with superusers. Otherwise, a user with * CREATEROLE can rename a role for which they have ADMIN OPTION. */ - if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper) + if (authform->rolsuper) { if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to rename superusers"))); + errmsg("permission denied to rename role"), + errdetail("You must have %s privilege to rename roles with %s.", + "SUPERUSER", "SUPERUSER"))); } else { @@ -1391,7 +1427,9 @@ RenameRole(const char *oldname, const char *newname) !is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to rename role"))); + errmsg("permission denied to rename role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATEROLE", "ADMIN OPTION", NameStr(authform->rolname)))); } /* OK, construct the modified tuple */ @@ -1554,7 +1592,9 @@ DropOwnedObjects(DropOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to drop objects"))); + errmsg("permission denied to drop objects"), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(roleid, false)))); } /* Ok, do it */ @@ -1581,7 +1621,9 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to reassign objects"))); + errmsg("permission denied to reassign objects"), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(roleid, false)))); } /* Must have privileges on the receiving side too */ @@ -1590,7 +1632,9 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), newrole)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to reassign objects"))); + errmsg("permission denied to reassign objects"), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(newrole, false)))); /* Ok, do it */ shdepReassignOwned(role_ids, newrole); @@ -1738,7 +1782,8 @@ AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid, if (memberid == BOOTSTRAP_SUPERUSERID) ereport(ERROR, (errcode(ERRCODE_INVALID_GRANT_OPERATION), - errmsg("admin option cannot be granted back to your own grantor"))); + errmsg("%s cannot be granted back to your own grantor", + "ADMIN OPTION"))); plan_member_revoke(memlist, actions, memberid); } @@ -1763,7 +1808,8 @@ AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid, if (i >= memlist->n_members) ereport(ERROR, (errcode(ERRCODE_INVALID_GRANT_OPERATION), - errmsg("admin option cannot be granted back to your own grantor"))); + errmsg("%s cannot be granted back to your own grantor", + "ADMIN OPTION"))); ReleaseSysCacheList(memlist); } @@ -2081,9 +2127,22 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid, if (superuser_arg(roleid)) { if (!superuser_arg(currentUserId)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superusers"))); + { + if (is_grant) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to grant role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s privilege to grant roles with %s.", + "SUPERUSER", "SUPERUSER"))); + else + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to revoke role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s privilege to revoke roles with %s.", + "SUPERUSER", "SUPERUSER"))); + } } else { @@ -2091,10 +2150,22 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid, * Otherwise, must have admin option on the role to be changed. */ if (!is_admin_of_role(currentUserId, roleid)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\"", - GetUserNameFromId(roleid, false)))); + { + if (is_grant) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to grant role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s on role \"%s\".", + "ADMIN OPTION", GetUserNameFromId(roleid, false)))); + else + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to revoke role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s on role \"%s\".", + "ADMIN OPTION", GetUserNameFromId(roleid, false)))); + } } } @@ -2173,14 +2244,18 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to grant privileges as role \"%s\"", - GetUserNameFromId(grantorId, false)))); + GetUserNameFromId(grantorId, false)), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(grantorId, false)))); if (grantorId != BOOTSTRAP_SUPERUSERID && select_best_admin(grantorId, roleid) != grantorId) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("grantor must have ADMIN OPTION on \"%s\"", - GetUserNameFromId(roleid, false)))); + errmsg("permission denied to grant privileges as role \"%s\"", + GetUserNameFromId(grantorId, false)), + errdetail("The grantor must have %s on role \"%s\".", + "ADMIN OPTION", GetUserNameFromId(roleid, false)))); } else { @@ -2188,7 +2263,9 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to revoke privileges granted by role \"%s\"", - GetUserNameFromId(grantorId, false)))); + GetUserNameFromId(grantorId, false)), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(grantorId, false)))); } /* diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out index 9f431bd4f5..2a7d14dba9 100644 --- a/src/test/regress/expected/create_role.out +++ b/src/test/regress/expected/create_role.out @@ -7,26 +7,35 @@ CREATE ROLE regress_role_normal; -- fail, CREATEROLE user can't give away role attributes without having them SET SESSION AUTHORIZATION regress_role_limited_admin; CREATE ROLE regress_nosuch_superuser SUPERUSER; -ERROR: must be superuser to create superusers +ERROR: permission denied to create role +DETAIL: You must have SUPERUSER privilege to create roles with SUPERUSER. CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS; -ERROR: must have replication permission to create replication users +ERROR: permission denied to create role +DETAIL: You must have REPLICATION privilege to create roles with REPLICATION. CREATE ROLE regress_nosuch_replication REPLICATION; -ERROR: must have replication permission to create replication users +ERROR: permission denied to create role +DETAIL: You must have REPLICATION privilege to create roles with REPLICATION. CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; -ERROR: must have bypassrls to create bypassrls users +ERROR: permission denied to create role +DETAIL: You must have BYPASSRLS privilege to create roles with BYPASSRLS. CREATE ROLE regress_nosuch_createdb CREATEDB; -ERROR: must have createdb permission to create createdb users +ERROR: permission denied to create role +DETAIL: You must have CREATEDB privilege to create roles with CREATEDB. -- ok, can create a role without any special attributes CREATE ROLE regress_role_limited; -- fail, can't give it in any of the restricted attributes ALTER ROLE regress_role_limited SUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have SUPERUSER privilege to change the SUPERUSER attribute. ALTER ROLE regress_role_limited REPLICATION; -ERROR: must have replication privilege to change replication attribute +ERROR: permission denied to alter role +DETAIL: You must have REPLICATION privilege to change the REPLICATION attribute. ALTER ROLE regress_role_limited CREATEDB; -ERROR: must have createdb privilege to change createdb attribute +ERROR: permission denied to alter role +DETAIL: You must have CREATEDB privilege to change the CREATEDB attribute. ALTER ROLE regress_role_limited BYPASSRLS; -ERROR: must have bypassrls privilege to change bypassrls attribute +ERROR: permission denied to alter role +DETAIL: You must have BYPASSRLS privilege to change the BYPASSRLS attribute. DROP ROLE regress_role_limited; -- ok, can give away these role attributes if you have them SET SESSION AUTHORIZATION regress_role_admin; @@ -43,9 +52,11 @@ ALTER ROLE regress_createdb NOCREATEDB; ALTER ROLE regress_createdb CREATEDB; -- fail, can't toggle SUPERUSER ALTER ROLE regress_createdb SUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have SUPERUSER privilege to change the SUPERUSER attribute. ALTER ROLE regress_createdb NOSUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have SUPERUSER privilege to change the SUPERUSER attribute. -- ok, having CREATEROLE is enough to create users with these privileges CREATE ROLE regress_createrole CREATEROLE NOINHERIT; GRANT CREATE ON DATABASE regression TO regress_createrole WITH GRANT OPTION; @@ -59,7 +70,8 @@ CREATE ROLE regress_noiseword SYSID 12345; 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 +ERROR: permission denied to grant role "regress_role_super" +DETAIL: You must have SUPERUSER privilege to grant roles with SUPERUSER. -- fail, database owner cannot have members CREATE ROLE regress_nosuch_dbowner IN ROLE pg_database_owner; ERROR: role "pg_database_owner" cannot have explicit members @@ -97,8 +109,10 @@ COMMENT ON ROLE regress_role_normal IS 'some comment'; ERROR: must have admin option on role "regress_role_normal" ALTER ROLE regress_role_normal RENAME TO regress_role_abnormal; ERROR: permission denied to rename role +DETAIL: You must have CREATEROLE privilege and ADMIN OPTION on role "regress_role_normal". ALTER ROLE regress_role_normal NOINHERIT NOLOGIN CONNECTION LIMIT 7; -ERROR: permission denied +ERROR: permission denied to alter role +DETAIL: You must have CREATEROLE privilege and ADMIN OPTION on role "regress_role_normal". -- ok, regress_tenant can create objects within the database SET SESSION AUTHORIZATION regress_tenant; CREATE TABLE tenant_table (i integer); @@ -123,6 +137,7 @@ ERROR: must be able to SET ROLE "regress_tenant" -- fail, we don't inherit permissions from regress_tenant REASSIGN OWNED BY regress_tenant TO regress_createrole; ERROR: permission denied to reassign objects +DETAIL: You must have privileges of role "regress_tenant". -- ok, create a role with a value for createrole_self_grant SET createrole_self_grant = 'set, inherit'; CREATE ROLE regress_tenant2; @@ -150,25 +165,35 @@ ERROR: must be able to SET ROLE "regress_tenant2" DROP TABLE tenant2_table; -- fail, CREATEROLE is not enough to create roles in privileged roles CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data; -ERROR: must have admin option on role "pg_read_all_data" +ERROR: permission denied to grant role "pg_read_all_data" +DETAIL: You must have ADMIN OPTION on role "pg_read_all_data". CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data; -ERROR: must have admin option on role "pg_write_all_data" +ERROR: permission denied to grant role "pg_write_all_data" +DETAIL: You must have ADMIN OPTION on role "pg_write_all_data". CREATE ROLE regress_monitor IN ROLE pg_monitor; -ERROR: must have admin option on role "pg_monitor" +ERROR: permission denied to grant role "pg_monitor" +DETAIL: You must have ADMIN OPTION on role "pg_monitor". CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings; -ERROR: must have admin option on role "pg_read_all_settings" +ERROR: permission denied to grant role "pg_read_all_settings" +DETAIL: You must have ADMIN OPTION on role "pg_read_all_settings". CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats; -ERROR: must have admin option on role "pg_read_all_stats" +ERROR: permission denied to grant role "pg_read_all_stats" +DETAIL: You must have ADMIN OPTION on role "pg_read_all_stats". CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables; -ERROR: must have admin option on role "pg_stat_scan_tables" +ERROR: permission denied to grant role "pg_stat_scan_tables" +DETAIL: You must have ADMIN OPTION on role "pg_stat_scan_tables". CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files; -ERROR: must have admin option on role "pg_read_server_files" +ERROR: permission denied to grant role "pg_read_server_files" +DETAIL: You must have ADMIN OPTION on role "pg_read_server_files". CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files; -ERROR: must have admin option on role "pg_write_server_files" +ERROR: permission denied to grant role "pg_write_server_files" +DETAIL: You must have ADMIN OPTION on role "pg_write_server_files". CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program; -ERROR: must have admin option on role "pg_execute_server_program" +ERROR: permission denied to grant role "pg_execute_server_program" +DETAIL: You must have ADMIN OPTION on role "pg_execute_server_program". CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend; -ERROR: must have admin option on role "pg_signal_backend" +ERROR: permission denied to grant role "pg_signal_backend" +DETAIL: You must have ADMIN OPTION on role "pg_signal_backend". -- fail, role still owns database objects DROP ROLE regress_tenant; ERROR: role "regress_tenant" cannot be dropped because some objects depend on it @@ -211,11 +236,13 @@ DROP ROLE regress_inroles; DROP ROLE regress_adminroles; -- fail, cannot drop ourself, nor superusers or roles we lack ADMIN for DROP ROLE regress_role_super; -ERROR: must be superuser to drop superusers +ERROR: permission denied to drop role +DETAIL: You must have SUPERUSER privilege to drop roles with SUPERUSER. DROP ROLE regress_role_admin; ERROR: current user cannot be dropped DROP ROLE regress_rolecreator; -ERROR: must have admin option on role "regress_rolecreator" +ERROR: permission denied to drop role +DETAIL: You must have CREATEROLE privilege and ADMIN OPTION on role "regress_rolecreator". -- ok RESET SESSION AUTHORIZATION; REVOKE CREATE ON DATABASE regression FROM regress_role_admin CASCADE; diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out index 520035f6a0..78ee4d7448 100644 --- a/src/test/regress/expected/dependency.out +++ b/src/test/regress/expected/dependency.out @@ -48,12 +48,16 @@ SET SESSION AUTHORIZATION regress_dep_user0; -- permission denied DROP OWNED BY regress_dep_user1; ERROR: permission denied to drop objects +DETAIL: You must have privileges of role "regress_dep_user1". DROP OWNED BY regress_dep_user0, regress_dep_user2; ERROR: permission denied to drop objects +DETAIL: You must have privileges of role "regress_dep_user2". REASSIGN OWNED BY regress_dep_user0 TO regress_dep_user1; ERROR: permission denied to reassign objects +DETAIL: You must have privileges of role "regress_dep_user1". REASSIGN OWNED BY regress_dep_user1 TO regress_dep_user0; ERROR: permission denied to reassign objects +DETAIL: You must have privileges of role "regress_dep_user1". -- this one is allowed DROP OWNED BY regress_dep_user0; CREATE TABLE deptest1 (f1 int unique); diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 95d1e5515f..30c1d11a7c 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -37,7 +37,7 @@ CREATE ROLE regress_priv_role; GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION; GRANT regress_priv_user1 TO regress_priv_user3 WITH ADMIN OPTION GRANTED BY regress_priv_user2; GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION GRANTED BY regress_priv_user3; -ERROR: admin option cannot be granted back to your own grantor +ERROR: ADMIN OPTION cannot be granted back to your own grantor -- need CASCADE to revoke grant or admin option if dependent grants exist REVOKE ADMIN OPTION FOR regress_priv_user1 FROM regress_priv_user2; -- fail ERROR: dependent privileges exist @@ -156,7 +156,8 @@ ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- duplicate NOTICE: role "regress_priv_user2" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user1" ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; ALTER USER regress_priv_user2 PASSWORD 'verysecret'; -- not permitted -ERROR: must have CREATEROLE privilege to change another user's password +ERROR: permission denied to alter role +DETAIL: You must have CREATEROLE privilege and ADMIN OPTION on role "regress_priv_user2". RESET SESSION AUTHORIZATION; ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; REVOKE ADMIN OPTION FOR regress_priv_group2 FROM regress_priv_user1; @@ -168,7 +169,8 @@ CREATE FUNCTION leak(integer,integer) RETURNS boolean ALTER FUNCTION leak(integer,integer) OWNER TO regress_priv_user1; -- test owner privileges GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY regress_priv_role; -- error, doesn't have ADMIN OPTION -ERROR: grantor must have ADMIN OPTION on "regress_priv_role" +ERROR: permission denied to grant privileges as role "regress_priv_role" +DETAIL: The grantor must have ADMIN OPTION on role "regress_priv_role". GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY CURRENT_ROLE; REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY foo; -- error ERROR: role "foo" does not exist @@ -1795,7 +1797,8 @@ REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation CONTEXT: SQL function "mv_action" statement 1 BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". CONTEXT: SQL function "unwanted_grant" statement 1 SQL statement "SELECT unwanted_grant()" PL/pgSQL function sro_trojan() line 1 at PERFORM @@ -1825,10 +1828,12 @@ CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION SET ROLE regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended privilege -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_user1; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN NOTICE: role "regress_priv_user5" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user4" dogrant_ok @@ -1838,10 +1843,12 @@ NOTICE: role "regress_priv_user5" has already been granted membership in role " SET ROLE regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no self-admin -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_user4; DROP FUNCTION dogrant_ok(); REVOKE regress_priv_group2 FROM regress_priv_user5; -- 2.25.1 --PNTmBPCT7hxwcZjr-- ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v4 1/2] Improve user.c error messages. @ 2023-01-26 19:05 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Nathan Bossart @ 2023-01-26 19:05 UTC (permalink / raw) --- src/backend/commands/user.c | 168 ++++++++++++++++------ src/test/regress/expected/create_role.out | 77 ++++++---- src/test/regress/expected/dependency.out | 4 + src/test/regress/expected/privileges.out | 23 +-- 4 files changed, 197 insertions(+), 75 deletions(-) diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index 3a92e930c0..26b533f1be 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -316,23 +316,33 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) if (!has_createrole_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to create role"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles.", + "CREATEROLE"))); if (issuper) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to create superusers"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "SUPERUSER", "SUPERUSER"))); if (createdb && !have_createdb_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have createdb permission to create createdb users"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "CREATEDB", "CREATEDB"))); if (isreplication && !has_rolreplication(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have replication permission to create replication users"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "REPLICATION", "REPLICATION"))); if (bypassrls && !has_bypassrls_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have bypassrls to create bypassrls users"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "BYPASSRLS", "BYPASSRLS"))); } /* @@ -744,10 +754,18 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) roleid = authform->oid; /* To mess with a superuser in any way you gotta be superuser. */ - if (!superuser() && (authform->rolsuper || dissuper)) + if (!superuser() && authform->rolsuper) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superuser roles or change superuser attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to alter roles with %s.", + "SUPERUSER", "SUPERUSER"))); + if (!superuser() && dissuper) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "SUPERUSER", "SUPERUSER"))); /* * Most changes to a role require that you both have CREATEROLE privileges @@ -761,13 +779,17 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) dvalidUntil || disreplication || dbypassRLS) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATEROLE", "ADMIN OPTION", rolename))); /* an unprivileged user can change their own password */ if (dpassword && roleid != currentUserId) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have CREATEROLE privilege to change another user's password"))); + errmsg("permission denied to alter role"), + errdetail("To change another role's password, you must have %s privilege and %s on the role.", + "CREATEROLE", "ADMIN OPTION"))); } else if (!superuser()) { @@ -779,23 +801,30 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (dcreatedb && !have_createdb_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have createdb privilege to change createdb attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "CREATEDB", "CREATEDB"))); if (disreplication && !has_rolreplication(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have replication privilege to change replication attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "REPLICATION", "REPLICATION"))); if (dbypassRLS && !has_bypassrls_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have bypassrls privilege to change bypassrls attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "BYPASSRLS", "BYPASSRLS"))); } /* To add members to a role, you need ADMIN OPTION. */ if (drolemembers && !is_admin_of_role(currentUserId, roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\" to add members", - rolename))); + errmsg("permission denied to alter role"), + errdetail("You must have %s on role \"%s\" to add members.", + "ADMIN OPTION", rolename))); /* Convert validuntil to internal form */ if (dvalidUntil) @@ -837,8 +866,9 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (!should_be_super && roleid == BOOTSTRAP_SUPERUSERID) ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied: bootstrap user must be superuser"))); + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("permission denied to alter role"), + errdetail("The bootstrap user must be superuser."))); new_record[Anum_pg_authid_rolsuper - 1] = BoolGetDatum(should_be_super); new_record_repl[Anum_pg_authid_rolsuper - 1] = true; @@ -999,7 +1029,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superusers"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to alter roles with %s.", + "SUPERUSER", "SUPERUSER"))); } else { @@ -1008,7 +1040,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) && roleid != GetUserId()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATROLE", "ADMIN OPTION", NameStr(roleform->rolname)))); } ReleaseSysCache(roletuple); @@ -1038,7 +1072,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter settings globally"))); + errmsg("permission denied to alter setting"), + errdetail("You must have %s privilege to alter settings globally.", + "SUPERUSER"))); } AlterSetting(databaseid, roleid, stmt->setstmt); @@ -1061,7 +1097,9 @@ DropRole(DropRoleStmt *stmt) if (!have_createrole_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to drop role"))); + errmsg("permission denied to drop role"), + errdetail("You must have %s privilege and %s on the target roles.", + "CREATEROLE", "ADMIN OPTION"))); /* * Scan the pg_authid relation to find the Oid of the role(s) to be @@ -1131,12 +1169,15 @@ DropRole(DropRoleStmt *stmt) if (roleform->rolsuper && !superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to drop superusers"))); + errmsg("permission denied to drop role"), + errdetail("You must have %s privilege to drop roles with %s.", + "SUPERUSER", "SUPERUSER"))); if (!is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\"", - role))); + errmsg("permission denied to drop role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATEROLE", "ADMIN OPTION", NameStr(roleform->rolname)))); /* DROP hook for the role being removed */ InvokeObjectDropHook(AuthIdRelationId, roleid, 0); @@ -1378,12 +1419,14 @@ RenameRole(const char *oldname, const char *newname) * Only superusers can mess with superusers. Otherwise, a user with * CREATEROLE can rename a role for which they have ADMIN OPTION. */ - if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper) + if (authform->rolsuper) { if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to rename superusers"))); + errmsg("permission denied to rename role"), + errdetail("You must have %s privilege to rename roles with %s.", + "SUPERUSER", "SUPERUSER"))); } else { @@ -1391,7 +1434,9 @@ RenameRole(const char *oldname, const char *newname) !is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to rename role"))); + errmsg("permission denied to rename role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATEROLE", "ADMIN OPTION", NameStr(authform->rolname)))); } /* OK, construct the modified tuple */ @@ -1554,7 +1599,9 @@ DropOwnedObjects(DropOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to drop objects"))); + errmsg("permission denied to drop objects"), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(roleid, false)))); } /* Ok, do it */ @@ -1581,7 +1628,9 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to reassign objects"))); + errmsg("permission denied to reassign objects"), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(roleid, false)))); } /* Must have privileges on the receiving side too */ @@ -1590,7 +1639,9 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), newrole)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to reassign objects"))); + errmsg("permission denied to reassign objects"), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(newrole, false)))); /* Ok, do it */ shdepReassignOwned(role_ids, newrole); @@ -1738,7 +1789,8 @@ AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid, if (memberid == BOOTSTRAP_SUPERUSERID) ereport(ERROR, (errcode(ERRCODE_INVALID_GRANT_OPERATION), - errmsg("admin option cannot be granted back to your own grantor"))); + errmsg("%s cannot be granted back to your own grantor", + "ADMIN OPTION"))); plan_member_revoke(memlist, actions, memberid); } @@ -1763,7 +1815,8 @@ AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid, if (i >= memlist->n_members) ereport(ERROR, (errcode(ERRCODE_INVALID_GRANT_OPERATION), - errmsg("admin option cannot be granted back to your own grantor"))); + errmsg("%s cannot be granted back to your own grantor", + "ADMIN OPTION"))); ReleaseSysCacheList(memlist); } @@ -2081,9 +2134,22 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid, if (superuser_arg(roleid)) { if (!superuser_arg(currentUserId)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superusers"))); + { + if (is_grant) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to grant role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s privilege to grant roles with %s.", + "SUPERUSER", "SUPERUSER"))); + else + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to revoke role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s privilege to revoke roles with %s.", + "SUPERUSER", "SUPERUSER"))); + } } else { @@ -2091,10 +2157,22 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid, * Otherwise, must have admin option on the role to be changed. */ if (!is_admin_of_role(currentUserId, roleid)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\"", - GetUserNameFromId(roleid, false)))); + { + if (is_grant) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to grant role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s on role \"%s\".", + "ADMIN OPTION", GetUserNameFromId(roleid, false)))); + else + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to revoke role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s on role \"%s\".", + "ADMIN OPTION", GetUserNameFromId(roleid, false)))); + } } } @@ -2173,14 +2251,18 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to grant privileges as role \"%s\"", - GetUserNameFromId(grantorId, false)))); + GetUserNameFromId(grantorId, false)), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(grantorId, false)))); if (grantorId != BOOTSTRAP_SUPERUSERID && select_best_admin(grantorId, roleid) != grantorId) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("grantor must have ADMIN OPTION on \"%s\"", - GetUserNameFromId(roleid, false)))); + errmsg("permission denied to grant privileges as role \"%s\"", + GetUserNameFromId(grantorId, false)), + errdetail("The grantor must have %s on role \"%s\".", + "ADMIN OPTION", GetUserNameFromId(roleid, false)))); } else { @@ -2188,7 +2270,9 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to revoke privileges granted by role \"%s\"", - GetUserNameFromId(grantorId, false)))); + GetUserNameFromId(grantorId, false)), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(grantorId, false)))); } /* diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out index 9f431bd4f5..2a7d14dba9 100644 --- a/src/test/regress/expected/create_role.out +++ b/src/test/regress/expected/create_role.out @@ -7,26 +7,35 @@ CREATE ROLE regress_role_normal; -- fail, CREATEROLE user can't give away role attributes without having them SET SESSION AUTHORIZATION regress_role_limited_admin; CREATE ROLE regress_nosuch_superuser SUPERUSER; -ERROR: must be superuser to create superusers +ERROR: permission denied to create role +DETAIL: You must have SUPERUSER privilege to create roles with SUPERUSER. CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS; -ERROR: must have replication permission to create replication users +ERROR: permission denied to create role +DETAIL: You must have REPLICATION privilege to create roles with REPLICATION. CREATE ROLE regress_nosuch_replication REPLICATION; -ERROR: must have replication permission to create replication users +ERROR: permission denied to create role +DETAIL: You must have REPLICATION privilege to create roles with REPLICATION. CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; -ERROR: must have bypassrls to create bypassrls users +ERROR: permission denied to create role +DETAIL: You must have BYPASSRLS privilege to create roles with BYPASSRLS. CREATE ROLE regress_nosuch_createdb CREATEDB; -ERROR: must have createdb permission to create createdb users +ERROR: permission denied to create role +DETAIL: You must have CREATEDB privilege to create roles with CREATEDB. -- ok, can create a role without any special attributes CREATE ROLE regress_role_limited; -- fail, can't give it in any of the restricted attributes ALTER ROLE regress_role_limited SUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have SUPERUSER privilege to change the SUPERUSER attribute. ALTER ROLE regress_role_limited REPLICATION; -ERROR: must have replication privilege to change replication attribute +ERROR: permission denied to alter role +DETAIL: You must have REPLICATION privilege to change the REPLICATION attribute. ALTER ROLE regress_role_limited CREATEDB; -ERROR: must have createdb privilege to change createdb attribute +ERROR: permission denied to alter role +DETAIL: You must have CREATEDB privilege to change the CREATEDB attribute. ALTER ROLE regress_role_limited BYPASSRLS; -ERROR: must have bypassrls privilege to change bypassrls attribute +ERROR: permission denied to alter role +DETAIL: You must have BYPASSRLS privilege to change the BYPASSRLS attribute. DROP ROLE regress_role_limited; -- ok, can give away these role attributes if you have them SET SESSION AUTHORIZATION regress_role_admin; @@ -43,9 +52,11 @@ ALTER ROLE regress_createdb NOCREATEDB; ALTER ROLE regress_createdb CREATEDB; -- fail, can't toggle SUPERUSER ALTER ROLE regress_createdb SUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have SUPERUSER privilege to change the SUPERUSER attribute. ALTER ROLE regress_createdb NOSUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have SUPERUSER privilege to change the SUPERUSER attribute. -- ok, having CREATEROLE is enough to create users with these privileges CREATE ROLE regress_createrole CREATEROLE NOINHERIT; GRANT CREATE ON DATABASE regression TO regress_createrole WITH GRANT OPTION; @@ -59,7 +70,8 @@ CREATE ROLE regress_noiseword SYSID 12345; 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 +ERROR: permission denied to grant role "regress_role_super" +DETAIL: You must have SUPERUSER privilege to grant roles with SUPERUSER. -- fail, database owner cannot have members CREATE ROLE regress_nosuch_dbowner IN ROLE pg_database_owner; ERROR: role "pg_database_owner" cannot have explicit members @@ -97,8 +109,10 @@ COMMENT ON ROLE regress_role_normal IS 'some comment'; ERROR: must have admin option on role "regress_role_normal" ALTER ROLE regress_role_normal RENAME TO regress_role_abnormal; ERROR: permission denied to rename role +DETAIL: You must have CREATEROLE privilege and ADMIN OPTION on role "regress_role_normal". ALTER ROLE regress_role_normal NOINHERIT NOLOGIN CONNECTION LIMIT 7; -ERROR: permission denied +ERROR: permission denied to alter role +DETAIL: You must have CREATEROLE privilege and ADMIN OPTION on role "regress_role_normal". -- ok, regress_tenant can create objects within the database SET SESSION AUTHORIZATION regress_tenant; CREATE TABLE tenant_table (i integer); @@ -123,6 +137,7 @@ ERROR: must be able to SET ROLE "regress_tenant" -- fail, we don't inherit permissions from regress_tenant REASSIGN OWNED BY regress_tenant TO regress_createrole; ERROR: permission denied to reassign objects +DETAIL: You must have privileges of role "regress_tenant". -- ok, create a role with a value for createrole_self_grant SET createrole_self_grant = 'set, inherit'; CREATE ROLE regress_tenant2; @@ -150,25 +165,35 @@ ERROR: must be able to SET ROLE "regress_tenant2" DROP TABLE tenant2_table; -- fail, CREATEROLE is not enough to create roles in privileged roles CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data; -ERROR: must have admin option on role "pg_read_all_data" +ERROR: permission denied to grant role "pg_read_all_data" +DETAIL: You must have ADMIN OPTION on role "pg_read_all_data". CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data; -ERROR: must have admin option on role "pg_write_all_data" +ERROR: permission denied to grant role "pg_write_all_data" +DETAIL: You must have ADMIN OPTION on role "pg_write_all_data". CREATE ROLE regress_monitor IN ROLE pg_monitor; -ERROR: must have admin option on role "pg_monitor" +ERROR: permission denied to grant role "pg_monitor" +DETAIL: You must have ADMIN OPTION on role "pg_monitor". CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings; -ERROR: must have admin option on role "pg_read_all_settings" +ERROR: permission denied to grant role "pg_read_all_settings" +DETAIL: You must have ADMIN OPTION on role "pg_read_all_settings". CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats; -ERROR: must have admin option on role "pg_read_all_stats" +ERROR: permission denied to grant role "pg_read_all_stats" +DETAIL: You must have ADMIN OPTION on role "pg_read_all_stats". CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables; -ERROR: must have admin option on role "pg_stat_scan_tables" +ERROR: permission denied to grant role "pg_stat_scan_tables" +DETAIL: You must have ADMIN OPTION on role "pg_stat_scan_tables". CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files; -ERROR: must have admin option on role "pg_read_server_files" +ERROR: permission denied to grant role "pg_read_server_files" +DETAIL: You must have ADMIN OPTION on role "pg_read_server_files". CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files; -ERROR: must have admin option on role "pg_write_server_files" +ERROR: permission denied to grant role "pg_write_server_files" +DETAIL: You must have ADMIN OPTION on role "pg_write_server_files". CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program; -ERROR: must have admin option on role "pg_execute_server_program" +ERROR: permission denied to grant role "pg_execute_server_program" +DETAIL: You must have ADMIN OPTION on role "pg_execute_server_program". CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend; -ERROR: must have admin option on role "pg_signal_backend" +ERROR: permission denied to grant role "pg_signal_backend" +DETAIL: You must have ADMIN OPTION on role "pg_signal_backend". -- fail, role still owns database objects DROP ROLE regress_tenant; ERROR: role "regress_tenant" cannot be dropped because some objects depend on it @@ -211,11 +236,13 @@ DROP ROLE regress_inroles; DROP ROLE regress_adminroles; -- fail, cannot drop ourself, nor superusers or roles we lack ADMIN for DROP ROLE regress_role_super; -ERROR: must be superuser to drop superusers +ERROR: permission denied to drop role +DETAIL: You must have SUPERUSER privilege to drop roles with SUPERUSER. DROP ROLE regress_role_admin; ERROR: current user cannot be dropped DROP ROLE regress_rolecreator; -ERROR: must have admin option on role "regress_rolecreator" +ERROR: permission denied to drop role +DETAIL: You must have CREATEROLE privilege and ADMIN OPTION on role "regress_rolecreator". -- ok RESET SESSION AUTHORIZATION; REVOKE CREATE ON DATABASE regression FROM regress_role_admin CASCADE; diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out index 520035f6a0..78ee4d7448 100644 --- a/src/test/regress/expected/dependency.out +++ b/src/test/regress/expected/dependency.out @@ -48,12 +48,16 @@ SET SESSION AUTHORIZATION regress_dep_user0; -- permission denied DROP OWNED BY regress_dep_user1; ERROR: permission denied to drop objects +DETAIL: You must have privileges of role "regress_dep_user1". DROP OWNED BY regress_dep_user0, regress_dep_user2; ERROR: permission denied to drop objects +DETAIL: You must have privileges of role "regress_dep_user2". REASSIGN OWNED BY regress_dep_user0 TO regress_dep_user1; ERROR: permission denied to reassign objects +DETAIL: You must have privileges of role "regress_dep_user1". REASSIGN OWNED BY regress_dep_user1 TO regress_dep_user0; ERROR: permission denied to reassign objects +DETAIL: You must have privileges of role "regress_dep_user1". -- this one is allowed DROP OWNED BY regress_dep_user0; CREATE TABLE deptest1 (f1 int unique); diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 95d1e5515f..6794645e56 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -37,7 +37,7 @@ CREATE ROLE regress_priv_role; GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION; GRANT regress_priv_user1 TO regress_priv_user3 WITH ADMIN OPTION GRANTED BY regress_priv_user2; GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION GRANTED BY regress_priv_user3; -ERROR: admin option cannot be granted back to your own grantor +ERROR: ADMIN OPTION cannot be granted back to your own grantor -- need CASCADE to revoke grant or admin option if dependent grants exist REVOKE ADMIN OPTION FOR regress_priv_user1 FROM regress_priv_user2; -- fail ERROR: dependent privileges exist @@ -156,7 +156,8 @@ ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- duplicate NOTICE: role "regress_priv_user2" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user1" ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; ALTER USER regress_priv_user2 PASSWORD 'verysecret'; -- not permitted -ERROR: must have CREATEROLE privilege to change another user's password +ERROR: permission denied to alter role +DETAIL: To change another role's password, you must have CREATEROLE privilege and ADMIN OPTION on the role. RESET SESSION AUTHORIZATION; ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; REVOKE ADMIN OPTION FOR regress_priv_group2 FROM regress_priv_user1; @@ -168,7 +169,8 @@ CREATE FUNCTION leak(integer,integer) RETURNS boolean ALTER FUNCTION leak(integer,integer) OWNER TO regress_priv_user1; -- test owner privileges GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY regress_priv_role; -- error, doesn't have ADMIN OPTION -ERROR: grantor must have ADMIN OPTION on "regress_priv_role" +ERROR: permission denied to grant privileges as role "regress_priv_role" +DETAIL: The grantor must have ADMIN OPTION on role "regress_priv_role". GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY CURRENT_ROLE; REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY foo; -- error ERROR: role "foo" does not exist @@ -1795,7 +1797,8 @@ REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation CONTEXT: SQL function "mv_action" statement 1 BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". CONTEXT: SQL function "unwanted_grant" statement 1 SQL statement "SELECT unwanted_grant()" PL/pgSQL function sro_trojan() line 1 at PERFORM @@ -1825,10 +1828,12 @@ CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION SET ROLE regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended privilege -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_user1; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN NOTICE: role "regress_priv_user5" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user4" dogrant_ok @@ -1838,10 +1843,12 @@ NOTICE: role "regress_priv_user5" has already been granted membership in role " SET ROLE regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no self-admin -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_user4; DROP FUNCTION dogrant_ok(); REVOKE regress_priv_group2 FROM regress_priv_user5; -- 2.25.1 --azLHFNyN32YCQGCU Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v4-0002-Improve-more-insufficient-privileges-error-messag.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v6 1/2] Improve user.c error messages. @ 2023-01-26 19:05 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Nathan Bossart @ 2023-01-26 19:05 UTC (permalink / raw) --- src/backend/commands/user.c | 169 ++++++++++++++++------ src/test/regress/expected/create_role.out | 77 ++++++---- src/test/regress/expected/dependency.out | 4 + src/test/regress/expected/privileges.out | 23 ++- 4 files changed, 198 insertions(+), 75 deletions(-) diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index 3a92e930c0..e92bb656f9 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -316,23 +316,33 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) if (!has_createrole_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to create role"))); + errmsg("permission denied to create role"), + errdetail("You must have the %s attribute to create roles.", + "CREATEROLE"))); if (issuper) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to create superusers"))); + errmsg("permission denied to create role"), + errdetail("You must have the %s attribute to create roles with %s.", + "SUPERUSER", "SUPERUSER"))); if (createdb && !have_createdb_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have createdb permission to create createdb users"))); + errmsg("permission denied to create role"), + errdetail("You must have the %s attribute to create roles with %s.", + "CREATEDB", "CREATEDB"))); if (isreplication && !has_rolreplication(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have replication permission to create replication users"))); + errmsg("permission denied to create role"), + errdetail("You must have the %s attribute to create roles with %s.", + "REPLICATION", "REPLICATION"))); if (bypassrls && !has_bypassrls_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have bypassrls to create bypassrls users"))); + errmsg("permission denied to create role"), + errdetail("You must have the %s attribute to create roles with %s.", + "BYPASSRLS", "BYPASSRLS"))); } /* @@ -744,10 +754,18 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) roleid = authform->oid; /* To mess with a superuser in any way you gotta be superuser. */ - if (!superuser() && (authform->rolsuper || dissuper)) + if (!superuser() && authform->rolsuper) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superuser roles or change superuser attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have the %s attribute to alter roles with %s.", + "SUPERUSER", "SUPERUSER"))); + if (!superuser() && dissuper) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to alter role"), + errdetail("You must have the %s attribute to change the %s attribute.", + "SUPERUSER", "SUPERUSER"))); /* * Most changes to a role require that you both have CREATEROLE privileges @@ -761,13 +779,17 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) dvalidUntil || disreplication || dbypassRLS) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied"))); + errmsg("permission denied to alter role"), + errdetail("You must have the %s attribute and the %s option on role \"%s\".", + "CREATEROLE", "ADMIN", rolename))); /* an unprivileged user can change their own password */ if (dpassword && roleid != currentUserId) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have CREATEROLE privilege to change another user's password"))); + errmsg("permission denied to alter role"), + errdetail("To change another role's password, you must have the %s attribute and the %s option on the role.", + "CREATEROLE", "ADMIN"))); } else if (!superuser()) { @@ -779,23 +801,30 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (dcreatedb && !have_createdb_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have createdb privilege to change createdb attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have the %s attribute to change the %s attribute.", + "CREATEDB", "CREATEDB"))); if (disreplication && !has_rolreplication(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have replication privilege to change replication attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have the %s attribute to change the %s attribute.", + "REPLICATION", "REPLICATION"))); if (dbypassRLS && !has_bypassrls_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have bypassrls privilege to change bypassrls attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have the %s attribute to change the %s attribute.", + "BYPASSRLS", "BYPASSRLS"))); } /* To add members to a role, you need ADMIN OPTION. */ if (drolemembers && !is_admin_of_role(currentUserId, roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\" to add members", - rolename))); + errmsg("permission denied to alter role"), + errdetail("You must have the %s option on role \"%s\" to add members.", + "ADMIN", rolename))); /* Convert validuntil to internal form */ if (dvalidUntil) @@ -837,8 +866,10 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (!should_be_super && roleid == BOOTSTRAP_SUPERUSERID) ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied: bootstrap user must be superuser"))); + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("permission denied to alter role"), + errdetail("The bootstrap user must have the %s attribute.", + "SUPERUSER"))); new_record[Anum_pg_authid_rolsuper - 1] = BoolGetDatum(should_be_super); new_record_repl[Anum_pg_authid_rolsuper - 1] = true; @@ -999,7 +1030,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superusers"))); + errmsg("permission denied to alter role"), + errdetail("You must have the %s attribute to alter roles with %s.", + "SUPERUSER", "SUPERUSER"))); } else { @@ -1008,7 +1041,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) && roleid != GetUserId()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied"))); + errmsg("permission denied to alter role"), + errdetail("You must have the %s attribute and the %s option on role \"%s\".", + "CREATROLE", "ADMIN", NameStr(roleform->rolname)))); } ReleaseSysCache(roletuple); @@ -1038,7 +1073,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter settings globally"))); + errmsg("permission denied to alter setting"), + errdetail("You must have the %s attribute to alter settings globally.", + "SUPERUSER"))); } AlterSetting(databaseid, roleid, stmt->setstmt); @@ -1061,7 +1098,9 @@ DropRole(DropRoleStmt *stmt) if (!have_createrole_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to drop role"))); + errmsg("permission denied to drop role"), + errdetail("You must have the %s attribute and the %s option on the target roles.", + "CREATEROLE", "ADMIN"))); /* * Scan the pg_authid relation to find the Oid of the role(s) to be @@ -1131,12 +1170,15 @@ DropRole(DropRoleStmt *stmt) if (roleform->rolsuper && !superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to drop superusers"))); + errmsg("permission denied to drop role"), + errdetail("You must have the %s attribute to drop roles with %s.", + "SUPERUSER", "SUPERUSER"))); if (!is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\"", - role))); + errmsg("permission denied to drop role"), + errdetail("You must have the %s attribute and the %s option on role \"%s\".", + "CREATEROLE", "ADMIN", NameStr(roleform->rolname)))); /* DROP hook for the role being removed */ InvokeObjectDropHook(AuthIdRelationId, roleid, 0); @@ -1378,12 +1420,14 @@ RenameRole(const char *oldname, const char *newname) * Only superusers can mess with superusers. Otherwise, a user with * CREATEROLE can rename a role for which they have ADMIN OPTION. */ - if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper) + if (authform->rolsuper) { if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to rename superusers"))); + errmsg("permission denied to rename role"), + errdetail("You must have the %s attribute to rename roles with %s.", + "SUPERUSER", "SUPERUSER"))); } else { @@ -1391,7 +1435,9 @@ RenameRole(const char *oldname, const char *newname) !is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to rename role"))); + errmsg("permission denied to rename role"), + errdetail("You must have the %s attribute and the %s option on role \"%s\".", + "CREATEROLE", "ADMIN", NameStr(authform->rolname)))); } /* OK, construct the modified tuple */ @@ -1554,7 +1600,9 @@ DropOwnedObjects(DropOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to drop objects"))); + errmsg("permission denied to drop objects"), + errdetail("You must inherit privileges of role \"%s\".", + GetUserNameFromId(roleid, false)))); } /* Ok, do it */ @@ -1581,7 +1629,9 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to reassign objects"))); + errmsg("permission denied to reassign objects"), + errdetail("You must inherit privileges of role \"%s\".", + GetUserNameFromId(roleid, false)))); } /* Must have privileges on the receiving side too */ @@ -1590,7 +1640,9 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), newrole)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to reassign objects"))); + errmsg("permission denied to reassign objects"), + errdetail("You must inherit privileges role \"%s\".", + GetUserNameFromId(newrole, false)))); /* Ok, do it */ shdepReassignOwned(role_ids, newrole); @@ -1738,7 +1790,8 @@ AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid, if (memberid == BOOTSTRAP_SUPERUSERID) ereport(ERROR, (errcode(ERRCODE_INVALID_GRANT_OPERATION), - errmsg("admin option cannot be granted back to your own grantor"))); + errmsg("%s option cannot be granted back to your own grantor", + "ADMIN"))); plan_member_revoke(memlist, actions, memberid); } @@ -1763,7 +1816,8 @@ AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid, if (i >= memlist->n_members) ereport(ERROR, (errcode(ERRCODE_INVALID_GRANT_OPERATION), - errmsg("admin option cannot be granted back to your own grantor"))); + errmsg("%s option cannot be granted back to your own grantor", + "ADMIN"))); ReleaseSysCacheList(memlist); } @@ -2081,9 +2135,22 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid, if (superuser_arg(roleid)) { if (!superuser_arg(currentUserId)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superusers"))); + { + if (is_grant) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to grant role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have the %s attribute to grant roles with %s.", + "SUPERUSER", "SUPERUSER"))); + else + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to revoke role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have the %s attribute to revoke roles with %s.", + "SUPERUSER", "SUPERUSER"))); + } } else { @@ -2091,10 +2158,22 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid, * Otherwise, must have admin option on the role to be changed. */ if (!is_admin_of_role(currentUserId, roleid)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\"", - GetUserNameFromId(roleid, false)))); + { + if (is_grant) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to grant role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have the %s option on role \"%s\".", + "ADMIN", GetUserNameFromId(roleid, false)))); + else + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to revoke role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have the %s option on role \"%s\".", + "ADMIN", GetUserNameFromId(roleid, false)))); + } } } @@ -2173,14 +2252,18 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to grant privileges as role \"%s\"", - GetUserNameFromId(grantorId, false)))); + GetUserNameFromId(grantorId, false)), + errdetail("You must inherit privileges of role \"%s\".", + GetUserNameFromId(grantorId, false)))); if (grantorId != BOOTSTRAP_SUPERUSERID && select_best_admin(grantorId, roleid) != grantorId) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("grantor must have ADMIN OPTION on \"%s\"", - GetUserNameFromId(roleid, false)))); + errmsg("permission denied to grant privileges as role \"%s\"", + GetUserNameFromId(grantorId, false)), + errdetail("The grantor must have the %s option on role \"%s\".", + "ADMIN", GetUserNameFromId(roleid, false)))); } else { @@ -2188,7 +2271,9 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to revoke privileges granted by role \"%s\"", - GetUserNameFromId(grantorId, false)))); + GetUserNameFromId(grantorId, false)), + errdetail("You must inherit privileges of role \"%s\".", + GetUserNameFromId(grantorId, false)))); } /* diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out index 9f431bd4f5..7157d4c589 100644 --- a/src/test/regress/expected/create_role.out +++ b/src/test/regress/expected/create_role.out @@ -7,26 +7,35 @@ CREATE ROLE regress_role_normal; -- fail, CREATEROLE user can't give away role attributes without having them SET SESSION AUTHORIZATION regress_role_limited_admin; CREATE ROLE regress_nosuch_superuser SUPERUSER; -ERROR: must be superuser to create superusers +ERROR: permission denied to create role +DETAIL: You must have the SUPERUSER attribute to create roles with SUPERUSER. CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS; -ERROR: must have replication permission to create replication users +ERROR: permission denied to create role +DETAIL: You must have the REPLICATION attribute to create roles with REPLICATION. CREATE ROLE regress_nosuch_replication REPLICATION; -ERROR: must have replication permission to create replication users +ERROR: permission denied to create role +DETAIL: You must have the REPLICATION attribute to create roles with REPLICATION. CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; -ERROR: must have bypassrls to create bypassrls users +ERROR: permission denied to create role +DETAIL: You must have the BYPASSRLS attribute to create roles with BYPASSRLS. CREATE ROLE regress_nosuch_createdb CREATEDB; -ERROR: must have createdb permission to create createdb users +ERROR: permission denied to create role +DETAIL: You must have the CREATEDB attribute to create roles with CREATEDB. -- ok, can create a role without any special attributes CREATE ROLE regress_role_limited; -- fail, can't give it in any of the restricted attributes ALTER ROLE regress_role_limited SUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have the SUPERUSER attribute to change the SUPERUSER attribute. ALTER ROLE regress_role_limited REPLICATION; -ERROR: must have replication privilege to change replication attribute +ERROR: permission denied to alter role +DETAIL: You must have the REPLICATION attribute to change the REPLICATION attribute. ALTER ROLE regress_role_limited CREATEDB; -ERROR: must have createdb privilege to change createdb attribute +ERROR: permission denied to alter role +DETAIL: You must have the CREATEDB attribute to change the CREATEDB attribute. ALTER ROLE regress_role_limited BYPASSRLS; -ERROR: must have bypassrls privilege to change bypassrls attribute +ERROR: permission denied to alter role +DETAIL: You must have the BYPASSRLS attribute to change the BYPASSRLS attribute. DROP ROLE regress_role_limited; -- ok, can give away these role attributes if you have them SET SESSION AUTHORIZATION regress_role_admin; @@ -43,9 +52,11 @@ ALTER ROLE regress_createdb NOCREATEDB; ALTER ROLE regress_createdb CREATEDB; -- fail, can't toggle SUPERUSER ALTER ROLE regress_createdb SUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have the SUPERUSER attribute to change the SUPERUSER attribute. ALTER ROLE regress_createdb NOSUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have the SUPERUSER attribute to change the SUPERUSER attribute. -- ok, having CREATEROLE is enough to create users with these privileges CREATE ROLE regress_createrole CREATEROLE NOINHERIT; GRANT CREATE ON DATABASE regression TO regress_createrole WITH GRANT OPTION; @@ -59,7 +70,8 @@ CREATE ROLE regress_noiseword SYSID 12345; 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 +ERROR: permission denied to grant role "regress_role_super" +DETAIL: You must have the SUPERUSER attribute to grant roles with SUPERUSER. -- fail, database owner cannot have members CREATE ROLE regress_nosuch_dbowner IN ROLE pg_database_owner; ERROR: role "pg_database_owner" cannot have explicit members @@ -97,8 +109,10 @@ COMMENT ON ROLE regress_role_normal IS 'some comment'; ERROR: must have admin option on role "regress_role_normal" ALTER ROLE regress_role_normal RENAME TO regress_role_abnormal; ERROR: permission denied to rename role +DETAIL: You must have the CREATEROLE attribute and the ADMIN option on role "regress_role_normal". ALTER ROLE regress_role_normal NOINHERIT NOLOGIN CONNECTION LIMIT 7; -ERROR: permission denied +ERROR: permission denied to alter role +DETAIL: You must have the CREATEROLE attribute and the ADMIN option on role "regress_role_normal". -- ok, regress_tenant can create objects within the database SET SESSION AUTHORIZATION regress_tenant; CREATE TABLE tenant_table (i integer); @@ -123,6 +137,7 @@ ERROR: must be able to SET ROLE "regress_tenant" -- fail, we don't inherit permissions from regress_tenant REASSIGN OWNED BY regress_tenant TO regress_createrole; ERROR: permission denied to reassign objects +DETAIL: You must inherit privileges of role "regress_tenant". -- ok, create a role with a value for createrole_self_grant SET createrole_self_grant = 'set, inherit'; CREATE ROLE regress_tenant2; @@ -150,25 +165,35 @@ ERROR: must be able to SET ROLE "regress_tenant2" DROP TABLE tenant2_table; -- fail, CREATEROLE is not enough to create roles in privileged roles CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data; -ERROR: must have admin option on role "pg_read_all_data" +ERROR: permission denied to grant role "pg_read_all_data" +DETAIL: You must have the ADMIN option on role "pg_read_all_data". CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data; -ERROR: must have admin option on role "pg_write_all_data" +ERROR: permission denied to grant role "pg_write_all_data" +DETAIL: You must have the ADMIN option on role "pg_write_all_data". CREATE ROLE regress_monitor IN ROLE pg_monitor; -ERROR: must have admin option on role "pg_monitor" +ERROR: permission denied to grant role "pg_monitor" +DETAIL: You must have the ADMIN option on role "pg_monitor". CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings; -ERROR: must have admin option on role "pg_read_all_settings" +ERROR: permission denied to grant role "pg_read_all_settings" +DETAIL: You must have the ADMIN option on role "pg_read_all_settings". CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats; -ERROR: must have admin option on role "pg_read_all_stats" +ERROR: permission denied to grant role "pg_read_all_stats" +DETAIL: You must have the ADMIN option on role "pg_read_all_stats". CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables; -ERROR: must have admin option on role "pg_stat_scan_tables" +ERROR: permission denied to grant role "pg_stat_scan_tables" +DETAIL: You must have the ADMIN option on role "pg_stat_scan_tables". CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files; -ERROR: must have admin option on role "pg_read_server_files" +ERROR: permission denied to grant role "pg_read_server_files" +DETAIL: You must have the ADMIN option on role "pg_read_server_files". CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files; -ERROR: must have admin option on role "pg_write_server_files" +ERROR: permission denied to grant role "pg_write_server_files" +DETAIL: You must have the ADMIN option on role "pg_write_server_files". CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program; -ERROR: must have admin option on role "pg_execute_server_program" +ERROR: permission denied to grant role "pg_execute_server_program" +DETAIL: You must have the ADMIN option on role "pg_execute_server_program". CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend; -ERROR: must have admin option on role "pg_signal_backend" +ERROR: permission denied to grant role "pg_signal_backend" +DETAIL: You must have the ADMIN option on role "pg_signal_backend". -- fail, role still owns database objects DROP ROLE regress_tenant; ERROR: role "regress_tenant" cannot be dropped because some objects depend on it @@ -211,11 +236,13 @@ DROP ROLE regress_inroles; DROP ROLE regress_adminroles; -- fail, cannot drop ourself, nor superusers or roles we lack ADMIN for DROP ROLE regress_role_super; -ERROR: must be superuser to drop superusers +ERROR: permission denied to drop role +DETAIL: You must have the SUPERUSER attribute to drop roles with SUPERUSER. DROP ROLE regress_role_admin; ERROR: current user cannot be dropped DROP ROLE regress_rolecreator; -ERROR: must have admin option on role "regress_rolecreator" +ERROR: permission denied to drop role +DETAIL: You must have the CREATEROLE attribute and the ADMIN option on role "regress_rolecreator". -- ok RESET SESSION AUTHORIZATION; REVOKE CREATE ON DATABASE regression FROM regress_role_admin CASCADE; diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out index 520035f6a0..d1e8dcdf5c 100644 --- a/src/test/regress/expected/dependency.out +++ b/src/test/regress/expected/dependency.out @@ -48,12 +48,16 @@ SET SESSION AUTHORIZATION regress_dep_user0; -- permission denied DROP OWNED BY regress_dep_user1; ERROR: permission denied to drop objects +DETAIL: You must inherit privileges of role "regress_dep_user1". DROP OWNED BY regress_dep_user0, regress_dep_user2; ERROR: permission denied to drop objects +DETAIL: You must inherit privileges of role "regress_dep_user2". REASSIGN OWNED BY regress_dep_user0 TO regress_dep_user1; ERROR: permission denied to reassign objects +DETAIL: You must inherit privileges role "regress_dep_user1". REASSIGN OWNED BY regress_dep_user1 TO regress_dep_user0; ERROR: permission denied to reassign objects +DETAIL: You must inherit privileges of role "regress_dep_user1". -- this one is allowed DROP OWNED BY regress_dep_user0; CREATE TABLE deptest1 (f1 int unique); diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 95d1e5515f..72829ad764 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -37,7 +37,7 @@ CREATE ROLE regress_priv_role; GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION; GRANT regress_priv_user1 TO regress_priv_user3 WITH ADMIN OPTION GRANTED BY regress_priv_user2; GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION GRANTED BY regress_priv_user3; -ERROR: admin option cannot be granted back to your own grantor +ERROR: ADMIN option cannot be granted back to your own grantor -- need CASCADE to revoke grant or admin option if dependent grants exist REVOKE ADMIN OPTION FOR regress_priv_user1 FROM regress_priv_user2; -- fail ERROR: dependent privileges exist @@ -156,7 +156,8 @@ ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- duplicate NOTICE: role "regress_priv_user2" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user1" ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; ALTER USER regress_priv_user2 PASSWORD 'verysecret'; -- not permitted -ERROR: must have CREATEROLE privilege to change another user's password +ERROR: permission denied to alter role +DETAIL: To change another role's password, you must have the CREATEROLE attribute and the ADMIN option on the role. RESET SESSION AUTHORIZATION; ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; REVOKE ADMIN OPTION FOR regress_priv_group2 FROM regress_priv_user1; @@ -168,7 +169,8 @@ CREATE FUNCTION leak(integer,integer) RETURNS boolean ALTER FUNCTION leak(integer,integer) OWNER TO regress_priv_user1; -- test owner privileges GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY regress_priv_role; -- error, doesn't have ADMIN OPTION -ERROR: grantor must have ADMIN OPTION on "regress_priv_role" +ERROR: permission denied to grant privileges as role "regress_priv_role" +DETAIL: The grantor must have the ADMIN option on role "regress_priv_role". GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY CURRENT_ROLE; REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY foo; -- error ERROR: role "foo" does not exist @@ -1795,7 +1797,8 @@ REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation CONTEXT: SQL function "mv_action" statement 1 BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have the ADMIN option on role "regress_priv_group2". CONTEXT: SQL function "unwanted_grant" statement 1 SQL statement "SELECT unwanted_grant()" PL/pgSQL function sro_trojan() line 1 at PERFORM @@ -1825,10 +1828,12 @@ CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION SET ROLE regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended privilege -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have the ADMIN option on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_user1; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have the ADMIN option on role "regress_priv_group2". SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN NOTICE: role "regress_priv_user5" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user4" dogrant_ok @@ -1838,10 +1843,12 @@ NOTICE: role "regress_priv_user5" has already been granted membership in role " SET ROLE regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have the ADMIN option on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no self-admin -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have the ADMIN option on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_user4; DROP FUNCTION dogrant_ok(); REVOKE regress_priv_group2 FROM regress_priv_user5; -- 2.25.1 --CE+1k2dSO48ffgeK Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v6-0002-Improve-more-insufficient-privileges-error-messag.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v3 1/1] Improve user.c error messages. @ 2023-01-26 19:05 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Nathan Bossart @ 2023-01-26 19:05 UTC (permalink / raw) --- src/backend/commands/user.c | 166 ++++++++++++++++------ src/test/regress/expected/create_role.out | 77 ++++++---- src/test/regress/expected/dependency.out | 4 + src/test/regress/expected/privileges.out | 23 +-- 4 files changed, 196 insertions(+), 74 deletions(-) diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index 3a92e930c0..ad22a89298 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -316,23 +316,33 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) if (!has_createrole_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to create role"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles.", + "CREATEROLE"))); if (issuper) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to create superusers"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "SUPERUSER", "SUPERUSER"))); if (createdb && !have_createdb_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have createdb permission to create createdb users"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "CREATEDB", "CREATEDB"))); if (isreplication && !has_rolreplication(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have replication permission to create replication users"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "REPLICATION", "REPLICATION"))); if (bypassrls && !has_bypassrls_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have bypassrls to create bypassrls users"))); + errmsg("permission denied to create role"), + errdetail("You must have %s privilege to create roles with %s.", + "BYPASSRLS", "BYPASSRLS"))); } /* @@ -744,10 +754,18 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) roleid = authform->oid; /* To mess with a superuser in any way you gotta be superuser. */ - if (!superuser() && (authform->rolsuper || dissuper)) + if (!superuser() && authform->rolsuper) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superuser roles or change superuser attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to alter roles with %s.", + "SUPERUSER", "SUPERUSER"))); + if (!superuser() && dissuper) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "SUPERUSER", "SUPERUSER"))); /* * Most changes to a role require that you both have CREATEROLE privileges @@ -761,13 +779,17 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) dvalidUntil || disreplication || dbypassRLS) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATEROLE", "ADMIN OPTION", rolename))); /* an unprivileged user can change their own password */ if (dpassword && roleid != currentUserId) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have CREATEROLE privilege to change another user's password"))); + errmsg("permission denied to alter role"), + errdetail("To change another role's password, you must have %s privilege and %s on the role.", + "CREATEROLE", "ADMIN OPTION"))); } else if (!superuser()) { @@ -779,23 +801,30 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (dcreatedb && !have_createdb_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have createdb privilege to change createdb attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "CREATEDB", "CREATEDB"))); if (disreplication && !has_rolreplication(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have replication privilege to change replication attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "REPLICATION", "REPLICATION"))); if (dbypassRLS && !has_bypassrls_privilege(currentUserId)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have bypassrls privilege to change bypassrls attribute"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to change the %s attribute.", + "BYPASSRLS", "BYPASSRLS"))); } /* To add members to a role, you need ADMIN OPTION. */ if (drolemembers && !is_admin_of_role(currentUserId, roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\" to add members", - rolename))); + errmsg("permission denied to alter role"), + errdetail("You must have %s on role \"%s\" to add members.", + "ADMIN OPTION", rolename))); /* Convert validuntil to internal form */ if (dvalidUntil) @@ -838,7 +867,8 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (!should_be_super && roleid == BOOTSTRAP_SUPERUSERID) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied: bootstrap user must be superuser"))); + errmsg("permission denied to alter role"), + errdetail("The bootstrap user must be superuser."))); new_record[Anum_pg_authid_rolsuper - 1] = BoolGetDatum(should_be_super); new_record_repl[Anum_pg_authid_rolsuper - 1] = true; @@ -999,7 +1029,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superusers"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege to alter roles with %s.", + "SUPERUSER", "SUPERUSER"))); } else { @@ -1008,7 +1040,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) && roleid != GetUserId()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied"))); + errmsg("permission denied to alter role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATROLE", "ADMIN OPTION", NameStr(roleform->rolname)))); } ReleaseSysCache(roletuple); @@ -1038,7 +1072,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter settings globally"))); + errmsg("permission denied to alter setting"), + errdetail("You must have %s privilege to alter settings globally.", + "SUPERUSER"))); } AlterSetting(databaseid, roleid, stmt->setstmt); @@ -1061,7 +1097,9 @@ DropRole(DropRoleStmt *stmt) if (!have_createrole_privilege()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to drop role"))); + errmsg("permission denied to drop role"), + errdetail("You must have %s privilege and %s on the target roles.", + "CREATEROLE", "ADMIN OPTION"))); /* * Scan the pg_authid relation to find the Oid of the role(s) to be @@ -1131,12 +1169,15 @@ DropRole(DropRoleStmt *stmt) if (roleform->rolsuper && !superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to drop superusers"))); + errmsg("permission denied to drop role"), + errdetail("You must have %s privilege to drop roles with %s.", + "SUPERUSER", "SUPERUSER"))); if (!is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\"", - role))); + errmsg("permission denied to drop role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATEROLE", "ADMIN OPTION", NameStr(roleform->rolname)))); /* DROP hook for the role being removed */ InvokeObjectDropHook(AuthIdRelationId, roleid, 0); @@ -1378,12 +1419,14 @@ RenameRole(const char *oldname, const char *newname) * Only superusers can mess with superusers. Otherwise, a user with * CREATEROLE can rename a role for which they have ADMIN OPTION. */ - if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper) + if (authform->rolsuper) { if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to rename superusers"))); + errmsg("permission denied to rename role"), + errdetail("You must have %s privilege to rename roles with %s.", + "SUPERUSER", "SUPERUSER"))); } else { @@ -1391,7 +1434,9 @@ RenameRole(const char *oldname, const char *newname) !is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to rename role"))); + errmsg("permission denied to rename role"), + errdetail("You must have %s privilege and %s on role \"%s\".", + "CREATEROLE", "ADMIN OPTION", NameStr(authform->rolname)))); } /* OK, construct the modified tuple */ @@ -1554,7 +1599,9 @@ DropOwnedObjects(DropOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to drop objects"))); + errmsg("permission denied to drop objects"), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(roleid, false)))); } /* Ok, do it */ @@ -1581,7 +1628,9 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to reassign objects"))); + errmsg("permission denied to reassign objects"), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(roleid, false)))); } /* Must have privileges on the receiving side too */ @@ -1590,7 +1639,9 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt) if (!has_privs_of_role(GetUserId(), newrole)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to reassign objects"))); + errmsg("permission denied to reassign objects"), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(newrole, false)))); /* Ok, do it */ shdepReassignOwned(role_ids, newrole); @@ -1738,7 +1789,8 @@ AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid, if (memberid == BOOTSTRAP_SUPERUSERID) ereport(ERROR, (errcode(ERRCODE_INVALID_GRANT_OPERATION), - errmsg("admin option cannot be granted back to your own grantor"))); + errmsg("%s cannot be granted back to your own grantor", + "ADMIN OPTION"))); plan_member_revoke(memlist, actions, memberid); } @@ -1763,7 +1815,8 @@ AddRoleMems(Oid currentUserId, const char *rolename, Oid roleid, if (i >= memlist->n_members) ereport(ERROR, (errcode(ERRCODE_INVALID_GRANT_OPERATION), - errmsg("admin option cannot be granted back to your own grantor"))); + errmsg("%s cannot be granted back to your own grantor", + "ADMIN OPTION"))); ReleaseSysCacheList(memlist); } @@ -2081,9 +2134,22 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid, if (superuser_arg(roleid)) { if (!superuser_arg(currentUserId)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to alter superusers"))); + { + if (is_grant) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to grant role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s privilege to grant roles with %s.", + "SUPERUSER", "SUPERUSER"))); + else + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to revoke role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s privilege to revoke roles with %s.", + "SUPERUSER", "SUPERUSER"))); + } } else { @@ -2091,10 +2157,22 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid, * Otherwise, must have admin option on the role to be changed. */ if (!is_admin_of_role(currentUserId, roleid)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\"", - GetUserNameFromId(roleid, false)))); + { + if (is_grant) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to grant role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s on role \"%s\".", + "ADMIN OPTION", GetUserNameFromId(roleid, false)))); + else + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to revoke role \"%s\"", + GetUserNameFromId(roleid, false)), + errdetail("You must have %s on role \"%s\".", + "ADMIN OPTION", GetUserNameFromId(roleid, false)))); + } } } @@ -2173,14 +2251,18 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to grant privileges as role \"%s\"", - GetUserNameFromId(grantorId, false)))); + GetUserNameFromId(grantorId, false)), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(grantorId, false)))); if (grantorId != BOOTSTRAP_SUPERUSERID && select_best_admin(grantorId, roleid) != grantorId) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("grantor must have ADMIN OPTION on \"%s\"", - GetUserNameFromId(roleid, false)))); + errmsg("permission denied to grant privileges as role \"%s\"", + GetUserNameFromId(grantorId, false)), + errdetail("The grantor must have %s on role \"%s\".", + "ADMIN OPTION", GetUserNameFromId(roleid, false)))); } else { @@ -2188,7 +2270,9 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to revoke privileges granted by role \"%s\"", - GetUserNameFromId(grantorId, false)))); + GetUserNameFromId(grantorId, false)), + errdetail("You must have privileges of role \"%s\".", + GetUserNameFromId(grantorId, false)))); } /* diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out index 9f431bd4f5..2a7d14dba9 100644 --- a/src/test/regress/expected/create_role.out +++ b/src/test/regress/expected/create_role.out @@ -7,26 +7,35 @@ CREATE ROLE regress_role_normal; -- fail, CREATEROLE user can't give away role attributes without having them SET SESSION AUTHORIZATION regress_role_limited_admin; CREATE ROLE regress_nosuch_superuser SUPERUSER; -ERROR: must be superuser to create superusers +ERROR: permission denied to create role +DETAIL: You must have SUPERUSER privilege to create roles with SUPERUSER. CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS; -ERROR: must have replication permission to create replication users +ERROR: permission denied to create role +DETAIL: You must have REPLICATION privilege to create roles with REPLICATION. CREATE ROLE regress_nosuch_replication REPLICATION; -ERROR: must have replication permission to create replication users +ERROR: permission denied to create role +DETAIL: You must have REPLICATION privilege to create roles with REPLICATION. CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; -ERROR: must have bypassrls to create bypassrls users +ERROR: permission denied to create role +DETAIL: You must have BYPASSRLS privilege to create roles with BYPASSRLS. CREATE ROLE regress_nosuch_createdb CREATEDB; -ERROR: must have createdb permission to create createdb users +ERROR: permission denied to create role +DETAIL: You must have CREATEDB privilege to create roles with CREATEDB. -- ok, can create a role without any special attributes CREATE ROLE regress_role_limited; -- fail, can't give it in any of the restricted attributes ALTER ROLE regress_role_limited SUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have SUPERUSER privilege to change the SUPERUSER attribute. ALTER ROLE regress_role_limited REPLICATION; -ERROR: must have replication privilege to change replication attribute +ERROR: permission denied to alter role +DETAIL: You must have REPLICATION privilege to change the REPLICATION attribute. ALTER ROLE regress_role_limited CREATEDB; -ERROR: must have createdb privilege to change createdb attribute +ERROR: permission denied to alter role +DETAIL: You must have CREATEDB privilege to change the CREATEDB attribute. ALTER ROLE regress_role_limited BYPASSRLS; -ERROR: must have bypassrls privilege to change bypassrls attribute +ERROR: permission denied to alter role +DETAIL: You must have BYPASSRLS privilege to change the BYPASSRLS attribute. DROP ROLE regress_role_limited; -- ok, can give away these role attributes if you have them SET SESSION AUTHORIZATION regress_role_admin; @@ -43,9 +52,11 @@ ALTER ROLE regress_createdb NOCREATEDB; ALTER ROLE regress_createdb CREATEDB; -- fail, can't toggle SUPERUSER ALTER ROLE regress_createdb SUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have SUPERUSER privilege to change the SUPERUSER attribute. ALTER ROLE regress_createdb NOSUPERUSER; -ERROR: must be superuser to alter superuser roles or change superuser attribute +ERROR: permission denied to alter role +DETAIL: You must have SUPERUSER privilege to change the SUPERUSER attribute. -- ok, having CREATEROLE is enough to create users with these privileges CREATE ROLE regress_createrole CREATEROLE NOINHERIT; GRANT CREATE ON DATABASE regression TO regress_createrole WITH GRANT OPTION; @@ -59,7 +70,8 @@ CREATE ROLE regress_noiseword SYSID 12345; 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 +ERROR: permission denied to grant role "regress_role_super" +DETAIL: You must have SUPERUSER privilege to grant roles with SUPERUSER. -- fail, database owner cannot have members CREATE ROLE regress_nosuch_dbowner IN ROLE pg_database_owner; ERROR: role "pg_database_owner" cannot have explicit members @@ -97,8 +109,10 @@ COMMENT ON ROLE regress_role_normal IS 'some comment'; ERROR: must have admin option on role "regress_role_normal" ALTER ROLE regress_role_normal RENAME TO regress_role_abnormal; ERROR: permission denied to rename role +DETAIL: You must have CREATEROLE privilege and ADMIN OPTION on role "regress_role_normal". ALTER ROLE regress_role_normal NOINHERIT NOLOGIN CONNECTION LIMIT 7; -ERROR: permission denied +ERROR: permission denied to alter role +DETAIL: You must have CREATEROLE privilege and ADMIN OPTION on role "regress_role_normal". -- ok, regress_tenant can create objects within the database SET SESSION AUTHORIZATION regress_tenant; CREATE TABLE tenant_table (i integer); @@ -123,6 +137,7 @@ ERROR: must be able to SET ROLE "regress_tenant" -- fail, we don't inherit permissions from regress_tenant REASSIGN OWNED BY regress_tenant TO regress_createrole; ERROR: permission denied to reassign objects +DETAIL: You must have privileges of role "regress_tenant". -- ok, create a role with a value for createrole_self_grant SET createrole_self_grant = 'set, inherit'; CREATE ROLE regress_tenant2; @@ -150,25 +165,35 @@ ERROR: must be able to SET ROLE "regress_tenant2" DROP TABLE tenant2_table; -- fail, CREATEROLE is not enough to create roles in privileged roles CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data; -ERROR: must have admin option on role "pg_read_all_data" +ERROR: permission denied to grant role "pg_read_all_data" +DETAIL: You must have ADMIN OPTION on role "pg_read_all_data". CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data; -ERROR: must have admin option on role "pg_write_all_data" +ERROR: permission denied to grant role "pg_write_all_data" +DETAIL: You must have ADMIN OPTION on role "pg_write_all_data". CREATE ROLE regress_monitor IN ROLE pg_monitor; -ERROR: must have admin option on role "pg_monitor" +ERROR: permission denied to grant role "pg_monitor" +DETAIL: You must have ADMIN OPTION on role "pg_monitor". CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings; -ERROR: must have admin option on role "pg_read_all_settings" +ERROR: permission denied to grant role "pg_read_all_settings" +DETAIL: You must have ADMIN OPTION on role "pg_read_all_settings". CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats; -ERROR: must have admin option on role "pg_read_all_stats" +ERROR: permission denied to grant role "pg_read_all_stats" +DETAIL: You must have ADMIN OPTION on role "pg_read_all_stats". CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables; -ERROR: must have admin option on role "pg_stat_scan_tables" +ERROR: permission denied to grant role "pg_stat_scan_tables" +DETAIL: You must have ADMIN OPTION on role "pg_stat_scan_tables". CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files; -ERROR: must have admin option on role "pg_read_server_files" +ERROR: permission denied to grant role "pg_read_server_files" +DETAIL: You must have ADMIN OPTION on role "pg_read_server_files". CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files; -ERROR: must have admin option on role "pg_write_server_files" +ERROR: permission denied to grant role "pg_write_server_files" +DETAIL: You must have ADMIN OPTION on role "pg_write_server_files". CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program; -ERROR: must have admin option on role "pg_execute_server_program" +ERROR: permission denied to grant role "pg_execute_server_program" +DETAIL: You must have ADMIN OPTION on role "pg_execute_server_program". CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend; -ERROR: must have admin option on role "pg_signal_backend" +ERROR: permission denied to grant role "pg_signal_backend" +DETAIL: You must have ADMIN OPTION on role "pg_signal_backend". -- fail, role still owns database objects DROP ROLE regress_tenant; ERROR: role "regress_tenant" cannot be dropped because some objects depend on it @@ -211,11 +236,13 @@ DROP ROLE regress_inroles; DROP ROLE regress_adminroles; -- fail, cannot drop ourself, nor superusers or roles we lack ADMIN for DROP ROLE regress_role_super; -ERROR: must be superuser to drop superusers +ERROR: permission denied to drop role +DETAIL: You must have SUPERUSER privilege to drop roles with SUPERUSER. DROP ROLE regress_role_admin; ERROR: current user cannot be dropped DROP ROLE regress_rolecreator; -ERROR: must have admin option on role "regress_rolecreator" +ERROR: permission denied to drop role +DETAIL: You must have CREATEROLE privilege and ADMIN OPTION on role "regress_rolecreator". -- ok RESET SESSION AUTHORIZATION; REVOKE CREATE ON DATABASE regression FROM regress_role_admin CASCADE; diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out index 520035f6a0..78ee4d7448 100644 --- a/src/test/regress/expected/dependency.out +++ b/src/test/regress/expected/dependency.out @@ -48,12 +48,16 @@ SET SESSION AUTHORIZATION regress_dep_user0; -- permission denied DROP OWNED BY regress_dep_user1; ERROR: permission denied to drop objects +DETAIL: You must have privileges of role "regress_dep_user1". DROP OWNED BY regress_dep_user0, regress_dep_user2; ERROR: permission denied to drop objects +DETAIL: You must have privileges of role "regress_dep_user2". REASSIGN OWNED BY regress_dep_user0 TO regress_dep_user1; ERROR: permission denied to reassign objects +DETAIL: You must have privileges of role "regress_dep_user1". REASSIGN OWNED BY regress_dep_user1 TO regress_dep_user0; ERROR: permission denied to reassign objects +DETAIL: You must have privileges of role "regress_dep_user1". -- this one is allowed DROP OWNED BY regress_dep_user0; CREATE TABLE deptest1 (f1 int unique); diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 95d1e5515f..6794645e56 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -37,7 +37,7 @@ CREATE ROLE regress_priv_role; GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION; GRANT regress_priv_user1 TO regress_priv_user3 WITH ADMIN OPTION GRANTED BY regress_priv_user2; GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION GRANTED BY regress_priv_user3; -ERROR: admin option cannot be granted back to your own grantor +ERROR: ADMIN OPTION cannot be granted back to your own grantor -- need CASCADE to revoke grant or admin option if dependent grants exist REVOKE ADMIN OPTION FOR regress_priv_user1 FROM regress_priv_user2; -- fail ERROR: dependent privileges exist @@ -156,7 +156,8 @@ ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- duplicate NOTICE: role "regress_priv_user2" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user1" ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; ALTER USER regress_priv_user2 PASSWORD 'verysecret'; -- not permitted -ERROR: must have CREATEROLE privilege to change another user's password +ERROR: permission denied to alter role +DETAIL: To change another role's password, you must have CREATEROLE privilege and ADMIN OPTION on the role. RESET SESSION AUTHORIZATION; ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; REVOKE ADMIN OPTION FOR regress_priv_group2 FROM regress_priv_user1; @@ -168,7 +169,8 @@ CREATE FUNCTION leak(integer,integer) RETURNS boolean ALTER FUNCTION leak(integer,integer) OWNER TO regress_priv_user1; -- test owner privileges GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY regress_priv_role; -- error, doesn't have ADMIN OPTION -ERROR: grantor must have ADMIN OPTION on "regress_priv_role" +ERROR: permission denied to grant privileges as role "regress_priv_role" +DETAIL: The grantor must have ADMIN OPTION on role "regress_priv_role". GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY CURRENT_ROLE; REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY foo; -- error ERROR: role "foo" does not exist @@ -1795,7 +1797,8 @@ REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation CONTEXT: SQL function "mv_action" statement 1 BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". CONTEXT: SQL function "unwanted_grant" statement 1 SQL statement "SELECT unwanted_grant()" PL/pgSQL function sro_trojan() line 1 at PERFORM @@ -1825,10 +1828,12 @@ CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION SET ROLE regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended privilege -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_user1; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN NOTICE: role "regress_priv_user5" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user4" dogrant_ok @@ -1838,10 +1843,12 @@ NOTICE: role "regress_priv_user5" has already been granted membership in role " SET ROLE regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no self-admin -ERROR: must have admin option on role "regress_priv_group2" +ERROR: permission denied to grant role "regress_priv_group2" +DETAIL: You must have ADMIN OPTION on role "regress_priv_group2". SET SESSION AUTHORIZATION regress_priv_user4; DROP FUNCTION dogrant_ok(); REVOKE regress_priv_group2 FROM regress_priv_user5; -- 2.25.1 --8t9RHnE3ZwKMSgU+-- ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-01-26 19:05 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-12-11 12:54 [PATCH v27 4/5] Add ALTER INDEX ... ALTER COLLATION ... REFRESH VERSION. Julien Rouhaud <[email protected]> 2023-01-26 19:05 [PATCH v2 1/1] Improve user.c error messages. Nathan Bossart <[email protected]> 2023-01-26 19:05 [PATCH v4 1/2] Improve user.c error messages. Nathan Bossart <[email protected]> 2023-01-26 19:05 [PATCH v6 1/2] Improve user.c error messages. Nathan Bossart <[email protected]> 2023-01-26 19:05 [PATCH v3 1/1] Improve user.c error messages. Nathan Bossart <[email protected]> 2023-01-26 19:05 [PATCH v5 1/2] Improve user.c error messages. Nathan Bossart <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox