public inbox for [email protected]help / color / mirror / Atom feed
Re: Q: GRANT ... WITH ADMIN on PG 17 3+ messages / 3 participants [nested] [flat]
* Re: Q: GRANT ... WITH ADMIN on PG 17 @ 2025-08-21 16:29 Dominique Devienne <[email protected]> 0 siblings, 2 replies; 3+ messages in thread From: Dominique Devienne @ 2025-08-21 16:29 UTC (permalink / raw) To: Karsten Hilbert <[email protected]>; +Cc: Adrian Klaver <[email protected]>; [email protected] On Thu, Aug 21, 2025 at 6:00 PM Karsten Hilbert <[email protected]> wrote: > Am Thu, Aug 21, 2025 at 08:46:00AM -0700 schrieb Adrian Klaver: > > https://rhaas.blogspot.com/2023/01/surviving-without-superuser-coming-to.html > > Thanks, I did, but did not find the answer to: Is there a > way for a role that can manage membership in a group role to > not itself be a member of that group role ? Yes and no. Depends what you mean by MEMBER... You can be ADMIN of a ROLE, yet not have SET or INHERIT on that ROLE. As ADMIN you can grant yourself those SET and INHERIT, sure. But still, with only ADMIN, you can't SET ROLE. So are you a MEMBER? In the pg_has_role(), you are. In reality, not so much. In fact, I argued (after the fact it broke my architecture, introducing cycles) that have just ADMIN shouldn't have considered it a MEMBER of the role. But that ship had sailed already. 1 year later, we're finally catching up. I truly hope v18 won't reserve us similar backward-compatibility breaks. Read the docs for pg_auth_members. pg_has_role(). create role. If you have CREATEROLE, and do a CREATE ROLE foo, you'll have ADMIN on foo, but not SET or INHERIT (but you can grant them to yourself). Also look at pg_auth_members.grantor::regrole::text and you'll see that the postgres SUPERUSER itself gave you that ADMIN grant. But if you grant yourself the role, it's a separate pg_auth_members row, and you're now the grantor. So I didn't spend time studying your specific use case. That's your job :). But given my painful experience of the past year, I'd answer yes to your question, on logical grounds. If you see what I mean. --DD ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Q: GRANT ... WITH ADMIN on PG 17 @ 2025-08-21 16:37 Karsten Hilbert <[email protected]> parent: Dominique Devienne <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Karsten Hilbert @ 2025-08-21 16:37 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: [email protected] Am Thu, Aug 21, 2025 at 06:29:36PM +0200 schrieb Dominique Devienne: > > Thanks, I did, but did not find the answer to: Is there a > > way for a role that can manage membership in a group role to > > not itself be a member of that group role ? > > Yes and no. Depends what you mean by MEMBER... ... > So I didn't spend time studying your specific use case. That's your job :). > But given my painful experience of the past year, I'd answer yes to your > question, on logical grounds. If you see what I mean. --DD I followed your posts back then when you worked out your use case so I did have _some_ idea where to look ;-) I just wanted to confirm my understanding in relation to my current usage. David kindly provided the needed affirmation. Regards, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Q: GRANT ... WITH ADMIN on PG 17 @ 2025-08-21 17:43 Adrian Klaver <[email protected]> parent: Dominique Devienne <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Adrian Klaver @ 2025-08-21 17:43 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; Karsten Hilbert <[email protected]>; +Cc: [email protected] On 8/21/25 09:29, Dominique Devienne wrote: > On Thu, Aug 21, 2025 at 6:00 PM Karsten Hilbert <[email protected]> wrote: >> Am Thu, Aug 21, 2025 at 08:46:00AM -0700 schrieb Adrian Klaver: >>> https://rhaas.blogspot.com/2023/01/surviving-without-superuser-coming-to.html >> >> Thanks, I did, but did not find the answer to: Is there a >> way for a role that can manage membership in a group role to >> not itself be a member of that group role ? > > Yes and no. Depends what you mean by MEMBER... > Read the docs for pg_auth_members. pg_has_role(). create role. > If you have CREATEROLE, and do a CREATE ROLE foo, you'll > have ADMIN on foo, but not SET or INHERIT (but you can grant them to yourself). That is a matter of choice as described here: https://www.postgresql.org/docs/current/runtime-config-client.html createrole_self_grant (string) If a user who has CREATEROLE but not SUPERUSER creates a role, and if this is set to a non-empty value, the newly-created role will be granted to the creating user with the options specified. The value must be set, inherit, or a comma-separated list of these. The default value is an empty string, which disables the feature. The purpose of this option is to allow a CREATEROLE user who is not a superuser to automatically inherit, or automatically gain the ability to SET ROLE to, any created users. Since a CREATEROLE user is always implicitly granted ADMIN OPTION on created roles, that user could always execute a GRANT statement that would achieve the same effect as this setting. However, it can be convenient for usability reasons if the grant happens automatically. A superuser automatically inherits the privileges of every role and can always SET ROLE to any role, and this setting can be used to produce a similar behavior for CREATEROLE users for users which they create. > Also look at pg_auth_members.grantor::regrole::text and you'll see that the > postgres SUPERUSER itself gave you that ADMIN grant. But if you grant yourself > the role, it's a separate pg_auth_members row, and you're now the grantor. > > So I didn't spend time studying your specific use case. That's your job :). > But given my painful experience of the past year, I'd answer yes to your > question, on logical grounds. If you see what I mean. --DD -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-08-21 17:43 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-08-21 16:29 Re: Q: GRANT ... WITH ADMIN on PG 17 Dominique Devienne <[email protected]> 2025-08-21 16:37 ` Karsten Hilbert <[email protected]> 2025-08-21 17:43 ` Adrian Klaver <[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