Add pg_ls_logdir() and pg_ls_waldir() functions.

These functions are intended to be used by monitoring tools, and,
unlike pg_ls_dir(), access to them can be granted to non-superusers,
so that those monitoring tools can observe the principle of least
privilege.

Dave Page, revised by me, and also reviewed a bit by Thomas Munro.

Discussion: http://postgr.es/m/CA+OCxow-X=D2fWdKy+HP+vQ1LtrgbsYQ=CshzZBqyFT5jOYrFw@mail.gmail.com

5 files changed, 147 insertions, 3 deletions

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a521912317b..9518fa20388 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19646,7 +19646,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
     database cluster directory and the <varname>log_directory</> can be
     accessed.  Use a relative path for files in the cluster directory,
     and a path matching the <varname>log_directory</> configuration setting
-    for log files.  Use of these functions is restricted to superusers.
+    for log files.  Use of these functions is restricted to superusers
+    except where stated otherwise.
    </para>
 
    <table id="functions-admin-genfile-table">
@@ -19669,6 +19670,26 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
       </row>
       <row>
        <entry>
+        <literal><function>pg_ls_logdir()</function></literal>
+       </entry>
+       <entry><type>setof record</type></entry>
+       <entry>
+        List the name, size, and last modification time of files in the log
+        directory.  Access may be granted to non-superuser roles.
+       </entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_ls_waldir()</function></literal>
+       </entry>
+       <entry><type>setof record</type></entry>
+       <entry>
+        List the name, size, and last modification time of files in the WAL
+        directory.  Access may be granted to non-superuser roles.
+       </entry>
+      </row>
+      <row>
+       <entry>
         <literal><function>pg_read_file(<parameter>filename</> <type>text</> [, <parameter>offset</> <type>bigint</>, <parameter>length</> <type>bigint</> [, <parameter>missing_ok</> <type>boolean</>] ])</function></literal>
        </entry>
        <entry><type>text</type></entry>
@@ -19699,7 +19720,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
    </table>
 
    <para>
-    All of these functions take an optional <parameter>missing_ok</> parameter,
+    Some of these functions take an optional <parameter>missing_ok</> parameter,
     which specifies the behavior when the file or directory does not exist.
     If <literal>true</literal>, the function returns NULL (except
     <function>pg_ls_dir</>, which returns an empty result set). If
@@ -19720,6 +19741,26 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
    </para>
 
    <indexterm>
+    <primary>pg_ls_logdir</primary>
+   </indexterm>
+   <para>
+    <function>pg_ls_logdir</> returns the name, size, and last modified time
+    (mtime) of each file in the log directory. By default, only superusers
+    can use this function, but access may be granted to others using
+    <command>GRANT</command>.
+   </para>
+
+   <indexterm>
+    <primary>pg_ls_waldir</primary>
+   </indexterm>
+   <para>
+    <function>pg_ls_waldir</> returns the name, size, and last modified time
+    (mtime) of each file in the write ahead log (WAL) directory. By
+    default only superusers can use this function, but access may be granted
+    to others using <command>GRANT</command>.
+   </para>
+
+   <indexterm>
     <primary>pg_read_file</primary>
    </indexterm>
    <para>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 0bce20914e0..b6552da4b03 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1102,3 +1102,6 @@ REVOKE EXECUTE ON FUNCTION pg_stat_reset() FROM public;
 REVOKE EXECUTE ON FUNCTION pg_stat_reset_shared(text) FROM public;
 REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_table_counters(oid) FROM public;
 REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_function_counters(oid) FROM public;
+
+REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public;
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index 2147936dd8a..8d0a236e6d5 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -21,6 +21,7 @@
 #include <dirent.h>
 
 #include "access/htup_details.h"
+#include "access/xlog_internal.h"
 #include "catalog/pg_type.h"
 #include "funcapi.h"
 #include "mb/pg_wchar.h"
@@ -473,3 +474,96 @@ pg_ls_dir_1arg(PG_FUNCTION_ARGS)
 {
 	return pg_ls_dir(fcinfo);
 }
