Re: Add pg_ownerships and pg_privileges system views

On Sun, Oct 20, 2024, at 23:03, Joel Jacobson wrote:
> 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>

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

/Joel