Message-ID: <4a6f157edc4ae38a2050a5893e6729cbb98113bc.camel@cybertec.at>
Subject: Re: Version 14/15 documentation Section "Alter Default Privileges"
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: David Burns <david.burns@fedex.com>, "pgsql-docs@lists.postgresql.org"
	 <pgsql-docs@lists.postgresql.org>
Date: Thu, 03 Nov 2022 11:32:48 +0100
In-Reply-To: 
 <LV2PR12MB5725F7C1B8EB2FC38829F276E7399@LV2PR12MB5725.namprd12.prod.outlook.com>
References: 
	<LV2PR12MB5725F7C1B8EB2FC38829F276E7399@LV2PR12MB5725.namprd12.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/4a6f157edc4ae38a2050a5893e6729cbb98113bc.camel%40cybertec.at>
Precedence: bulk

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