Re: CREATEROLE and role ownership hierarchies

On Tue, Feb 1, 2022 at 6:38 PM Andrew Dunstan <andrew@dunslane.net> wrote:
> > In existing postgresql releases, having CREATEROLE means you can give away most attributes, including ones you
yourselfdon't have (createdb, login).  So we already have the concept of NOFOO WITH ADMIN OPTION, we just don't call it
that. In pre-v8 patches on this thread, I got rid of that; you *must* have the attribute to give it away.  But maybe
thatwas too restrictive, and we need a way to specify, attribute by attribute, how this works.  Is this just a problem
ofsurprising grammar?  Is it surprising behavior?  If the latter, I'm inclined to give up this WIP as having been a bad
move. If the former, I'll try to propose some less objectionable grammar. 
> >
>
> Certainly the grammar would need to be better. But I'm not sure any
> grammar that expresses what is supported here is not going to be
> confusing, because the underlying scheme seems complex. But I'm
> persuadable. I'd like to hear from others on the subject.

Well, we've been moving more and more in the direction of using
predefined roles to manage access. The things that are basically
Boolean flags on the role are mostly legacy stuff. So my tentative
opinion (and I'm susceptible to being persuaded that I'm wrong here)
is that putting a lot of work into fleshing out that infrastructure
does not necessarily make a ton of sense. Are we ever going to add
even one more flag that works that way?

Also, any account that can create roles is a pretty high-privilege
account. Maybe it's superuser, or maybe not, but it's certainly
powerful. In my opinion, that makes fine distinctions here less
important. Is there really an argument for saying "well, we're going
to let you bypass RLS, but we're not going to let you give that
privilege to others"? It seems contrived to think of restricting a
role that is powerful enough to create whole new accounts in such a
way. I'm not saying that someone couldn't have a use case for it, but
I think it'd be a pretty thin use case.

In short, I think it makes tons of sense to say that CREATEROLE lets
you give to others those role flags which you have, but not the ones
you lack. However, to me, it feels like overengineering to distinguish
between things you have and can give away, things you have and can't
give away, and things you don't even have.

--
Robert Haas
EDB: http://www.enterprisedb.com