Skip to content

Commit 9cd92d1

Browse files
michaelpqmichaelpq
committed
Add pg_ls_tmpdir function
This lists the contents of a temporary directory associated to a given tablespace, useful to get information about on-disk consumption caused by temporary files used by a session query. By default, pg_default is scanned, and a tablespace can be specified as argument. This function is 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. Access is also given by default to members of pg_monitor. Author: Nathan Bossart Reviewed-by: Laurenz Albe Discussion: https://postgr.es/m/92F458A2-6459-44B8-A7F2-2ADD3225046A@amazon.com
1 parent d73f4c7 commit 9cd92d1

File tree

5 files changed

+97
-8
lines changed

5 files changed

+97
-8
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20355,6 +20355,20 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
2035520355
role and may be granted to other non-superuser roles.
2035620356
</entry>
2035720357
</row>
20358+
<row>
20359+
<entry>
20360+
<literal><function>pg_ls_tmpdir(<optional><parameter>tablespace</parameter> <type>oid</type></optional>)</function></literal>
20361+
</entry>
20362+
<entry><type>setof record</type></entry>
20363+
<entry>
20364+
List the name, size, and last modification time of files in the
20365+
temporary directory for <parameter>tablespace</parameter>. If
20366+
<parameter>tablespace</parameter> is not provided, the
20367+
<literal>pg_default</literal> tablespace is used. Access is granted
20368+
to members of the <literal>pg_monitor</literal> role and may be
20369+
granted to other non-superuser roles.
20370+
</entry>
20371+
</row>
2035820372
<row>
2035920373
<entry>
2036020374
<literal><function>pg_read_file(<parameter>filename</parameter> <type>text</type> [, <parameter>offset</parameter> <type>bigint</type>, <parameter>length</parameter> <type>bigint</type> [, <parameter>missing_ok</parameter> <type>boolean</type>] ])</function></literal>
@@ -20428,6 +20442,19 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
2042820442
<command>GRANT</command>.
2042920443
</para>
2043020444

20445+
<indexterm>
20446+
<primary>pg_ls_tmpdir</primary>
20447+
</indexterm>
20448+
<para>
20449+
<function>pg_ls_tmpdir</function> returns the name, size, and last modified
20450+
time (mtime) of each file in the temporary file directory for the specified
20451+
<parameter>tablespace</parameter>. If <parameter>tablespace</parameter> is
20452+
not provided, the <literal>pg_default</literal> tablespace is used. By
20453+
default only superusers and members of the <literal>pg_monitor</literal>
20454+
role can use this function. Access may be granted to others using
20455+
<command>GRANT</command>.
20456+
</para>
20457+
2043120458
<indexterm>
2043220459
<primary>pg_read_file</primary>
2043320460
</indexterm>

‎src/backend/catalog/system_views.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1150,6 +1150,8 @@ REVOKE EXECUTE ON FUNCTION lo_export(oid, text) FROM public;
11501150

11511151
REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public;
11521152
REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public;
1153+
REVOKE EXECUTE ON FUNCTION pg_ls_tmpdir() FROM public;
1154+
REVOKE EXECUTE ON FUNCTION pg_ls_tmpdir(oid) FROM public;
11531155

11541156
REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM public;
11551157
REVOKE EXECUTE ON FUNCTION pg_read_file(text,bigint,bigint) FROM public;
@@ -1170,6 +1172,8 @@ REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public;
11701172
--
11711173
GRANT EXECUTE ON FUNCTION pg_ls_logdir() TO pg_monitor;
11721174
GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO pg_monitor;
1175+
GRANT EXECUTE ON FUNCTION pg_ls_tmpdir() TO pg_monitor;
1176+
GRANT EXECUTE ON FUNCTION pg_ls_tmpdir(oid) TO pg_monitor;
11731177

11741178
GRANT pg_read_all_settings TO pg_monitor;
11751179
GRANT pg_read_all_stats TO pg_monitor;

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

Lines changed: 55 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@
2323
#include "access/htup_details.h"
2424
#include "access/xlog_internal.h"
2525
#include "catalog/pg_authid.h"
26+
#include "catalog/pg_tablespace_d.h"
2627
#include "catalog/pg_type.h"
2728
#include "funcapi.h"
2829
#include "mb/pg_wchar.h"
@@ -31,6 +32,7 @@
3132
#include "storage/fd.h"
3233
#include "utils/builtins.h"
3334
#include "utils/memutils.h"
35+
#include "utils/syscache.h"
3436
#include "utils/timestamp.h"
3537

