Hi
Following various conversations on list and in person, including the
developer meeting in Brussels earlier this month, here is a patch that
implements pg_ls_logdir() and pg_ls_waldir() functions.
The ultimate aim of this (and followup work I'll be doing) is to
provide functionality to enable monitoring of PostgreSQL without
requiring a user with superuser permissions as many of us have users
for whom security policies prevent this or make it very difficult.
In order to achieve that, there are various pieces of functionality
such as pg_ls_dir() that need to have superuser checks removed to
allow permissions to be granted to a monitoring role. There were
objections in previous discussions to doing this with such generic
functions, hence this patch which adds two narrowly focussed functions
to allow tools to monitor the contents of the log and WAL directories.
Neither function has a hard-coded superuser check, but have ACLs that
prevent public execution by default.
Patch includes the code and doc updates.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d7738b18b7..ecd17a3528 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19360,6 +19360,24 @@ 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, last modification time and size of files in the log
directory.
+ </entry>
+ </row>
+ <row>
+ <entry>
+ <literal><function>pg_ls_waldir()</function></literal>
+ </entry>
+ <entry><type>setof record</type></entry>
+ <entry>
+ List the name, last modification time and size of files in the WAL
directory.
+ </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>
@@ -19390,7 +19408,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
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index 38be9cf1a0..4b67102439 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1096,3 +1096,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/misc.c b/src/backend/utils/adt/misc.c
index 66d09bcb0c..a8cdf3bcbf 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -15,12 +15,14 @@
#include "postgres.h"
#include <sys/file.h>
+#include <sys/stat.h>
#include <signal.h>
#include <dirent.h>
#include <math.h>
#include <unistd.h>
#include "access/sysattr.h"
+#include "access/xlog_internal.h"
#include "catalog/pg_authid.h"
#include "catalog/catalog.h"
#include "catalog/pg_tablespace.h"
@@ -46,6 +48,8 @@
#define atooid(x) ((Oid) strtoul((x), NULL, 10))
+/* Generic function to return a directory listing of files */
+Datum pg_ls_dir_files(FunctionCallInfo fcinfo, char *dir);
/*
* Common subroutine for num_nulls() and num_nonnulls().
@@ -892,3 +896,109 @@ parse_ident(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(makeArrayResult(astate, CurrentMemoryContext));
}
+
+
+typedef struct
+{
+ char *location;
+ DIR *dirdesc;
+} directory_fctx;
+
+/* Generic function to return a directory listing of files */
+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, "mtime",
+ TIMESTAMPTZOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "size",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "file",
+ TEXTOID, -1, 0);
+
+ funcctx->attinmeta = TupleDescGetAttInMetadata(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)
+ {
+ char *values[3];
+ char *path;
+ char mtime[25];
+ HeapTuple tuple;
+ struct stat attrib;
+
+ /*
+ * Skip hidden files
+ */
+ if (de->d_name[0] == '.')
+ continue;
+
+ /* Get the file info */
+ path = psprintf("%s/%s", fctx->location, de->d_name);
+ stat(path, &attrib);
+
+ /* Ignore anything but regular files */
+ if (!S_ISREG(attrib.st_mode))
+ continue;
+
+ /* Get the mtime and size */
+ strftime(mtime, 25, "%Y-%m-%d %H:%M:%S %Z",
localtime(&(attrib.st_ctime)));
+ const int n = snprintf(NULL, 0, "%lld", attrib.st_size);
+ char size[n+1];
+ snprintf(size, n+1, "%lld", attrib.st_size);
+
+ values[0] = mtime;
+ values[1] = size;
+ values[2] = de->d_name;
+
+ tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);
+
+ 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/pg_proc.h b/src/include/catalog/pg_proc.h
index bb7053a942..4ca8044e9a 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5361,6 +5361,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 "" "{1184,20,25}" "{o,o,o}" "{mtime,size,file}" _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 "" "{1184,20,25}" "{o,o,o}" "{mtime,size,file}" _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,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
