Re: WAL's listing in pg_xlog by some sql query

* Michael Paquier (michael(dot)paquier(at)gmail(dot)com) wrote:
> On Mon, Jun 6, 2016 at 9:51 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > * Vik Fearing (vik(at)2ndquadrant(dot)fr) wrote:
> >> On 03/06/16 04:32, Michael Paquier wrote:
> >> > On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com> wrote:
> >> >> On Fri, Jun 3, 2016 at 4:30 AM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> >> >>> Given the usefulness of this specific query and that it could be used
> >> >>> without risk of the user being able to gain superuser access through it,
> >> >>> I'd like to see a new function added which does not have the superuser
> >> >>> check, but is not allowed to be called by public initially either.
> >>
> >> CREATE FUNCTION ls_dir(text)
> >> RETURNS SETOF text
> >> LANGUAGE sql
> >> SECURITY DEFINER
> >> AS 'select * from pg_ls_dir($1)';
> >
> > This isn't a good idea as it allows access to a great deal more than
> > just the number of xlogs. Further, as described above, it gives that
> > access to everyone and not just to specific roles.
>
> Sure, because it is possible to a path string at will. In the context
> of this use case, Alex could just hardcode pg_xlog and nothing else
> than the list of files in this path would be leaked to a user who has
> the execution grant right of this function.

Part of what I was getting at is that it's better for a specific
function to be designed and implemented carefully than everyone having
to write their own (often with poor results, as above). For a common
use-case, such as this, that function would be best provided as part of
core rather than out in a contrib module or only in the mailing list
archives or similar.

> > This is a great example of why we should provide an explicit function
> > which is documented (both in our documentation and in the documentation
> > of tools like check_postgres.pl) that users can use and can GRANT access
> > to for their monitoring systems which gives access to only the
> > information needed- that is, the number of xlog segments.
>
> I have been wondering for some time now about the possibility to have
> at SQL level a representation of the shared memory structure
> XLogCtlData instead, though there is no tracking of what is the newest
> segment that has been recycled ahead, but it could be a reason to
> track that as well.

I've not looked into it myself, but off-hand that does seem useful.

Thanks!

Stephen