+
+/* Generic function to return a directory listing of files */
+static Datum
+pg_ls_dir_files(FunctionCallInfo fcinfo, char *dir)
+{
+	FuncCallContext *funcctx;
+	struct dirent *de;
+	directory_fctx *fctx;
+
+	if (SRF_IS_FIRSTCALL())
+	{
+		MemoryContext oldcontext;
+		TupleDesc       tupdesc;
+
+		funcctx = SRF_FIRSTCALL_INIT();
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		fctx = palloc(sizeof(directory_fctx));
+
+		tupdesc = CreateTemplateTupleDesc(3, false);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
+						   TEXTOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 2, "size",
+						   INT8OID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 3, "modification",
+						   TIMESTAMPTZOID, -1, 0);
+		funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+
+		fctx->location = pstrdup(dir);
+		fctx->dirdesc = AllocateDir(fctx->location);
+
+		if (!fctx->dirdesc)
+			ereport(ERROR,
+					(errcode_for_file_access(),
+					 errmsg("could not read directory \"%s\": %m",
+							fctx->location)));
+
+		funcctx->user_fctx = fctx;
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	funcctx = SRF_PERCALL_SETUP();
+	fctx = (directory_fctx *) funcctx->user_fctx;
+
+	while ((de = ReadDir(fctx->dirdesc, fctx->location)) != NULL)
+	{
+		Datum		values[3];
+		bool		nulls[3];
+		char		path[MAXPGPATH];
+		struct		stat attrib;
+		HeapTuple	tuple;
+
+		/* Skip hidden files */
+		if (de->d_name[0] == '.')
+			continue;
+
+		/* Get the file info */
+		snprintf(path, MAXPGPATH, "%s/%s", fctx->location, de->d_name);
+		if (stat(path, &attrib) < 0)
+			ereport(ERROR,
+					(errcode_for_file_access(),
+					 errmsg("could not stat directory \"%s\": %m", dir)));
+
+		/* Ignore anything but regular files */
+		if (!S_ISREG(attrib.st_mode))
+			continue;
+
+		values[0] = CStringGetTextDatum(de->d_name);
+		values[1] = Int64GetDatum((int64) attrib.st_size);
+		values[2] = TimestampTzGetDatum(time_t_to_timestamptz(attrib.st_mtime));
+		memset(nulls, 0, sizeof(nulls));
+
+		tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+		SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
+	}
+
+	FreeDir(fctx->dirdesc);
+	SRF_RETURN_DONE(funcctx);
+}
+
+/* Function to return the list of files in the log directory */
+Datum
+pg_ls_logdir(PG_FUNCTION_ARGS)
+{
+	return pg_ls_dir_files(fcinfo, Log_directory);
+}
+
+/* Function to return the list of files in the WAL directory */
+Datum
+pg_ls_waldir(PG_FUNCTION_ARGS)
+{
+	return pg_ls_dir_files(fcinfo, XLOGDIR);
+}
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index b24e3953a1d..b4f1b9a6c2a 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201703151
+#define CATALOG_VERSION_NO	201703161
 
 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 3d5d8660718..836d6ff0b20 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5398,6 +5398,12 @@ DESCR("pg_controldata init state information as a function");
 DATA(insert OID = 3445 ( pg_import_system_collations PGNSP PGUID 12 100 0 0 0 f f f f t f v r 2 0 2278 "16 4089" _null_ _null_ "{if_not_exists,schema}" _null_ _null_ pg_import_system_collations _null_ _null_ _null_ ));
 DESCR("import collations from operating system");
 
+/* system management/monitoring related functions */
+DATA(insert OID = 3353 (  pg_ls_logdir               PGNSP PGUID 12 10 20 0 0 f f f f t t v s 0 0 2249 "" "{25,20,1184}" "{o,o,o}" "{name,size,modification}" _null_ _null_ pg_ls_logdir _null_ _null_ _null_ ));
+DESCR("list files in the log directory");
+DATA(insert OID = 3354 (  pg_ls_waldir               PGNSP PGUID 12 10 20 0 0 f f f f t t v s 0 0 2249 "" "{25,20,1184}" "{o,o,o}" "{name,size,modification}" _null_ _null_ pg_ls_waldir _null_ _null_ _null_ ));
+DESCR("list of files in the WAL directory");
+
 /*
  * Symbolic values for provolatile column: these indicate whether the result
  * of a function is dependent *only* on the values of its explicit arguments,