Обсуждение: WAL's listing in pg_xlog by some sql query

Hello!

Can I list all WAL files in pg_xlog  by using some sql query in Postgres?

-- 
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

* Sameer Kumar (sameer.kumar@ashnik.com) wrote:
> On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, <a.ignatov@postgrespro.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.

Thanks!

Stephen

David,

* David G. Johnston (david.g.johnston@gmail.com) wrote:
> On Thu, Jun 2, 2016 at 4:29 PM, Stephen Frost <sfrost@snowman.net> wrote:
>
> > * Sameer Kumar (sameer.kumar@ashnik.com) wrote:
> > > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, <a.ignatov@postgrespro.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

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:
>>
>> * Sameer Kumar (sameer.kumar@ashnik.com) wrote:
>> > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, <a.ignatov@postgrespro.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.
>
>
> Can I not wrap it around another user defined function with SECURITY DEFINER
> and grant privilege to specific users who can use it?

pg_ls_dir() has a check on superuser() embedded in its code.
--
Michael

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:
>>>
>>> * Sameer Kumar (sameer.kumar@ashnik.com) wrote:
>>>> On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, <a.ignatov@postgrespro.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.

CREATE FUNCTION ls_dir(text)
 RETURNS SETOF text
 LANGUAGE sql
 SECURITY DEFINER
AS 'select * from pg_ls_dir($1)';

>> Can I not wrap it around another user defined function with SECURITY DEFINER
>> and grant privilege to specific users who can use it?

Yes, as shown above.

> pg_ls_dir() has a check on superuser() embedded in its code.

So what?  That's what SECURITY DEFINER is all about.
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

On Sat, Jun 4, 2016 at 11:34 PM, Vik Fearing <vik@2ndquadrant.fr> wrote:
> On 03/06/16 04:32, Michael Paquier wrote:
>> pg_ls_dir() has a check on superuser() embedded in its code.
>
> So what?  That's what SECURITY DEFINER is all about.

Yes you are right. I missed completely the point :)
Thanks for the reminder and the correctness.
--
Michael

* 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

On Mon, Jun 6, 2016 at 9:51 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * 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.

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.

> 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.
--
Michael

* Michael Paquier (michael.paquier@gmail.com) wrote:
> On Mon, Jun 6, 2016 at 9:51 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > * 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.
>
> 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