Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit ccd3802

Browse filesBrowse files
nathan-bossartnathan-bossart
committed
Introduce pg_signal_autovacuum_worker.
Since commit 3a9b18b, 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. Bumps catversion. Author: Kirill Reshke Reviewed-by: Anthony Leung, Michael Paquier, Andrey Borodin Discussion: https://postgr.es/m/CALdSSPhC4GGmbnugHfB9G0%3DfAxjCSug_-rmL9oUh0LTxsyBfsg%40mail.gmail.com
1 parent 629520b commit ccd3802
Copy full SHA for ccd3802

File tree

Expand file treeCollapse file tree

5 files changed

+56
-10
lines changed
Filter options
Expand file treeCollapse file tree

5 files changed

+56
-10
lines changed

‎doc/src/sgml/func.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/func.sgml
+8Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28163,6 +28163,10 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
2816328163
calling role is a member of the role whose backend is being canceled or
2816428164
the calling role has privileges of <literal>pg_signal_backend</literal>,
2816528165
however only superusers can cancel superuser backends.
28166+
As an exception, roles with privileges of
28167+
<literal>pg_signal_autovacuum_worker</literal> are permitted to
28168+
cancel autovacuum worker processes, which are otherwise considered
28169+
superuser backends.
2816628170
</para></entry>
2816728171
</row>
2816828172

@@ -28237,6 +28241,10 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
2823728241
is a member of the role whose backend is being terminated or the
2823828242
calling role has privileges of <literal>pg_signal_backend</literal>,
2823928243
however only superusers can terminate superuser backends.
28244+
As an exception, roles with privileges of
28245+
<literal>pg_signal_autovacuum_worker</literal> are permitted to
28246+
terminate autovacuum worker processes, which are otherwise considered
28247+
superuser backends.
2824028248
</para>
2824128249
<para>
2824228250
If <parameter>timeout</parameter> is not specified or zero, this

‎doc/src/sgml/user-manag.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/user-manag.sgml
+5Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -661,6 +661,11 @@ DROP ROLE doomed_role;
661661
<entry>pg_signal_backend</entry>
662662
<entry>Signal another backend to cancel a query or terminate its session.</entry>
663663
</row>
664+
<row>
665+
<entry>pg_signal_autovacuum_worker</entry>
666+
<entry>Signal an autovacuum worker to cancel the current table's vacuum
667+
or terminate its session.</entry>
668+
</row>
664669
<row>
665670
<entry>pg_read_server_files</entry>
666671
<entry>Allow reading files from any location the database can access on the server with COPY and

‎src/backend/storage/ipc/signalfuncs.c

