public inbox for [email protected]
help / color / mirror / Atom feedVersion 14/15 documentation Section "Alter Default Privileges"
3+ messages / 2 participants
[nested] [flat]
* Version 14/15 documentation Section "Alter Default Privileges"
@ 2022-11-02 19:29 David Burns <[email protected]>
2022-11-03 10:32 ` Re: Version 14/15 documentation Section "Alter Default Privileges" Laurenz Albe <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: David Burns @ 2022-11-02 19:29 UTC (permalink / raw)
To: [email protected] <[email protected]>
To Whom It May Concern;
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. 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;
Also, additional explanation about the differences between global defaults versus schema-level defaults, and how to identify them, would be helpful.
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.
Thank you.
David E. Burns, Jr. | Domain Architect | FedEx Services IT | Dock and Edge Services | Mobile 412.304.8303
1000 FedEx Drive, Moon Township, PA 15108 | [email protected]<mailto:[email protected]>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Version 14/15 documentation Section "Alter Default Privileges"
2022-11-02 19:29 Version 14/15 documentation Section "Alter Default Privileges" David Burns <[email protected]>
@ 2022-11-03 10:32 ` Laurenz Albe <[email protected]>
2022-11-04 09:49 ` Re: Version 14/15 documentation Section "Alter Default Privileges" Laurenz Albe <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2022-11-03 10:32 UTC (permalink / raw)
To: David Burns <[email protected]>; [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Version 14/15 documentation Section "Alter Default Privileges"
2022-11-02 19:29 Version 14/15 documentation Section "Alter Default Privileges" David Burns <[email protected]>
2022-11-03 10:32 ` Re: Version 14/15 documentation Section "Alter Default Privileges" Laurenz Albe <[email protected]>
@ 2022-11-04 09:49 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2022-11-04 09:49 UTC (permalink / raw)
To: David Burns <[email protected]>; [email protected] <[email protected]>
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
Attachments:
[text/x-patch] 0001-Improve-ALTER-DEFAULT-PRIVILEGES-documentation.patch (1.4K, ../../[email protected]/2-0001-Improve-ALTER-DEFAULT-PRIVILEGES-documentation.patch)
download | inline diff:
From 5f7e664ba6fea08dd5cac0c1c71a0ee77522d7c3 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <[email protected]>
Date: Fri, 4 Nov 2022 10:48:38 +0100
Subject: [PATCH] Improve ALTER DEFAULT PRIVILEGES documentation
Clarify that default privileges are only applied to objects
created by the target role. This has been a frequent source
of misunderstandings.
Author: Laurenz Albe, per request from David Burns
Discussion: https://postgr.es/m/LV2PR12MB5725F7C1B8EB2FC38829F276E7399%40LV2PR12MB5725.namprd12.prod.outlook.com
---
doc/src/sgml/ref/alter_default_privileges.sgml | 5 +++++
1 file changed, 5 insertions(+)
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml
index f1d54f5aa3..fcff17e642 100644
--- a/doc/src/sgml/ref/alter_default_privileges.sgml
+++ b/doc/src/sgml/ref/alter_default_privileges.sgml
@@ -138,6 +138,11 @@ REVOKE [ GRANT OPTION FOR ]
<para>
The name of an existing role of which the current role is a member.
If <literal>FOR ROLE</literal> is omitted, the current role is assumed.
+ Default privileges are only applied to objects created by the
+ <replaceable>target_role</replaceable>. There is no way to set default
+ privileges for objects created by arbitrary roles; for that, you'd have
+ to run <command>ALTER DEFAULT PRIVILEGES</command> for each role that can
+ create objects.
</para>
</listitem>
</varlistentry>
--
2.38.1
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-11-04 09:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-02 19:29 Version 14/15 documentation Section "Alter Default Privileges" David Burns <[email protected]>
2022-11-03 10:32 ` Laurenz Albe <[email protected]>
2022-11-04 09:49 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox