public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: David Burns <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Version 14/15 documentation Section "Alter Default Privileges"
Date: Thu, 03 Nov 2022 11:32:48 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <LV2PR12MB5725F7C1B8EB2FC38829F276E7399@LV2PR12MB5725.namprd12.prod.outlook.com>
References: <LV2PR12MB5725F7C1B8EB2FC38829F276E7399@LV2PR12MB5725.namprd12.prod.outlook.com>

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

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Version 14/15 documentation Section "Alter Default Privileges"
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox