Skip to content

Commit 1922d7c

Browse files
michaelpqmichaelpq
committed
Add SQL functions to monitor the directory contents of replication slots
This commit adds a set of functions able to look at the contents of various paths related to replication slots: - pg_ls_logicalsnapdir, for pg_logical/snapshots/ - pg_ls_logicalmapdir, for pg_logical/mappings/ - pg_ls_replslotdir, for pg_replslot/<slot_name>/ These are intended to be used by monitoring tools. Unlike pg_ls_dir(), execution permission can be granted to non-superusers. Roles members of pg_monitor gain have access to those functions. Bump catalog version. Author: Bharath Rupireddy Reviewed-by: Nathan Bossart, Justin Pryzby Discussion: https://postgr.es/m/CALj2ACWsfizZjMN6bzzdxOk1ADQQeSw8HhEjhmVXn_Pu+7VzLw@mail.gmail.com
1 parent b55f2b6 commit 1922d7c

File tree

9 files changed

+250
-1
lines changed

9 files changed

+250
-1
lines changed

‎contrib/test_decoding/expected/slot.out

+21
Original file line numberDiff line numberDiff line change
@@ -48,6 +48,27 @@ SELECT pg_drop_replication_slot('regression_slot_t');
4848
ERROR: replication slot "regression_slot_t" does not exist
4949
SELECT pg_drop_replication_slot('regression_slot_t2');
5050
ERROR: replication slot "regression_slot_t2" does not exist
51+
-- monitoring functions for slot directories
52+
SELECT count(*) >= 0 AS ok FROM pg_ls_logicalmapdir();
53+
ok
54+
----
55+
t
56+
(1 row)
57+
58+
SELECT count(*) >= 0 AS ok FROM pg_ls_logicalsnapdir();
59+
ok
60+
----
61+
t
62+
(1 row)
63+
64+
SELECT count(*) >= 0 AS ok FROM pg_ls_replslotdir('regression_slot_p');
65+
ok
66+
----
67+
t
68+
(1 row)
69+
70+
SELECT count(*) >= 0 AS ok FROM pg_ls_replslotdir('not_existing_slot'); -- fails
71+
ERROR: replication slot "not_existing_slot" does not exist
5172
-- permanent slot has survived
5273
SELECT pg_drop_replication_slot('regression_slot_p');
5374
pg_drop_replication_slot

‎contrib/test_decoding/sql/slot.sql

+6
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,12 @@ end';
2828
SELECT pg_drop_replication_slot('regression_slot_t');
2929
SELECT pg_drop_replication_slot('regression_slot_t2');
3030

31+
-- monitoring functions for slot directories
32+
SELECT count(*) >= 0 AS ok FROM pg_ls_logicalmapdir();
33+
SELECT count(*) >= 0 AS ok FROM pg_ls_logicalsnapdir();
34+
SELECT count(*) >= 0 AS ok FROM pg_ls_replslotdir('regression_slot_p');
35+
SELECT count(*) >= 0 AS ok FROM pg_ls_replslotdir('not_existing_slot'); -- fails
36+
3137
-- permanent slot has survived
3238
SELECT pg_drop_replication_slot('regression_slot_p');
3339

‎doc/src/sgml/func.sgml

