Skip to content

Commit befd73c

Browse files
robertmhaasrobertmhaas
committed
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
1 parent b30fb56 commit befd73c

File tree

5 files changed

+147
-3
lines changed

5 files changed

+147
-3
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 43 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -19646,7 +19646,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
1964619646
database cluster directory and the <varname>log_directory</> can be
1964719647
accessed. Use a relative path for files in the cluster directory,
1964819648
and a path matching the <varname>log_directory</> configuration setting
19649-
for log files. Use of these functions is restricted to superusers.
19649+
for log files. Use of these functions is restricted to superusers
19650+
except where stated otherwise.
1965019651
</para>
1965119652

1965219653
<table id="functions-admin-genfile-table">
@@ -19667,6 +19668,26 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
1966719668
List the contents of a directory.
1966819669
</entry>
1966919670
</row>
19671+
<row>
19672+
<entry>
19673+
<literal><function>pg_ls_logdir()</function></literal>
19674+
</entry>
19675+
<entry><type>setof record</type></entry>
19676+
<entry>
19677+
List the name, size, and last modification time of files in the log
19678+
directory. Access may be granted to non-superuser roles.
19679+
</entry>
19680+
</row>
19681+
<row>
19682+
<entry>
19683+
<literal><function>pg_ls_waldir()</function></literal>
19684+
</entry>
19685+
<entry><type>setof record</type></entry>
19686+
<entry>
19687+
List the name, size, and last modification time of files in the WAL
19688+
directory. Access may be granted to non-superuser roles.
19689+
</entry>
19690+
</row>
1967019691
<row>
1967119692
<entry>
1967219693
<literal><function>pg_read_file(<parameter>filename</> <type>text</> [, <parameter>offset</> <type>bigint</>, <parameter>length</> <type>bigint</> [, <parameter>missing_ok</> <type>boolean</>] ])</function></literal>
@@ -19699,7 +19720,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
1969919720
</table>
1970019721

1970119722
<para>
19702-
All of these functions take an optional <parameter>missing_ok</> parameter,
19723+
Some of these functions take an optional <parameter>missing_ok</> parameter,
1970319724
which specifies the behavior when the file or directory does not exist.
1970419725
If <literal>true</literal>, the function returns NULL (except
1970519726
<function>pg_ls_dir</>, which returns an empty result set). If
@@ -19719,6 +19740,26 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
1971919740
empty directory from an non-existent directory.
1972019741
</para>
1972119742

19743+
<indexterm>
19744+
<primary>pg_ls_logdir</primary>
19745+
</indexterm>
19746+
<para>
19747+
<function>pg_ls_logdir</> returns the name, size, and last modified time
19748+
(mtime) of each file in the log directory. By default, only superusers
19749+
can use this function, but access may be granted to others using
19750+
<command>GRANT</command>.
19751+
</para>
19752+
19753+
<indexterm>
19754+
<primary>pg_ls_waldir</primary>
19755+
</indexterm>
19756+
<para>
19757+
<function>pg_ls_waldir</> returns the name, size, and last modified time
19758+
(mtime) of each file in the write ahead log (WAL) directory. By
19759+
default only superusers can use this function, but access may be granted
19760+
to others using <command>GRANT</command>.
19761+
</para>
19762+
1972219763
<indexterm>
1972319764
<primary>pg_read_file</primary>
1972419765
</indexterm>

‎src/backend/catalog/system_views.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1102,3 +1102,6 @@ REVOKE EXECUTE ON FUNCTION pg_stat_reset() FROM public;
11021102
REVOKE EXECUTE ON FUNCTION pg_stat_reset_shared(text) FROM public;
11031103
REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_table_counters(oid) FROM public;
11041104
REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_function_counters(oid) FROM public;
1105+
1106+
REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public;
1107+
REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public;

‎src/backend/utils/adt/genfile.c

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@
2121
#include <dirent.h>
2222

