From: | Stephen Frost <sfrost(at)snowman(dot)net> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: WAL's listing in pg_xlog by some sql query |
Date: | 2016-06-02 20:47:27 |
Message-ID: | 20160602204727.GT21416@tamriel.snowman.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
David,
* David G. Johnston (david(dot)g(dot)johnston(at)gmail(dot)com) wrote:
> On Thu, Jun 2, 2016 at 4:29 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> > * Sameer Kumar (sameer(dot)kumar(at)ashnik(dot)com) wrote:
> > > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, <a(dot)ignatov(at)postgrespro(dot)ru>
> > > wrote:
> > > > Can I list all WAL files in pg_xlog by using some sql query in
> > Postgres?
> > >
> > > Try
> > >
> > > Select pg_ls_dir('pg_xlog');
> >
> > Note that this currently requires superuser privileges.
> >
> > 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.
> >
> > Something along the lines of 'pg_xlog_file_list()', perhaps. There is a
> > check in check_postgres.pl which could take advantage of this also.
> > Should be a very straight-forward function to write, perhaps good as a
> > starter project for someone.
> >
>
> Isn't this the reason we created the newfangled pg_* roles in 9.6?
No, the default roles are specifically to address situations where our
GRANT system is unable to provide the privilege granularity necessary;
ie: the function needs to be executable by 'public' but should behave
differently depending on if the individual calling it has privileged
access or not.
In other words, a case like pg_cancel_query/pg_terminate_backend, where
users can cancel queries of roles they are a member of, superusers can
can cancel queries of all roles, and members of pg_signal_backend can
cancel queries for all non-superusers.
In this case, I think we'd want a whole new function, in which case it
does not need to be callable by a non-privileged individual and does not
need to distinguish between a non-privileged user, a privileged user,
and superuser.
Technically, we could have the pg_ls_dir() function check its argument
and decide to allow it if some new default role 'pg_allow_xlog_ls'
existed and the user was a member of it, but that strikes me as a whole
lot of unnecessary complexity and potential for issue, not to mention
that it certainly wouldn't be very straight-forward to document or
explain to users.
The suggested function would also be able to take additional arguments,
or maybe a second column in the result set, to extract/identify subsets
of xlogs ("xlogs waiting to be archived via archive_cmd", "xlogs being
held due to wal_keep_segments", etc).
Thanks!
Stephen
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick Baker | 2016-06-02 21:03:19 | Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3 |
Previous Message | David G. Johnston | 2016-06-02 20:34:59 | Re: WAL's listing in pg_xlog by some sql query |