+73
Original file line numberDiff line numberDiff line change
@@ -27417,6 +27417,79 @@ SELECT convert_from(pg_read_binary_file('file_in_utf8.txt'), 'UTF8');
2741727417
can be granted EXECUTE to run the function.
2741827418
</para></entry>
2741927419
</row>
27420+
27421+
<row>
27422+
<entry role="func_table_entry"><para role="func_signature">
27423+
<indexterm>
27424+
<primary>pg_ls_logicalsnapdir</primary>
27425+
</indexterm>
27426+
<function>pg_ls_logicalsnapdir</function> ()
27427+
<returnvalue>setof record</returnvalue>
27428+
( <parameter>name</parameter> <type>text</type>,
27429+
<parameter>size</parameter> <type>bigint</type>,
27430+
<parameter>modification</parameter> <type>timestamp with time zone</type> )
27431+
</para>
27432+
<para>
27433+
Returns the name, size, and last modification time (mtime) of each
27434+
ordinary file in the server's <filename>pg_logical/snapshots</filename>
27435+
directory. Filenames beginning with a dot, directories, and other
27436+
special files are excluded.
27437+
</para>
27438+
<para>
27439+
This function is restricted to superusers and members of
27440+
the <literal>pg_monitor</literal> role by default, but other users can
27441+
be granted EXECUTE to run the function.
27442+
</para></entry>
27443+
</row>
27444+
27445+
<row>
27446+
<entry role="func_table_entry"><para role="func_signature">
27447+
<indexterm>
27448+
<primary>pg_ls_logicalmapdir</primary>
27449+
</indexterm>
27450+
<function>pg_ls_logicalmapdir</function> ()
27451+
<returnvalue>setof record</returnvalue>
27452+
( <parameter>name</parameter> <type>text</type>,
27453+
<parameter>size</parameter> <type>bigint</type>,
27454+
<parameter>modification</parameter> <type>timestamp with time zone</type> )
27455+
</para>
27456+
<para>
27457+
Returns the name, size, and last modification time (mtime) of each
27458+
ordinary file in the server's <filename>pg_logical/mappings</filename>
27459+
directory. Filenames beginning with a dot, directories, and other
27460+
special files are excluded.
27461+
</para>
27462+
<para>
27463+
This function is restricted to superusers and members of
27464+
the <literal>pg_monitor</literal> role by default, but other users can
27465+
be granted EXECUTE to run the function.
27466+
</para></entry>
27467+
</row>
27468+
27469+
<row>
27470+
<entry role="func_table_entry"><para role="func_signature">
27471+
<indexterm>
27472+
<primary>pg_ls_replslotdir</primary>
27473+
</indexterm>
27474+
<function>pg_ls_replslotdir</function> ( <parameter>slot_name</parameter> <type>text</type> )
27475+
<returnvalue>setof record</returnvalue>
27476+
( <parameter>name</parameter> <type>text</type>,
27477+
<parameter>size</parameter> <type>bigint</type>,
27478+
<parameter>modification</parameter> <type>timestamp with time zone</type> )
27479+
</para>
27480+
<para>
27481+
Returns the name, size, and last modification time (mtime) of each
27482+
ordinary file in the server's <filename>pg_replslot/slot_name</filename>
27483+
directory, where <parameter>slot_name</parameter> is the name of the
27484+
replication slot provided as input of the function. Filenames beginning
27485+
with a dot, directories, and other special files are excluded.
27486+
</para>
27487+
<para>
27488+
This function is restricted to superusers and members of
27489+
the <literal>pg_monitor</literal> role by default, but other users can
27490+
be granted EXECUTE to run the function.
27491+
</para></entry>
27492+
</row>
2742027493
</tbody>
2742127494
</tgroup>
2742227495
</table>

‎src/backend/catalog/system_functions.sql

+12
Original file line numberDiff line numberDiff line change
@@ -701,6 +701,12 @@ REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public;
701701

702702
REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer) FROM PUBLIC;
703703

704+
REVOKE EXECUTE ON FUNCTION pg_ls_logicalsnapdir() FROM PUBLIC;
705+
706+
REVOKE EXECUTE ON FUNCTION pg_ls_logicalmapdir() FROM PUBLIC;
707+
708+
REVOKE EXECUTE ON FUNCTION pg_ls_replslotdir(text) FROM PUBLIC;
709+
704710
--
705711
-- We also set up some things as accessible to standard roles.
706712
--
@@ -715,6 +721,12 @@ GRANT EXECUTE ON FUNCTION pg_ls_tmpdir() TO pg_monitor;
715721

716722
GRANT EXECUTE ON FUNCTION pg_ls_tmpdir(oid) TO pg_monitor;
717723

724+
GRANT EXECUTE ON FUNCTION pg_ls_logicalsnapdir() TO pg_monitor;
725+
726+
GRANT EXECUTE ON FUNCTION pg_ls_logicalmapdir() TO pg_monitor;
727+
728+
GRANT EXECUTE ON FUNCTION pg_ls_replslotdir(text) TO pg_monitor;
729+
718730
GRANT pg_read_all_settings TO pg_monitor;
719731

