On Mon, 2021-11-01 at 12:50 -0400, Stephen Frost wrote:
> All that said, I wonder if we can have our cake and eat it too. I
> haven't looked into this at all yet and perhaps it's foolish on its
> face, but, could we make CHECKPOINT; basically turn around and just
> run
> select pg_checkpoint(); with the regular privilege checking
> happening?
> Then we'd keep the existing syntax working, but if the user is
> allowed
> to run the command would depend on if they've been GRANT'd EXECUTE
> rights on the function or not.
Great idea! Patch attached.
This feels like a good pattern that we might want to use elsewhere, if
the need arises.
Regards,
Jeff Davis
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 4b49dff2ffc..1e3152c39b1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25487,6 +25487,23 @@ LOG: Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
</thead>
<tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_checkpoint</primary>
+ </indexterm>
+ <function>pg_checkpoint</function> ()
+ </para>
+ <para>
+ Request an immediate checkpoint. This function implements the <xref
+ linkend="sql-checkpoint"/> command, so the behavior is identical. This
+ function is restricted to superusers by default, but other users can
+ be granted EXECUTE to run the function. If a user has permission to
+ execute this function, they also have permission to issue a
+ <command>CHECKPOINT</command> command.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/doc/src/sgml/ref/checkpoint.sgml b/doc/src/sgml/ref/checkpoint.sgml
index 2afee6d7b59..ec2c1a62050 100644
--- a/doc/src/sgml/ref/checkpoint.sgml
+++ b/doc/src/sgml/ref/checkpoint.sgml
@@ -52,7 +52,10 @@ CHECKPOINT
</para>
<para>
- Only superusers can call <command>CHECKPOINT</command>.
+ By default, only superusers can call <command>CHECKPOINT</command>, but
+ permission can be granted to other users by granting privileges on the
+ <link linkend="functions-admin-backup-table">pg_checkpoint()</link>
+ function.
</para>
</refsect1>
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index b98deb72ec6..7ecaca47885 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -44,6 +44,17 @@
static StringInfo label_file;
static StringInfo tblspc_map_file;
+/*
+ * Implements the CHECKPOINT command. To allow non-superusers to perform the
+ * CHECKPOINT command, grant privileges on this function.
+ */
+Datum
+pg_checkpoint(PG_FUNCTION_ARGS)
+{
+ RequestCheckpoint(CHECKPOINT_IMMEDIATE | CHECKPOINT_WAIT |
+ (RecoveryInProgress() ? 0 : CHECKPOINT_FORCE));
+}
+
/*
* pg_start_backup: set up for taking an on-line backup dump
*
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 54c93b16c4c..4437eb3010b 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -603,6 +603,8 @@ AS 'unicode_is_normalized';
-- available to superuser / cluster owner, if they choose.
--
+REVOKE EXECUTE ON FUNCTION pg_checkpoint() FROM public;
+
REVOKE EXECUTE ON FUNCTION pg_start_backup(text, boolean, boolean) FROM public;
REVOKE EXECUTE ON FUNCTION pg_stop_backup() FROM public;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index bf085aa93b2..cb55544d7be 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/objectaccess.h"
#include "catalog/pg_inherits.h"
#include "catalog/toasting.h"
#include "commands/alter.h"
@@ -67,6 +68,7 @@
#include "tcop/pquery.h"
#include "tcop/utility.h"
#include "utils/acl.h"
+#include "utils/fmgroids.h"
#include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
@@ -939,13 +941,29 @@ standard_ProcessUtility(PlannedStmt *pstmt,
break;
case T_CheckPointStmt:
- if (!superuser())
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to do CHECKPOINT")));
+ {
+ /*
+ * Invoke pg_checkpoint(). Implementing the CHECKPOINT command
+ * with a function allows administrators to grant privileges
+ * on the CHECKPOINT command by granting privileges on the
+ * pg_checkpoint() function. It also calls the function
+ * execute hook, if present.
+ */
+ AclResult aclresult;
+ FmgrInfo flinfo;
+
+ aclresult = pg_proc_aclcheck(F_PG_CHECKPOINT, GetUserId(),
+ ACL_EXECUTE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_FUNCTION,
+ get_func_name(F_PG_CHECKPOINT));
- RequestCheckpoint(CHECKPOINT_IMMEDIATE | CHECKPOINT_WAIT |
- (RecoveryInProgress() ? 0 : CHECKPOINT_FORCE));
+ InvokeFunctionExecuteHook(F_PG_CHECKPOINT);
+
+ fmgr_info(F_PG_CHECKPOINT, &flinfo);
+
+ (void) FunctionCall0Coll(&flinfo, InvalidOid);
+ }
break;
case T_ReindexStmt:
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 9faf017457a..35c399e77dd 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 202111021
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d068d6532ec..538efbc3642 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6258,6 +6258,9 @@
proname => 'pg_terminate_backend', provolatile => 'v', prorettype => 'bool',
proargtypes => 'int4 int8', proargnames => '{pid,timeout}',
prosrc => 'pg_terminate_backend' },
+{ oid => '2137', descr => 'request a checkpoint',
+ proname => 'pg_checkpoint', provolatile => 'v', proparallel => 'r',
+ prorettype => 'void', proargtypes => '', prosrc => 'pg_checkpoint' },
{ oid => '2172', descr => 'prepare for taking an online backup',
proname => 'pg_start_backup', provolatile => 'v', proparallel => 'r',
prorettype => 'pg_lsn', proargtypes => 'text bool bool',
