pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
creative work-arounds to obtain auto_explain's benefits for non-superuser 2+ messages / 2 participants [nested] [flat]
* creative work-arounds to obtain auto_explain's benefits for non-superuser @ 2020-02-13 21:46 Jacque <jacqueedmund@gmail.com> 0 siblings, 1 reply; 2+ messages in thread From: Jacque @ 2020-02-13 21:46 UTC (permalink / raw) To: pgsql-admin I gotta admit. The logging of auto_explain is an embarrassment of riches. But most operational work is not done under the superuser role but one with the lowest opportunity risk of compromise. I'd like to be embarrassed by an operator role. Any Mcgeyors out there with ideas to be thus embarrased? Honestly, the only solution I've come up with is to duplicate every non-superuser SQL object in a superuser-ONLY schema and have the super user perform the same SQL as the operator role (perhaps on alternate days). Then I could configure that superuser work with all the auto_explain.* settings I could ever want. As I write this, its clear to me that an extension called "Rogue" is the perfect name that could excise the essential abilities of an extension without obtaining full superuser powers, eh... ur privaledges. God bless my C if he deems to grant me that mission. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: creative work-arounds to obtain auto_explain's benefits for non-superuser @ 2020-02-14 13:52 Jeff Janes <jeff.janes@gmail.com> parent: Jacque <jacqueedmund@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Jeff Janes @ 2020-02-14 13:52 UTC (permalink / raw) To: Jacque <jacqueedmund@gmail.com>; +Cc: pgsql-admin On Thu, Feb 13, 2020 at 4:46 PM Jacque <jacqueedmund@gmail.com> wrote: > I gotta admit. The logging of auto_explain is an embarrassment of riches. > > > But most operational work is not done under the superuser role but one > with the lowest opportunity risk of compromise. I'd like to be > embarrassed by an operator role. Any Mcgeyors out there with ideas to > be thus embarrased? > What problem you are trying to solve? auto_explain needs to be set up by a superuser, but once that is done it functions for anyone. The problem would be getting at the logs to see what is in them, and I don't see how your proposal helps that at all. The client could set client_min_messages=LOG so that it can see its own log messages (including the ones generated by auto_explain), but the client is rarely prepared to do anything with those messages in real time. You could configure the client to save such messages to a client-side log for future analysis by the non-superuser who has access to them. Honestly, the only solution I've come up with is to duplicate every > non-superuser SQL object in a superuser-ONLY schema and have the super > user perform the same SQL as the operator role (perhaps on alternate > days). If you want to manually run queries just to see the plans (not because you need the results of the query), why not just run them with EXPLAIN (ANALYZE, BUFFERS) or whatever settings you want? The point of auto_explain is that it works with queries that are being run organically. Cheers, Jeff ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-02-14 13:52 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-02-13 21:46 creative work-arounds to obtain auto_explain's benefits for non-superuser Jacque <jacqueedmund@gmail.com> 2020-02-14 13:52 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox