Allow non-superuser to cancel superuser tasks.

From d096d391630186fa89a937f1bd209d35754181c2 Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Mon, 26 Feb 2024 20:10:21 +0000
Subject: [PATCH v1] Allow non-superuser to cancel superuser tasks.

Patch adds new pre-defined role `pg_signal_autovacuum` and tap tests.
Role, granted with `pg_signal_autovacuum` is able to cancel running
autovacuum worker. Note that to cancel all other type of queries
role need to be granted with `pg_singal_backend`.
---
 src/backend/storage/ipc/signalfuncs.c       | 28 ++++++---
 src/include/catalog/pg_authid.dat           |  5 ++
 src/test/signals/.gitignore                 |  2 +
 src/test/signals/Makefile                   | 23 ++++++++
 src/test/signals/t/001_signal_autovacuum.pl | 63 +++++++++++++++++++++
 5 files changed, 113 insertions(+), 8 deletions(-)
 create mode 100644 src/test/signals/.gitignore
 create mode 100644 src/test/signals/Makefile
 create mode 100644 src/test/signals/t/001_signal_autovacuum.pl

diff --git a/src/backend/storage/ipc/signalfuncs.c b/src/backend/storage/ipc/signalfuncs.c
index 81d1a59659..c006f4101e 100644
--- a/src/backend/storage/ipc/signalfuncs.c
+++ b/src/backend/storage/ipc/signalfuncs.c
@@ -78,15 +78,27 @@ pg_signal_backend(int pid, int sig)
 	 * Only allow superusers to signal superuser-owned backends.  Any process
 	 * not advertising a role might have the importance of a superuser-owned
 	 * backend, so treat it that way.
+	 * In later case, check if pid is actually autovacuum_worker, and allow
+	 * to signal if role has proper priviledge.
 	 */