Copy file name to clipboardExpand all lines: src/backend/storage/ipc/signalfuncs.c
+37-9Lines changed: 37 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -34,8 +34,9 @@
3434
* role as the backend being signaled. For "dangerous" signals, an explicit
3535
* check for superuser needs to be done prior to calling this function.
3636
*
37-
* Returns 0 on success, 1 on general failure, 2 on normal permission error
38-
* and 3 if the caller needs to be a superuser.
37+
* Returns 0 on success, 1 on general failure, 2 on normal permission error,
38+
* 3 if the caller needs to be a superuser, and 4 if the caller needs to have
39+
* privileges of pg_signal_autovacuum_worker.
3940
*
4041
* In the event of a general failure (return code 1), a warning message will
4142
* be emitted. For permission errors, doing that is the responsibility of
@@ -45,6 +46,7 @@
4546
#define SIGNAL_BACKEND_ERROR 1
4647
#define SIGNAL_BACKEND_NOPERMISSION 2
4748
#define SIGNAL_BACKEND_NOSUPERUSER 3
49+
#define SIGNAL_BACKEND_NOAUTOVAC 4
4850
static int
4951
pg_signal_backend(int pid, int sig)
5052
{
@@ -77,15 +79,27 @@ pg_signal_backend(int pid, int sig)
7779
/*
7880
* Only allow superusers to signal superuser-owned backends. Any process
7981
* not advertising a role might have the importance of a superuser-owned
80-
* backend, so treat it that way.
82+
* backend, so treat it that way. As an exception, we allow roles with
83+
* privileges of pg_signal_autovacuum_worker to signal autovacuum workers
84+
* (which do not advertise a role).
85+
*
86+
* Otherwise, users can signal backends for roles they have privileges of.
8187
*/
82-
if ((!OidIsValid(proc->roleId) || superuser_arg(proc->roleId)) &&
83-
!superuser())
84-
return SIGNAL_BACKEND_NOSUPERUSER;
88+
if (!OidIsValid(proc->roleId) || superuser_arg(proc->roleId))
89+
{
90+
ProcNumber procNumber = GetNumberFromPGProc(proc);
91+
PgBackendStatus *procStatus = pgstat_get_beentry_by_proc_number(procNumber);
8592

86-
/* Users can signal backends they have role membership in. */
87-
if (!has_privs_of_role(GetUserId(), proc->roleId) &&
88-
!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
93+
if (procStatus && procStatus->st_backendType == B_AUTOVAC_WORKER)
94+
{
95+
if (!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_AUTOVACUUM_WORKER))
96+
return SIGNAL_BACKEND_NOAUTOVAC;
97+
}
98+
else if (!superuser())
99+
return SIGNAL_BACKEND_NOSUPERUSER;
100+
}
101+
else if (!has_privs_of_role(GetUserId(), proc->roleId) &&
102+
!has_privs_of_role(GetUserId(), ROLE_PG_SIGNAL_BACKEND))
89103
return SIGNAL_BACKEND_NOPERMISSION;
90104

91105
/*
@@ -130,6 +144,13 @@ pg_cancel_backend(PG_FUNCTION_ARGS)
130144
errdetail("Only roles with the %s attribute may cancel queries of roles with the %s attribute.",
131145
"SUPERUSER", "SUPERUSER")));
132146

147+
if (r == SIGNAL_BACKEND_NOAUTOVAC)
148+
ereport(ERROR,
149+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
150+
errmsg("permission denied to cancel query"),
151+
errdetail("Only roles with privileges of the \"%s\" role may cancel autovacuum workers.",
152+
"pg_signal_autovacuum_worker")));
153+
133154
if (r == SIGNAL_BACKEND_NOPERMISSION)
134155
ereport(ERROR,
135156
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
@@ -236,6 +257,13 @@ pg_terminate_backend(PG_FUNCTION_ARGS)
236257
errdetail("Only roles with the %s attribute may terminate processes of roles with the %s attribute.",
237258
"SUPERUSER", "SUPERUSER")));
238259

260+
if (r == SIGNAL_BACKEND_NOAUTOVAC)
261+
ereport(ERROR,
262+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
263+
errmsg("permission denied to terminate process"),
264+
errdetail("Only roles with privileges of the \"%s\" role may terminate autovacuum workers.",
265+
"pg_signal_autovacuum_worker")));
266+
239267
if (r == SIGNAL_BACKEND_NOPERMISSION)
240268
ereport(ERROR,
241269
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),

‎src/include/catalog/catversion.h

Copy file name to clipboardExpand all lines: src/include/catalog/catversion.h
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202407082
60+
#define CATALOG_VERSION_NO 202407091
6161

6262
#endif

‎src/include/catalog/pg_authid.dat

Copy file name to clipboardExpand all lines: src/include/catalog/pg_authid.dat
+5Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -99,5 +99,10 @@
9999
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
100100
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
101101
rolpassword => '_null_', rolvaliduntil => '_null_' },
102+
{ oid => '8916', oid_symbol => 'ROLE_PG_SIGNAL_AUTOVACUUM_WORKER',
103+
rolname => 'pg_signal_autovacuum_worker', rolsuper => 'f', rolinherit => 't',
104+
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
105+
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
106+
rolpassword => '_null_', rolvaliduntil => '_null_' },
102107

103108
]

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.