From: | Noah Misch <noah(at)leadboat(dot)com> |
---|---|
To: | Stephen Frost <sfrost(at)snowman(dot)net> |
Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org, Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com>, Andrew Dunstan <andrew(at)dunslane(dot)net> |
Subject: | Re: Role Self-Administration |
Date: | 2021-10-05 04:34:38 |
Message-ID: | 20211005043438.GB314685@rfd.leadboat.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Oct 04, 2021 at 10:57:46PM -0400, Stephen Frost wrote:
> "A role is not considered to hold WITH ADMIN OPTION on itself, but it
> may grant or revoke membership in itself from a database session where
> the session user matches the role."
> Here's the thing - having looked back through the standard, it seems
> we're missing a bit that's included there and that makes a heap of
> difference. Specifically, the SQL standard basically says that to
> revoke a privilege, you need to have been able to grant that privilege
> in the first place (as Andrew Dunstan actually also brought up in a
> recent thread about related CREATEROLE things-
> https://www.postgresql.org/message-id/837cc50a-532a-85f5-a231-9d68f2184e52%40dunslane.net
> ) and that isn't something we've been considering when it comes to role
> 'self administration' thus far, at least as it relates to the particular
> field of the "grantor".
Which SQL standard clauses are you paraphrasing? (A reference could take the
form of a spec version number, section number, and rule number. Alternately,
a page number and URL to a PDF would suffice.)
> We can't possibly make things like EVENT TRIGGERs or CREATEROLE work
> with role trees if a given role can basically just 'opt out' of being
> part of the tree to which they were assigned by the user who created
> them. Therefore, I suggest we contemplate two changes in this area:
I suspect we'll regret using the GRANT system to modify behaviors other than
whether or not one gets "permission denied". Hence, -1 on using role
membership to control event trigger firing, whether or not $SUBJECT changes.
> - Allow a user who is able to create roles decide if the role created is
> able to 'self administor' (that is- GRANT their own role to someone
> else) itself.
>
> - Disallow roles from being able to REVOKE role membership that they
> didn't GRANT in the first place.
Either of those could be reasonable. Does the SQL standard take a position
relevant to the decision? A third option is to track each role's creator and
make is_admin_of_role() return true for the creator, whether or not the
creator remains a member. That would also benefit cases where the creator is
rolinherit and wants its ambient privileges to shed the privileges of the role
it's creating.
> We should probably do a more thorough review
> to see if there's other cases where a given role is able to REVOKE
> rights that have been GRANT'd by some other role on a particular object,
> as it seems like we should probably be consistent in this regard across
> everything and not just for roles. That might be a bit of a pain but it
> seems likely to be worth it in the long run and feels like it'd bring us
> more in-line with the SQL standard too.
Does the SQL standard take a position on whether REVOKE SELECT should work
that way?
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2021-10-05 04:51:56 | Re: Triage on old commitfest entries |
Previous Message | Greg Nancarrow | 2021-10-05 04:25:26 | Re: On login trigger: take three |