Re: Add pg_ownerships and pg_privileges system views

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