Subject: Re: Add pg_ownerships and pg_privileges system views

From: "Joel Jacobson" <[email-masked]>
Date: Sun, 20 Oct 2024 23:03:42 +0200

On Sun, Oct 20, 2024, at 16:52, Joel Jacobson wrote:
> On Sun, Oct 20, 2024, at 12:14, Alvaro Herrera wrote:
>> I think the function calls should be in the FROM clause, and restrict the
>> pg_shdepend rows to only the ones in the current database:
>
> Cool. I assume pg_ownerships should be changed in the same way?
> New patch attached.
>
>> Now, depending on pg_shdepend for this means that you don't report
>> anything for an object until a GRANT to another user has been executed.
>> For example if you REVOKE some priv from the object owner, nothing is
>> shown until a GRANT is done for another user (and at that point onwards,
>> privs by the owner are shown).  This seems less than ideal, but I'm not
>> sure how to do different, other than ditching the use of pg_shdepend
>> entirely.
>
> Hmm, yeah that's a bit awkward. Maybe okay if clearly documented.

I've tried to explain this behavior in the docs like this:

   <note>
    <para>
     This view reports privileges only when they have been explicitly granted
     to a role other than the object owner. By default, the object owner has all
     privileges on the object, but these default privileges are not displayed
     in this view until a privilege is granted to another role. For example,
     if you revoke some privileges from the object owner, nothing is shown in
     this view until a privilege is granted to another role, after which the
     owner's privileges are also displayed.
    </para>
   </note>

/Joel

---

From: "Joel Jacobson" <[email-masked]>
Date: Sun, 20 Oct 2024 23:09:43 +0200

>      owner's privileges are also displayed.

Ops, sorry, forgot to update expected/rules.out, fixed.

/Joel