You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by re...@apache.org on 2025/09/19 14:05:09 UTC
(cloudberry) 03/04: MDB admin patch & tests
This is an automated email from the ASF dual-hosted git repository.
reshke pushed a commit to branch mdb_admin_2
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit a9dd2c5c8072ba48abdd8d1a29e5a5041beb6cd5
Author: reshke <re...@double.cloud>
AuthorDate: Fri Sep 19 13:22:50 2025 +0000
MDB admin patch & tests
This patch introcudes new pseudo-pre-defined role "mdb_admin".
Introduces 2 new function:
extern bool mdb_admin_allow_bypass_owner_checks(Oid userId, Oid ownerId);
extern void check_mdb_admin_is_member_of_role(Oid member, Oid role);
To check mdb admin belongship and role-to-role ownership transfer
correctness.
Our mdb_admin ACL model is the following:
* Any roles user or/and roles can be granted with mdb_admin
* mdb_admin memeber can tranfser ownershup of relations,
namespaces and functions to other roles, if target role in neither:
superuser, pg_read_server_files, pg_write_server_files nor
pg_execute_server_program.
This patch allows mdb admin to tranfers ownership on non-superuser objects
---
src/backend/catalog/namespace.c | 20 ++++-
src/backend/commands/alter.c | 8 +-
src/backend/commands/functioncmds.c | 20 +++--
src/backend/commands/schemacmds.c | 13 ++-
src/backend/commands/tablecmds.c | 12 +--
src/backend/storage/ipc/signalfuncs.c | 28 +++++-
src/backend/utils/activity/backend_status.c | 16 ++++
src/backend/utils/adt/acl.c | 112 ++++++++++++++++++++++++
src/backend/utils/misc/guc.c | 2 +-
src/include/utils/acl.h | 7 ++
src/include/utils/backend_status.h | 3 +
src/include/utils/guc_tables.h | 2 +
src/test/Makefile | 3 +
src/test/mdb_admin/.gitignore | 2 +
src/test/mdb_admin/Makefile | 23 +++++
src/test/mdb_admin/t/signals.pl | 74 ++++++++++++++++
src/test/regress/expected/create_function_3.out | 4 +-
src/test/regress/expected/mdb_admin.out | 81 +++++++++++++++++
src/test/regress/parallel_schedule | 4 +
src/test/regress/sql/mdb_admin.sql | 77 ++++++++++++++++
20 files changed, 486 insertions(+), 25 deletions(-)
diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index f367b00a675..be09847022b 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -2971,7 +2971,6 @@ LookupExplicitNamespace(const char *nspname, bool missing_ok)
{
Oid namespaceId;
AclResult aclresult;
-
/* check for pg_temp alias */
if (strcmp(nspname, "pg_temp") == 0)
{
@@ -2989,7 +2988,24 @@ LookupExplicitNamespace(const char *nspname, bool missing_ok)
if (missing_ok && !OidIsValid(namespaceId))
return InvalidOid;
- aclresult = pg_namespace_aclcheck(namespaceId, GetUserId(), ACL_USAGE);
+ HeapTuple tuple;
+ Oid ownerId;
+
+ tuple = SearchSysCache1(NAMESPACEOID, ObjectIdGetDatum(namespaceId));
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_SCHEMA),
+ errmsg("schema with OID %u does not exist", namespaceId)));
+
+ ownerId = ((Form_pg_namespace) GETSTRUCT(tuple))->nspowner;
+
+ ReleaseSysCache(tuple);
+
+ if (!mdb_admin_allow_bypass_owner_checks(GetUserId(), ownerId)) {
+ aclresult = pg_namespace_aclcheck(namespaceId, GetUserId(), ACL_USAGE);
+ } else {
+ aclresult = ACLCHECK_OK;
+ }
if (aclresult != ACLCHECK_OK)
aclcheck_error(aclresult, OBJECT_SCHEMA,
nspname);
diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c
index f5dfd6ff126..6f370a2c9aa 100644
--- a/src/backend/commands/alter.c
+++ b/src/backend/commands/alter.c
@@ -1085,7 +1085,8 @@ AlterObjectOwner_internal(Relation rel, Oid objectId, Oid new_ownerId)
if (!superuser())
{
/* must be owner */
- if (!has_privs_of_role(GetUserId(), old_ownerId))
+ if (!has_privs_of_role(GetUserId(), old_ownerId)
+ && !mdb_admin_allow_bypass_owner_checks(GetUserId(), old_ownerId))
{
char *objname;
char namebuf[NAMEDATALEN];
@@ -1105,14 +1106,13 @@ AlterObjectOwner_internal(Relation rel, Oid objectId, Oid new_ownerId)
aclcheck_error(ACLCHECK_NOT_OWNER, get_object_type(classId, objectId),
objname);
}
- /* Must be able to become new owner */
- check_is_member_of_role(GetUserId(), new_ownerId);
+
+ check_mdb_admin_is_member_of_role(GetUserId(), new_ownerId);
/* New owner must have CREATE privilege on namespace */
if (OidIsValid(namespaceId))
{
AclResult aclresult;
-
aclresult = pg_namespace_aclcheck(namespaceId, new_ownerId,
ACL_CREATE);
if (aclresult != ACLCHECK_OK)
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index b99b2419fcc..8a570fa6965 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -1525,9 +1525,13 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
* by security barrier views or row-level security policies.
*/
if (isLeakProof && !superuser())
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("only superuser can define a leakproof function")));
+ {
+ Oid role = get_role_oid("mdb_admin", true);
+ if (!is_member_of_role(GetUserId(), role))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("only superuser or mdb_admin can define a leakproof function")));
+ }
if (transformDefElem)
{
@@ -1852,9 +1856,13 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt)
{
procForm->proleakproof = intVal(leakproof_item->arg);
if (procForm->proleakproof && !superuser())
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("only superuser can define a leakproof function")));
+ {
+ Oid role = get_role_oid("mdb_admin", true);
+ if (!is_member_of_role(GetUserId(), role))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("only superuser or mdb_admin can define a leakproof function")));
+ }
}
if (cost_item)
{
diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c
index 96757eaa814..03f96bb6499 100644
--- a/src/backend/commands/schemacmds.c
+++ b/src/backend/commands/schemacmds.c
@@ -598,12 +598,12 @@ AlterSchemaOwner_internal(HeapTuple tup, Relation rel, Oid newOwnerId)
AclResult aclresult;
/* Otherwise, must be owner of the existing object */
- if (!pg_namespace_ownercheck(nspForm->oid, GetUserId()))
+ if (!mdb_admin_allow_bypass_owner_checks(GetUserId(), nspForm->nspowner)
+ && !pg_namespace_ownercheck(nspForm->oid, GetUserId()))
aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_SCHEMA,
NameStr(nspForm->nspname));
- /* Must be able to become new owner */
- check_is_member_of_role(GetUserId(), newOwnerId);
+ check_mdb_admin_is_member_of_role(GetUserId(), newOwnerId);
/*
* must have create-schema rights
@@ -614,8 +614,13 @@ AlterSchemaOwner_internal(HeapTuple tup, Relation rel, Oid newOwnerId)
* schemas. Because superusers will always have this right, we need
* no special case for them.
*/
- aclresult = pg_database_aclcheck(MyDatabaseId, GetUserId(),
+ if (mdb_admin_allow_bypass_owner_checks(GetUserId(), nspForm->nspowner)) {
+ aclresult = ACLCHECK_OK;
+ } else {
+ aclresult = pg_database_aclcheck(MyDatabaseId, GetUserId(),
ACL_CREATE);
+ }
+
if (aclresult != ACLCHECK_OK)
aclcheck_error(aclresult, OBJECT_DATABASE,
get_database_name(MyDatabaseId));
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index e731e37142c..301c3548e5e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -15708,13 +15708,14 @@ ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, LOCKMODE lock
AclResult aclresult;
/* Otherwise, must be owner of the existing object */
- if (!pg_class_ownercheck(relationOid, GetUserId()))
+ if (!mdb_admin_allow_bypass_owner_checks(GetUserId(), tuple_class->relowner)
+ && !pg_class_ownercheck(relationOid, GetUserId()))
aclcheck_error(ACLCHECK_NOT_OWNER, get_relkind_objtype(get_rel_relkind(relationOid)),
RelationGetRelationName(target_rel));
- /* Must be able to become new owner */
- check_is_member_of_role(GetUserId(), newOwnerId);
+ check_mdb_admin_is_member_of_role(GetUserId(), newOwnerId);
+
/* New owner must have CREATE privilege on namespace */
aclresult = pg_namespace_aclcheck(namespaceOid, newOwnerId,
ACL_CREATE);
@@ -20795,7 +20796,7 @@ RangeVarCallbackForAlterRelation(const RangeVar *rv, Oid relid, Oid oldrelid,
Form_pg_class classform;
AclResult aclresult;
char relkind;
-
+
tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
if (!HeapTupleIsValid(tuple))
return; /* concurrently dropped */
@@ -20803,7 +20804,8 @@ RangeVarCallbackForAlterRelation(const RangeVar *rv, Oid relid, Oid oldrelid,
relkind = classform->relkind;
/* Must own relation. */
- if (!pg_class_ownercheck(relid, GetUserId()))
+ if (!mdb_admin_allow_bypass_owner_checks(GetUserId(), classform->relowner)
+ && !pg_class_ownercheck(relid, GetUserId()))
aclcheck_error(ACLCHECK_NOT_OWNER, get_relkind_objtype(get_rel_relkind(relid)), rv->relname);
/* No system table modifications unless explicitly allowed. */
diff --git a/src/backend/storage/ipc/signalfuncs.c b/src/backend/storage/ipc/signalfuncs.c
index 0d5ccaa201d..753b94752d3 100644
--- a/src/backend/storage/ipc/signalfuncs.c
+++ b/src/backend/storage/ipc/signalfuncs.c
@@ -52,6 +52,7 @@ static int
pg_signal_backend(int pid, int sig, char *msg)
{
PGPROC *proc = BackendPidGetProc(pid);
+ LocalPgBackendStatus *local_beentry;
/*
* BackendPidGetProc returns NULL if the pid isn't valid; but by the time
@@ -72,9 +73,34 @@ pg_signal_backend(int pid, int sig, char *msg)
return SIGNAL_BACKEND_ERROR;
}
+ local_beentry = pgstat_fetch_stat_local_beentry_by_pid(pid);
+
/* Only allow superusers to signal superuser-owned backends. */
if (superuser_arg(proc->roleId) && !superuser())
- return SIGNAL_BACKEND_NOSUPERUSER;
+ {
+ Oid role;
+ char * appname;
+
+ if (local_beentry == NULL) {
+ return SIGNAL_BACKEND_NOSUPERUSER;
+ }
+
+ role = get_role_oid("mdb_admin", true /*if nodoby created mdb_admin role in this database*/);
+ appname = local_beentry->backendStatus.st_appname;
+
+ // only allow mdb_admin to kill su queries
+ if (!is_member_of_role(GetUserId(), role)) {
+ return SIGNAL_BACKEND_NOSUPERUSER;
+ }
+
+ if (local_beentry->backendStatus.st_backendType == B_AUTOVAC_WORKER) {
+ // ok
+ } else if (appname != NULL && strcmp(appname, "MDB") == 0) {
+ // ok
+ } else {
+ return SIGNAL_BACKEND_NOSUPERUSER;
+ }
+ }
/* Users can signal backends they have role membership in. */
if (!has_privs_of_role(GetUserId(), proc->roleId) &&
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index 9a0918bceff..217483c1c61 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -1102,6 +1102,22 @@ pgstat_fetch_stat_local_beentry(int beid)
return &localBackendStatusTable[beid - 1];
}
+/* -- mdb admin patch -- */
+LocalPgBackendStatus *
+pgstat_fetch_stat_local_beentry_by_pid(int pid)
+{
+ pgstat_read_current_status();
+
+ for (int i = 1; i <= localNumBackends; ++i) {
+ if (localBackendStatusTable[i - 1].backendStatus.st_procpid == pid) {
+ return &localBackendStatusTable[i - 1];
+ }
+ }
+
+ return NULL;
+}
+
+/* -- mdb admin patch end -- */
/* ----------
* pgstat_fetch_stat_numbackends() -
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 714a536e93d..fc566a575f4 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -5012,6 +5012,60 @@ has_privs_of_role(Oid member, Oid role)
}
+// -- non-upstream patch begin
+/*
+ * Is userId allowed to bypass ownership check
+ * and tranfer onwership to ownerId role?
+ */
+bool
+mdb_admin_allow_bypass_owner_checks(Oid userId, Oid ownerId)
+{
+ Oid mdb_admin_roleoid;
+ /*
+ * Never allow nobody to grant objects to
+ * superusers.
+ * This can result in various CVE.
+ * For paranoic reasons, check this even before
+ * membership of mdb_admin role.
+ */
+ if (superuser_arg(ownerId)) {
+ return false;
+ }
+
+ mdb_admin_roleoid = get_role_oid("mdb_admin", true /* superuser suggested to be mdb_admin*/);
+ /* Is userId actually member of mdb admin? */
+ if (!is_member_of_role(userId, mdb_admin_roleoid)) {
+ /* if no, disallow. */
+ return false;
+ }
+
+ /*
+ * Now, we need to check if ownerId
+ * is some dangerous role to trasfer membership to.
+ *
+ * For now, we check that ownerId does not have
+ * priviledge to execute server program or/and
+ * read/write server files.
+ */
+
+ if (has_privs_of_role(ownerId, ROLE_PG_READ_SERVER_FILES)) {
+ return false;
+ }
+
+ if (has_privs_of_role(ownerId, ROLE_PG_WRITE_SERVER_FILES)) {
+ return false;
+ }
+
+ if (has_privs_of_role(ownerId, ROLE_PG_EXECUTE_SERVER_PROGRAM)) {
+ return false;
+ }
+
+ /* All checks passed, hope will not be hacked here (again) */
+ return true;
+}
+
+// -- non-upstream patch end
+
/*
* Is member a member of role (directly or indirectly)?
*
@@ -5051,6 +5105,64 @@ check_is_member_of_role(Oid member, Oid role)
GetUserNameFromId(role, false))));
}
+// -- mdb admin patch
+/*
+ * check_mdb_admin_is_member_of_role
+ * is_member_of_role with a standard permission-violation error if not in usual case
+ * Is case `member` in mdb_admin we check that role is neither of superuser, pg_read/write
+ * server files nor pg_execute_server_program
+ */
+void
+check_mdb_admin_is_member_of_role(Oid member, Oid role)
+{
+ Oid mdb_admin_roleoid;
+ /* fast path - if we are superuser, its ok */
+ if (superuser_arg(member)) {
+ return;
+ }
+
+ mdb_admin_roleoid = get_role_oid("mdb_admin", true /* superuser suggested to be mdb_admin*/);
+ /* Is userId actually member of mdb admin? */
+ if (is_member_of_role(member, mdb_admin_roleoid)) {
+ /* role is mdb admin */
+ if (superuser_arg(role)) {
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("cannot transfer ownership to superuser \"%s\"",
+ GetUserNameFromId(role, false))));
+ }
+
+ if (has_privs_of_role(role, ROLE_PG_READ_SERVER_FILES)) {
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("cannot transfer ownership to pg_read_server_files role in Cloud")));
+ }
+
+ if (has_privs_of_role(role, ROLE_PG_WRITE_SERVER_FILES)) {
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("cannot transfer ownership to pg_write_server_files role in Cloud")));
+ }
+
+ if (has_privs_of_role(role, ROLE_PG_EXECUTE_SERVER_PROGRAM)) {
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("cannot transfer ownership to pg_execute_server_program role in Cloud")));
+ }
+ } else {
+ /* if no, check membership transfer in usual way. */
+
+ if (!is_member_of_role(member, role)) {
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be member of role \"%s\"",
+ GetUserNameFromId(role, false))));
+ }
+ }
+}
+
+// -- mdb admin patch
+
/*
* Is member a member of role, not considering superuserness?
*
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index d8cdf027df5..f73f99851b7 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -4918,7 +4918,7 @@ static struct config_enum ConfigureNamesEnum[] =
{
{"session_replication_role", PGC_SUSET, CLIENT_CONN_STATEMENT,
gettext_noop("Sets the session's behavior for triggers and rewrite rules."),
- NULL
+ NULL, 0, 0, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL, 0, true,
},
&SessionReplicationRole,
SESSION_REPLICATION_ROLE_ORIGIN, session_replication_role_options,
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h
index 223175099bd..271ac942f6f 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -210,6 +210,13 @@ extern bool has_privs_of_role(Oid member, Oid role);
extern bool is_member_of_role(Oid member, Oid role);
extern bool is_member_of_role_nosuper(Oid member, Oid role);
extern bool is_admin_of_role(Oid member, Oid role);
+
+// -- non-upstream patch begin
+extern bool mdb_admin_allow_bypass_owner_checks(Oid userId, Oid ownerId);
+
+extern void check_mdb_admin_is_member_of_role(Oid member, Oid role);
+// -- non-upstream patch end
+
extern void check_is_member_of_role(Oid member, Oid role);
extern Oid get_role_oid(const char *rolename, bool missing_ok);
extern Oid get_role_oid_or_public(const char *rolename);
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 139b7355d13..139646d4a40 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -319,6 +319,9 @@ extern uint64 pgstat_get_my_query_id(void);
extern int pgstat_fetch_stat_numbackends(void);
extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid);
extern LocalPgBackendStatus *pgstat_fetch_stat_local_beentry(int beid);
+/* -- mdb admin patch -- */
+extern LocalPgBackendStatus *pgstat_fetch_stat_local_beentry_by_pid(int pid);
+/* -- mdb admin patch end -- */
extern char *pgstat_clip_activity(const char *raw_activity);
diff --git a/src/include/utils/guc_tables.h b/src/include/utils/guc_tables.h
index 17d2a166b09..08584e4db54 100644
--- a/src/include/utils/guc_tables.h
+++ b/src/include/utils/guc_tables.h
@@ -204,6 +204,8 @@ struct config_generic
char *sourcefile; /* file current setting is from (NULL if not
* set in config file) */
int sourceline; /* line in source file */
+
+ bool mdb_admin_allowed; /* is mdb admin allowed to change this, makes sence only for superuser/not superuser ctx */
};
/* bit values in status field */
diff --git a/src/test/Makefile b/src/test/Makefile
index d84edb282df..150c4e97b73 100644
--- a/src/test/Makefile
+++ b/src/test/Makefile
@@ -18,6 +18,9 @@ SUBDIRS = perl regress isolation modules authentication recovery
SUBDIRS += fsync walrep heap_checksum isolation2 fdw singlenode_regress singlenode_isolation2
+# MDB addon
+SUBDIRS += mdb_admin
+
# Test suites that are not safe by default but can be run if selected
# by the user via the whitespace-separated list in variable
# PG_TEST_EXTRA:
diff --git a/src/test/mdb_admin/.gitignore b/src/test/mdb_admin/.gitignore
new file mode 100644
index 00000000000..871e943d50e
--- /dev/null
+++ b/src/test/mdb_admin/.gitignore
@@ -0,0 +1,2 @@
+# Generated by test suite
+/tmp_check/
diff --git a/src/test/mdb_admin/Makefile b/src/test/mdb_admin/Makefile
new file mode 100644
index 00000000000..e4e82367da9
--- /dev/null
+++ b/src/test/mdb_admin/Makefile
@@ -0,0 +1,23 @@
+#-------------------------------------------------------------------------
+#
+# Makefile for src/test/mdb_admin
+#
+# Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group
+# Portions Copyright (c) 1994, Regents of the University of California
+#
+# src/test/mdb_admin/Makefile
+#
+#-------------------------------------------------------------------------
+
+subdir = src/test/mdb_admin
+top_builddir = ../../..
+include $(top_builddir)/src/Makefile.global
+
+check:
+ $(prove_check)
+
+installcheck:
+ $(prove_installcheck)
+
+clean distclean maintainer-clean:
+ rm -rf tmp_check
diff --git a/src/test/mdb_admin/t/signals.pl b/src/test/mdb_admin/t/signals.pl
new file mode 100644
index 00000000000..a11db27a527
--- /dev/null
+++ b/src/test/mdb_admin/t/signals.pl
@@ -0,0 +1,74 @@
+
+# Copyright (c) 2024-2024, MDB, Mother Russia
+
+# Minimal test testing streaming replication
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize primary node
+my $node_primary = PostgreSQL::Test::Cluster->new('primary');
+$node_primary->init();
+$node_primary->start;
+
+# Create some content on primary and check its presence in standby nodes
+$node_primary->safe_psql('postgres',
+ "
+ CREATE DATABASE regress;
+ CREATE ROLE mdb_admin;
+ CREATE ROLE mdb_reg_lh_1;
+ CREATE ROLE mdb_reg_lh_2;
+ GRANT pg_signal_backend TO mdb_admin;
+ GRANT pg_signal_backend TO mdb_reg_lh_1;
+ GRANT mdb_admin TO mdb_reg_lh_2;
+");
+
+# Create some content on primary and check its presence in standby nodes
+$node_primary->safe_psql('regress',
+ "
+ CREATE TABLE tab_int(i int);
+ INSERT INTO tab_int SELECT * FROm generate_series(1, 1000000);
+ ALTER SYSTEM SET autovacuum_vacuum_cost_limit TO 1;
+ ALTER SYSTEM SET autovacuum_vacuum_cost_delay TO 100;
+ ALTER SYSTEM SET autovacuum_naptime TO 1;
+");
+
+$node_primary->restart;
+
+sleep 1;
+
+my $res_pid = $node_primary->safe_psql('regress',
+ "
+ SELECT pid FROM pg_stat_activity WHERE backend_type = 'autovacuum worker' and datname = 'regress';;
+");
+
+
+print "pid is $res_pid\n";
+
+ok(1);
+
+
+my ($res_reg_lh_1, $stdout_reg_lh_1, $stderr_reg_lh_1) = $node_primary->psql('regress',
+ "
+ SET ROLE mdb_reg_lh_1;
+ SELECT pg_terminate_backend($res_pid);
+");
+
+# print ($res_reg_lh_1, $stdout_reg_lh_1, $stderr_reg_lh_1, "\n");
+
+ok($res_reg_lh_1 != 0, "should fail for non-mdb_admin");
+like($stderr_reg_lh_1, qr/ERROR: must be a superuser to terminate superuser process/, "matches");
+
+my ($res_reg_lh_2, $stdout_reg_lh_2, $stderr_reg_lh_2) = $node_primary->psql('regress',
+ "
+ SET ROLE mdb_reg_lh_2;
+ SELECT pg_terminate_backend($res_pid);
+");
+
+ok($res_reg_lh_2 == 0, "should success for mdb_admin");
+
+# print ($res_reg_lh_2, $stdout_reg_lh_2, $stderr_reg_lh_2, "\n");
+
+done_testing();
\ No newline at end of file
diff --git a/src/test/regress/expected/create_function_3.out b/src/test/regress/expected/create_function_3.out
index 8380df1591f..7842a3c1c82 100644
--- a/src/test/regress/expected/create_function_3.out
+++ b/src/test/regress/expected/create_function_3.out
@@ -166,10 +166,10 @@ SET SESSION AUTHORIZATION regress_unpriv_user;
SET search_path TO temp_func_test, public;
ALTER FUNCTION functest_E_1(int) NOT LEAKPROOF;
ALTER FUNCTION functest_E_2(int) LEAKPROOF;
-ERROR: only superuser can define a leakproof function
+ERROR: only superuser or mdb_admin can define a leakproof function
CREATE FUNCTION functest_E_3(int) RETURNS bool LANGUAGE 'sql'
LEAKPROOF AS 'SELECT $1 < 200'; -- fail
-ERROR: only superuser can define a leakproof function
+ERROR: only superuser or mdb_admin can define a leakproof function
RESET SESSION AUTHORIZATION;
--
-- CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
diff --git a/src/test/regress/expected/mdb_admin.out b/src/test/regress/expected/mdb_admin.out
new file mode 100644
index 00000000000..5fc2dab10cb
--- /dev/null
+++ b/src/test/regress/expected/mdb_admin.out
@@ -0,0 +1,81 @@
+CREATE ROLE regress_mdb_admin_user1;
+CREATE ROLE regress_mdb_admin_user2;
+CREATE ROLE regress_mdb_admin_user3;
+CREATE ROLE mdb_admin;
+CREATE ROLE regress_superuser WITH SUPERUSER;
+GRANT mdb_admin TO regress_mdb_admin_user1;
+GRANT CREATE ON DATABASE regression TO regress_mdb_admin_user2;
+GRANT CREATE ON DATABASE regression TO regress_mdb_admin_user3;
+-- mdb admin trasfers ownership to another role
+SET ROLE regress_mdb_admin_user2;
+CREATE FUNCTION regress_mdb_admin_add(integer, integer) RETURNS integer
+ AS 'SELECT $1 + $2;'
+ LANGUAGE SQL
+ IMMUTABLE
+ RETURNS NULL ON NULL INPUT;
+CREATE SCHEMA regress_mdb_admin_schema;
+GRANT CREATE ON SCHEMA regress_mdb_admin_schema TO regress_mdb_admin_user3;
+CREATE TABLE regress_mdb_admin_schema.regress_mdb_admin_table();
+CREATE TABLE regress_mdb_admin_table();
+CREATE VIEW regress_mdb_admin_view as SELECT 1;
+SET ROLE regress_mdb_admin_user1;
+ALTER FUNCTION regress_mdb_admin_add (integer, integer) OWNER TO regress_mdb_admin_user3;
+ALTER VIEW regress_mdb_admin_view OWNER TO regress_mdb_admin_user3;
+ALTER TABLE regress_mdb_admin_schema.regress_mdb_admin_table OWNER TO regress_mdb_admin_user3;
+ALTER TABLE regress_mdb_admin_table OWNER TO regress_mdb_admin_user3;
+ALTER SCHEMA regress_mdb_admin_schema OWNER TO regress_mdb_admin_user3;
+-- mdb admin fails to transfer ownership to superusers and system roles
+ALTER FUNCTION regress_mdb_admin_add (integer, integer) OWNER TO regress_superuser;
+ERROR: cannot transfer ownership to superuser "regress_superuser"
+ALTER VIEW regress_mdb_admin_view OWNER TO regress_superuser;
+ERROR: cannot transfer ownership to superuser "regress_superuser"
+ALTER TABLE regress_mdb_admin_schema.regress_mdb_admin_table OWNER TO regress_superuser;
+ERROR: cannot transfer ownership to superuser "regress_superuser"
+ALTER TABLE regress_mdb_admin_table OWNER TO regress_superuser;
+ERROR: cannot transfer ownership to superuser "regress_superuser"
+ALTER SCHEMA regress_mdb_admin_schema OWNER TO regress_superuser;
+ERROR: cannot transfer ownership to superuser "regress_superuser"
+ALTER FUNCTION regress_mdb_admin_add (integer, integer) OWNER TO pg_execute_server_program;
+ERROR: cannot transfer ownership to pg_execute_server_program role in Cloud
+ALTER VIEW regress_mdb_admin_view OWNER TO pg_execute_server_program;
+ERROR: cannot transfer ownership to pg_execute_server_program role in Cloud
+ALTER TABLE regress_mdb_admin_schema.regress_mdb_admin_table OWNER TO pg_execute_server_program;
+ERROR: cannot transfer ownership to pg_execute_server_program role in Cloud
+ALTER TABLE regress_mdb_admin_table OWNER TO pg_execute_server_program;
+ERROR: cannot transfer ownership to pg_execute_server_program role in Cloud
+ALTER SCHEMA regress_mdb_admin_schema OWNER TO pg_execute_server_program;
+ERROR: cannot transfer ownership to pg_execute_server_program role in Cloud
+ALTER FUNCTION regress_mdb_admin_add (integer, integer) OWNER TO pg_write_server_files;
+ERROR: cannot transfer ownership to pg_write_server_files role in Cloud
+ALTER VIEW regress_mdb_admin_view OWNER TO pg_write_server_files;
+ERROR: cannot transfer ownership to pg_write_server_files role in Cloud
+ALTER TABLE regress_mdb_admin_schema.regress_mdb_admin_table OWNER TO pg_write_server_files;
+ERROR: cannot transfer ownership to pg_write_server_files role in Cloud
+ALTER TABLE regress_mdb_admin_table OWNER TO pg_write_server_files;
+ERROR: cannot transfer ownership to pg_write_server_files role in Cloud
+ALTER SCHEMA regress_mdb_admin_schema OWNER TO pg_write_server_files;
+ERROR: cannot transfer ownership to pg_write_server_files role in Cloud
+ALTER FUNCTION regress_mdb_admin_add (integer, integer) OWNER TO pg_read_server_files;
+ERROR: cannot transfer ownership to pg_read_server_files role in Cloud
+ALTER VIEW regress_mdb_admin_view OWNER TO pg_read_server_files;
+ERROR: cannot transfer ownership to pg_read_server_files role in Cloud
+ALTER TABLE regress_mdb_admin_schema.regress_mdb_admin_table OWNER TO pg_read_server_files;
+ERROR: cannot transfer ownership to pg_read_server_files role in Cloud
+ALTER TABLE regress_mdb_admin_table OWNER TO pg_read_server_files;
+ERROR: cannot transfer ownership to pg_read_server_files role in Cloud
+ALTER SCHEMA regress_mdb_admin_schema OWNER TO pg_read_server_files;
+ERROR: cannot transfer ownership to pg_read_server_files role in Cloud
+-- end tests
+RESET SESSION AUTHORIZATION;
+--
+REVOKE CREATE ON DATABASE regression FROM regress_mdb_admin_user2;
+REVOKE CREATE ON DATABASE regression FROM regress_mdb_admin_user3;
+DROP VIEW regress_mdb_admin_view;
+DROP FUNCTION regress_mdb_admin_add;
+DROP TABLE regress_mdb_admin_schema.regress_mdb_admin_table;
+DROP TABLE regress_mdb_admin_table;
+DROP SCHEMA regress_mdb_admin_schema;
+DROP ROLE regress_mdb_admin_user1;
+DROP ROLE regress_mdb_admin_user2;
+DROP ROLE regress_mdb_admin_user3;
+DROP ROLE mdb_admin;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index e2df0208627..6ebdd67731e 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -5,6 +5,10 @@
# this limits the number of connections needed to run the tests.
# ----------
+# mdb admin simple checks
+
+test: mdb_admin
+
# run tablespace by itself, and first, because it forces a checkpoint;
# we'd prefer not to have checkpoints later in the tests because that
# interferes with crash-recovery testing.
diff --git a/src/test/regress/sql/mdb_admin.sql b/src/test/regress/sql/mdb_admin.sql
new file mode 100644
index 00000000000..8552bbdd48a
--- /dev/null
+++ b/src/test/regress/sql/mdb_admin.sql
@@ -0,0 +1,77 @@
+CREATE ROLE regress_mdb_admin_user1;
+CREATE ROLE regress_mdb_admin_user2;
+CREATE ROLE regress_mdb_admin_user3;
+CREATE ROLE mdb_admin;
+
+CREATE ROLE regress_superuser WITH SUPERUSER;
+
+GRANT mdb_admin TO regress_mdb_admin_user1;
+GRANT CREATE ON DATABASE regression TO regress_mdb_admin_user2;
+GRANT CREATE ON DATABASE regression TO regress_mdb_admin_user3;
+
+-- mdb admin trasfers ownership to another role
+
+SET ROLE regress_mdb_admin_user2;
+CREATE FUNCTION regress_mdb_admin_add(integer, integer) RETURNS integer
+ AS 'SELECT $1 + $2;'
+ LANGUAGE SQL
+ IMMUTABLE
+ RETURNS NULL ON NULL INPUT;
+
+CREATE SCHEMA regress_mdb_admin_schema;
+GRANT CREATE ON SCHEMA regress_mdb_admin_schema TO regress_mdb_admin_user3;
+CREATE TABLE regress_mdb_admin_schema.regress_mdb_admin_table();
+CREATE TABLE regress_mdb_admin_table();
+CREATE VIEW regress_mdb_admin_view as SELECT 1;
+SET ROLE regress_mdb_admin_user1;
+
+ALTER FUNCTION regress_mdb_admin_add (integer, integer) OWNER TO regress_mdb_admin_user3;
+ALTER VIEW regress_mdb_admin_view OWNER TO regress_mdb_admin_user3;
+ALTER TABLE regress_mdb_admin_schema.regress_mdb_admin_table OWNER TO regress_mdb_admin_user3;
+ALTER TABLE regress_mdb_admin_table OWNER TO regress_mdb_admin_user3;
+ALTER SCHEMA regress_mdb_admin_schema OWNER TO regress_mdb_admin_user3;
+
+
+-- mdb admin fails to transfer ownership to superusers and system roles
+
+ALTER FUNCTION regress_mdb_admin_add (integer, integer) OWNER TO regress_superuser;
+ALTER VIEW regress_mdb_admin_view OWNER TO regress_superuser;
+ALTER TABLE regress_mdb_admin_schema.regress_mdb_admin_table OWNER TO regress_superuser;
+ALTER TABLE regress_mdb_admin_table OWNER TO regress_superuser;
+ALTER SCHEMA regress_mdb_admin_schema OWNER TO regress_superuser;
+
+ALTER FUNCTION regress_mdb_admin_add (integer, integer) OWNER TO pg_execute_server_program;
+ALTER VIEW regress_mdb_admin_view OWNER TO pg_execute_server_program;
+ALTER TABLE regress_mdb_admin_schema.regress_mdb_admin_table OWNER TO pg_execute_server_program;
+ALTER TABLE regress_mdb_admin_table OWNER TO pg_execute_server_program;
+ALTER SCHEMA regress_mdb_admin_schema OWNER TO pg_execute_server_program;
+
+ALTER FUNCTION regress_mdb_admin_add (integer, integer) OWNER TO pg_write_server_files;
+ALTER VIEW regress_mdb_admin_view OWNER TO pg_write_server_files;
+ALTER TABLE regress_mdb_admin_schema.regress_mdb_admin_table OWNER TO pg_write_server_files;
+ALTER TABLE regress_mdb_admin_table OWNER TO pg_write_server_files;
+ALTER SCHEMA regress_mdb_admin_schema OWNER TO pg_write_server_files;
+
+ALTER FUNCTION regress_mdb_admin_add (integer, integer) OWNER TO pg_read_server_files;
+ALTER VIEW regress_mdb_admin_view OWNER TO pg_read_server_files;
+ALTER TABLE regress_mdb_admin_schema.regress_mdb_admin_table OWNER TO pg_read_server_files;
+ALTER TABLE regress_mdb_admin_table OWNER TO pg_read_server_files;
+ALTER SCHEMA regress_mdb_admin_schema OWNER TO pg_read_server_files;
+
+
+-- end tests
+
+RESET SESSION AUTHORIZATION;
+--
+REVOKE CREATE ON DATABASE regression FROM regress_mdb_admin_user2;
+REVOKE CREATE ON DATABASE regression FROM regress_mdb_admin_user3;
+
+DROP VIEW regress_mdb_admin_view;
+DROP FUNCTION regress_mdb_admin_add;
+DROP TABLE regress_mdb_admin_schema.regress_mdb_admin_table;
+DROP TABLE regress_mdb_admin_table;
+DROP SCHEMA regress_mdb_admin_schema;
+DROP ROLE regress_mdb_admin_user1;
+DROP ROLE regress_mdb_admin_user2;
+DROP ROLE regress_mdb_admin_user3;
+DROP ROLE mdb_admin;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org