3638
typedef struct
@@ -520,7 +522,7 @@ pg_ls_dir_1arg(PG_FUNCTION_ARGS)
520522

521523
/* Generic function to return a directory listing of files */
522524
static Datum
523-
pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir)
525+
pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
524526
{
525527
FuncCallContext *funcctx;
526528
struct dirent *de;
@@ -549,10 +551,18 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir)
549551
fctx->dirdesc = AllocateDir(fctx->location);
550552

551553
if (!fctx->dirdesc)
552-
ereport(ERROR,
553-
(errcode_for_file_access(),
554-
errmsg("could not open directory \"%s\": %m",
555-
fctx->location)));
554+
{
555+
if (missing_ok && errno == ENOENT)
556+
{
557+
MemoryContextSwitchTo(oldcontext);
558+
SRF_RETURN_DONE(funcctx);
559+
}
560+
else
561+
ereport(ERROR,
562+
(errcode_for_file_access(),
563+
errmsg("could not open directory \"%s\": %m",
564+
fctx->location)));
565+
}
556566

557567
funcctx->user_fctx = fctx;
558568
MemoryContextSwitchTo(oldcontext);
@@ -601,12 +611,50 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir)
601611
Datum
602612
pg_ls_logdir(PG_FUNCTION_ARGS)
603613
{
604-
return pg_ls_dir_files(fcinfo, Log_directory);
614+
return pg_ls_dir_files(fcinfo, Log_directory, false);
605615
}
606616

607617
/* Function to return the list of files in the WAL directory */
608618
Datum
609619
pg_ls_waldir(PG_FUNCTION_ARGS)
610620
{
611-
return pg_ls_dir_files(fcinfo, XLOGDIR);
621+
return pg_ls_dir_files(fcinfo, XLOGDIR, false);
622+
}
623+
624+
/*
625+
* Generic function to return the list of files in pgsql_tmp
626+
*/
627+
static Datum
628+
pg_ls_tmpdir(FunctionCallInfo fcinfo, Oid tblspc)
629+
{
630+
char path[MAXPGPATH];
631+
632+
if (!SearchSysCacheExists1(TABLESPACEOID, ObjectIdGetDatum(tblspc)))
633+
ereport(ERROR,
634+
(errcode(ERRCODE_UNDEFINED_OBJECT),
635+
errmsg("tablespace with OID %u does not exist",
636+
tblspc)));
637+
638+
TempTablespacePath(path, tblspc);
639+
return pg_ls_dir_files(fcinfo, path, true);
640+
}
641+
642+
/*
643+
* Function to return the list of temporary files in the pg_default tablespace's
644+
* pgsql_tmp directory
645+
*/
646+
Datum
647+
pg_ls_tmpdir_noargs(PG_FUNCTION_ARGS)
648+
{
649+
return pg_ls_tmpdir(fcinfo, DEFAULTTABLESPACE_OID);
650+
}
651+
652+
/*
653+
* Function to return the list of temporary files in the specified tablespace's
654+
* pgsql_tmp directory
655+
*/
656+
Datum
657+
pg_ls_tmpdir_1arg(PG_FUNCTION_ARGS)
658+
{
659+
return pg_ls_tmpdir(fcinfo, PG_GETARG_OID(0));
612660
}

‎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 201809301
56+
#define CATALOG_VERSION_NO 201810051
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10199,6 +10199,16 @@
1019910199
provolatile => 'v', prorettype => 'record', proargtypes => '',
1020010200
proallargtypes => '{text,int8,timestamptz}', proargmodes => '{o,o,o}',
1020110201
proargnames => '{name,size,modification}', prosrc => 'pg_ls_waldir' },
10202+
{ oid => '5029', descr => 'list files in the pgsql_tmp directory',
10203+
proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't',
10204+
provolatile => 'v', prorettype => 'record', proargtypes => '',
10205+
proallargtypes => '{text,int8,timestamptz}', proargmodes => '{o,o,o}',
10206+
proargnames => '{name,size,modification}', prosrc => 'pg_ls_tmpdir_noargs' },
10207+
{ oid => '5030', descr => 'list files in the pgsql_tmp directory',
10208+
proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't',
10209+
provolatile => 'v', prorettype => 'record', proargtypes => 'oid',
10210+
proallargtypes => '{oid,text,int8,timestamptz}', proargmodes => '{i,o,o,o}',
10211+
proargnames => '{tablespace,name,size,modification}', prosrc => 'pg_ls_tmpdir_1arg' },
1020210212

1020310213
# hash partitioning constraint function
1020410214
{ oid => '5028', descr => 'hash partition CHECK constraint',

0 commit comments

Comments
 (0)