On Mon, 2021-11-08 at 12:47 -0500, Stephen Frost wrote:
>
> I don't feel as strongly as others apparently do on this point
> though,
> and I'd rather have non-superusers able to run CHECKPOINT *somehow*
> than not, so if the others feel like a predefined role is a better
> approach then I'm alright with that. Seems a bit overkill to me but
> it's also not like it's actually all that much code or work to do.
+1. It seems like the pg_checkpointer predefined role is the most
acceptable to everyone (even if not universally liked).
Attached a rebased version of that patch.
Regards,
Jeff Davis
diff --git a/doc/src/sgml/ref/checkpoint.sgml b/doc/src/sgml/ref/checkpoint.sgml
index 2afee6d7b59..aebe8f8cd77 100644
--- a/doc/src/sgml/ref/checkpoint.sgml
+++ b/doc/src/sgml/ref/checkpoint.sgml
@@ -52,7 +52,8 @@ CHECKPOINT
</para>
<para>
- Only superusers can call <command>CHECKPOINT</command>.
+ Only superusers or members of the <literal>pg_checkpointer</literal> role
+ can call <command>CHECKPOINT</command>.
</para>
</refsect1>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index afbf67c28cf..d5482cd5fc6 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -582,6 +582,10 @@ DROP ROLE doomed_role;
<entry>Allow executing programs on the database server as the user the database runs as with
COPY and other functions which allow executing a server-side program.</entry>
</row>
+ <row>
+ <entry>pg_checkpointer</entry>
+ <entry>Allow executing the <command>CHECKPOINT</command> command.</entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index bf085aa93b2..0ff832a62c2 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -24,6 +24,7 @@
#include "catalog/catalog.h"
#include "catalog/index.h"
#include "catalog/namespace.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_inherits.h"
#include "catalog/toasting.h"
#include "commands/alter.h"
@@ -939,10 +940,10 @@ standard_ProcessUtility(PlannedStmt *pstmt,
break;
case T_CheckPointStmt:
- if (!superuser())
+ if (!has_privs_of_role(GetUserId(), ROLE_PG_CHECKPOINTER))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to do CHECKPOINT")));
+ errmsg("must be member of pg_checkpointer to do CHECKPOINT")));
RequestCheckpoint(CHECKPOINT_IMMEDIATE | CHECKPOINT_WAIT |
(RecoveryInProgress() ? 0 : CHECKPOINT_FORCE));
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 9faf017457a..f0707678037 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202110272
+#define CATALOG_VERSION_NO 202111081
#endif
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 3da68016b61..9c65174f3c6 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -79,5 +79,10 @@
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '4544', oid_symbol => 'ROLE_PG_CHECKPOINTER',
+ rolname => 'pg_checkpointer', rolsuper => 'f', rolinherit => 't',
+ rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+ rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+ rolpassword => '_null_', rolvaliduntil => '_null_' },
]
