Re: BUG #18387: Erroneous permission checks and/or misleading error messages with refresh materialized view

Bruce Momjian <bruce@momjian.us> writes:
> On Tue, Mar 12, 2024 at 01:22:33PM +0100, Laurenz Albe wrote:
>> I don't see why it should be a problem if a superuser gets
>> "permission denied" in such a case.  They can also get it if
>> they call a SECURITY DEFINER function owned by a non-superuser.

> Can we improve the error that superusers get so they realize how to fix
> it?

I think there's been a policy of being minimalistic on
permission-denied errors to avoid giving away security information,
but I'm not sure how much sense that really makes.  We already show
the specific object that didn't have permissions.  I think it would
be good for these errors to also mention the specific role whose
permissions were checked.  Perhaps also show the specific privileges
that were missing --- although it might be hard to do that in a
non-confusing way for complicated cases, such as queries that are
valid if you have either table- or column-level permissions.

If we just add the role I'd envision

ERROR:  permission denied to role "foo" for [object]

although with any more detail that would get too long.
Another way could be

ERROR:  permission denied for [object]
DETAIL:  Role "foo" lacks permission [permission].

Mentioning the role that was checked should address the concern
of "I'm a superuser, why did I get this error?".  However,
fixing it requires knowing which privilege to grant.  I'm not
sure if that's always obvious.

            regards, tom lane