Re: role self-revocation

Greetings,

* Mark Dilger (mark(dot)dilger(at)enterprisedb(dot)com) wrote:
> > On Mar 11, 2022, at 4:56 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > First … I outlined a fair bit of further description in the message you just responded to but neglected to include in your response, which strikes me as odd that you’re now asking for further explanation.
>
> > When it comes to completing the idea of role ownership- I didn’t come up with that idea nor champion it
>
> Sorry, not "completing", but "competing". It seems we're discussing different ways to fix how roles and CREATEROLE work, and we have several ideas competing against each other. (This differs from *people* competing against each other, as I don't necessarily like the patch I wrote better than I like your idea.)
>
> > and therefore I’m not really sure how many of the steps are required to fully support that concept..?
>
> There are problems that the ownership concepts solve. I strongly suspect that your proposal could also solve those same problems, and just trying to identify the specific portions of your proposal necessary to do so.

I'm happy to help try to identify those, but it seems we'd need to have
the exact problems that ownership solves defined first. Robert defined
what he's looking for as:

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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. They cannot break out to the OS. I think it's
> pretty similar to what you are describing, with a couple of possible
> exceptions. For example, would you imagine that being an admin of a
> login role would let you change that user's password? Because that
> would be desirable behavior from where I sit.

Which sure sounds like it's just about covered in step #1 of what I
outlined before, except that the above description implies that one
can't "get away" from the user who created their role, in which case we
do need step #2 also.

> > For my part, I would think that those steps necessary to satisfy the spec would get us pretty darn close to what true folks advocating for role ownership are asking for
>
> I have little idea what "true folks" means in this context. As for "advocating for role ownership", I'm not in that group. Whether role ownership or something else, I just want some solution to a set of problems, mostly to do with needing superuser to do role management tasks.

... I'm not entirely sure what I meant there either, my hunch is that
'true' was actually just a leftover word from some other framing of that
sentence and I had meant to remove it. Apologies for that. What I was
trying to get at there is that steps #1 & #2 are the ones that I view as
getting us closer to spec compliance and that doing so would get us to
where Robert's ask above would be answered.

> > , but that doesn’t include the superuser-only alter role attributes piece. Is that included in role ownership? I wouldn’t think so, but some might argue otherwise, and I don’t know that it is actually useful to divert into a discussion about what is or isn’t in that.
>
> Introducing the idea of role ownership doesn't fix that. But a patch which introduces role ownership is useless unless CREATEROLE is also fixed. There isn't any point having non-superusers create and own roles if, to do so, they need a privilege which can break into superuser. But that argument is no different with a patch along the lines of what you are proposing. CREATEROLE needs fixing either way.

There's a few ways to have the 'CREATEROLE' role attribute be fixed-

- Remove it entirely (replacing with pg_create_role or such)
- Remove its ability to GRANT out rights that the role running it
doesn't have
- Make it superfluous (leave it as-is, but add in pg_create_role which
allows a role to create another role but doesn't include the magic
GRANT whatever-role TO whatever-role that CREATEROLE has)

I agree that we need to do something here to allow roles to create other
roles while not having or being able to trivially get superuser
themselves.

> > If we agree that the role attribute bits are independent
>
> Yes.

Great.

> > then I think I agree that we need 1 and 2 to get the capabilities that the folks asking for role ownership want
>
> Yes.

Ok.

> > as 2 is where we make sure that one admin of a role can’t revoke another admin’s rights over that role.
>
> Exactly, so #2 is part of the competing proposal. (I get the sense you might not see these as competing proposals, but I find that framing useful for deciding which approach to pursue.)

... and is also part of getting us closer to the spec.

> > Perhaps 2 isn’t strictly necessary in a carefully managed environment where no one else is given admin rights over the mini-superuser roles, but I’d rather not have folks depending on that.
>
> I think it is necessary, and for the reason you say.

Great.

> > I’d still push back though and ask those advocating for this if it meets what they’re asking for. I got the impression that it did but maybe I misunderstood.
> >
> > In terms of exactly how things would work with these changes… I thought I explained it pretty clearly, so it’s kind of hard to answer that further without a specific question to answer. Did you have something specific in mind? Perhaps I could answer a specific question and provide more clarity that way.
>
> Your emails contained a lot of "we could do this or that depending on what people want, and maybe this other thing, but that isn't really necessary, and ...." which left me unclear on the proposal. I don't mean to disparage your communication style; it's just that when trying to distill technical details, high level conversation can be hard to grok.

Feel free to quote me explicitly in such places that you're looking for
clarification and I'd be happy to drill down on those.

> I have the sense that you aren't going to submit a patch, so I wanted this thread to contain enough detail for somebody else to do so. Thanks.

So ... do you feel like that's now the case? Or were you looking for
more?

Thanks,

Stephen