Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 9cd92d1

Browse filesBrowse 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
Copy full SHA for 9cd92d1

File tree

Expand file treeCollapse file tree

5 files changed

+97
-8
lines changed
Filter options
Expand file treeCollapse file tree

5 files changed

+97
-8
lines changed

‎doc/src/sgml/func.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/func.sgml
+27Lines 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

Copy file name to clipboardExpand all lines: src/backend/catalog/system_views.sql
+4Lines 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

Copy file name to clipboardExpand all lines: src/backend/utils/adt/genfile.c
+55-7Lines 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

Copy file name to clipboardExpand all lines: src/include/catalog/catversion.h
+1-1Lines 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

Copy file name to clipboardExpand all lines: src/include/catalog/pg_proc.dat
+10Lines 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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.