720732
GRANT pg_read_all_stats TO pg_monitor;

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

+44
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,7 @@
2929
#include "mb/pg_wchar.h"
3030
#include "miscadmin.h"
3131
#include "postmaster/syslogger.h"
32+
#include "replication/slot.h"
3233
#include "storage/fd.h"
3334
#include "utils/acl.h"
3435
#include "utils/builtins.h"
@@ -720,3 +721,46 @@ pg_ls_archive_statusdir(PG_FUNCTION_ARGS)
720721
{
721722
return pg_ls_dir_files(fcinfo, XLOGDIR "/archive_status", true);
722723
}
724+
725+
/*
726+
* Function to return the list of files in the pg_logical/snapshots directory.
727+
*/
728+
Datum
729+
pg_ls_logicalsnapdir(PG_FUNCTION_ARGS)
730+
{
731+
return pg_ls_dir_files(fcinfo, "pg_logical/snapshots", false);
732+
}
733+
734+
/*
735+
* Function to return the list of files in the pg_logical/mappings directory.
736+
*/
737+
Datum
738+
pg_ls_logicalmapdir(PG_FUNCTION_ARGS)
739+
{
740+
return pg_ls_dir_files(fcinfo, "pg_logical/mappings", false);
741+
}
742+
743+
/*
744+
* Function to return the list of files in the pg_replslot/<replication_slot>
745+
* directory.
746+
*/
747+
Datum
748+
pg_ls_replslotdir(PG_FUNCTION_ARGS)
749+
{
750+
text *slotname_t;
751+
char path[MAXPGPATH];
752+
char *slotname;
753+
754+
slotname_t = PG_GETARG_TEXT_PP(0);
755+
756+
slotname = text_to_cstring(slotname_t);
757+
758+
if (!SearchNamedReplicationSlot(slotname, true))
759+
ereport(ERROR,
760+
(errcode(ERRCODE_UNDEFINED_OBJECT),
761+
errmsg("replication slot \"%s\" does not exist",
762+
slotname)));
763+
764+
snprintf(path, sizeof(path), "pg_replslot/%s", slotname);
765+
return pg_ls_dir_files(fcinfo, path, false);
766+
}

‎src/include/catalog/catversion.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 202111171
56+
#define CATALOG_VERSION_NO 202111231
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

+22
Original file line numberDiff line numberDiff line change
@@ -11623,6 +11623,28 @@
1162311623
proallargtypes => '{oid,text,int8,timestamptz}', proargmodes => '{i,o,o,o}',
1162411624
proargnames => '{tablespace,name,size,modification}',
1162511625
prosrc => 'pg_ls_tmpdir_1arg' },
11626+
{ oid => '9858',
11627+
descr => 'list of files in the pg_logical/snapshots directory',
11628+
proname => 'pg_ls_logicalsnapdir', procost => '10', prorows => '20',
11629+
proretset => 't', provolatile => 'v', prorettype => 'record',
11630+
proargtypes => '', proallargtypes => '{text,int8,timestamptz}',
11631+
proargmodes => '{o,o,o}', proargnames => '{name,size,modification}',
11632+
prosrc => 'pg_ls_logicalsnapdir' },
11633+
{ oid => '9859',
11634+
descr => 'list of files in the pg_logical/mappings directory',
11635+
proname => 'pg_ls_logicalmapdir', procost => '10', prorows => '20',
11636+
proretset => 't', provolatile => 'v', prorettype => 'record',
11637+
proargtypes => '', proallargtypes => '{text,int8,timestamptz}',
11638+
proargmodes => '{o,o,o}', proargnames => '{name,size,modification}',
11639+
prosrc => 'pg_ls_logicalmapdir' },
11640+
{ oid => '9860',
11641+
descr => 'list of files in the pg_replslot/slot_name directory',
11642+
proname => 'pg_ls_replslotdir', procost => '10', prorows => '20',
11643+
proretset => 't', provolatile => 'v', prorettype => 'record',
11644+
proargtypes => 'text', proallargtypes => '{text,text,int8,timestamptz}',
11645+
proargmodes => '{i,o,o,o}',
11646+
proargnames => '{slot_name,name,size,modification}',
11647+
prosrc => 'pg_ls_replslotdir' },
1162611648

