Обсуждение: 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
On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, <a.ignatov@postgrespro.ru> wrote:
Hello!
Can I list all WAL files in pg_xlog by using some sql query in Postgres?
Try
Select pg_ls_dir('pg_xlog');
-- Alex Ignatov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
--
--
Best Regards
Sameer Kumar | DB Solution Architect
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
* 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
Вложения
* 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?
David J.
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 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?
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
--
--
Best Regards
Sameer Kumar | DB Solution Architect
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
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