Re: role self-revocation - Mailing list pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:
> Probably easier to just say it again: I want to have users that can
> create roles and then have superuser-like powers with respect to those
> roles. They can freely exercise the privileges of those roles, and
> they can do all the things that a superuser can do but only with
> respect to those roles.

This seems reasonable in isolation, but

(1) it implies a persistent relationship between creating and created
roles.  Whether you want to call that ownership or not, it sure walks
and quacks like ownership.

(2) it seems exactly contradictory to your later point that

> Agree. I also think that it would be a good idea to attribute grants
> performed by any superuser to the bootstrap superuser, or leave them
> unattributed somehow. Because otherwise dropping superusers becomes a
> pain in the tail for no good reason.

Either there's a persistent relationship or there's not.  I don't
think it's sensible to treat superusers differently here.

I think that this argument about the difficulty of dropping superusers
may in fact be the motivation for the existing behavior that object-
permissions GRANTs done by superusers are attributed to the object
owner; something you were unhappy about upthread.

In the end these requirements seem mutually contradictory.  Either
we can have a persistent ownership relationship or not, but I don't
think we can have it apply in some cases and not others without
creating worse problems than we solve.  I'm inclined to toss overboard
the requirement that superusers need to be an easy thing to drop.
Why is that important, anyway?

> We might also need to think carefully about what happens if for
> example the table owner is changed. If bob owns the table and we
> change the owner to mary, but bob's previous grants are still
> attributed to bob, I'm not sure that's going to be very convenient.

That's already handled, is it not?

regression=# create user alice;
CREATE ROLE
regression=# create user bob;
CREATE ROLE
regression=# create user charlie;
CREATE ROLE
regression=# \c - alice
You are now connected to database "regression" as user "alice".
regression=> create table alices_table (f1 int);
CREATE TABLE
regression=> grant select on alices_table to bob;
GRANT
regression=> \c - postgres
You are now connected to database "regression" as user "postgres".
regression=# alter table alices_table owner to charlie;
ALTER TABLE
regression=# \dp alices_table
                                   Access privileges
 Schema |     Name     | Type  |    Access privileges    | Column privileges | Policies
--------+--------------+-------+-------------------------+-------------------+----------
 public | alices_table | table | charlie=arwdDxt/charlie+|                   |
        |              |       | bob=r/charlie           |                   |
(1 row)

I'm a bit disturbed that parts of this discussion seem to be getting
conducted with little understanding of the system's existing behaviors.
We should not be reinventing things we already have perfectly good
solutions for in the object-privileges domain.

            regards, tom lane