Thread: [MASSMAIL]What permissions are required for e.g. EXPLAIN UPDATE ...

Hello,

Or put another way, does an EXPLAIN UPDATE on a table really require 
UPDATE permissions to run? Why can't it be done without UPDATE 
permissions? I can understand EXPLAIN ANALYZE UPDATE requiring it, since 
it actually executes the statement, but why would a regular EXPLAIN 
UPDATE require it? Is this documented somewhere in standard postgres 
docs? The only answer I've been able to find on the internet is "The 
user needs all privileges needed for the query that should be explained" 
found here: https://stackoverflow.com/q/72984543

What does an EXPLAIN do exactly to require the extra permissions? I've 
been searching the postgres documentation and the internet, but haven't 
managed to find an answer.

Thanks for any help!

Cheers,
Thomas

Thomas Nyberg <twn@thomasnyberg.com> writes:
> Or put another way, does an EXPLAIN UPDATE on a table really require 
> UPDATE permissions to run? Why can't it be done without UPDATE 
> permissions?

IIRC, the reasoning is this: should you be allowed to run an EXPLAIN
on a table that you have no permissions for at all?  We've felt that
the answer to that has to be "no".  An example of why not is that
EXPLAIN must take at least a shared lock on the table, which should
not be allowed to someone without any permissions.

Having decided that, the next question is what permissions are enough,
and we've concluded that "the same as it'd take to actually run the
query" is a perfectly appropriate answer.  That in turn lets us
decide that "what strength of table lock should be taken?" can be
answered the same for EXPLAIN as for the underlying query.  This
simplifies life by not requiring there to be different code paths
for EXPLAIN and normal query running in various places.

            regards, tom lane