Обсуждение: Version 14/15 documentation Section "Alter Default Privileges"

On Wed, 2022-11-02 at 19:29 +0000, David Burns wrote:
> To Whom It May Concern;

It concerns me, because I often see questions from people who misunderstand this.

> Some additional clarity in the versions 14/15 documentation would be helpful specifically
> surrounding the "target_role" clause for the ALTER DEFAULT PRIVILEGES command.
> To the uninitiated, the current description seems vague.  Maybe something like the following would help:
>  
> target_role
>        The name of an existing role of which the current role is a member.
>        Default privileges are only applied to objects created by the targeted role/user (FOR ROLE target_role).
>        If the FOR ROLE clause is omitted, the targeted user defaults to the current user executing the
>        ALTER DEFAULT PRIVILEGES command.

+1

I like the wording, except that I would replace "targeted role/user (FOR ROLE target_role)" with
"target role" for added clarity.

>   The result can be seen using the following query:
>  
> select   table_catalog as database
>          ,table_schema
>          ,table_name
>          ,privilege_type
>          ,grantee
>          ,'revoke '||privilege_type||' on '||table_schema||'.'||table_name||' from '||grantee||';' as revoke_stmt
> from     information_schema.table_privileges
> where    table_schema = 'my_schema'
> and      table_name = 'my_table'
> order by 1,2,3,5,4;

I am not so happy with that query; I thinks that is going too far.
Perhaps we can say that the "psql" command "\ddp" can be used to view default privileges.

> Also, additional explanation about the differences between global defaults versus
> schema-level defaults, and how to identify them, would be helpful.

The examples already cover that in some detail.

> Additional explanation about exactly what is happening would help to put this command into perspective.
> On successful execution with the correct parameter values, and using both the FOR ROLE and
> IN SCHEMA clauses, I also received privilege grants directed to the user executing the
> ALTER DEFAULT PRIVILEGES command.  This was in addition to the expected privileges specified in the command.
> I'm not sure why this occurred or how to eliminate it, in the interest of establishing "least privilege"
permissions.

ALTER DEFAULT PRIVILEGES does nothing like that...

Yours,
Laurenz Albe

On Thu, 2022-11-03 at 11:32 +0100, Laurenz Albe wrote:
> On Wed, 2022-11-02 at 19:29 +0000, David Burns wrote:
> > To Whom It May Concern;
> 
> It concerns me, because I often see questions from people who misunderstand this.
> 
> > Some additional clarity in the versions 14/15 documentation would be helpful specifically
> > surrounding the "target_role" clause for the ALTER DEFAULT PRIVILEGES command.
> > To the uninitiated, the current description seems vague.  Maybe something like the following would help:
> >  
> > target_role
> >        The name of an existing role of which the current role is a member.
> >        Default privileges are only applied to objects created by the targeted role/user (FOR ROLE target_role).
> >        If the FOR ROLE clause is omitted, the targeted user defaults to the current user executing the
> >        ALTER DEFAULT PRIVILEGES command.
> 
> +1

After some more thinking, I came up with the attached patch.

Yours,
Laurenz Albe