This is an automated email from the ASF dual-hosted git repository.
leborchuk pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new eaf8e2569bd Add predefined role pg_manage_resource_groups (#1795)
eaf8e2569bd is described below
commit eaf8e2569bd30691bf84ee78718e5154ecd3c5fd
Author: Alena Rybakina <[email protected]>
AuthorDate: Thu Aug 27 18:16:19 2026 +0300
Add predefined role pg_manage_resource_groups (#1795)
In Cloudberry/Greenplum only a superuser can manage resource groups.
In managed-service deployments superuser cannot be granted to the
client, so the cloud admin has no way to tune their own CPU and
memory limits.
Add a predefined role pg_manage_resource_groups (OID 6300) in
pg_authid.dat, following the upstream convention used by pg_monitor
and pg_read_all_data. Members of this role can CREATE, ALTER and DROP
resource groups and call pg_resgroup_move_query() without being a
superuser. Delegation is done with standard SQL:
GRANT pg_manage_resource_groups TO <user>;
The regression and isolation2 tests exercise the new role via GRANT.
The basic CREATE/ALTER/DROP test cases in the regression test are
adapted from open-gpdb/gpdb commit 3ac99962ad2.
Co-authored-by: Andrey Borodin <[email protected]>
---
doc/src/sgml/user-manag.sgml | 21 ++++
src/backend/commands/resgroupcmds.c | 49 ++++++--
src/backend/utils/resgroup/resgroup_helper.c | 11 +-
src/include/catalog/pg_authid.dat | 5 +
.../expected/resgroup/resgroup_mdb_admin.out | 123 ++++++++++++++++++
.../resgroup_pg_manage_resource_groups.out | 137 +++++++++++++++++++++
.../isolation2/isolation2_resgroup_v1_schedule | 1 +
.../isolation2/isolation2_resgroup_v2_schedule | 1 +
.../isolation2/sql/resgroup/resgroup_mdb_admin.sql | 89 +++++++++++++
.../resgroup_pg_manage_resource_groups.sql | 93 ++++++++++++++
src/test/regress/expected/resource_group.out | 94 +++++++++++++-
src/test/regress/sql/resource_group.sql | 68 +++++++++-
12 files changed, 674 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 1f7d7e75ce9..46fb9679dd9 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -693,6 +693,16 @@ DROP ROLE doomed_role;
database to issue
<link linkend="sql-createsubscription"><command>CREATE
SUBSCRIPTION</command></link>.</entry>
</row>
+ <row>
+ <entry>pg_manage_resource_groups</entry>
+ <entry>Allow
+ <link
linkend="sql-createresourcegroup"><command>CREATE</command></link>,
+ <link linkend="sql-alterresourcegroup"><command>ALTER</command></link>
and
+ <link linkend="sql-dropresourcegroup"><command>DROP RESOURCE
GROUP</command></link>,
+ and execution of <function>pg_resgroup_move_query()</function>, normally
+ restricted to superusers. The system <literal>admin_group</literal> can
+ still only be altered or dropped by a superuser.</entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -727,6 +737,17 @@ DROP ROLE doomed_role;
superuser. See <xref linkend="functions-admin-signal"/>.
</para>
+ <para>
+ The <literal>pg_manage_resource_groups</literal> role is intended to allow
+ trusted, non-superuser administrators (for example, the database owner in
+ managed deployments) to define and tune resource groups without holding
+ full superuser privileges. Members of this role can create, alter and drop
+ resource groups, and move running queries between groups with
+ <function>pg_resgroup_move_query()</function>. The system
+ <literal>admin_group</literal> is reserved for the cluster's control plane
+ and can only be altered or dropped by a real superuser.
+ </para>
+
<para>
The <literal>pg_read_server_files</literal>,
<literal>pg_write_server_files</literal> and
<literal>pg_execute_server_program</literal> roles are intended to allow
administrators to have
diff --git a/src/backend/commands/resgroupcmds.c
b/src/backend/commands/resgroupcmds.c
index 98545870244..af6feaf25dd 100644
--- a/src/backend/commands/resgroupcmds.c
+++ b/src/backend/commands/resgroupcmds.c
@@ -34,6 +34,7 @@
#include "commands/resgroupcmds.h"
#include "miscadmin.h"
#include "nodes/pg_list.h"
+#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/datetime.h"
#include "utils/fmgroids.h"
@@ -103,11 +104,15 @@ CreateResourceGroup(CreateResourceGroupStmt *stmt)
int nResGroups;
MemoryContext oldContext;
- /* Permission check - only superuser can create groups. */
- if (!superuser())
+ /*
+ * Permission check - superusers and members of the predefined role
+ * pg_manage_resource_groups can create resource groups.
+ */
+ if (!has_privs_of_role(GetUserId(), ROLE_PG_MANAGE_RESOURCE_GROUPS))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to create resource
groups")));
+ errmsg("permission denied to create resource
group"),
+ errhint("Must be superuser or have privileges
of the pg_manage_resource_groups role.")));
/*
* Check for an illegal name ('none' is used to signify no group in
ALTER ROLE).
@@ -269,11 +274,15 @@ DropResourceGroup(DropResourceGroupStmt *stmt)
Oid groupid;
ResourceGroupCallbackContext *callbackCtx;
- /* Permission check - only superuser can drop resource groups. */
- if (!superuser())
+ /*
+ * Permission check - superusers and members of the predefined role
+ * pg_manage_resource_groups can drop resource groups.
+ */
+ if (!has_privs_of_role(GetUserId(), ROLE_PG_MANAGE_RESOURCE_GROUPS))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to drop resource
groups")));
+ errmsg("permission denied to drop resource
group \"%s\"", stmt->name),
+ errhint("Must be superuser or have privileges
of the pg_manage_resource_groups role.")));
/*
* Check the pg_resgroup relation to be certain the resource group
already
@@ -375,11 +384,15 @@ AlterResourceGroup(AlterResourceGroupStmt *stmt)
ResourceGroupCallbackContext *callbackCtx;
MemoryContext oldContext;
- /* Permission check - only superuser can alter resource groups. */
- if (!superuser())
+ /*
+ * Permission check - superusers and members of the predefined role
+ * pg_manage_resource_groups can alter resource groups.
+ */
+ if (!has_privs_of_role(GetUserId(), ROLE_PG_MANAGE_RESOURCE_GROUPS))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to alter resource
groups")));
+ errmsg("permission denied to alter resource
group \"%s\"", stmt->name),
+ errhint("Must be superuser or have privileges
of the pg_manage_resource_groups role.")));
/* Currently we only support to ALTER one limit at one time */
Assert(list_length(stmt->options) == 1);
@@ -412,6 +425,18 @@ AlterResourceGroup(AlterResourceGroupStmt *stmt)
*/
groupid = get_resgroup_oid(stmt->name, false);
+ /*
+ * The built-in system resource groups may only be altered by a real
+ * superuser, never by a member of pg_manage_resource_groups
+ */
+ if (!superuser() &&
+ (groupid == ADMINRESGROUP_OID ||
+ groupid == SYSTEMRESGROUP_OID))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to alter resource
group \"%s\"", stmt->name),
+ errhint("Must be superuser to alter a system
resource group.")));
+
if (limitType == RESGROUP_LIMIT_TYPE_CONCURRENCY &&
value == 0 &&
groupid == ADMINRESGROUP_OID)
@@ -500,7 +525,7 @@ AlterResourceGroup(AlterResourceGroupStmt *stmt)
RESGROUP_DEFAULT_CPU_WEIGHT, "");
updateResgroupCapabilityEntry(pg_resgroupcapability_rel,
-
groupid, RESGROUP_LIMIT_TYPE_CPUSET,
+
groupid, RESGROUP_LIMIT_TYPE_CPUSET,
0,
caps.cpuset);
}
else if (limitType == RESGROUP_LIMIT_TYPE_CPU)
@@ -1007,7 +1032,7 @@ parseStmtOptions(CreateResourceGroupStmt *stmt,
ResGroupCaps *caps)
else
mask |= 1 << type;
- if (type == RESGROUP_LIMIT_TYPE_CPUSET)
+ if (type == RESGROUP_LIMIT_TYPE_CPUSET)
{
const char *cpuset = defGetString(defel);
strlcpy(caps->cpuset, cpuset, sizeof(caps->cpuset));
@@ -1611,7 +1636,7 @@ checkCpuSetByRole(const char *cpuset)
* ex:
* cpuset = "1;4"
* then we should assign '1' to corrdinator and '4' to segment
- *
+ *
* cpuset = "1"
* assign '1' to both coordinator and segment
*/
diff --git a/src/backend/utils/resgroup/resgroup_helper.c
b/src/backend/utils/resgroup/resgroup_helper.c
index 00aaded168d..319d94e4f2b 100644
--- a/src/backend/utils/resgroup/resgroup_helper.c
+++ b/src/backend/utils/resgroup/resgroup_helper.c
@@ -15,12 +15,14 @@
#include "funcapi.h"
#include "libpq-fe.h"
#include "miscadmin.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_resgroup.h"
#include "cdb/cdbdisp_query.h"
#include "cdb/cdbdispatchresult.h"
#include "cdb/cdbvars.h"
#include "commands/resgroupcmds.h"
#include "storage/procarray.h"
+#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/datetime.h"
#include "utils/resgroup.h"
@@ -464,10 +466,15 @@ pg_resgroup_move_query(PG_FUNCTION_ARGS)
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
(errmsg("resource group is not
enabled"))));
- if (!superuser())
+ /*
+ * Superusers and members of the predefined role
+ * pg_manage_resource_groups can move a query between resource groups.
+ */
+ if (!has_privs_of_role(GetUserId(), ROLE_PG_MANAGE_RESOURCE_GROUPS))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be superuser to
move query"))));
+ errmsg("permission denied to move query
between resource groups"),
+ errhint("Must be superuser or have privileges
of the pg_manage_resource_groups role.")));
if (Gp_role == GP_ROLE_DISPATCH)
{
diff --git a/src/include/catalog/pg_authid.dat
b/src/include/catalog/pg_authid.dat
index 6b4a0aaaad9..3c0ee0d1d10 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -89,6 +89,11 @@
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '6312', oid_symbol => 'ROLE_PG_MANAGE_RESOURCE_GROUPS',
+ rolname => 'pg_manage_resource_groups', rolsuper => 'f', rolinherit => 't',
+ rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+ rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+ rolpassword => '_null_', rolvaliduntil => '_null_' },
{ oid => '6304', oid_symbol => 'ROLE_PG_CREATE_SUBSCRIPTION',
rolname => 'pg_create_subscription', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
diff --git a/src/test/isolation2/expected/resgroup/resgroup_mdb_admin.out
b/src/test/isolation2/expected/resgroup/resgroup_mdb_admin.out
new file mode 100644
index 00000000000..d74f382821b
--- /dev/null
+++ b/src/test/isolation2/expected/resgroup/resgroup_mdb_admin.out
@@ -0,0 +1,123 @@
+-- Tests permission checks for the mdb_admin role with
+-- resource groups enabled.
+
+-- start_matchsubs
+-- m/ERROR: cannot find process: \d+/
+-- s/\d+/XXX/g
+-- end_matchsubs
+
+DROP ROLE IF EXISTS role_rg_admin;
+DROP
+DROP ROLE IF EXISTS role_rg_noadmin;
+DROP
+DROP ROLE IF EXISTS mdb_admin;
+DROP
+-- start_ignore
+DROP RESOURCE GROUP rg_perm_admin1;
+DROP RESOURCE GROUP rg_perm_admin2;
+DROP RESOURCE GROUP rg_perm_revoke1;
+DROP RESOURCE GROUP rg_perm_revoke2;
+DROP RESOURCE GROUP rg_perm_test;
+-- end_ignore
+
+-- ---------------------------------------------------------------------
+-- Setup. The mdb_admin role is not predefined in the catalog; it is
+-- created here the same way the control plane provisions it at runtime.
+-- ---------------------------------------------------------------------
+CREATE RESOURCE GROUP rg_perm_test WITH (concurrency=2, cpu_max_percent=10);
+CREATE
+CREATE ROLE mdb_admin;
+CREATE
+CREATE ROLE role_rg_admin RESOURCE GROUP rg_perm_test;
+CREATE
+CREATE ROLE role_rg_noadmin RESOURCE GROUP rg_perm_test;
+CREATE
+GRANT mdb_admin TO role_rg_admin;
+GRANT
+
+-- ---------------------------------------------------------------------
+-- 1. Member of mdb_admin can CREATE/ALTER/DROP resource groups
+-- (statements are dispatched to segments).
+-- ---------------------------------------------------------------------
+1: SET ROLE role_rg_admin;
+SET
+1: CREATE RESOURCE GROUP rg_perm_admin1 WITH (concurrency=1,
cpu_max_percent=5);
+CREATE
+1: ALTER RESOURCE GROUP rg_perm_admin1 SET cpu_max_percent 6;
+ALTER
+1: DROP RESOURCE GROUP rg_perm_admin1;
+DROP
+
+-- 2. Even a member cannot ALTER or DROP the system admin_group.
+1: ALTER RESOURCE GROUP admin_group SET cpu_max_percent 99;
+ERROR: must be superuser to alter resource group admin_group
+1: DROP RESOURCE GROUP admin_group;
+ERROR: must be superuser to drop resource group admin_group
+1q: ... <quitting>
+
+-- ---------------------------------------------------------------------
+-- 3. A non-member is rejected on every entry point.
+-- ---------------------------------------------------------------------
+2: SET ROLE role_rg_noadmin;
+SET
+2: CREATE RESOURCE GROUP rg_perm_admin2 WITH (concurrency=1,
cpu_max_percent=5);
+ERROR: must be mdb_admin to create resource groups
+2: ALTER RESOURCE GROUP rg_perm_test SET cpu_max_percent 7;
+ERROR: must be mdb_admin to alter resource groups
+2: DROP RESOURCE GROUP rg_perm_test;
+ERROR: must be mdb_admin to drop resource groups
+2q: ... <quitting>
+
+-- ---------------------------------------------------------------------
+-- 4. pg_resgroup_move_query() honours the same permission check.
+-- The first call (non-member) must fail with "must be mdb_admin".
+-- The second call (member) gets past the permission gate and
+-- fails on the pid lookup (masked by start_matchsubs above).
+-- ---------------------------------------------------------------------
+3: SET ROLE role_rg_noadmin;
+SET
+3: SELECT pg_resgroup_move_query(999999999, 'admin_group');
+ERROR: must be mdb_admin to move query
+3: RESET ROLE;
+RESET
+3: SET ROLE role_rg_admin;
+SET
+3: SELECT pg_resgroup_move_query(999999999, 'admin_group');
+ERROR: cannot find process: XXX
+3q: ... <quitting>
+
+-- ---------------------------------------------------------------------
+-- 5. Cross-session REVOKE takes effect on the granted session's
+-- next statement (the privilege is re-checked per command, not
+-- cached at SET ROLE time).
+-- ---------------------------------------------------------------------
+4: SET ROLE role_rg_admin;
+SET
+4: CREATE RESOURCE GROUP rg_perm_revoke1 WITH (concurrency=1,
cpu_max_percent=5);
+CREATE
+5: REVOKE mdb_admin FROM role_rg_admin;
+REVOKE
+4: CREATE RESOURCE GROUP rg_perm_revoke2 WITH (concurrency=1,
cpu_max_percent=5);
+ERROR: must be mdb_admin to create resource groups
+4: DROP RESOURCE GROUP rg_perm_revoke1;
+ERROR: must be mdb_admin to drop resource groups
+4q: ... <quitting>
+5q: ... <quitting>
+
+-- ---------------------------------------------------------------------
+-- Cleanup. Roles must be dropped before the resource group they
+-- reference, otherwise DROP RESOURCE GROUP fails with
+-- "resource group is used by at least one role".
+-- ---------------------------------------------------------------------
+RESET ROLE;
+RESET
+DROP ROLE role_rg_admin;
+DROP
+DROP ROLE role_rg_noadmin;
+DROP
+DROP ROLE mdb_admin;
+DROP
+DROP RESOURCE GROUP rg_perm_revoke1;
+DROP
+DROP RESOURCE GROUP rg_perm_test;
+DROP
diff --git
a/src/test/isolation2/expected/resgroup/resgroup_pg_manage_resource_groups.out
b/src/test/isolation2/expected/resgroup/resgroup_pg_manage_resource_groups.out
new file mode 100644
index 00000000000..6d1ccd74afc
--- /dev/null
+++
b/src/test/isolation2/expected/resgroup/resgroup_pg_manage_resource_groups.out
@@ -0,0 +1,137 @@
+-- Tests permission checks for pg_manage_resource_groups with
+-- resource groups enabled.
+
+-- start_matchsubs
+-- m/ERROR: cannot find process: \d+/
+-- s/\d+/XXX/g
+-- end_matchsubs
+
+DROP ROLE IF EXISTS role_rg_admin;
+DROP
+DROP ROLE IF EXISTS role_rg_noadmin;
+DROP
+-- start_ignore
+DROP RESOURCE GROUP rg_perm_admin1;
+DROP RESOURCE GROUP rg_perm_admin2;
+DROP RESOURCE GROUP rg_perm_revoke1;
+DROP RESOURCE GROUP rg_perm_revoke2;
+DROP RESOURCE GROUP rg_perm_test;
+-- end_ignore
+
+-- ---------------------------------------------------------------------
+-- Setup.
+-- ---------------------------------------------------------------------
+CREATE RESOURCE GROUP rg_perm_test WITH (concurrency=2, cpu_max_percent=10);
+CREATE
+CREATE ROLE role_rg_admin RESOURCE GROUP rg_perm_test;
+CREATE
+CREATE ROLE role_rg_noadmin RESOURCE GROUP rg_perm_test;
+CREATE
+GRANT pg_manage_resource_groups TO role_rg_admin;
+GRANT
+
+-- The predefined role must be present after initdb.
+SELECT 1 AS role_exists FROM pg_roles WHERE rolname =
'pg_manage_resource_groups';
+ role_exists
+-------------
+ 1
+(1 row)
+
+-- ---------------------------------------------------------------------
+-- 1. Member of pg_manage_resource_groups can CREATE/ALTER/DROP
+-- resource groups (statements are dispatched to segments).
+-- ---------------------------------------------------------------------
+1: SET ROLE role_rg_admin;
+SET
+1: CREATE RESOURCE GROUP rg_perm_admin1 WITH (concurrency=1,
cpu_max_percent=5);
+CREATE
+1: ALTER RESOURCE GROUP rg_perm_admin1 SET cpu_max_percent 6;
+ALTER
+1: DROP RESOURCE GROUP rg_perm_admin1;
+DROP
+
+-- 2. Even a member cannot ALTER or DROP the system admin_group.
+1: ALTER RESOURCE GROUP admin_group SET cpu_max_percent 99;
+ERROR: permission denied to alter resource group "admin_group"
+HINT: Must be superuser to alter a system resource group.
+1: DROP RESOURCE GROUP admin_group;
+ERROR: cannot drop default resource group "admin_group"
+1q: ... <quitting>
+
+-- 2b. A real superuser can still ALTER a system group (invariant preserved,
+-- value restored to its default afterwards).
+ALTER RESOURCE GROUP admin_group SET cpu_max_percent 9;
+ALTER
+ALTER RESOURCE GROUP admin_group SET cpu_max_percent 10;
+ALTER
+
+-- ---------------------------------------------------------------------
+-- 3. A non-member is rejected on every entry point.
+-- ---------------------------------------------------------------------
+2: SET ROLE role_rg_noadmin;
+SET
+2: CREATE RESOURCE GROUP rg_perm_admin2 WITH (concurrency=1,
cpu_max_percent=5);
+ERROR: permission denied to create resource group
+HINT: Must be superuser or have privileges of the pg_manage_resource_groups
role.
+2: ALTER RESOURCE GROUP rg_perm_test SET cpu_max_percent 7;
+ERROR: permission denied to alter resource group "rg_perm_test"
+HINT: Must be superuser or have privileges of the pg_manage_resource_groups
role.
+2: DROP RESOURCE GROUP rg_perm_test;
+ERROR: permission denied to drop resource group "rg_perm_test"
+HINT: Must be superuser or have privileges of the pg_manage_resource_groups
role.
+2q: ... <quitting>
+
+-- ---------------------------------------------------------------------
+-- 4. pg_resgroup_move_query() honours the same permission check.
+-- The first call (non-member) must fail with "permission denied".
+-- The second call (member) gets past the permission gate and
+-- fails on the pid lookup (masked by start_matchsubs above).
+-- ---------------------------------------------------------------------
+3: SET ROLE role_rg_noadmin;
+SET
+3: SELECT pg_resgroup_move_query(999999999, 'admin_group');
+ERROR: permission denied to move query between resource groups
+HINT: Must be superuser or have privileges of the pg_manage_resource_groups
role.
+3: RESET ROLE;
+RESET
+3: SET ROLE role_rg_admin;
+SET
+3: SELECT pg_resgroup_move_query(999999999, 'admin_group');
+ERROR: cannot find process: XXX
+3q: ... <quitting>
+
+-- ---------------------------------------------------------------------
+-- 5. Cross-session REVOKE takes effect on the granted session's
+-- next statement (the privilege is re-checked per command, not
+-- cached at SET ROLE time).
+-- ---------------------------------------------------------------------
+4: SET ROLE role_rg_admin;
+SET
+4: CREATE RESOURCE GROUP rg_perm_revoke1 WITH (concurrency=1,
cpu_max_percent=5);
+CREATE
+5: REVOKE pg_manage_resource_groups FROM role_rg_admin;
+REVOKE
+4: CREATE RESOURCE GROUP rg_perm_revoke2 WITH (concurrency=1,
cpu_max_percent=5);
+ERROR: permission denied to create resource group
+HINT: Must be superuser or have privileges of the pg_manage_resource_groups
role.
+4: DROP RESOURCE GROUP rg_perm_revoke1;
+ERROR: permission denied to drop resource group "rg_perm_revoke1"
+HINT: Must be superuser or have privileges of the pg_manage_resource_groups
role.
+4q: ... <quitting>
+5q: ... <quitting>
+
+-- ---------------------------------------------------------------------
+-- Cleanup. Roles must be dropped before the resource group they
+-- reference, otherwise DROP RESOURCE GROUP fails with
+-- "resource group is used by at least one role".
+-- ---------------------------------------------------------------------
+RESET ROLE;
+RESET
+DROP ROLE role_rg_admin;
+DROP
+DROP ROLE role_rg_noadmin;
+DROP
+DROP RESOURCE GROUP rg_perm_revoke1;
+DROP
+DROP RESOURCE GROUP rg_perm_test;
+DROP
diff --git a/src/test/isolation2/isolation2_resgroup_v1_schedule
b/src/test/isolation2/isolation2_resgroup_v1_schedule
index f5dea2d4012..756d46df1e0 100644
--- a/src/test/isolation2/isolation2_resgroup_v1_schedule
+++ b/src/test/isolation2/isolation2_resgroup_v1_schedule
@@ -34,6 +34,7 @@ test: resgroup/resgroup_move_query
# regression tests
test: resgroup/resgroup_recreate
test: resgroup/resgroup_functions
+test: resgroup/resgroup_pg_manage_resource_groups
# dump info
test: resgroup/resgroup_dumpinfo
diff --git a/src/test/isolation2/isolation2_resgroup_v2_schedule
b/src/test/isolation2/isolation2_resgroup_v2_schedule
index 0190efcd3d8..252d9deafcf 100644
--- a/src/test/isolation2/isolation2_resgroup_v2_schedule
+++ b/src/test/isolation2/isolation2_resgroup_v2_schedule
@@ -35,6 +35,7 @@ test: resgroup/resgroup_io_limit
# regression tests
test: resgroup/resgroup_recreate
test: resgroup/resgroup_functions
+test: resgroup/resgroup_pg_manage_resource_groups
# parallel tests
#test: resgroup/restore_default_resgroup
diff --git a/src/test/isolation2/sql/resgroup/resgroup_mdb_admin.sql
b/src/test/isolation2/sql/resgroup/resgroup_mdb_admin.sql
new file mode 100644
index 00000000000..3eedba5bc4e
--- /dev/null
+++ b/src/test/isolation2/sql/resgroup/resgroup_mdb_admin.sql
@@ -0,0 +1,89 @@
+-- Tests permission checks for the mdb_admin role with
+-- resource groups enabled.
+
+-- start_matchsubs
+-- m/ERROR: cannot find process: \d+/
+-- s/\d+/XXX/g
+-- end_matchsubs
+
+DROP ROLE IF EXISTS role_rg_admin;
+DROP ROLE IF EXISTS role_rg_noadmin;
+DROP ROLE IF EXISTS mdb_admin;
+-- start_ignore
+DROP RESOURCE GROUP rg_perm_admin1;
+DROP RESOURCE GROUP rg_perm_admin2;
+DROP RESOURCE GROUP rg_perm_revoke1;
+DROP RESOURCE GROUP rg_perm_revoke2;
+DROP RESOURCE GROUP rg_perm_test;
+-- end_ignore
+
+-- ---------------------------------------------------------------------
+-- Setup. The mdb_admin role is not predefined in the catalog; it is
+-- created here the same way the control plane provisions it at runtime.
+-- ---------------------------------------------------------------------
+CREATE RESOURCE GROUP rg_perm_test WITH (concurrency=2, cpu_max_percent=10);
+CREATE ROLE mdb_admin;
+CREATE ROLE role_rg_admin RESOURCE GROUP rg_perm_test;
+CREATE ROLE role_rg_noadmin RESOURCE GROUP rg_perm_test;
+GRANT mdb_admin TO role_rg_admin;
+
+-- ---------------------------------------------------------------------
+-- 1. Member of mdb_admin can CREATE/ALTER/DROP resource groups
+-- (statements are dispatched to segments).
+-- ---------------------------------------------------------------------
+1: SET ROLE role_rg_admin;
+1: CREATE RESOURCE GROUP rg_perm_admin1 WITH (concurrency=1,
cpu_max_percent=5);
+1: ALTER RESOURCE GROUP rg_perm_admin1 SET cpu_max_percent 6;
+1: DROP RESOURCE GROUP rg_perm_admin1;
+
+-- 2. Even a member cannot ALTER or DROP the system admin_group.
+1: ALTER RESOURCE GROUP admin_group SET cpu_max_percent 99;
+1: DROP RESOURCE GROUP admin_group;
+1q:
+
+-- ---------------------------------------------------------------------
+-- 3. A non-member is rejected on every entry point.
+-- ---------------------------------------------------------------------
+2: SET ROLE role_rg_noadmin;
+2: CREATE RESOURCE GROUP rg_perm_admin2 WITH (concurrency=1,
cpu_max_percent=5);
+2: ALTER RESOURCE GROUP rg_perm_test SET cpu_max_percent 7;
+2: DROP RESOURCE GROUP rg_perm_test;
+2q:
+
+-- ---------------------------------------------------------------------
+-- 4. pg_resgroup_move_query() honours the same permission check.
+-- The first call (non-member) must fail with "must be mdb_admin".
+-- The second call (member) gets past the permission gate and
+-- fails on the pid lookup (masked by start_matchsubs above).
+-- ---------------------------------------------------------------------
+3: SET ROLE role_rg_noadmin;
+3: SELECT pg_resgroup_move_query(999999999, 'admin_group');
+3: RESET ROLE;
+3: SET ROLE role_rg_admin;
+3: SELECT pg_resgroup_move_query(999999999, 'admin_group');
+3q:
+
+-- ---------------------------------------------------------------------
+-- 5. Cross-session REVOKE takes effect on the granted session's
+-- next statement (the privilege is re-checked per command, not
+-- cached at SET ROLE time).
+-- ---------------------------------------------------------------------
+4: SET ROLE role_rg_admin;
+4: CREATE RESOURCE GROUP rg_perm_revoke1 WITH (concurrency=1,
cpu_max_percent=5);
+5: REVOKE mdb_admin FROM role_rg_admin;
+4: CREATE RESOURCE GROUP rg_perm_revoke2 WITH (concurrency=1,
cpu_max_percent=5);
+4: DROP RESOURCE GROUP rg_perm_revoke1;
+4q:
+5q:
+
+-- ---------------------------------------------------------------------
+-- Cleanup. Roles must be dropped before the resource group they
+-- reference, otherwise DROP RESOURCE GROUP fails with
+-- "resource group is used by at least one role".
+-- ---------------------------------------------------------------------
+RESET ROLE;
+DROP ROLE role_rg_admin;
+DROP ROLE role_rg_noadmin;
+DROP ROLE mdb_admin;
+DROP RESOURCE GROUP rg_perm_revoke1;
+DROP RESOURCE GROUP rg_perm_test;
diff --git
a/src/test/isolation2/sql/resgroup/resgroup_pg_manage_resource_groups.sql
b/src/test/isolation2/sql/resgroup/resgroup_pg_manage_resource_groups.sql
new file mode 100644
index 00000000000..e0347610fbf
--- /dev/null
+++ b/src/test/isolation2/sql/resgroup/resgroup_pg_manage_resource_groups.sql
@@ -0,0 +1,93 @@
+-- Tests permission checks for pg_manage_resource_groups with
+-- resource groups enabled.
+
+-- start_matchsubs
+-- m/ERROR: cannot find process: \d+/
+-- s/\d+/XXX/g
+-- end_matchsubs
+
+DROP ROLE IF EXISTS role_rg_admin;
+DROP ROLE IF EXISTS role_rg_noadmin;
+-- start_ignore
+DROP RESOURCE GROUP rg_perm_admin1;
+DROP RESOURCE GROUP rg_perm_admin2;
+DROP RESOURCE GROUP rg_perm_revoke1;
+DROP RESOURCE GROUP rg_perm_revoke2;
+DROP RESOURCE GROUP rg_perm_test;
+-- end_ignore
+
+-- ---------------------------------------------------------------------
+-- Setup.
+-- ---------------------------------------------------------------------
+CREATE RESOURCE GROUP rg_perm_test WITH (concurrency=2, cpu_max_percent=10);
+CREATE ROLE role_rg_admin RESOURCE GROUP rg_perm_test;
+CREATE ROLE role_rg_noadmin RESOURCE GROUP rg_perm_test;
+GRANT pg_manage_resource_groups TO role_rg_admin;
+
+-- The predefined role must be present after initdb.
+SELECT 1 AS role_exists FROM pg_roles WHERE rolname =
'pg_manage_resource_groups';
+
+-- ---------------------------------------------------------------------
+-- 1. Member of pg_manage_resource_groups can CREATE/ALTER/DROP
+-- resource groups (statements are dispatched to segments).
+-- ---------------------------------------------------------------------
+1: SET ROLE role_rg_admin;
+1: CREATE RESOURCE GROUP rg_perm_admin1 WITH (concurrency=1,
cpu_max_percent=5);
+1: ALTER RESOURCE GROUP rg_perm_admin1 SET cpu_max_percent 6;
+1: DROP RESOURCE GROUP rg_perm_admin1;
+
+-- 2. Even a member cannot ALTER or DROP the system admin_group.
+1: ALTER RESOURCE GROUP admin_group SET cpu_max_percent 99;
+1: DROP RESOURCE GROUP admin_group;
+1q:
+
+-- 2b. A real superuser can still ALTER a system group (invariant preserved,
+-- value restored to its default afterwards).
+ALTER RESOURCE GROUP admin_group SET cpu_max_percent 9;
+ALTER RESOURCE GROUP admin_group SET cpu_max_percent 10;
+
+-- ---------------------------------------------------------------------
+-- 3. A non-member is rejected on every entry point.
+-- ---------------------------------------------------------------------
+2: SET ROLE role_rg_noadmin;
+2: CREATE RESOURCE GROUP rg_perm_admin2 WITH (concurrency=1,
cpu_max_percent=5);
+2: ALTER RESOURCE GROUP rg_perm_test SET cpu_max_percent 7;
+2: DROP RESOURCE GROUP rg_perm_test;
+2q:
+
+-- ---------------------------------------------------------------------
+-- 4. pg_resgroup_move_query() honours the same permission check.
+-- The first call (non-member) must fail with "permission denied".
+-- The second call (member) gets past the permission gate and
+-- fails on the pid lookup (masked by start_matchsubs above).
+-- ---------------------------------------------------------------------
+3: SET ROLE role_rg_noadmin;
+3: SELECT pg_resgroup_move_query(999999999, 'admin_group');
+3: RESET ROLE;
+3: SET ROLE role_rg_admin;
+3: SELECT pg_resgroup_move_query(999999999, 'admin_group');
+3q:
+
+-- ---------------------------------------------------------------------
+-- 5. Cross-session REVOKE takes effect on the granted session's
+-- next statement (the privilege is re-checked per command, not
+-- cached at SET ROLE time).
+-- ---------------------------------------------------------------------
+4: SET ROLE role_rg_admin;
+4: CREATE RESOURCE GROUP rg_perm_revoke1 WITH (concurrency=1,
cpu_max_percent=5);
+5: REVOKE pg_manage_resource_groups FROM role_rg_admin;
+4: CREATE RESOURCE GROUP rg_perm_revoke2 WITH (concurrency=1,
cpu_max_percent=5);
+4: DROP RESOURCE GROUP rg_perm_revoke1;
+4q:
+5q:
+
+-- ---------------------------------------------------------------------
+-- Cleanup. Roles must be dropped before the resource group they
+-- reference, otherwise DROP RESOURCE GROUP fails with
+-- "resource group is used by at least one role".
+-- ---------------------------------------------------------------------
+RESET ROLE;
+DROP ROLE role_rg_admin;
+DROP ROLE role_rg_noadmin;
+DROP RESOURCE GROUP rg_perm_revoke1;
+DROP RESOURCE GROUP rg_perm_test;
diff --git a/src/test/regress/expected/resource_group.out
b/src/test/regress/expected/resource_group.out
index 0bcf2b14474..655cd4bc0db 100644
--- a/src/test/regress/expected/resource_group.out
+++ b/src/test/regress/expected/resource_group.out
@@ -4,9 +4,9 @@
-- this test creates resource group objects and roles associated with
-- resource groups so pg_dumpall/pg_upgrade can dump those objects at
-- the end of ICW.
---
+--
-- NOTE: please always put this test in the end of this file and do not
--- drop them.
+-- drop the rg_dump_test* objects.
-- start_ignore
DROP ROLE IF EXISTS role_dump_test1;
NOTICE: role "role_dump_test1" does not exist, skipping
@@ -14,13 +14,33 @@ DROP ROLE IF EXISTS role_dump_test2;
NOTICE: role "role_dump_test2" does not exist, skipping
DROP ROLE IF EXISTS role_dump_test3;
NOTICE: role "role_dump_test3" does not exist, skipping
+DROP ROLE IF EXISTS rg_admin_test;
+NOTICE: role "rg_admin_test" does not exist, skipping
+DROP ROLE IF EXISTS rg_noadmin_test;
+NOTICE: role "rg_noadmin_test" does not exist, skipping
DROP RESOURCE GROUP rg_dump_test1;
ERROR: resource group "rg_dump_test1" does not exist
DROP RESOURCE GROUP rg_dump_test2;
ERROR: resource group "rg_dump_test2" does not exist
DROP RESOURCE GROUP rg_dump_test3;
ERROR: resource group "rg_dump_test3" does not exist
+DROP RESOURCE GROUP rg_admin_test1;
+ERROR: resource group "rg_admin_test1" does not exist
+DROP RESOURCE GROUP rg_admin_test2;
+ERROR: resource group "rg_admin_test2" does not exist
-- end_ignore
+-- The predefined role must be present after initdb.
+SELECT 1 AS role_exists
+ FROM pg_roles
+ WHERE rolname = 'pg_manage_resource_groups';
+ role_exists
+-------------
+ 1
+(1 row)
+
+-- The predefined role is pinned and cannot be dropped, even by a superuser.
+DROP ROLE pg_manage_resource_groups;
+ERROR: cannot drop role pg_manage_resource_groups because it is required by
the database system
CREATE RESOURCE GROUP rg_dump_test1 WITH (concurrency=2, cpu_max_percent=5);
WARNING: resource group is disabled
HINT: To enable set gp_resource_manager=group
@@ -42,3 +62,73 @@ CREATE ROLE role_dump_test3 RESOURCE GROUP rg_dump_test3;
NOTICE: resource queue required -- using default resource queue "pg_default"
WARNING: resource group is disabled
HINT: To enable set gp_resource_manager=group
+-- Test that members of the predefined role pg_manage_resource_groups
+-- can manage resource groups without superuser, but cannot touch
+-- the system admin_group.
+CREATE ROLE rg_admin_test RESOURCE GROUP rg_dump_test1;
+NOTICE: resource queue required -- using default resource queue "pg_default"
+WARNING: resource group is disabled
+HINT: To enable set gp_resource_manager=group
+CREATE ROLE rg_noadmin_test RESOURCE GROUP rg_dump_test1;
+NOTICE: resource queue required -- using default resource queue "pg_default"
+WARNING: resource group is disabled
+HINT: To enable set gp_resource_manager=group
+GRANT pg_manage_resource_groups TO rg_admin_test;
+SET ROLE rg_admin_test;
+CREATE RESOURCE GROUP rg_admin_test1 WITH (concurrency=2, cpu_max_percent=5,
memory_quota=5);
+WARNING: resource group is disabled
+HINT: To enable set gp_resource_manager=group
+CREATE RESOURCE GROUP rg_admin_test2 WITH (concurrency=2, cpu_max_percent=5,
memory_quota=5);
+WARNING: resource group is disabled
+HINT: To enable set gp_resource_manager=group
+ALTER RESOURCE GROUP rg_admin_test1 SET cpu_max_percent 2;
+DROP RESOURCE GROUP rg_admin_test1;
+ALTER RESOURCE GROUP admin_group SET cpu_max_percent 2;
+ERROR: permission denied to alter resource group "admin_group"
+HINT: Must be superuser to alter a system resource group.
+DROP RESOURCE GROUP admin_group;
+ERROR: cannot drop default resource group "admin_group"
+-- But a real superuser can still ALTER a system resource group (restored
after).
+RESET ROLE;
+ALTER RESOURCE GROUP admin_group SET cpu_max_percent 9;
+ALTER RESOURCE GROUP admin_group SET cpu_max_percent 10;
+SET ROLE rg_noadmin_test;
+CREATE RESOURCE GROUP rg_admin_test1 WITH (concurrency=2, cpu_max_percent=5,
memory_quota=5);
+ERROR: permission denied to create resource group
+HINT: Must be superuser or have privileges of the pg_manage_resource_groups
role.
+ALTER RESOURCE GROUP rg_admin_test2 SET cpu_max_percent 2;
+ERROR: permission denied to alter resource group "rg_admin_test2"
+HINT: Must be superuser or have privileges of the pg_manage_resource_groups
role.
+DROP RESOURCE GROUP rg_admin_test2;
+ERROR: permission denied to drop resource group "rg_admin_test2"
+HINT: Must be superuser or have privileges of the pg_manage_resource_groups
role.
+DROP RESOURCE GROUP admin_group;
+ERROR: permission denied to drop resource group "admin_group"
+HINT: Must be superuser or have privileges of the pg_manage_resource_groups
role.
+-- After REVOKE the role loses its privileges immediately.
+RESET ROLE;
+REVOKE pg_manage_resource_groups FROM rg_admin_test;
+SET ROLE rg_admin_test;
+CREATE RESOURCE GROUP rg_admin_test3 WITH (concurrency=2, cpu_max_percent=5,
memory_quota=5);
+ERROR: permission denied to create resource group
+HINT: Must be superuser or have privileges of the pg_manage_resource_groups
role.
+ALTER RESOURCE GROUP rg_admin_test2 SET cpu_max_percent 3;
+ERROR: permission denied to alter resource group "rg_admin_test2"
+HINT: Must be superuser or have privileges of the pg_manage_resource_groups
role.
+DROP RESOURCE GROUP rg_admin_test2;
+ERROR: permission denied to drop resource group "rg_admin_test2"
+HINT: Must be superuser or have privileges of the pg_manage_resource_groups
role.
+-- Transitive membership through an intermediate role must also work.
+RESET ROLE;
+CREATE ROLE rg_admin_grp;
+GRANT pg_manage_resource_groups TO rg_admin_grp;
+GRANT rg_admin_grp TO rg_admin_test;
+SET ROLE rg_admin_test;
+ALTER RESOURCE GROUP rg_admin_test2 SET cpu_max_percent 4;
+DROP RESOURCE GROUP rg_admin_test2;
+RESET ROLE;
+REVOKE rg_admin_grp FROM rg_admin_test;
+REVOKE pg_manage_resource_groups FROM rg_admin_grp;
+DROP ROLE rg_admin_grp;
+DROP ROLE rg_admin_test;
+DROP ROLE rg_noadmin_test;
diff --git a/src/test/regress/sql/resource_group.sql
b/src/test/regress/sql/resource_group.sql
index 1411eb0d61f..2795b43068c 100644
--- a/src/test/regress/sql/resource_group.sql
+++ b/src/test/regress/sql/resource_group.sql
@@ -4,20 +4,32 @@
-- this test creates resource group objects and roles associated with
-- resource groups so pg_dumpall/pg_upgrade can dump those objects at
-- the end of ICW.
---
+--
-- NOTE: please always put this test in the end of this file and do not
--- drop them.
+-- drop the rg_dump_test* objects.
-- start_ignore
DROP ROLE IF EXISTS role_dump_test1;
DROP ROLE IF EXISTS role_dump_test2;
DROP ROLE IF EXISTS role_dump_test3;
+DROP ROLE IF EXISTS rg_admin_test;
+DROP ROLE IF EXISTS rg_noadmin_test;
DROP RESOURCE GROUP rg_dump_test1;
DROP RESOURCE GROUP rg_dump_test2;
DROP RESOURCE GROUP rg_dump_test3;
+DROP RESOURCE GROUP rg_admin_test1;
+DROP RESOURCE GROUP rg_admin_test2;
-- end_ignore
+-- The predefined role must be present after initdb.
+SELECT 1 AS role_exists
+ FROM pg_roles
+ WHERE rolname = 'pg_manage_resource_groups';
+
+-- The predefined role is pinned and cannot be dropped, even by a superuser.
+DROP ROLE pg_manage_resource_groups;
+
CREATE RESOURCE GROUP rg_dump_test1 WITH (concurrency=2, cpu_max_percent=5);
CREATE RESOURCE GROUP rg_dump_test2 WITH (concurrency=2, cpu_max_percent=5);
CREATE RESOURCE GROUP rg_dump_test3 WITH (concurrency=2, cpu_max_percent=5);
@@ -25,3 +37,55 @@ CREATE RESOURCE GROUP rg_dump_test3 WITH (concurrency=2,
cpu_max_percent=5);
CREATE ROLE role_dump_test1 RESOURCE GROUP rg_dump_test1;
CREATE ROLE role_dump_test2 RESOURCE GROUP rg_dump_test2;
CREATE ROLE role_dump_test3 RESOURCE GROUP rg_dump_test3;
+
+-- Test that members of the predefined role pg_manage_resource_groups
+-- can manage resource groups without superuser, but cannot touch
+-- the system admin_group.
+CREATE ROLE rg_admin_test RESOURCE GROUP rg_dump_test1;
+CREATE ROLE rg_noadmin_test RESOURCE GROUP rg_dump_test1;
+GRANT pg_manage_resource_groups TO rg_admin_test;
+
+SET ROLE rg_admin_test;
+
+CREATE RESOURCE GROUP rg_admin_test1 WITH (concurrency=2, cpu_max_percent=5,
memory_quota=5);
+CREATE RESOURCE GROUP rg_admin_test2 WITH (concurrency=2, cpu_max_percent=5,
memory_quota=5);
+ALTER RESOURCE GROUP rg_admin_test1 SET cpu_max_percent 2;
+DROP RESOURCE GROUP rg_admin_test1;
+ALTER RESOURCE GROUP admin_group SET cpu_max_percent 2;
+DROP RESOURCE GROUP admin_group;
+
+-- But a real superuser can still ALTER a system resource group (restored
after).
+RESET ROLE;
+ALTER RESOURCE GROUP admin_group SET cpu_max_percent 9;
+ALTER RESOURCE GROUP admin_group SET cpu_max_percent 10;
+
+SET ROLE rg_noadmin_test;
+
+CREATE RESOURCE GROUP rg_admin_test1 WITH (concurrency=2, cpu_max_percent=5,
memory_quota=5);
+ALTER RESOURCE GROUP rg_admin_test2 SET cpu_max_percent 2;
+DROP RESOURCE GROUP rg_admin_test2;
+DROP RESOURCE GROUP admin_group;
+
+-- After REVOKE the role loses its privileges immediately.
+RESET ROLE;
+REVOKE pg_manage_resource_groups FROM rg_admin_test;
+SET ROLE rg_admin_test;
+CREATE RESOURCE GROUP rg_admin_test3 WITH (concurrency=2, cpu_max_percent=5,
memory_quota=5);
+ALTER RESOURCE GROUP rg_admin_test2 SET cpu_max_percent 3;
+DROP RESOURCE GROUP rg_admin_test2;
+
+-- Transitive membership through an intermediate role must also work.
+RESET ROLE;
+CREATE ROLE rg_admin_grp;
+GRANT pg_manage_resource_groups TO rg_admin_grp;
+GRANT rg_admin_grp TO rg_admin_test;
+SET ROLE rg_admin_test;
+ALTER RESOURCE GROUP rg_admin_test2 SET cpu_max_percent 4;
+DROP RESOURCE GROUP rg_admin_test2;
+
+RESET ROLE;
+REVOKE rg_admin_grp FROM rg_admin_test;
+REVOKE pg_manage_resource_groups FROM rg_admin_grp;
+DROP ROLE rg_admin_grp;
+DROP ROLE rg_admin_test;
+DROP ROLE rg_noadmin_test;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
