From c48101620151f78ee184e5f81766cbac347d9a11 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Tue, 9 Oct 2018 22:29:09 +0900
Subject: [PATCH] Add pg_ls_archive_statusdir function

This function lists the contents of the WAL archive status directory,
and is intended to be used by monitoring tools.  Unlike pg_ls_dir(),
access to it can be granted to non-superusers so that those monitoring
tools can observe the principle of least privilege.  Access is also
given by default to members of pg_monitor.

Author:  Christoph Moench-Tegeder
Reviewed-by: Aya Iwata
Discussion: https://postgr.es/m/20180930205920.GA64534@elch.exwg.net
---
 doc/src/sgml/func.sgml               | 24 ++++++++++++++++++++++++
 src/backend/catalog/system_views.sql |  2 ++
 src/backend/utils/adt/genfile.c      |  9 +++++++++
 src/include/catalog/catversion.h     |  2 +-
 src/include/catalog/pg_proc.dat      |  5 +++++
 5 files changed, 41 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f984d069e1..5193df3366 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20355,6 +20355,18 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
         role and may be granted to other non-superuser roles.
        </entry>
       </row>
+      <row>
+       <entry>
+        <literal><function>pg_ls_archive_statusdir()</function></literal>
+       </entry>
+       <entry><type>setof record</type></entry>
+       <entry>
+        List the name, size, and last modification time of files in the WAL
+        archive status directory. Access is granted to members of the
+        <literal>pg_monitor</literal> role and may be granted to other
+        non-superuser roles.
+       </entry>
+      </row>
       <row>
        <entry>
         <literal><function>pg_ls_tmpdir(<optional><parameter>tablespace</parameter> <type>oid</type></optional>)</function></literal>
@@ -20442,6 +20454,18 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
     <command>GRANT</command>.
    </para>
 
+   <indexterm>
+    <primary>pg_ls_archive_statusdir</primary>
+   </indexterm>
+   <para>
+    <function>pg_ls_archive_statusdir</function> returns the name, size, and
+    last modified time (mtime) of each file in the WAL archive status
+    directory <literal>pg_wal/archive_status</literal>. By default only
+    superusers and members of the <literal>pg_monitor</literal> role can
+    use this function. Access may be granted to others using
+    <command>GRANT</command>.
+   </para>
+
    <indexterm>
     <primary>pg_ls_tmpdir</primary>
    </indexterm>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 020f28cbf6..0c1bcebb0d 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1150,6 +1150,7 @@ REVOKE EXECUTE ON FUNCTION lo_export(oid, text) FROM public;
 
 REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public;
 REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_ls_archive_statusdir() FROM public;
 REVOKE EXECUTE ON FUNCTION pg_ls_tmpdir() FROM public;
 REVOKE EXECUTE ON FUNCTION pg_ls_tmpdir(oid) FROM public;
 
@@ -1172,6 +1173,7 @@ REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public;
 --
 GRANT EXECUTE ON FUNCTION pg_ls_logdir() TO pg_monitor;
 GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_ls_archive_statusdir() TO pg_monitor;
 GRANT EXECUTE ON FUNCTION pg_ls_tmpdir() TO pg_monitor;
 GRANT EXECUTE ON FUNCTION pg_ls_tmpdir(oid) TO pg_monitor;
 
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index 85bea8d502..d4dc92c2fd 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -658,3 +658,12 @@ pg_ls_tmpdir_1arg(PG_FUNCTION_ARGS)
 {
 	return pg_ls_tmpdir(fcinfo, PG_GETARG_OID(0));
 }
+
+/*
+ * Function to return the list of files in the WAL archive status directory.
+ */
+Datum
+pg_ls_archive_statusdir(PG_FUNCTION_ARGS)
+{
+	return pg_ls_dir_files(fcinfo, XLOGDIR "/archive_status", true);
+}
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 1e38656fa7..a8e7d4b34e 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201810061
+#define CATALOG_VERSION_NO	201810091
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 963ff6848a..038a11d6cd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10200,6 +10200,11 @@
   provolatile => 'v', prorettype => 'record', proargtypes => '',
   proallargtypes => '{text,int8,timestamptz}', proargmodes => '{o,o,o}',
   proargnames => '{name,size,modification}', prosrc => 'pg_ls_waldir' },
+{ oid => '5031', descr => 'list of files in the archive_status directory',
+  proname => 'pg_ls_archive_statusdir', procost => '10', prorows => '20', proretset => 't',
+  provolatile => 'v', prorettype => 'record', proargtypes => '',
+  proallargtypes => '{text,int8,timestamptz}', proargmodes => '{o,o,o}',
+  proargnames => '{name,size,modification}', prosrc => 'pg_ls_archive_statusdir' },
 { oid => '5029', descr => 'list files in the pgsql_tmp directory',
   proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => '',
-- 
2.40.0