Re: role self-revocation

On Wed, Mar 9, 2022 at 2:31 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > Well, the problem is that as far as I can see, the admin option is an
> > optional feature of membership. You can grant someone membership
> > without admin option, or with admin option, but you can't grant them
> > the admin option without membership, just like you can't purchase an
> > upgrade to first class without the underlying plane ticket. What would
> > the syntax look even like for this? GRANT foo TO bar WITH ADMIN OPTION
> > BUT WITHOUT MEMBERSHIP? Yikes.
>
> I don't think we need syntax to describe it. As I just said in my
> other reply, we have a perfectly good precedent for this already
> in ordinary object permissions. That is: an object owner always,
> implicitly, has GRANT OPTION for all the object's privileges, even
> if she revoked the corresponding plain privilege from herself.
>

So CREATE ROLE will assign ownership of AND membership in the newly created
role to the session_user UNLESS the OWNER clause is present in which case
the named role, so long as the session_user can SET ROLE to the named role,
becomes the owner & member. Subsequent to that the owner can issue: REVOKE
new_role FROM role_name where role_name is again the session_user role or
one that can be SET ROLE to.

> Yeah, this does mean that we're effectively deciding that the creator
> of a role is its owner. What's the problem with that?
>

I'm fine with this. It does introduce an OWNER concept to roles and so at
minimum we need to add:

ALTER ROLE foo OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER |
SESSION_USER }

And similar for CREATE ROLE
And keep the USER alias commands in sync.
GROUP commands are only present for backward compatibility and so don't get
updated with new features by design.

Obviously a superuser can change ownership.

Playing with table ownership I find this behavior:
-- superuser
CREATE ROLE tblowner;
CREATE TABLE tblowner_test (id serial primary key);
ALTER TABLE tblowner_test OWNER TO tblowner;

CREATE ROLE boss;
GRANT boss TO tblowner;

SET SESSION AUTHORIZATION tblowner;
ALTER TABLE tblowner_test OWNER TO boss; --works

So tblowner can push their ownership attribute to any group they are a
member of. Is that the behavior we want for roles as well?

David J.