1162711649
# hash partitioning constraint function
1162811650
{ oid => '5028', descr => 'hash partition CHECK constraint',

‎src/test/regress/expected/misc_functions.out

+50
Original file line numberDiff line numberDiff line change
@@ -243,6 +243,56 @@ select count(*) > 0 from
243243
t
244244
(1 row)
245245

246+
--
247+
-- Test replication slot directory functions
248+
--
249+
CREATE ROLE regress_slot_dir_funcs;
250+
-- Not available by default.
251+
SELECT has_function_privilege('regress_slot_dir_funcs',
252+
'pg_ls_logicalsnapdir()', 'EXECUTE');
253+
has_function_privilege
254+
------------------------
255+
f
256+
(1 row)
257+
258+
SELECT has_function_privilege('regress_slot_dir_funcs',
259+
'pg_ls_logicalmapdir()', 'EXECUTE');
260+
has_function_privilege
261+
------------------------
262+
f
263+
(1 row)
264+
265+
SELECT has_function_privilege('regress_slot_dir_funcs',
266+
'pg_ls_replslotdir(text)', 'EXECUTE');
267+
has_function_privilege
268+
------------------------
269+
f
270+
(1 row)
271+
272+
GRANT pg_monitor TO regress_slot_dir_funcs;
273+
-- Role is now part of pg_monitor, so these are available.
274+
SELECT has_function_privilege('regress_slot_dir_funcs',
275+
'pg_ls_logicalsnapdir()', 'EXECUTE');
276+
has_function_privilege
277+
------------------------
278+
t
279+
(1 row)
280+
281+
SELECT has_function_privilege('regress_slot_dir_funcs',
282+
'pg_ls_logicalmapdir()', 'EXECUTE');
283+
has_function_privilege
284+
------------------------
285+
t
286+
(1 row)
287+
288+
SELECT has_function_privilege('regress_slot_dir_funcs',
289+
'pg_ls_replslotdir(text)', 'EXECUTE');
290+
has_function_privilege
291+
------------------------
292+
t
293+
(1 row)
294+
295+
DROP ROLE regress_slot_dir_funcs;
246296
--
247297
-- Test adding a support function to a subject function
248298
--

‎src/test/regress/sql/misc_functions.sql

+21
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,27 @@ select count(*) > 0 from
9191
where spcname = 'pg_default') pts
9292
join pg_database db on pts.pts = db.oid;
9393

94+
--
95+
-- Test replication slot directory functions
96+
--
97+
CREATE ROLE regress_slot_dir_funcs;
98+
-- Not available by default.
99+
SELECT has_function_privilege('regress_slot_dir_funcs',
100+
'pg_ls_logicalsnapdir()', 'EXECUTE');
101+
SELECT has_function_privilege('regress_slot_dir_funcs',
102+
'pg_ls_logicalmapdir()', 'EXECUTE');
103+
SELECT has_function_privilege('regress_slot_dir_funcs',
104+
'pg_ls_replslotdir(text)', 'EXECUTE');
105+
GRANT pg_monitor TO regress_slot_dir_funcs;
106+
-- Role is now part of pg_monitor, so these are available.
107+
SELECT has_function_privilege('regress_slot_dir_funcs',
108+
'pg_ls_logicalsnapdir()', 'EXECUTE');
109+
SELECT has_function_privilege('regress_slot_dir_funcs',
110+
'pg_ls_logicalmapdir()', 'EXECUTE');
111+
SELECT has_function_privilege('regress_slot_dir_funcs',
112+
'pg_ls_replslotdir(text)', 'EXECUTE');
113+
DROP ROLE regress_slot_dir_funcs;
114+
94115
--
95116
-- Test adding a support function to a subject function
96117
--

0 commit comments

Comments
 (0)