-	if ((!OidIsValid(proc->roleId) || superuser_arg(proc->roleId)) &&
-		!superuser())
-		return SIGNAL_BACKEND_NOSUPERUSER;
-
-	/* Users can signal backends they have role membership in. */
-	if (!has_privs_of_role(GetUserId(), proc->roleId) &&
-		!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
-		return SIGNAL_BACKEND_NOPERMISSION;
+	if (!superuser()) {
+		if (!OidIsValid(proc->roleId)) {
+			LocalPgBackendStatus *local_beentry;
+			local_beentry = pgstat_get_local_beentry_by_backend_id(proc->backendId);
+
+			if (!(local_beentry && local_beentry->backendStatus.st_backendType == B_AUTOVAC_WORKER && 
+				has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_AUTOVACUUM)))
+					return SIGNAL_BACKEND_NOSUPERUSER;
+		} else {
+			if (superuser_arg(proc->roleId))
+				return SIGNAL_BACKEND_NOSUPERUSER;
+
+			/* Users can signal backends they have role membership in. */
+			if (!has_privs_of_role(GetUserId(), proc->roleId) &&
+				!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
+				return SIGNAL_BACKEND_NOPERMISSION;
+		}
+	}
 
 	/*
 	 * Can the process we just validated above end, followed by the pid being
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 82a2ec2862..aa0d9845ef 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -94,5 +94,10 @@
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
   rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '6312', oid_symbol => 'ROLE_PG_SIGNAL_AUTOVACUUM',
+  rolname => 'pg_signal_autovacuum', rolsuper => 'f', rolinherit => 't',
+  rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+  rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+  rolpassword => '_null_', rolvaliduntil => '_null_' },
 
 ]
diff --git a/src/test/signals/.gitignore b/src/test/signals/.gitignore
new file mode 100644
index 0000000000..871e943d50
--- /dev/null
+++ b/src/test/signals/.gitignore
@@ -0,0 +1,2 @@
+# Generated by test suite
+/tmp_check/
diff --git a/src/test/signals/Makefile b/src/test/signals/Makefile
new file mode 100644
index 0000000000..688f6a7e9e
--- /dev/null
+++ b/src/test/signals/Makefile
@@ -0,0 +1,23 @@
+#-------------------------------------------------------------------------
+#
+# Makefile for src/test/recovery
+#
+# Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+# Portions Copyright (c) 1994, Regents of the University of California
+#
+# src/test/recovery/Makefile
+#
+#-------------------------------------------------------------------------
+
+subdir = src/test/signals
+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/signals/t/001_signal_autovacuum.pl b/src/test/signals/t/001_signal_autovacuum.pl
new file mode 100644
index 0000000000..ec4b3e78de
--- /dev/null
+++ b/src/test/signals/t/001_signal_autovacuum.pl
@@ -0,0 +1,63 @@
+# Copyright (c) 2024-2024, PostgreSQL Global Development Group
+
+# Minimal test testing pg_signal_autovacuum role.
+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;
+
+$node_primary->safe_psql('postgres',
+	"
+    CREATE DATABASE regress;
+    CREATE ROLE psa_reg_role_1;
+    CREATE ROLE psa_reg_role_2;
+    GRANT pg_signal_backend TO psa_reg_role_1;
+    GRANT pg_signal_autovacuum TO psa_reg_role_2;
+");
+
+# Create some content on primary and set autovacuum setting such that
+# it would be triggered.
+$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;
+
+#wait for autovac to start.
+
+sleep 1;
+
+my $res_pid = $node_primary->safe_psql('regress',
+	"
+    SELECT pid FROM pg_stat_activity WHERE backend_type = 'autovacuum worker' and datname = 'regress';;
+");
+
+
+my ($res_reg_psa_1, $stdout_reg_psa_1, $stderr_reg_psa_1)  = $node_primary->psql('regress',
+	"
+    SET ROLE psa_reg_role_1;
+    SELECT pg_terminate_backend($res_pid);
+");
+
+ok($res_reg_psa_1 != 0, "should fail for non pg_signal_autovacuum");
+like($stderr_reg_psa_1, qr/Only roles with the SUPERUSER attribute may terminate processes of roles with the SUPERUSER attribute./, "matches");
+
+my ($res_reg_psa_2, $stdout_reg_psa_2, $stderr_reg_psa_2) = $node_primary->psql('regress', "
+    SET ROLE psa_reg_role_2;
+    SELECT pg_terminate_backend($res_pid);
+");
+
+ok($res_reg_psa_2 == 0, "should success for pg_signal_autovacuum");
+
+done_testing();
-- 
2.34.1

From 763f9052f291f4cfe32197379f58b9d9bc35ebb7 Mon Sep 17 00:00:00 2001
From: Anthony Leung <antholeu@amazon.com>
Date: Mon, 1 Apr 2024 04:46:55 +0000
Subject: [PATCH] Introduce pg_signal_autovacuum role to allow non-superuser to
 termiante autovacuum worker

Non-superusers are currently not allowed to termiante autovacuum
backends which can create friction in some scenarios when performing
maintenance. This commit introduce a new pre-defined role
'pg_signal_autovacuum'. Non-superusers granted with this role are
allowed to terminate backends that are running autovacuum.
---
 doc/src/sgml/user-manag.sgml                |  4 ++
 src/backend/storage/ipc/signalfuncs.c       | 31 ++++++++---
 src/backend/utils/activity/backend_status.c | 20 +++++++
 src/include/catalog/pg_authid.dat           |  5 ++
 src/include/utils/backend_status.h          |  2 +-
 src/test/signals/.gitignore                 |  2 +
 src/test/signals/Makefile                   | 23 ++++++++
 src/test/signals/t/001_signal_autovacuum.pl | 59 +++++++++++++++++++++
 8 files changed, 137 insertions(+), 9 deletions(-)
 create mode 100644 src/test/signals/.gitignore
 create mode 100644 src/test/signals/Makefile
 create mode 100644 src/test/signals/t/001_signal_autovacuum.pl

diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 1f7d7e75ce..541a45ab4a 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -693,6 +693,10 @@ DROP ROLE doomed_role;
        database to issue
        <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>.</entry>
       </row>
+      <row>
+       <entry>pg_signal_autovacuum</entry>
+       <entry>Allow terminating backend running autovacuum</entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/storage/ipc/signalfuncs.c b/src/backend/storage/ipc/signalfuncs.c
index b595c2d691..a9f0466c46 100644
--- a/src/backend/storage/ipc/signalfuncs.c
+++ b/src/backend/storage/ipc/signalfuncs.c
@@ -79,14 +79,29 @@ pg_signal_backend(int pid, int sig)
 	 * not advertising a role might have the importance of a superuser-owned
 	 * backend, so treat it that way.
 	 */
-	if ((!OidIsValid(proc->roleId) || superuser_arg(proc->roleId)) &&
-		!superuser())
-		return SIGNAL_BACKEND_NOSUPERUSER;
-
-	/* Users can signal backends they have role membership in. */
-	if (!has_privs_of_role(GetUserId(), proc->roleId) &&
-		!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
-		return SIGNAL_BACKEND_NOPERMISSION;
+	if (!superuser())
+	{
+		if (!OidIsValid(proc->roleId))
+		{
+			/*
+			 * We only allow user with pg_signal_autovacuum role to terminate
+			 * autovacuum worker as an exception. 
+			 */
+			if (!(pg_stat_is_backend_autovac_worker(proc->backendId) &&
+				has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_AUTOVACUUM)))
+				return SIGNAL_BACKEND_NOSUPERUSER;
+		}
+		else
+		{
+			if (superuser_arg(proc->roleId))
+				return SIGNAL_BACKEND_NOSUPERUSER;
+
+			/* Users can signal backends they have role membership in. */
+			if (!has_privs_of_role(GetUserId(), proc->roleId) &&
+				!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
+				return SIGNAL_BACKEND_NOPERMISSION;
+		}
+	}
 
 	/*
 	 * Can the process we just validated above end, followed by the pid being
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index f78ccc204b..b38ba319d2 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -1057,6 +1057,26 @@ pgstat_get_my_query_id(void)
 	return MyBEEntry->st_query_id;
 }
 
+/* ----------
+ * pg_stat_is_backend_autovac_worker() -
+ *
+ * Return whether the backend of the given backend id is of type autovacuum worker.
+ */
+bool
+pg_stat_is_backend_autovac_worker(BackendId beid)
+{
+	PgBackendStatus *ret;
+
+	Assert(beid != InvalidBackendId);
+
+	ret = pgstat_get_beentry_by_backend_id(beid);
+
+	if (!ret)
+		return false;
+
+	return ret->st_backendType == B_AUTOVAC_WORKER;
+}
+
 /* ----------
  * cmp_lbestatus
  *
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 6b4a0aaaad..556a38f702 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -94,5 +94,10 @@
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
   rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '6312', oid_symbol => 'ROLE_PG_SIGNAL_AUTOVACUUM',
+  rolname => 'pg_signal_autovacuum', rolsuper => 'f', rolinherit => 't',
+  rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+  rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+  rolpassword => '_null_', rolvaliduntil => '_null_' },
 
 ]
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index d51c840daf..a84aafabd9 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -325,7 +325,7 @@ extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
 extern const char *pgstat_get_crashed_backend_activity(int pid, char *buffer,
 													   int buflen);
 extern uint64 pgstat_get_my_query_id(void);
-
+extern bool pg_stat_is_backend_autovac_worker(BackendId beid);
 
 /* ----------
  * Support functions for the SQL-callable functions to
diff --git a/src/test/signals/.gitignore b/src/test/signals/.gitignore
new file mode 100644
index 0000000000..871e943d50
--- /dev/null
+++ b/src/test/signals/.gitignore
@@ -0,0 +1,2 @@
+# Generated by test suite
+/tmp_check/
diff --git a/src/test/signals/Makefile b/src/test/signals/Makefile
new file mode 100644
index 0000000000..688f6a7e9e
--- /dev/null
+++ b/src/test/signals/Makefile
@@ -0,0 +1,23 @@
+#-------------------------------------------------------------------------
+#
+# Makefile for src/test/recovery
+#
+# Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+# Portions Copyright (c) 1994, Regents of the University of California
+#
+# src/test/recovery/Makefile
+#
+#-------------------------------------------------------------------------
+
+subdir = src/test/signals
+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/signals/t/001_signal_autovacuum.pl b/src/test/signals/t/001_signal_autovacuum.pl
new file mode 100644
index 0000000000..93432b3a6b
--- /dev/null
+++ b/src/test/signals/t/001_signal_autovacuum.pl
@@ -0,0 +1,59 @@
+# Copyright (c) 2024-2024, PostgreSQL Global Development Group
+
+# Minimal test testing pg_signal_autovacuum role.
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize postgres
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init();
+$node->start;
+
+$node->safe_psql('postgres', qq(
+    CREATE DATABASE regress;
+    CREATE ROLE psa_reg_role_1;
+    CREATE ROLE psa_reg_role_2;
+    GRANT pg_signal_backend TO psa_reg_role_1;
+    GRANT pg_signal_autovacuum TO psa_reg_role_2;
+));
+
+# Create some content in a table and set autovacuum setting such that
+# it would be triggered.
+$node->safe_psql('regress', qq(
+    CREATE TABLE t(i int);
+    INSERT INTO t 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->restart;
+
+# wait for autovacuum to start.
+
+sleep 1;
+
+my $res_pid = $node->safe_psql('regress', qq(
+    SELECT pid FROM pg_stat_activity WHERE backend_type = 'autovacuum worker' and datname = 'regress';;
+));
+
+
+my ($res_reg_psa_1, $stdout_reg_psa_1, $stderr_reg_psa_1)  = $node->psql('regress', qq(
+    SET ROLE psa_reg_role_1;
+    SELECT pg_terminate_backend($res_pid);
+));
+
+ok($res_reg_psa_1 != 0, "should fail for non pg_signal_autovacuum");
+like($stderr_reg_psa_1, qr/Only roles with the SUPERUSER attribute may terminate processes of roles with the SUPERUSER attribute./, "matches");
+
+my ($res_reg_psa_2, $stdout_reg_psa_2, $stderr_reg_psa_2) = $node->psql('regress', qq(
+    SET ROLE psa_reg_role_2;
+    SELECT pg_terminate_backend($res_pid);
+));
+
+ok($res_reg_psa_2 == 0, "should success for pg_signal_autovacuum");
+
+done_testing();
-- 
2.40.1

From dc132bf5b15ddde69615a297db5f7969aef93547 Mon Sep 17 00:00:00 2001
From: Anthony Leung <antholeu@amazon.com>
Date: Thu, 4 Apr 2024 20:24:33 +0000
Subject: [PATCH] [PATCH v2 1/2] Introduce pg_signal_autovacuum role to allow
 non-superuser to cancel or termiante autovacuum worker

Non-superusers are currently not allowed to termiante autovacuum
backends which can create friction in some scenarios when performing
maintenance. This commit introduce a new pre-defined role
'pg_signal_autovacuum'. Non-superusers granted with this role are
allowed to terminate backends that are running autovacuum.
---
 doc/src/sgml/user-manag.sgml                |  4 +++
 src/backend/storage/ipc/signalfuncs.c       | 39 +++++++++++++++++----
 src/backend/utils/activity/backend_status.c | 19 ++++++++++
 src/include/catalog/pg_authid.dat           |  5 +++
 src/include/utils/backend_status.h          |  2 +-
 5 files changed, 62 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 07a16247d7..de8b6c28a4 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -705,6 +705,10 @@ DROP ROLE doomed_role;
        database to issue
        <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>.</entry>
       </row>
+      <row>
+       <entry>pg_signal_autovacuum</entry>
+       <entry>Allow signaling autovacuum worker backend to cancel or terminate</entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/storage/ipc/signalfuncs.c b/src/backend/storage/ipc/signalfuncs.c
index 88e9bf8125..5a533eb6ac 100644
--- a/src/backend/storage/ipc/signalfuncs.c
+++ b/src/backend/storage/ipc/signalfuncs.c
@@ -45,6 +45,7 @@
 #define SIGNAL_BACKEND_ERROR 1
 #define SIGNAL_BACKEND_NOPERMISSION 2
 #define SIGNAL_BACKEND_NOSUPERUSER 3
+#define SIGNAL_BACKEND_NOAUTOVACUUM 4
 static int
 pg_signal_backend(int pid, int sig)
 {
@@ -74,19 +75,31 @@ pg_signal_backend(int pid, int sig)
 		return SIGNAL_BACKEND_ERROR;
 	}
 
+	/*
+	 * If the backend is autovacuum worker, allow user with the privileges of
+	 * pg_signal_autovacuum role to signal the backend.
+	 */
+	if (pgstat_get_backend_type(GetNumberFromPGProc(proc)) == B_AUTOVAC_WORKER)
+	{
+		if (!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_AUTOVACUUM))
+			return SIGNAL_BACKEND_NOAUTOVACUUM;
+	}
 	/*
 	 * Only allow superusers to signal superuser-owned backends.  Any process
 	 * not advertising a role might have the importance of a superuser-owned
 	 * backend, so treat it that way.
-	 */
-	if ((!OidIsValid(proc->roleId) || superuser_arg(proc->roleId)) &&
-		!superuser())
+	*/
+	else if ((!OidIsValid(proc->roleId) || superuser_arg(proc->roleId)) &&
+			!superuser())
+	{
 		return SIGNAL_BACKEND_NOSUPERUSER;
-
+	}
 	/* Users can signal backends they have role membership in. */
-	if (!has_privs_of_role(GetUserId(), proc->roleId) &&
-		!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
+	else if (!has_privs_of_role(GetUserId(), proc->roleId) &&
+			!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
+	{
 		return SIGNAL_BACKEND_NOPERMISSION;
+	}
 
 	/*
 	 * Can the process we just validated above end, followed by the pid being
@@ -137,6 +150,13 @@ pg_cancel_backend(PG_FUNCTION_ARGS)
 				 errdetail("Only roles with privileges of the role whose query is being canceled or with privileges of the \"%s\" role may cancel this query.",
 						   "pg_signal_backend")));
 
+	if (r == SIGNAL_BACKEND_NOAUTOVACUUM)
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to cancel autovacuum worker backend"),
+				 errdetail("Only roles with the %s attribute or with privileges of the \"%s\" role may cancel autovacuum worker backend",
+						   "SUPERUSER", "pg_signal_autovacuum")));
+
 	PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
 }
 
@@ -243,6 +263,13 @@ pg_terminate_backend(PG_FUNCTION_ARGS)
 				 errdetail("Only roles with privileges of the role whose process is being terminated or with privileges of the \"%s\" role may terminate this process.",
 						   "pg_signal_backend")));
 
+	if (r == SIGNAL_BACKEND_NOAUTOVACUUM)
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to terminate autovacuum worker process"),
+				 errdetail("Only roles with the %s attribute or with privileges of the \"%s\" role may terminate autovacuum worker backend",
+						   "SUPERUSER", "pg_signal_autovacuum")));
+
 	/* Wait only on success and if actually requested */
 	if (r == SIGNAL_BACKEND_SUCCESS && timeout > 0)
 		PG_RETURN_BOOL(pg_wait_until_termination(pid, timeout));
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index 1ccf4c6d83..edbb4341f5 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -1038,6 +1038,25 @@ pgstat_get_my_query_id(void)
 	return MyBEEntry->st_query_id;
 }
 
+/* ----------
+ * pgstat_get_backend_type() -
+ *
+ * Return the backend type of the backend for the given proc number.
+ * ----------
+ */
+BackendType
+pgstat_get_backend_type(ProcNumber procNumber)
+{
+	PgBackendStatus *ret;
+
+	ret = pgstat_get_beentry_by_proc_number(procNumber);
+
+	if (!ret)
+		return false;
+
+	return ret->st_backendType;
+}
+
 /* ----------
  * cmp_lbestatus
  *
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 55cabdda6f..a481f0bcf1 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -99,5 +99,10 @@
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
   rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '6312', oid_symbol => 'ROLE_PG_SIGNAL_AUTOVACUUM',
+  rolname => 'pg_signal_autovacuum', rolsuper => 'f', rolinherit => 't',
+  rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+  rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+  rolpassword => '_null_', rolvaliduntil => '_null_' },
 
 ]
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 7b7f6f59d0..7fd9cd7167 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -323,7 +323,7 @@ extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
 extern const char *pgstat_get_crashed_backend_activity(int pid, char *buffer,
 													   int buflen);
 extern uint64 pgstat_get_my_query_id(void);
-
+extern BackendType pgstat_get_backend_type(ProcNumber procNumber);
 
 /* ----------
  * Support functions for the SQL-callable functions to
-- 
2.40.1

From 6b11275dbe04068a9ed1b744c3eeff4395f2ef81 Mon Sep 17 00:00:00 2001
From: Anthony Leung <antholeu@amazon.com>
Date: Thu, 4 Apr 2024 20:24:33 +0000
Subject: [PATCH] Introduce pg_signal_autovacuum role to allow non-superuser to
 signal autovacuum worker

Non-superusers are currently not allowed to signal autovacuum
workers which can create friction in some scenarios when performing
maintenance. This commit introduce a new pre-defined role
'pg_signal_autovacuum'. Non-superusers granted with this role are
allowed to signal backends that are running autovacuum.
---
 doc/src/sgml/user-manag.sgml                |  4 +++
 src/backend/storage/ipc/signalfuncs.c       | 39 +++++++++++++++++----
 src/backend/utils/activity/backend_status.c | 19 ++++++++++
 src/include/catalog/pg_authid.dat           |  5 +++
 src/include/utils/backend_status.h          |  2 +-
 5 files changed, 62 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 07a16247d7..de8b6c28a4 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -705,6 +705,10 @@ DROP ROLE doomed_role;
        database to issue
        <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>.</entry>
       </row>
+      <row>
+       <entry>pg_signal_autovacuum</entry>
+       <entry>Allow signaling autovacuum worker backend to cancel or terminate</entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/storage/ipc/signalfuncs.c b/src/backend/storage/ipc/signalfuncs.c
index 88e9bf8125..4d769c85d3 100644
--- a/src/backend/storage/ipc/signalfuncs.c
+++ b/src/backend/storage/ipc/signalfuncs.c
@@ -45,6 +45,7 @@
 #define SIGNAL_BACKEND_ERROR 1
 #define SIGNAL_BACKEND_NOPERMISSION 2
 #define SIGNAL_BACKEND_NOSUPERUSER 3
+#define SIGNAL_BACKEND_NOAUTOVACUUM 4
 static int
 pg_signal_backend(int pid, int sig)
 {
@@ -74,19 +75,31 @@ pg_signal_backend(int pid, int sig)
 		return SIGNAL_BACKEND_ERROR;
 	}
 
+	/*
+	 * If the backend is autovacuum worker, allow user with the privileges of
+	 * pg_signal_autovacuum role to signal the backend.
+	 */
+	if (pgstat_get_backend_type(GetNumberFromPGProc(proc)) == B_AUTOVAC_WORKER)
+	{
+		if (!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_AUTOVACUUM))
+			return SIGNAL_BACKEND_NOAUTOVACUUM;
+	}
 	/*
 	 * Only allow superusers to signal superuser-owned backends.  Any process
 	 * not advertising a role might have the importance of a superuser-owned
 	 * backend, so treat it that way.
-	 */
-	if ((!OidIsValid(proc->roleId) || superuser_arg(proc->roleId)) &&
-		!superuser())
+	*/
+	else if ((!OidIsValid(proc->roleId) || superuser_arg(proc->roleId)) &&
+			!superuser())
+	{
 		return SIGNAL_BACKEND_NOSUPERUSER;
-
+	}
 	/* Users can signal backends they have role membership in. */
-	if (!has_privs_of_role(GetUserId(), proc->roleId) &&
-		!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
+	else if (!has_privs_of_role(GetUserId(), proc->roleId) &&
+			!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
+	{
 		return SIGNAL_BACKEND_NOPERMISSION;
+	}
 
 	/*
 	 * Can the process we just validated above end, followed by the pid being
@@ -137,6 +150,13 @@ pg_cancel_backend(PG_FUNCTION_ARGS)
 				 errdetail("Only roles with privileges of the role whose query is being canceled or with privileges of the \"%s\" role may cancel this query.",
 						   "pg_signal_backend")));
 
+	if (r == SIGNAL_BACKEND_NOAUTOVACUUM)
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to cancel autovacuum worker backend"),
+				 errdetail("Only roles with the %s attribute or with privileges of the \"%s\" role may cancel autovacuum worker backend",
+						   "SUPERUSER", "pg_signal_autovacuum")));
+
 	PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
 }
 
@@ -243,6 +263,13 @@ pg_terminate_backend(PG_FUNCTION_ARGS)
 				 errdetail("Only roles with privileges of the role whose process is being terminated or with privileges of the \"%s\" role may terminate this process.",
 						   "pg_signal_backend")));
 
+	if (r == SIGNAL_BACKEND_NOAUTOVACUUM)
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to terminate autovacuum worker backend"),
+				 errdetail("Only roles with the %s attribute or with privileges of the \"%s\" role may terminate autovacuum worker backend",
+						   "SUPERUSER", "pg_signal_autovacuum")));
+
 	/* Wait only on success and if actually requested */
 	if (r == SIGNAL_BACKEND_SUCCESS && timeout > 0)
 		PG_RETURN_BOOL(pg_wait_until_termination(pid, timeout));
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index 1ccf4c6d83..edbb4341f5 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -1038,6 +1038,25 @@ pgstat_get_my_query_id(void)
 	return MyBEEntry->st_query_id;
 }
 
+/* ----------
+ * pgstat_get_backend_type() -
+ *
+ * Return the backend type of the backend for the given proc number.
+ * ----------
+ */
+BackendType
+pgstat_get_backend_type(ProcNumber procNumber)
+{
+	PgBackendStatus *ret;
+
+	ret = pgstat_get_beentry_by_proc_number(procNumber);
+
+	if (!ret)
+		return false;
+
+	return ret->st_backendType;
+}
+
 /* ----------
  * cmp_lbestatus
  *
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 55cabdda6f..a481f0bcf1 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -99,5 +99,10 @@
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
   rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '6312', oid_symbol => 'ROLE_PG_SIGNAL_AUTOVACUUM',
+  rolname => 'pg_signal_autovacuum', rolsuper => 'f', rolinherit => 't',
+  rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+  rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+  rolpassword => '_null_', rolvaliduntil => '_null_' },
 
 ]
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 7b7f6f59d0..7fd9cd7167 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -323,7 +323,7 @@ extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
 extern const char *pgstat_get_crashed_backend_activity(int pid, char *buffer,
 													   int buflen);
 extern uint64 pgstat_get_my_query_id(void);
-
+extern BackendType pgstat_get_backend_type(ProcNumber procNumber);
 
 /* ----------
  * Support functions for the SQL-callable functions to
-- 
2.40.1

From 83345fd5405039ed28b728ebe7f05ab84b9562f8 Mon Sep 17 00:00:00 2001
From: Anthony Leung <antholeu@amazon.com>
Date: Thu, 4 Apr 2024 23:33:13 +0000
Subject: [PATCH] Add tap test for pg_signal_autovacuum role

Add a tap test to validate the following:

1. Normal user cannot signal autovacuum worker backend
2. User with pg_signal_backend role cannot signal autovacuum worker
   backend
3. User with pg_signal_autovacuum role can signal autovacuum worker
   backend
---
 src/backend/postmaster/autovacuum.c         |  3 +
 src/test/signals/.gitignore                 |  2 +
 src/test/signals/Makefile                   | 27 +++++++
 src/test/signals/meson.build                | 15 ++++
 src/test/signals/t/001_signal_autovacuum.pl | 87 +++++++++++++++++++++
 5 files changed, 134 insertions(+)
 create mode 100644 src/test/signals/.gitignore
 create mode 100644 src/test/signals/Makefile
 create mode 100644 src/test/signals/meson.build
 create mode 100644 src/test/signals/t/001_signal_autovacuum.pl

diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index c367ede6f8..241d8712c4 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -100,6 +100,7 @@
 #include "utils/fmgroids.h"
 #include "utils/fmgrprotos.h"
 #include "utils/guc_hooks.h"
+#include "utils/injection_point.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/ps_status.h"
@@ -1889,6 +1890,8 @@ do_autovacuum(void)
 	bool		found_concurrent_worker = false;
 	int			i;
 
+	INJECTION_POINT("autovacuum-start");
+
 	/*
 	 * StartTransactionCommand and CommitTransactionCommand will automatically
 	 * switch to other contexts.  We need this one to keep the list of
diff --git a/src/test/signals/.gitignore b/src/test/signals/.gitignore
new file mode 100644
index 0000000000..871e943d50
--- /dev/null
+++ b/src/test/signals/.gitignore
@@ -0,0 +1,2 @@
+# Generated by test suite
+/tmp_check/
diff --git a/src/test/signals/Makefile b/src/test/signals/Makefile
new file mode 100644
index 0000000000..18d8d2d0af
--- /dev/null
+++ b/src/test/signals/Makefile
@@ -0,0 +1,27 @@
+#-------------------------------------------------------------------------
+#
+# Makefile for src/test/recovery
+#
+# Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+# Portions Copyright (c) 1994, Regents of the University of California
+#
+# src/test/recovery/Makefile
+#
+#-------------------------------------------------------------------------
+
+EXTRA_INSTALL=src/test/modules/injection_points
+
+subdir = src/test/signals
+top_builddir = ../../..
+include $(top_builddir)/src/Makefile.global
+
+export enable_injection_points enable_injection_points
+
+check:
+	$(prove_check)
+
+installcheck:
+	$(prove_installcheck)
+
+clean distclean maintainer-clean:
+	rm -rf tmp_check
diff --git a/src/test/signals/meson.build b/src/test/signals/meson.build
new file mode 100644
index 0000000000..3280d79ebc
--- /dev/null
+++ b/src/test/signals/meson.build
@@ -0,0 +1,15 @@
+# Copyright (c) 2022-2024, PostgreSQL Global Development Group
+
+tests += {
+  'name': 'signals',
+  'sd': meson.current_source_dir(),
+  'bd': meson.current_build_dir(),
+  'tap': {
+    'env': {
+       'enable_injection_points': get_option('injection_points') ? 'yes' : 'no',
+    },
+    'tests': [
+      't/001_signal_autovacuum.pl',
+    ],
+  },
+}
diff --git a/src/test/signals/t/001_signal_autovacuum.pl b/src/test/signals/t/001_signal_autovacuum.pl
new file mode 100644
index 0000000000..a219b3e2a1
--- /dev/null
+++ b/src/test/signals/t/001_signal_autovacuum.pl
@@ -0,0 +1,87 @@
+# Copyright (c) 2024-2024, PostgreSQL Global Development Group
+
+# Test signaling for pg_signal_autovacuum role.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+if ($ENV{enable_injection_points} ne 'yes')
+{
+	plan skip_all => 'Injection points not supported by this build';
+}
+
+# Initialize postgres
+my $psql_err = '';
+my $psql_out = '';
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init();
+$node->append_conf(
+	'postgresql.conf', 'autovacuum_naptime = 1
+');
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION injection_points;');
+
+# Regular user and user with pg_signal_backend role cannot signal autovacuum worker
+# User with pg_signal_backend can signal autovacuum worker 
+$node->safe_psql('postgres', qq(
+    CREATE ROLE regular_role;
+    CREATE ROLE signal_backend_role;
+    CREATE ROLE signal_autovacuum_role;
+    GRANT pg_signal_backend TO signal_backend_role;
+    GRANT pg_signal_autovacuum TO signal_autovacuum_role;
+));
+
+# From this point, autovacuum worker will wait before doing any vacuum.
+$node->safe_psql('postgres',
+	"SELECT injection_points_attach('autovacuum-start', 'wait');");
+
+# Create some content and set autovacuum setting such that it would be triggered.
+$node->safe_psql('postgres', qq(
+    CREATE TABLE tab_int(i int);
+    ALTER TABLE tab_int SET (autovacuum_vacuum_cost_limit = 1);
+    ALTER TABLE tab_int SET (autovacuum_vacuum_cost_delay = 100);
+));
+
+$node->safe_psql('postgres', qq(
+    INSERT INTO tab_int SELECT * FROM generate_series(1, 1000000);
+));
+
+# Wait until the autovacuum worker starts
+$node->wait_for_event('autovacuum worker', 'autovacuum-start');
+
+my $av_pid = $node->safe_psql('postgres', qq(
+    SELECT pid FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
+));
+
+# Regular user cannot terminate autovacuum worker
+my $terminate_with_no_pg_signal_av = $node->psql('postgres', qq( 
+    SET ROLE regular_role;
+    SELECT pg_terminate_backend($av_pid);
+), stdout => \$psql_out, stderr => \$psql_err);
+
+ok($terminate_with_no_pg_signal_av != 0, "Terminating autovacuum worker should not succeed without pg_signal_autovacuum role");
+like($psql_err, qr/ERROR:  permission denied to terminate autovacuum worker backend\nDETAIL:  Only roles with the SUPERUSER attribute or with privileges of the "pg_signal_autovacuum" role may terminate autovacuum worker backend/,
+    "passcheck errors gracefully when passcheck database is invalid");
+
+# User with signal_backend_role cannot terminate autovacuum worker
+my $terminate_with_signal_backend_role = $node->psql('postgres', qq( 
+    SET ROLE signal_backend_role;
+    SELECT pg_terminate_backend($av_pid);
+), stdout => \$psql_out, stderr => \$psql_err);
+
+ok($terminate_with_no_pg_signal_av != 0, "Terminating autovacuum worker should not succeed without pg_signal_autovacuum role");
+like($psql_err, qr/ERROR:  permission denied to terminate autovacuum worker backend\nDETAIL:  Only roles with the SUPERUSER attribute or with privileges of the "pg_signal_autovacuum" role may terminate autovacuum worker backend/,
+    "passcheck errors gracefully when passcheck database is invalid");
+
+# User with pg_signal_backend can terminate autovacuum worker 
+my $terminate_with_pg_signal_av = $node->psql('postgres', qq( 
+    SET ROLE signal_autovacuum_role;
+    SELECT pg_terminate_backend($av_pid);
+), stdout => \$psql_out, stderr => \$psql_err);
+
+ok($terminate_with_pg_signal_av == 0, "Terminating autovacuum worker should succeed with pg_signal_autovacuum role");
+
+done_testing();
-- 
2.40.1

From 591fffa188eaecf59280ff32fe377fc782dd6457 Mon Sep 17 00:00:00 2001
From: Anthony Leung <antholeu@amazon.com>
Date: Thu, 4 Apr 2024 20:24:33 +0000
Subject: [PATCH v4 1/2] Introduce pg_signal_autovacuum role to allow
 non-superuser to signal autovacuum worker

Non-superusers are currently not allowed to signal autovacuum
workers which can create friction in some scenarios when performing
maintenance. This commit introduce a new pre-defined role
'pg_signal_autovacuum'. Non-superusers granted with this role are
allowed to signal backends that are running autovacuum.
---
 doc/src/sgml/user-manag.sgml                |  4 +++
 src/backend/storage/ipc/signalfuncs.c       | 40 +++++++++++++++++----
 src/backend/utils/activity/backend_status.c | 19 ++++++++++
 src/include/catalog/pg_authid.dat           |  5 +++
 src/include/utils/backend_status.h          |  2 +-
 5 files changed, 63 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 07a16247d7..2c5b569d4d 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -705,6 +705,10 @@ DROP ROLE doomed_role;
        database to issue
        <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>.</entry>
       </row>
+      <row>
+       <entry>pg_signal_autovacuum_worker</entry>
+       <entry>Allow signaling autovacuum worker backend to cancel or terminate</entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/storage/ipc/signalfuncs.c b/src/backend/storage/ipc/signalfuncs.c
index 88e9bf8125..12d93ba83f 100644
--- a/src/backend/storage/ipc/signalfuncs.c
+++ b/src/backend/storage/ipc/signalfuncs.c
@@ -45,6 +45,7 @@
 #define SIGNAL_BACKEND_ERROR 1
 #define SIGNAL_BACKEND_NOPERMISSION 2
 #define SIGNAL_BACKEND_NOSUPERUSER 3
+#define SIGNAL_BACKEND_NOAUTOVACUUM 4
 static int
 pg_signal_backend(int pid, int sig)
 {
@@ -74,19 +75,32 @@ pg_signal_backend(int pid, int sig)
 		return SIGNAL_BACKEND_ERROR;
 	}
 
+	/*
+	 * If the backend is autovacuum worker, allow user with the privileges of
+	 * pg_signal_autovacuum_worker role to signal the backend.
+	 */
+	if ((!OidIsValid(proc->roleId) || superuser_arg(proc->roleId))
+		&& pgstat_get_backend_type(GetNumberFromPGProc(proc)) == B_AUTOVAC_WORKER)
+	{
+		if (!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_AUTOVACUUM_WORKER))
+			return SIGNAL_BACKEND_NOAUTOVACUUM;
+	}
 	/*
 	 * Only allow superusers to signal superuser-owned backends.  Any process
 	 * not advertising a role might have the importance of a superuser-owned
 	 * backend, so treat it that way.
-	 */
-	if ((!OidIsValid(proc->roleId) || superuser_arg(proc->roleId)) &&
-		!superuser())
+	*/
+	else if ((!OidIsValid(proc->roleId) || superuser_arg(proc->roleId)) &&
+			!superuser())
+	{
 		return SIGNAL_BACKEND_NOSUPERUSER;
-
+	}
 	/* Users can signal backends they have role membership in. */
-	if (!has_privs_of_role(GetUserId(), proc->roleId) &&
-		!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
+	else if (!has_privs_of_role(GetUserId(), proc->roleId) &&
+			!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
+	{
 		return SIGNAL_BACKEND_NOPERMISSION;
+	}
 
 	/*
 	 * Can the process we just validated above end, followed by the pid being
@@ -137,6 +151,13 @@ pg_cancel_backend(PG_FUNCTION_ARGS)
 				 errdetail("Only roles with privileges of the role whose query is being canceled or with privileges of the \"%s\" role may cancel this query.",
 						   "pg_signal_backend")));
 
+	if (r == SIGNAL_BACKEND_NOAUTOVACUUM)
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to cancel autovacuum worker backend"),
+				 errdetail("Only roles with the %s attribute or with privileges of the \"%s\" role may cancel autovacuum worker backend",
+						   "SUPERUSER", "pg_signal_autovacuum_worker")));
+
 	PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
 }
 
@@ -243,6 +264,13 @@ pg_terminate_backend(PG_FUNCTION_ARGS)
 				 errdetail("Only roles with privileges of the role whose process is being terminated or with privileges of the \"%s\" role may terminate this process.",
 						   "pg_signal_backend")));
 
+	if (r == SIGNAL_BACKEND_NOAUTOVACUUM)
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to terminate autovacuum worker backend"),
+				 errdetail("Only roles with the %s attribute or with privileges of the \"%s\" role may terminate autovacuum worker backend",
+						   "SUPERUSER", "pg_signal_autovacuum_worker")));
+
 	/* Wait only on success and if actually requested */
 	if (r == SIGNAL_BACKEND_SUCCESS && timeout > 0)
 		PG_RETURN_BOOL(pg_wait_until_termination(pid, timeout));
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index 1ccf4c6d83..a6c7899318 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -1038,6 +1038,25 @@ pgstat_get_my_query_id(void)
 	return MyBEEntry->st_query_id;
 }
 
+/* ----------
+ * pgstat_get_backend_type() -
+ *
+ * Return the backend type of the backend for the given proc number.
+ * ----------
+ */
+BackendType
+pgstat_get_backend_type(ProcNumber procNumber)
+{
+	PgBackendStatus *ret;
+
+	ret = pgstat_get_beentry_by_proc_number(procNumber);
+
+	if (!ret)
+		return B_INVALID;
+
+	return ret->st_backendType;
+}
+
 /* ----------
  * cmp_lbestatus
  *
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 55cabdda6f..6156fc55dd 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -99,5 +99,10 @@
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
   rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '8916', oid_symbol => 'ROLE_PG_SIGNAL_AUTOVACUUM_WORKER',
+  rolname => 'pg_signal_autovacuum_worker', rolsuper => 'f', rolinherit => 't',
+  rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+  rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+  rolpassword => '_null_', rolvaliduntil => '_null_' },
 
 ]
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 7b7f6f59d0..7fd9cd7167 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -323,7 +323,7 @@ extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
 extern const char *pgstat_get_crashed_backend_activity(int pid, char *buffer,
 													   int buflen);
 extern uint64 pgstat_get_my_query_id(void);
-
+extern BackendType pgstat_get_backend_type(ProcNumber procNumber);
 
 /* ----------
  * Support functions for the SQL-callable functions to
-- 
2.25.1

From 7661fb08515c2d1f159111e3f11a92d406050cb0 Mon Sep 17 00:00:00 2001
From: Anthony Leung <antholeu@amazon.com>
Date: Thu, 4 Apr 2024 23:33:13 +0000
Subject: [PATCH v4 2/2] Add tap test for pg_signal_autovacuum role

Add a tap test to validate the following:

1. Normal user cannot signal autovacuum worker backend
2. User with pg_signal_backend role cannot signal autovacuum worker
   backend
3. User with pg_signal_autovacuum role can signal autovacuum worker
   backend
---
 src/backend/postmaster/autovacuum.c         |  3 +
 src/test/signals/.gitignore                 |  2 +
 src/test/signals/Makefile                   | 27 ++++++
 src/test/signals/meson.build                | 15 ++++
 src/test/signals/t/001_signal_autovacuum.pl | 93 +++++++++++++++++++++
 5 files changed, 140 insertions(+)
 create mode 100644 src/test/signals/.gitignore
 create mode 100644 src/test/signals/Makefile
 create mode 100644 src/test/signals/meson.build
 create mode 100644 src/test/signals/t/001_signal_autovacuum.pl

diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 170b973cc5..f13076f3b3 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -100,6 +100,7 @@
 #include "utils/fmgroids.h"
 #include "utils/fmgrprotos.h"
 #include "utils/guc_hooks.h"
+#include "utils/injection_point.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/ps_status.h"
@@ -1889,6 +1890,8 @@ do_autovacuum(void)
 	bool		found_concurrent_worker = false;
 	int			i;
 
+	INJECTION_POINT("autovacuum-worker-start");
+
 	/*
 	 * StartTransactionCommand and CommitTransactionCommand will automatically
 	 * switch to other contexts.  We need this one to keep the list of
diff --git a/src/test/signals/.gitignore b/src/test/signals/.gitignore
new file mode 100644
index 0000000000..871e943d50
--- /dev/null
+++ b/src/test/signals/.gitignore
@@ -0,0 +1,2 @@
+# Generated by test suite
+/tmp_check/
diff --git a/src/test/signals/Makefile b/src/test/signals/Makefile
new file mode 100644
index 0000000000..b5c1d65be4
--- /dev/null
+++ b/src/test/signals/Makefile
@@ -0,0 +1,27 @@
+#-------------------------------------------------------------------------
+#
+# Makefile for src/test/signals
+#
+# Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+# Portions Copyright (c) 1994, Regents of the University of California
+#
+# src/test/signals/Makefile
+#
+#-------------------------------------------------------------------------
+
+EXTRA_INSTALL=src/test/modules/injection_points
+
+subdir = src/test/signals
+top_builddir = ../../..
+include $(top_builddir)/src/Makefile.global
+
+export enable_injection_points enable_injection_points
+
+check:
+	$(prove_check)
+
+installcheck:
+	$(prove_installcheck)
+
+clean distclean maintainer-clean:
+	rm -rf tmp_check
diff --git a/src/test/signals/meson.build b/src/test/signals/meson.build
new file mode 100644
index 0000000000..aec7437fb0
--- /dev/null
+++ b/src/test/signals/meson.build
@@ -0,0 +1,15 @@
+# Copyright (c) 2024-2024, PostgreSQL Global Development Group
+
+tests += {
+  'name': 'signals',
+  'sd': meson.current_source_dir(),
+  'bd': meson.current_build_dir(),
+  'tap': {
+    'env': {
+       'enable_injection_points': get_option('injection_points') ? 'yes' : 'no',
+    },
+    'tests': [
+      't/001_signal_autovacuum.pl',
+    ],
+  },
+}
diff --git a/src/test/signals/t/001_signal_autovacuum.pl b/src/test/signals/t/001_signal_autovacuum.pl
new file mode 100644
index 0000000000..fd5850958c
--- /dev/null
+++ b/src/test/signals/t/001_signal_autovacuum.pl
@@ -0,0 +1,93 @@
+# Copyright (c) 2024-2024, PostgreSQL Global Development Group
+
+# Test signaling autovacuum worker backend by non-superuser role.
+# Regular roles are tested to fail to signals autovacuum worker, roles
+# with pg_signal_autovacuum_worker are tested to success this.
+# Test uses an injection point to ensure that the worker is present
+# for the whole duration of the test.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+if ($ENV{enable_injection_points} ne 'yes')
+{
+	plan skip_all => 'Injection points not supported by this build';
+}
+
+# Initialize postgres
+my $psql_err = '';
+my $psql_out = '';
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init();
+$node->append_conf(
+	'postgresql.conf', 'autovacuum_naptime = 1
+');
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION injection_points;');
+
+# Regular user and user with pg_signal_backend role cannot signal autovacuum worker
+# User with pg_signal_backend can signal autovacuum worker 
+$node->safe_psql('postgres', qq(
+    CREATE ROLE regular_role;
+    CREATE ROLE signal_backend_role;
+    CREATE ROLE signal_autovacuum_worker_role;
+    GRANT pg_signal_backend TO signal_backend_role;
+    GRANT pg_signal_autovacuum_worker TO signal_autovacuum_worker_role;
+));
+
+# From this point, autovacuum worker will wait before doing any vacuum.
+$node->safe_psql('postgres',
+	"SELECT injection_points_attach('autovacuum-worker-start', 'wait');");
+
+# Create some content and set autovacuum setting such that it would be triggered.
+$node->safe_psql('postgres', qq(
+    CREATE TABLE tab_int(i int);
+    ALTER TABLE tab_int SET (autovacuum_vacuum_cost_limit = 1);
+    ALTER TABLE tab_int SET (autovacuum_vacuum_cost_delay = 100);
+));
+
+$node->safe_psql('postgres', qq(
+    INSERT INTO tab_int VALUES(1);
+));
+
+# Wait until the autovacuum worker starts
+$node->wait_for_event('autovacuum worker', 'autovacuum-worker-start');
+
+my $av_pid = $node->safe_psql('postgres', qq(
+    SELECT pid FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
+));
+
+# Regular user cannot terminate autovacuum worker
+my $terminate_with_no_pg_signal_av = $node->psql('postgres', qq( 
+    SET ROLE regular_role;
+    SELECT pg_terminate_backend($av_pid);
+), stdout => \$psql_out, stderr => \$psql_err);
+
+ok($terminate_with_no_pg_signal_av != 0, "Terminating autovacuum worker should not succeed without pg_signal_autovacuum role");
+like($psql_err, qr/ERROR:  permission denied to terminate autovacuum worker backend\nDETAIL:  Only roles with the SUPERUSER attribute or with privileges of the "pg_signal_autovacuum_worker" role may terminate autovacuum worker backend/,
+    "Terminating autovacuum errors gracefully when role is not granted with pg_signal_autovacuum_worker");
+
+my $offset = -s $node->logfile;
+
+# User with pg_signal_autovacuum can terminate autovacuum worker 
+my $terminate_with_pg_signal_av = $node->psql('postgres', qq( 
+    SET ROLE signal_autovacuum_worker_role;
+    SELECT pg_terminate_backend($av_pid);
+), stdout => \$psql_out, stderr => \$psql_err);
+
+
+ok($terminate_with_pg_signal_av == 0, "Terminating autovacuum worker should succeed with pg_signal_autovacuum role");
+
+# Check that the primary server logs a FATAL indicating that
+# autovacuum is terminated.
+ok($node->log_contains(qr/FATAL:  terminating autovacuum process due to administrator command/, $offset), 
+        "Autovacuum terminates when role is granted with pg_signal_autovacuum_worker");
+
+# Release injection point.
+$node->safe_psql('postgres',
+	"SELECT injection_point_detach('autovacuum-worker-start');");
+
+done_testing();
-- 
2.25.1

From 118f95346fcf8099ab28d2f9186185171e3b88af Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 12 Jun 2024 15:38:14 -0500
Subject: [PATCH v5 1/1] Introduce pg_signal_autovacuum_worker.

Since commit 3a9b18b309, roles with privileges of pg_signal_backend
cannot signal autovacuum workers.  Many users treated the ability
to signal autovacuum workers as a feature instead of a bug, so we
are reintroducing it via a new predefined role.  Having privileges
of this new role, named pg_signal_autovacuum_worker, only permits
signaling autovacuum workers.  It does not permit signaling other
types of superuser backends.

Author: Kirill Reshke
Reviewed-by: Anthony Leung, Michael Paquier
Discussion: https://postgr.es/m/CALdSSPhC4GGmbnugHfB9G0%3DfAxjCSug_-rmL9oUh0LTxsyBfsg%40mail.gmail.com
---
 doc/src/sgml/func.sgml                |  8 +++--
 doc/src/sgml/user-manag.sgml          |  5 +++
 src/backend/storage/ipc/signalfuncs.c | 46 +++++++++++++++++++++------
 src/include/catalog/pg_authid.dat     |  5 +++
 4 files changed, 53 insertions(+), 11 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 17c44bc338..7d5b7acb68 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28037,7 +28037,9 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
        <para>
         Cancels the current query of the session whose backend process has the
         specified process ID.  This is also allowed if the
-        calling role is a member of the role whose backend is being canceled or
+        calling role has privileges of the role whose backend is being canceled,
+        the backend process is an autovacuum worker and the calling role has
+        privileges of <literal>pg_signal_autovacuum_worker</literal>, or
         the calling role has privileges of <literal>pg_signal_backend</literal>,
         however only superusers can cancel superuser backends.
        </para></entry>
@@ -28111,7 +28113,9 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
        <para>
         Terminates the session whose backend process has the
         specified process ID.  This is also allowed if the calling role
-        is a member of the role whose backend is being terminated or the
+        has privileges of the role whose backend is being terminated,
+        the backend process is an autovacuum worker and the calling role has
+        privileges of <literal>pg_signal_autovacuum_worker</literal>, or the
         calling role has privileges of <literal>pg_signal_backend</literal>,
         however only superusers can terminate superuser backends.
        </para>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 07a16247d7..340cefff70 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -661,6 +661,11 @@ DROP ROLE doomed_role;
        <entry>pg_signal_backend</entry>
        <entry>Signal another backend to cancel a query or terminate its session.</entry>
       </row>
+      <row>
+       <entry>pg_signal_autovacuum_worker</entry>
+       <entry>Signal an autovacuum worker to cancel the current table's vacuum
+       or terminate its session.</entry>
+      </row>
       <row>
        <entry>pg_read_server_files</entry>
        <entry>Allow reading files from any location the database can access on the server with COPY and
diff --git a/src/backend/storage/ipc/signalfuncs.c b/src/backend/storage/ipc/signalfuncs.c
index 88e9bf8125..780a1c682a 100644
--- a/src/backend/storage/ipc/signalfuncs.c
+++ b/src/backend/storage/ipc/signalfuncs.c
@@ -34,8 +34,9 @@
  * role as the backend being signaled. For "dangerous" signals, an explicit
  * check for superuser needs to be done prior to calling this function.
  *
- * Returns 0 on success, 1 on general failure, 2 on normal permission error
- * and 3 if the caller needs to be a superuser.
+ * Returns 0 on success, 1 on general failure, 2 on normal permission error,
+ * 3 if the caller needs to be a superuser, and 4 if the caller needs to have
+ * privileges of pg_signal_autovacuum_worker.
  *
  * In the event of a general failure (return code 1), a warning message will
  * be emitted. For permission errors, doing that is the responsibility of
@@ -45,6 +46,7 @@
 #define SIGNAL_BACKEND_ERROR 1
 #define SIGNAL_BACKEND_NOPERMISSION 2
 #define SIGNAL_BACKEND_NOSUPERUSER 3
+#define SIGNAL_BACKEND_NOAUTOVAC 4
 static int
 pg_signal_backend(int pid, int sig)
 {
@@ -77,15 +79,27 @@ pg_signal_backend(int pid, int sig)
 	/*
 	 * Only allow superusers to signal superuser-owned backends.  Any process
 	 * not advertising a role might have the importance of a superuser-owned
-	 * backend, so treat it that way.
+	 * backend, so treat it that way.  As an exception, we allow roles with
+	 * privileges of pg_signal_autovacuum_worker to signal autovacuum workers
+	 * (which do not advertise a role).
+	 *
+	 * Otherwise, users can signal backends for roles they have privileges of.
 	 */
-	if ((!OidIsValid(proc->roleId) || superuser_arg(proc->roleId)) &&
-		!superuser())
-		return SIGNAL_BACKEND_NOSUPERUSER;
+	if (!OidIsValid(proc->roleId) || superuser_arg(proc->roleId))
+	{
+		ProcNumber	procNumber = GetNumberFromPGProc(proc);
+		PgBackendStatus *procStatus = pgstat_get_beentry_by_proc_number(procNumber);
 
-	/* Users can signal backends they have role membership in. */
-	if (!has_privs_of_role(GetUserId(), proc->roleId) &&
-		!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
+		if (procStatus && procStatus->st_backendType == B_AUTOVAC_WORKER)
+		{
+			if (!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_AUTOVACUUM_WORKER))
+				return SIGNAL_BACKEND_NOAUTOVAC;
+		}
+		else if (!superuser())
+			return SIGNAL_BACKEND_NOSUPERUSER;
+	}
+	else if (!has_privs_of_role(GetUserId(), proc->roleId) &&
+			 !has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
 		return SIGNAL_BACKEND_NOPERMISSION;
 
 	/*
@@ -130,6 +144,13 @@ pg_cancel_backend(PG_FUNCTION_ARGS)
 				 errdetail("Only roles with the %s attribute may cancel queries of roles with the %s attribute.",
 						   "SUPERUSER", "SUPERUSER")));
 
+	if (r == SIGNAL_BACKEND_NOAUTOVAC)
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to cancel query"),
+				 errdetail("Only roles with privileges of the \"%s\" role may cancel queries of autovacuum workers.",
+						   "pg_signal_autovacuum_worker")));
+
 	if (r == SIGNAL_BACKEND_NOPERMISSION)
 		ereport(ERROR,
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
@@ -236,6 +257,13 @@ pg_terminate_backend(PG_FUNCTION_ARGS)
 				 errdetail("Only roles with the %s attribute may terminate processes of roles with the %s attribute.",
 						   "SUPERUSER", "SUPERUSER")));
 
+	if (r == SIGNAL_BACKEND_NOAUTOVAC)
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to terminate process"),
+				 errdetail("Only roles with privileges of the \"%s\" role may terminate autovacuum worker processes.",
+						   "pg_signal_autovacuum_worker")));
+
 	if (r == SIGNAL_BACKEND_NOPERMISSION)
 		ereport(ERROR,
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index bf00815c14..0129f67eaa 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -99,5 +99,10 @@
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
   rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '8916', oid_symbol => 'ROLE_PG_SIGNAL_AUTOVACUUM_WORKER',
+  rolname => 'pg_signal_autovacuum_worker', rolsuper => 'f', rolinherit => 't',
+  rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+  rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+  rolpassword => '_null_', rolvaliduntil => '_null_' },
 
 ]
-- 
2.39.3 (Apple Git-146)

From 2d3e219149b8dbdbaf2b465f619f898c61969b17 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 12 Jun 2024 15:38:14 -0500
Subject: [PATCH v6 1/2] Introduce pg_signal_autovacuum_worker.

Since commit 3a9b18b309, roles with privileges of pg_signal_backend
cannot signal autovacuum workers.  Many users treated the ability
to signal autovacuum workers as a feature instead of a bug, so we
are reintroducing it via a new predefined role.  Having privileges
of this new role, named pg_signal_autovacuum_worker, only permits
signaling autovacuum workers.  It does not permit signaling other
types of superuser backends.

Author: Kirill Reshke
Reviewed-by: Anthony Leung, Michael Paquier
Discussion: https://postgr.es/m/CALdSSPhC4GGmbnugHfB9G0%3DfAxjCSug_-rmL9oUh0LTxsyBfsg%40mail.gmail.com
---
 src/include/catalog/pg_authid.dat     |  5 +++
 src/backend/storage/ipc/signalfuncs.c | 46 +++++++++++++++++++++------
 doc/src/sgml/func.sgml                |  8 +++--
 doc/src/sgml/user-manag.sgml          |  5 +++
 4 files changed, 53 insertions(+), 11 deletions(-)

diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index bf00815c14..0129f67eaa 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -99,5 +99,10 @@
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
   rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '8916', oid_symbol => 'ROLE_PG_SIGNAL_AUTOVACUUM_WORKER',
+  rolname => 'pg_signal_autovacuum_worker', rolsuper => 'f', rolinherit => 't',
+  rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+  rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+  rolpassword => '_null_', rolvaliduntil => '_null_' },
 
 ]
diff --git a/src/backend/storage/ipc/signalfuncs.c b/src/backend/storage/ipc/signalfuncs.c
index 88e9bf8125..780a1c682a 100644
--- a/src/backend/storage/ipc/signalfuncs.c
+++ b/src/backend/storage/ipc/signalfuncs.c
@@ -34,8 +34,9 @@
  * role as the backend being signaled. For "dangerous" signals, an explicit
  * check for superuser needs to be done prior to calling this function.
  *
- * Returns 0 on success, 1 on general failure, 2 on normal permission error
- * and 3 if the caller needs to be a superuser.
+ * Returns 0 on success, 1 on general failure, 2 on normal permission error,
+ * 3 if the caller needs to be a superuser, and 4 if the caller needs to have
+ * privileges of pg_signal_autovacuum_worker.
  *
  * In the event of a general failure (return code 1), a warning message will
  * be emitted. For permission errors, doing that is the responsibility of
@@ -45,6 +46,7 @@
 #define SIGNAL_BACKEND_ERROR 1
 #define SIGNAL_BACKEND_NOPERMISSION 2
 #define SIGNAL_BACKEND_NOSUPERUSER 3
+#define SIGNAL_BACKEND_NOAUTOVAC 4
 static int
 pg_signal_backend(int pid, int sig)
 {
@@ -77,15 +79,27 @@ pg_signal_backend(int pid, int sig)
 	/*
 	 * Only allow superusers to signal superuser-owned backends.  Any process
 	 * not advertising a role might have the importance of a superuser-owned
-	 * backend, so treat it that way.
+	 * backend, so treat it that way.  As an exception, we allow roles with
+	 * privileges of pg_signal_autovacuum_worker to signal autovacuum workers
+	 * (which do not advertise a role).
+	 *
+	 * Otherwise, users can signal backends for roles they have privileges of.
 	 */
-	if ((!OidIsValid(proc->roleId) || superuser_arg(proc->roleId)) &&
-		!superuser())
-		return SIGNAL_BACKEND_NOSUPERUSER;
+	if (!OidIsValid(proc->roleId) || superuser_arg(proc->roleId))
+	{
+		ProcNumber	procNumber = GetNumberFromPGProc(proc);
+		PgBackendStatus *procStatus = pgstat_get_beentry_by_proc_number(procNumber);
 
-	/* Users can signal backends they have role membership in. */
-	if (!has_privs_of_role(GetUserId(), proc->roleId) &&
-		!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
+		if (procStatus && procStatus->st_backendType == B_AUTOVAC_WORKER)
+		{
+			if (!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_AUTOVACUUM_WORKER))
+				return SIGNAL_BACKEND_NOAUTOVAC;
+		}
+		else if (!superuser())
+			return SIGNAL_BACKEND_NOSUPERUSER;
+	}
+	else if (!has_privs_of_role(GetUserId(), proc->roleId) &&
+			 !has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
 		return SIGNAL_BACKEND_NOPERMISSION;
 
 	/*
@@ -130,6 +144,13 @@ pg_cancel_backend(PG_FUNCTION_ARGS)
 				 errdetail("Only roles with the %s attribute may cancel queries of roles with the %s attribute.",
 						   "SUPERUSER", "SUPERUSER")));
 
+	if (r == SIGNAL_BACKEND_NOAUTOVAC)
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to cancel query"),
+				 errdetail("Only roles with privileges of the \"%s\" role may cancel queries of autovacuum workers.",
+						   "pg_signal_autovacuum_worker")));
+
 	if (r == SIGNAL_BACKEND_NOPERMISSION)
 		ereport(ERROR,
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
@@ -236,6 +257,13 @@ pg_terminate_backend(PG_FUNCTION_ARGS)
 				 errdetail("Only roles with the %s attribute may terminate processes of roles with the %s attribute.",
 						   "SUPERUSER", "SUPERUSER")));
 
+	if (r == SIGNAL_BACKEND_NOAUTOVAC)
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to terminate process"),
+				 errdetail("Only roles with privileges of the \"%s\" role may terminate autovacuum worker processes.",
+						   "pg_signal_autovacuum_worker")));
+
 	if (r == SIGNAL_BACKEND_NOPERMISSION)
 		ereport(ERROR,
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2609269610..f73ba439c0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28040,7 +28040,9 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
        <para>
         Cancels the current query of the session whose backend process has the
         specified process ID.  This is also allowed if the
-        calling role is a member of the role whose backend is being canceled or
+        calling role has privileges of the role whose backend is being canceled,
+        the backend process is an autovacuum worker and the calling role has
+        privileges of <literal>pg_signal_autovacuum_worker</literal>, or
         the calling role has privileges of <literal>pg_signal_backend</literal>,
         however only superusers can cancel superuser backends.
        </para></entry>
@@ -28114,7 +28116,9 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
        <para>
         Terminates the session whose backend process has the
         specified process ID.  This is also allowed if the calling role
-        is a member of the role whose backend is being terminated or the
+        has privileges of the role whose backend is being terminated,
+        the backend process is an autovacuum worker and the calling role has
+        privileges of <literal>pg_signal_autovacuum_worker</literal>, or the
         calling role has privileges of <literal>pg_signal_backend</literal>,
         however only superusers can terminate superuser backends.
        </para>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 07a16247d7..340cefff70 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -661,6 +661,11 @@ DROP ROLE doomed_role;
        <entry>pg_signal_backend</entry>
        <entry>Signal another backend to cancel a query or terminate its session.</entry>
       </row>
+      <row>
+       <entry>pg_signal_autovacuum_worker</entry>
+       <entry>Signal an autovacuum worker to cancel the current table's vacuum
+       or terminate its session.</entry>
+      </row>
       <row>
        <entry>pg_read_server_files</entry>
        <entry>Allow reading files from any location the database can access on the server with COPY and
-- 
2.45.2

From 93b7d69585aa4a408749bee6239006d6494df1bb Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Fri, 21 Jun 2024 14:29:23 +0900
Subject: [PATCH v6 2/2] Add tap test for pg_signal_autovacuum role

---
 src/backend/postmaster/autovacuum.c           |   7 ++
 .../test_misc/t/006_signal_autovacuum.pl      | 100 ++++++++++++++++++
 2 files changed, 107 insertions(+)
 create mode 100644 src/test/modules/test_misc/t/006_signal_autovacuum.pl

diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 9a925a10cd..0d4e2c5140 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -100,6 +100,7 @@
 #include "utils/fmgroids.h"
 #include "utils/fmgrprotos.h"
 #include "utils/guc_hooks.h"
+#include "utils/injection_point.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/ps_status.h"
@@ -1902,6 +1903,12 @@ do_autovacuum(void)
 	/* Start a transaction so our commands have one to play into. */
 	StartTransactionCommand();
 
+	/*
+	 * This injection point is put in a transaction block to work with a wait
+	 * that uses a condition variable.
+	 */
+	INJECTION_POINT("autovacuum-worker-start");
+
 	/*
 	 * Compute the multixact age for which freezing is urgent.  This is
 	 * normally autovacuum_multixact_freeze_max_age, but may be less if we are
diff --git a/src/test/modules/test_misc/t/006_signal_autovacuum.pl b/src/test/modules/test_misc/t/006_signal_autovacuum.pl
new file mode 100644
index 0000000000..98cbea3744
--- /dev/null
+++ b/src/test/modules/test_misc/t/006_signal_autovacuum.pl
@@ -0,0 +1,100 @@
+# Copyright (c) 2024, PostgreSQL Global Development Group
+
+# Test signaling autovacuum worker backend by non-superuser role.
+#
+# Only non-superuser roles granted pg_signal_autovacuum_worker are allowed
+# to signal autovacuum workers.  This test uses an injection point located
+# at the beginning of the autovacuum worker startup.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+if ($ENV{enable_injection_points} ne 'yes')
+{
+	plan skip_all => 'Injection points not supported by this build';
+}
+
+# Initialize postgres
+my $psql_err = '';
+my $psql_out = '';
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init;
+
+# This ensures a quick worker spawn.
+$node->append_conf(
+	'postgresql.conf', 'autovacuum_naptime = 1');
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION injection_points;');
+
+$node->safe_psql(
+	'postgres', qq(
+    CREATE ROLE regular_role;
+    CREATE ROLE signal_autovacuum_worker_role;
+    GRANT pg_signal_autovacuum_worker TO signal_autovacuum_worker_role;
+));
+
+# From this point, autovacuum worker will wait at startup.
+$node->safe_psql('postgres',
+	"SELECT injection_points_attach('autovacuum-worker-start', 'wait');");
+
+# Create some content and set an aggressive autovacuum.
+$node->safe_psql(
+	'postgres', qq(
+    CREATE TABLE tab_int(i int);
+    ALTER TABLE tab_int SET (autovacuum_vacuum_cost_limit = 1);
+    ALTER TABLE tab_int SET (autovacuum_vacuum_cost_delay = 100);
+));
+
+$node->safe_psql(
+	'postgres', qq(
+    INSERT INTO tab_int VALUES(1);
+));
+
+# Wait until an autovacuum worker starts.
+$node->wait_for_event('autovacuum worker', 'autovacuum-worker-start');
+
+my $av_pid = $node->safe_psql(
+	'postgres', qq(
+    SELECT pid FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
+));
+
+# Regular role cannot terminate autovacuum worker.
+my $terminate_with_no_pg_signal_av = $node->psql(
+	'postgres', qq(
+    SET ROLE regular_role;
+    SELECT pg_terminate_backend($av_pid);
+),
+	stdout => \$psql_out,
+	stderr => \$psql_err);
+
+like(
+	$psql_err,
+	qr/ERROR:  permission denied to terminate process\nDETAIL:  Only roles with privileges of the "pg_signal_autovacuum_worker" role may terminate autovacuum worker processes./,
+	"autovacuum worker not signaled with regular role");
+
+my $offset = -s $node->logfile;
+
+# Role with pg_signal_autovacuum can terminate autovacuum worker.
+my $terminate_with_pg_signal_av = $node->psql(
+	'postgres', qq(
+    SET ROLE signal_autovacuum_worker_role;
+    SELECT pg_terminate_backend($av_pid);
+),
+	stdout => \$psql_out,
+	stderr => \$psql_err);
+
+# Check that the primary server logs a FATAL indicating that autovacuum
+# is terminated.
+ok( $node->log_contains(
+		qr/FATAL:  terminating autovacuum process due to administrator command/,
+		$offset),
+	"autovacuum worker signaled with pg_signal_autovacuum_worker granted"
+);
+
+# Release injection point.
+$node->safe_psql('postgres',
+	"SELECT injection_points_detach('autovacuum-worker-start');");
+
+done_testing();
-- 
2.45.2

From e07f8fbc8a7e5c024deda800b97456d5446e622f Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Fri, 21 Jun 2024 14:29:23 +0900
Subject: [PATCH v7] Add tap test for pg_signal_autovacuum role

---
 src/backend/postmaster/autovacuum.c           |   7 ++
 .../test_misc/t/006_signal_autovacuum.pl      | 100 ++++++++++++++++++
 2 files changed, 107 insertions(+)
 create mode 100644 src/test/modules/test_misc/t/006_signal_autovacuum.pl

diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 928754b51c..4e4a0ccbef 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -100,6 +100,7 @@
 #include "utils/fmgroids.h"
 #include "utils/fmgrprotos.h"
 #include "utils/guc_hooks.h"
+#include "utils/injection_point.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/ps_status.h"
@@ -1902,6 +1903,12 @@ do_autovacuum(void)
 	/* Start a transaction so our commands have one to play into. */
 	StartTransactionCommand();
 
+	/*
+	 * This injection point is put in a transaction block to work with a wait
+	 * that uses a condition variable.
+	 */
+	INJECTION_POINT("autovacuum-worker-start");
+
 	/*
 	 * Compute the multixact age for which freezing is urgent.  This is
 	 * normally autovacuum_multixact_freeze_max_age, but may be less if we are
diff --git a/src/test/modules/test_misc/t/006_signal_autovacuum.pl b/src/test/modules/test_misc/t/006_signal_autovacuum.pl
new file mode 100644
index 0000000000..250f598401
--- /dev/null
+++ b/src/test/modules/test_misc/t/006_signal_autovacuum.pl
@@ -0,0 +1,100 @@
+# Copyright (c) 2024, PostgreSQL Global Development Group
+
+# Test signaling autovacuum worker backend by non-superuser role.
+#
+# Only non-superuser roles granted pg_signal_autovacuum_worker are allowed
+# to signal autovacuum workers.  This test uses an injection point located
+# at the beginning of the autovacuum worker startup.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+if ($ENV{enable_injection_points} ne 'yes')
+{
+	plan skip_all => 'Injection points not supported by this build';
+}
+
+# Initialize postgres
+my $psql_err = '';
+my $psql_out = '';
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init;
+
+# This ensures a quick worker spawn.
+$node->append_conf(
+	'postgresql.conf', 'autovacuum_naptime = 1');
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION injection_points;');
+
+$node->safe_psql(
+	'postgres', qq(
+    CREATE ROLE regular_role;
+    CREATE ROLE signal_autovacuum_worker_role;
+    GRANT pg_signal_autovacuum_worker TO signal_autovacuum_worker_role;
+));
+
+# From this point, autovacuum worker will wait at startup.
+$node->safe_psql('postgres',
+	"SELECT injection_points_attach('autovacuum-worker-start', 'wait');");
+
+# Create some content and set an aggressive autovacuum.
+$node->safe_psql(
+	'postgres', qq(
+    CREATE TABLE tab_int(i int);
+    ALTER TABLE tab_int SET (autovacuum_vacuum_cost_limit = 1);
+    ALTER TABLE tab_int SET (autovacuum_vacuum_cost_delay = 100);
+));
+
+$node->safe_psql(
+	'postgres', qq(
+    INSERT INTO tab_int VALUES(1);
+));
+
+# Wait until an autovacuum worker starts.
+$node->wait_for_event('autovacuum worker', 'autovacuum-worker-start');
+
+my $av_pid = $node->safe_psql(
+	'postgres', qq(
+    SELECT pid FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
+));
+
+# Regular role cannot terminate autovacuum worker.
+my $terminate_with_no_pg_signal_av = $node->psql(
+	'postgres', qq(
+    SET ROLE regular_role;
+    SELECT pg_terminate_backend($av_pid);
+),
+	stdout => \$psql_out,
+	stderr => \$psql_err);
+
+like(
+	$psql_err,
+	qr/ERROR:  permission denied to terminate process\nDETAIL:  Only roles with privileges of the "pg_signal_autovacuum_worker" role may terminate autovacuum worker./,
+	"autovacuum worker not signaled with regular role");
+
+my $offset = -s $node->logfile;
+
+# Role with pg_signal_autovacuum can terminate autovacuum worker.
+my $terminate_with_pg_signal_av = $node->psql(
+	'postgres', qq(
+    SET ROLE signal_autovacuum_worker_role;
+    SELECT pg_terminate_backend($av_pid);
+),
+	stdout => \$psql_out,
+	stderr => \$psql_err);
+
+# Check that the primary server logs a FATAL indicating that autovacuum
+# is terminated.
+ok( $node->log_contains(
+		qr/FATAL:  terminating autovacuum process due to administrator command/,
+		$offset),
+	"autovacuum worker signaled with pg_signal_autovacuum_worker granted"
+);
+
+# Release injection point.
+$node->safe_psql('postgres',
+	"SELECT injection_points_detach('autovacuum-worker-start');");
+
+done_testing();
-- 
2.25.1

From 4f79a7741a0ff38ab063c02bc81a2ace60848d92 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Wed, 10 Jul 2024 14:13:00 +0900
Subject: [PATCH v7] Add tap test for pg_signal_autovacuum role

---
 src/backend/postmaster/autovacuum.c           |   7 ++
 .../test_misc/t/006_signal_autovacuum.pl      | 105 ++++++++++++++++++
 2 files changed, 112 insertions(+)
 create mode 100644 src/test/modules/test_misc/t/006_signal_autovacuum.pl

diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 928754b51c..4e4a0ccbef 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -100,6 +100,7 @@
 #include "utils/fmgroids.h"
 #include "utils/fmgrprotos.h"
 #include "utils/guc_hooks.h"
+#include "utils/injection_point.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/ps_status.h"
@@ -1902,6 +1903,12 @@ do_autovacuum(void)
 	/* Start a transaction so our commands have one to play into. */
 	StartTransactionCommand();
 
+	/*
+	 * This injection point is put in a transaction block to work with a wait
+	 * that uses a condition variable.
+	 */
+	INJECTION_POINT("autovacuum-worker-start");
+
 	/*
 	 * Compute the multixact age for which freezing is urgent.  This is
 	 * normally autovacuum_multixact_freeze_max_age, but may be less if we are
diff --git a/src/test/modules/test_misc/t/006_signal_autovacuum.pl b/src/test/modules/test_misc/t/006_signal_autovacuum.pl
new file mode 100644
index 0000000000..136dc8c52e
--- /dev/null
+++ b/src/test/modules/test_misc/t/006_signal_autovacuum.pl
@@ -0,0 +1,105 @@
+# Copyright (c) 2024, PostgreSQL Global Development Group
+
+# Test signaling autovacuum worker backend by non-superuser role.
+#
+# Only non-superuser roles granted pg_signal_autovacuum_worker are allowed
+# to signal autovacuum workers.  This test uses an injection point located
+# at the beginning of the autovacuum worker startup.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+if ($ENV{enable_injection_points} ne 'yes')
+{
+	plan skip_all => 'Injection points not supported by this build';
+}
+
+# Initialize postgres
+my $psql_err = '';
+my $psql_out = '';
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init;
+
+# This ensures a quick worker spawn.
+$node->append_conf(
+	'postgresql.conf', 'autovacuum_naptime = 1');
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION injection_points;');
+
+$node->safe_psql(
+	'postgres', qq(
+    CREATE ROLE regular_role;
+    CREATE ROLE signal_autovacuum_worker_role;
+    GRANT pg_signal_autovacuum_worker TO signal_autovacuum_worker_role;
+));
+
+# From this point, autovacuum worker will wait at startup.
+$node->safe_psql('postgres',
+	"SELECT injection_points_attach('autovacuum-worker-start', 'wait');");
+
+# Create some content and set an aggressive autovacuum.
+$node->safe_psql(
+	'postgres', qq(
+    CREATE TABLE tab_int(i int);
+    ALTER TABLE tab_int SET (autovacuum_vacuum_cost_limit = 1);
+    ALTER TABLE tab_int SET (autovacuum_vacuum_cost_delay = 100);
+));
+
+$node->safe_psql(
+	'postgres', qq(
+    INSERT INTO tab_int VALUES(1);
+));
+
+# Wait until an autovacuum worker starts.
+$node->wait_for_event('autovacuum worker', 'autovacuum-worker-start');
+
+my $av_pid = $node->safe_psql(
+	'postgres', qq(
+    SELECT pid FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
+));
+
+# Regular role cannot terminate autovacuum worker.
+my $terminate_with_no_pg_signal_av = $node->psql(
+	'postgres', qq(
+    SET ROLE regular_role;
+    SELECT pg_terminate_backend($av_pid);
+),
+	stdout => \$psql_out,
+	stderr => \$psql_err);
+
+like(
+	$psql_err,
+	qr/ERROR:  permission denied to terminate process\nDETAIL:  Only roles with privileges of the "pg_signal_autovacuum_worker" role may terminate autovacuum workers./,
+	"autovacuum worker not signaled with regular role");
+
+my $offset = -s $node->logfile;
+
+# Role with pg_signal_autovacuum can terminate autovacuum worker.
+my $terminate_with_pg_signal_av = $node->psql(
+	'postgres', qq(
+    SET ROLE signal_autovacuum_worker_role;
+    SELECT pg_terminate_backend($av_pid);
+),
+	stdout => \$psql_out,
+	stderr => \$psql_err);
+
+# Wait for the autovacuum worker to exit before scanning the logs.
+$node->poll_query_until('postgres',
+	"SELECT count(*) = 0 FROM pg_stat_activity "
+	. "WHERE pid = $av_pid AND backend_type = 'autovacuum worker';");
+
+# Check that the primary server logs a FATAL indicating that autovacuum
+# is terminated.
+ok( $node->log_contains(
+		qr/FATAL:  terminating autovacuum process due to administrator command/,
+		$offset),
+	"autovacuum worker signaled with pg_signal_autovacuum_worker granted"
+);
+
+# Release injection point.
+$node->safe_psql('postgres',
+	"SELECT injection_points_detach('autovacuum-worker-start');");
+
+done_testing();
-- 
2.45.2

From 8a827a8a59da4d3c2f2115151e1241cf663c11a1 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Fri, 12 Jul 2024 14:19:51 +0900
Subject: [PATCH v3] Add tap test for pg_signal_autovacuum role

---
 src/backend/postmaster/autovacuum.c           |  7 ++
 .../test_misc/t/006_signal_autovacuum.pl      | 94 +++++++++++++++++++
 2 files changed, 101 insertions(+)
 create mode 100644 src/test/modules/test_misc/t/006_signal_autovacuum.pl

diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 928754b51c..4e4a0ccbef 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -100,6 +100,7 @@
 #include "utils/fmgroids.h"
 #include "utils/fmgrprotos.h"
 #include "utils/guc_hooks.h"
+#include "utils/injection_point.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/ps_status.h"
@@ -1902,6 +1903,12 @@ do_autovacuum(void)
 	/* Start a transaction so our commands have one to play into. */
 	StartTransactionCommand();
 
+	/*
+	 * This injection point is put in a transaction block to work with a wait
+	 * that uses a condition variable.
+	 */
+	INJECTION_POINT("autovacuum-worker-start");
+
 	/*
 	 * Compute the multixact age for which freezing is urgent.  This is
 	 * normally autovacuum_multixact_freeze_max_age, but may be less if we are
diff --git a/src/test/modules/test_misc/t/006_signal_autovacuum.pl b/src/test/modules/test_misc/t/006_signal_autovacuum.pl
new file mode 100644
index 0000000000..ded42b1be9
--- /dev/null
+++ b/src/test/modules/test_misc/t/006_signal_autovacuum.pl
@@ -0,0 +1,94 @@
+# Copyright (c) 2024, PostgreSQL Global Development Group
+
+# Test signaling autovacuum worker with pg_signal_autovacuum_worker.
+#
+# Only roles with privileges of pg_signal_autovacuum_worker are allowed to
+# signal autovacuum workers.  This test uses an injection point located
+# at the beginning of the autovacuum worker startup.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+if ($ENV{enable_injection_points} ne 'yes')
+{
+	plan skip_all => 'Injection points not supported by this build';
+}
+
+# Initialize postgres
+my $psql_err = '';
+my $psql_out = '';
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init;
+
+# This ensures a quick worker spawn.
+$node->append_conf(
+	'postgresql.conf', 'autovacuum_naptime = 1');
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION injection_points;');
+
+$node->safe_psql(
+	'postgres', qq(
+    CREATE ROLE regress_regular_role;
+    CREATE ROLE regress_worker_role;
+    GRANT pg_signal_autovacuum_worker TO regress_worker_role;
+));
+
+# From this point, autovacuum worker will wait at startup.
+$node->safe_psql('postgres',
+	"SELECT injection_points_attach('autovacuum-worker-start', 'wait');");
+
+$node->reload();
+
+# Wait until an autovacuum worker starts.
+$node->wait_for_event('autovacuum worker', 'autovacuum-worker-start');
+
+my $av_pid = $node->safe_psql(
+	'postgres', qq(
+    SELECT pid FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
+));
+
+# Regular role cannot terminate autovacuum worker.
+my $terminate_with_no_pg_signal_av = $node->psql(
+	'postgres', qq(
+    SET ROLE regress_regular_role;
+    SELECT pg_terminate_backend($av_pid);
+),
+	stdout => \$psql_out,
+	stderr => \$psql_err);
+
+like(
+	$psql_err,
+	qr/ERROR:  permission denied to terminate process\nDETAIL:  Only roles with privileges of the "pg_signal_autovacuum_worker" role may terminate autovacuum workers./,
+	"autovacuum worker not signaled with regular role");
+
+my $offset = -s $node->logfile;
+
+# Role with pg_signal_autovacuum can terminate autovacuum worker.
+my $terminate_with_pg_signal_av = $node->psql(
+	'postgres', qq(
+    SET ROLE regress_worker_role;
+    SELECT pg_terminate_backend($av_pid);
+),
+	stdout => \$psql_out,
+	stderr => \$psql_err);
+
+# Wait for the autovacuum worker to exit before scanning the logs.
+$node->poll_query_until('postgres',
+	"SELECT count(*) = 0 FROM pg_stat_activity "
+	. "WHERE pid = $av_pid AND backend_type = 'autovacuum worker';");
+
+# Check that the primary server logs a FATAL indicating that autovacuum
+# is terminated.
+ok( $node->log_contains(
+		qr/FATAL:  terminating autovacuum process due to administrator command/,
+		$offset),
+	"autovacuum worker signaled with pg_signal_autovacuum_worker granted"
+);
+
+# Release injection point.
+$node->safe_psql('postgres',
+	"SELECT injection_points_detach('autovacuum-worker-start');");
+
+done_testing();
-- 
2.45.2

From a5a3eb803b255acf4757217b9715e76f0a3c192c Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 22 Nov 2024 13:19:44 -0600
Subject: [PATCH v1 1/1] avoid calling pgstat_read_current_status() in
 pg_signal_backend()

---
 src/backend/storage/ipc/signalfuncs.c       |  5 +--
 src/backend/utils/activity/backend_status.c | 37 +++++++++++++++++++++
 src/include/utils/backend_status.h          |  1 +
 3 files changed, 39 insertions(+), 4 deletions(-)

diff --git a/src/backend/storage/ipc/signalfuncs.c b/src/backend/storage/ipc/signalfuncs.c
index aa729a36e3..8e165e9729 100644
--- a/src/backend/storage/ipc/signalfuncs.c
+++ b/src/backend/storage/ipc/signalfuncs.c
@@ -87,10 +87,7 @@ pg_signal_backend(int pid, int sig)
 	 */
 	if (!OidIsValid(proc->roleId) || superuser_arg(proc->roleId))
 	{
-		ProcNumber	procNumber = GetNumberFromPGProc(proc);
-		PgBackendStatus *procStatus = pgstat_get_beentry_by_proc_number(procNumber);
-
-		if (procStatus && procStatus->st_backendType == B_AUTOVAC_WORKER)
+		if (pgstat_get_backend_type(pid) == B_AUTOVAC_WORKER)
 		{
 			if (!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_AUTOVACUUM_WORKER))
 				return SIGNAL_BACKEND_NOAUTOVAC;
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index bdb3a296ca..4b39e30ca5 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -941,6 +941,43 @@ pgstat_get_backend_current_activity(int pid, bool checkUser)
 	return "<backend information not available>";
 }
 
+BackendType
+pgstat_get_backend_type(int pid)
+{
+	PgBackendStatus *beentry = BackendStatusArray;
+
+	for (int i = 1; i <= MaxBackends; i++)
+	{
+		volatile PgBackendStatus *vbeentry = beentry;
+		bool		found;
+
+		for (;;)
+		{
+			int			before_changecount;
+			int			after_changecount;
+
+			pgstat_begin_read_activity(vbeentry, before_changecount);
+
+			found = (vbeentry->st_procpid == pid);
+
+			pgstat_end_read_activity(vbeentry, after_changecount);
+
+			if (pgstat_read_activity_complete(before_changecount,
+											  after_changecount))
+				break;
+
+			CHECK_FOR_INTERRUPTS();
+		}
+
+		if (found)
+			return beentry->st_backendType;
+
+		beentry++;
+	}
+
+	return B_INVALID;
+}
+
 /* ----------
  * pgstat_get_crashed_backend_activity() -
  *
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 97874300c3..dfa4aa6127 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -320,6 +320,7 @@ extern void pgstat_report_tempfile(size_t filesize);
 extern void pgstat_report_appname(const char *appname);
 extern void pgstat_report_xact_timestamp(TimestampTz tstamp);
 extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
+extern BackendType pgstat_get_backend_type(int pid);
 extern const char *pgstat_get_crashed_backend_activity(int pid, char *buffer,
 													   int buflen);
 extern uint64 pgstat_get_my_query_id(void);
-- 
2.39.5 (Apple Git-154)

From 50220e60b8f1e15a63bdfec22cdaef2ebce549d6 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 22 Nov 2024 20:39:35 -0600
Subject: [PATCH v2 1/1] avoid calling pgstat_read_current_status() in
 pg_signal_backend()

---
 src/backend/storage/ipc/signalfuncs.c       |  4 ++--
 src/backend/utils/activity/backend_status.c | 11 +++++++++++
 src/include/utils/backend_status.h          |  1 +
 3 files changed, 14 insertions(+), 2 deletions(-)

diff --git a/src/backend/storage/ipc/signalfuncs.c b/src/backend/storage/ipc/signalfuncs.c
index aa729a36e3..d835327600 100644
--- a/src/backend/storage/ipc/signalfuncs.c
+++ b/src/backend/storage/ipc/signalfuncs.c
@@ -88,9 +88,9 @@ pg_signal_backend(int pid, int sig)
 	if (!OidIsValid(proc->roleId) || superuser_arg(proc->roleId))
 	{
 		ProcNumber	procNumber = GetNumberFromPGProc(proc);
-		PgBackendStatus *procStatus = pgstat_get_beentry_by_proc_number(procNumber);
+		BackendType backendType = pgstat_get_backend_type_by_proc_number(procNumber);
 
-		if (procStatus && procStatus->st_backendType == B_AUTOVAC_WORKER)
+		if (backendType == B_AUTOVAC_WORKER)
 		{
 			if (!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_AUTOVACUUM_WORKER))
 				return SIGNAL_BACKEND_NOAUTOVAC;
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index bdb3a296ca..19796909e7 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -1134,6 +1134,17 @@ pgstat_get_local_beentry_by_index(int idx)
 }
 
 
+BackendType
+pgstat_get_backend_type_by_proc_number(ProcNumber procNumber)
+{
+	volatile PgBackendStatus *backendStatus;
+
+	backendStatus = &BackendStatusArray[procNumber];
+
+	return backendStatus->st_backendType;
+}
+
+
 /* ----------
  * pgstat_fetch_stat_numbackends() -
  *
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 97874300c3..9640820edd 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -334,6 +334,7 @@ extern int	pgstat_fetch_stat_numbackends(void);
 extern PgBackendStatus *pgstat_get_beentry_by_proc_number(ProcNumber procNumber);
 extern LocalPgBackendStatus *pgstat_get_local_beentry_by_proc_number(ProcNumber procNumber);
 extern LocalPgBackendStatus *pgstat_get_local_beentry_by_index(int idx);
+extern BackendType pgstat_get_backend_type_by_proc_number(ProcNumber procNumber);
 extern char *pgstat_clip_activity(const char *raw_activity);
 
 
-- 
2.39.5 (Apple Git-154)

From 42c35664c8a842c4d6df9cd68a58dc6cf1e35454 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 26 Nov 2024 15:04:41 -0600
Subject: [PATCH v3 1/1] Look up backend type in pg_signal_backend() more
 cheaply.

Commit ccd38024bc, which introduced the pg_signal_autovacuum_worker
role, added a call to pgstat_get_beentry_by_proc_number() for the
purpose of determining whether the targeted backend process is an
autovacuum worker.  This function calls
pgstat_read_current_status(), which can be fairly expensive and may
return cached, out-of-date information.  Since we just need to look
up the target backend's BackendType, and we already know its
ProcNumber, we can instead inspect the BackendStatusArray directly,
which is much less expensive and probably more up-to-date.  There
are some caveats with this approach (which are documented in the
code), but it's still substantially better than before.

Reported-by: Andres Freund
Reviewed-by: Andres Freund
Discussion: https://postgr.es/m/ujenaa2uabzfkwxwmfifawzdozh3ljr7geozlhftsuosgm7n7q%40g3utqqyyosb6
---
 src/backend/storage/ipc/signalfuncs.c       |  4 ++--
 src/backend/utils/activity/backend_status.c | 25 +++++++++++++++++++++
 src/include/utils/backend_status.h          |  1 +
 3 files changed, 28 insertions(+), 2 deletions(-)

diff --git a/src/backend/storage/ipc/signalfuncs.c b/src/backend/storage/ipc/signalfuncs.c
index aa729a36e3..d835327600 100644
--- a/src/backend/storage/ipc/signalfuncs.c
+++ b/src/backend/storage/ipc/signalfuncs.c
@@ -88,9 +88,9 @@ pg_signal_backend(int pid, int sig)
 	if (!OidIsValid(proc->roleId) || superuser_arg(proc->roleId))
 	{
 		ProcNumber	procNumber = GetNumberFromPGProc(proc);
-		PgBackendStatus *procStatus = pgstat_get_beentry_by_proc_number(procNumber);
+		BackendType backendType = pgstat_get_backend_type_by_proc_number(procNumber);
 
-		if (procStatus && procStatus->st_backendType == B_AUTOVAC_WORKER)
+		if (backendType == B_AUTOVAC_WORKER)
 		{
 			if (!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_AUTOVACUUM_WORKER))
 				return SIGNAL_BACKEND_NOAUTOVAC;
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index bdb3a296ca..777f7410a7 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -1036,6 +1036,31 @@ pgstat_get_my_query_id(void)
 	return MyBEEntry->st_query_id;
 }
 
+/* ----------
+ * pgstat_get_backend_type_by_proc_number() -
+ *
+ *	Return the type of the backend with the specified ProcNumber.  This looks
+ *	directly at the BackendStatusArray, so the return value may be out of date.
+ *	The only current use of this function is in pg_signal_backend(), which is
+ *	inherently racy, so we don't worry too much about this.
+ *
+ *	It is the caller's responsibility to use this wisely; at minimum, callers
+ *	should ensure that procNumber is valid and perform the required permissions
+ *	checking.
+ * ----------
+ */
+BackendType
+pgstat_get_backend_type_by_proc_number(ProcNumber procNumber)
+{
+	volatile PgBackendStatus *status = &BackendStatusArray[procNumber];
+
+	/*
+	 * We bypass the changecount mechanism since fetching and storing an int
+	 * is almost certainly atomic.
+	 */
+	return status->st_backendType;
+}
+
 /* ----------
  * cmp_lbestatus
  *
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 97874300c3..4e8b39a66d 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -323,6 +323,7 @@ extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
 extern const char *pgstat_get_crashed_backend_activity(int pid, char *buffer,
 													   int buflen);
 extern uint64 pgstat_get_my_query_id(void);
+extern BackendType pgstat_get_backend_type_by_proc_number(ProcNumber procNumber);
 
 
 /* ----------
-- 
2.39.5 (Apple Git-154)