2323
#include "access/htup_details.h"
24+
#include "access/xlog_internal.h"
2425
#include "catalog/pg_type.h"
2526
#include "funcapi.h"
2627
#include "mb/pg_wchar.h"
@@ -473,3 +474,96 @@ pg_ls_dir_1arg(PG_FUNCTION_ARGS)
473474
{
474475
return pg_ls_dir(fcinfo);
475476
}
477+
478+
/* Generic function to return a directory listing of files */
479+
static Datum
480+
pg_ls_dir_files(FunctionCallInfo fcinfo, char *dir)
481+
{
482+
FuncCallContext *funcctx;
483+
struct dirent *de;
484+
directory_fctx *fctx;
485+
486+
if (SRF_IS_FIRSTCALL())
487+
{
488+
MemoryContext oldcontext;
489+
TupleDesc tupdesc;
490+
491+
funcctx = SRF_FIRSTCALL_INIT();
492+
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
493+
494+
fctx = palloc(sizeof(directory_fctx));
495+
496+
tupdesc = CreateTemplateTupleDesc(3, false);
497+
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
498+
TEXTOID, -1, 0);
499+
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "size",
500+
INT8OID, -1, 0);
501+
TupleDescInitEntry(tupdesc, (AttrNumber) 3, "modification",
502+
TIMESTAMPTZOID, -1, 0);
503+
funcctx->tuple_desc = BlessTupleDesc(tupdesc);
504+
505+
fctx->location = pstrdup(dir);
506+
fctx->dirdesc = AllocateDir(fctx->location);
507+
508+
if (!fctx->dirdesc)
509+
ereport(ERROR,
510+
(errcode_for_file_access(),
511+
errmsg("could not read directory \"%s\": %m",
512+
fctx->location)));
513+
514+
funcctx->user_fctx = fctx;
515+
MemoryContextSwitchTo(oldcontext);
516+
}
517+
518+
funcctx = SRF_PERCALL_SETUP();
519+
fctx = (directory_fctx *) funcctx->user_fctx;
520+
521+
while ((de = ReadDir(fctx->dirdesc, fctx->location)) != NULL)
522+
{
523+
Datum values[3];
524+
bool nulls[3];
525+
char path[MAXPGPATH];
526+
struct stat attrib;
527+
HeapTuple tuple;
528+
529+
/* Skip hidden files */
530+
if (de->d_name[0] == '.')
531+
continue;
532+
533+
/* Get the file info */
534+
snprintf(path, MAXPGPATH, "%s/%s", fctx->location, de->d_name);
535+
if (stat(path, &attrib) < 0)
536+
ereport(ERROR,
537+
(errcode_for_file_access(),
538+
errmsg("could not stat directory \"%s\": %m", dir)));
539+
540+
/* Ignore anything but regular files */
541+
if (!S_ISREG(attrib.st_mode))
542+
continue;
543+
544+
values[0] = CStringGetTextDatum(de->d_name);
545+
values[1] = Int64GetDatum((int64) attrib.st_size);
546+
values[2] = TimestampTzGetDatum(time_t_to_timestamptz(attrib.st_mtime));
547+
memset(nulls, 0, sizeof(nulls));
548+
549+
tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
550+
SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
551+
}
552+
553+
FreeDir(fctx->dirdesc);
554+
SRF_RETURN_DONE(funcctx);
555+
}
556+
557+
/* Function to return the list of files in the log directory */
558+
Datum
559+
pg_ls_logdir(PG_FUNCTION_ARGS)
560+
{
561+
return pg_ls_dir_files(fcinfo, Log_directory);
562+
}
563+
564+
/* Function to return the list of files in the WAL directory */
565+
Datum
566+
pg_ls_waldir(PG_FUNCTION_ARGS)
567+
{
568+
return pg_ls_dir_files(fcinfo, XLOGDIR);
569+
}

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201703151
56+
#define CATALOG_VERSION_NO 201703161
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5398,6 +5398,12 @@ DESCR("pg_controldata init state information as a function");
53985398
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_ ));
53995399
DESCR("import collations from operating system");
54005400

5401+
/* system management/monitoring related functions */
5402+
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_ ));
5403+
DESCR("list files in the log directory");
5404+
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_ ));
5405+
DESCR("list of files in the WAL directory");
5406+
54015407
/*
54025408
* Symbolic values for provolatile column: these indicate whether the result
54035409
* of a function is dependent *only* on the values of its explicit arguments,

0 commit comments

Comments
 (0)