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

* Vik Fearing (vik@2ndquadrant.fr) wrote:
> On 03/06/16 04:32, Michael Paquier wrote:
> > On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:
> >> On Fri, Jun 3, 2016 at 4:30 AM Stephen Frost <sfrost@snowman.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.

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.

Thanks!

Stephen