Greetings, There's been various discussions about CREATEROLE, EVENT TRIGGERs, and other things which hinge around the general idea that we can create a 'tree' of roles where there's some root and then from that root there's a set of created roles, or at least roles which have been GRANT'd other roles as part of an explicit arrangement.
The issue with many of these suggestions is that roles, currently, are able to 'administer' themselves. That means that such role memberships aren't suitable for such controls. To wit, this happens: Superuser: =# create user u1; CREATE ROLE =# create user u2; CREATE ROLE =# grant u2 to u1; GRANT ROLE ... Log in as u2: => revoke u2 from u1; REVOKE ROLE ... This is because we allow 'self administration' of roles, meaning that they can decide what other roles they are a member of. This is documented as: "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." at: https://www.postgresql.org/docs/current/sql-grant.html Further, we comment this in the code: * A role can admin itself when it matches the session user and we're * outside any security-restricted operation, SECURITY DEFINER or * similar context. SQL-standard roles cannot self-admin. However, * SQL-standard users are distinct from roles, and they are not * grantable like roles: PostgreSQL's role-user duality extends the * standard. Checking for a session user match has the effect of * letting a role self-admin only when it's conspicuously behaving * like a user. Note that allowing self-admin under a mere SET ROLE * would make WITH ADMIN OPTION largely irrelevant; any member could * SET ROLE to issue the otherwise-forbidden command. in src/backend/utils/adt/acl.c 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". 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: - 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. This isn't as big a change as it might seem as we already track which role issued a given GRANT. 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. So, thoughts? Thanks! Stephen
signature.asc
Description: PGP signature
