Horton

Re: CREATEROLE and role ownership hierarchies

Andrew Dunstan <andrew@dunslane.net>

View thread View on postgr.es
From: Andrew Dunstan <andrew@dunslane.net>
To: Robert Haas <robertmhaas@gmail.com>, Stephen Frost <sfrost@snowman.net>
Cc: Mark Dilger <mark.dilger@enterprisedb.com>, Joshua Brindle <joshua.brindle@crunchydata.com>, Shinya Kato <Shinya11.Kato@oss.nttdata.com>, "Bossart, Nathan" <bossartn@amazon.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Jeff Davis <pgsql@j-davis.com>
Date: 2022-01-24T21:00:28Z
Lists: pgsql-hackers 
On 1/24/22 15:33, Robert Haas wrote:
> On Sat, Jan 22, 2022 at 4:20 PM Stephen Frost <sfrost@snowman.net> wrote:
>> Whoah, really?  No, I don't agree with this, it's throwing away the
>> entire concept around inheritance of role rights and how you can have
>> roles which you can get the privileges of by doing a SET ROLE to them
>> but you don't automatically have those rights.
> I see it differently. In my opinion, what that does is make the patch
> actually useful instead of largely a waste of time. If you are a
> service provider, you want to give your customers a super-user-like
> experience without actually making them superuser. You don't want to
> actually make them superuser, because then they could do things like
> change archive_command or install plperlu and shell out to the OS
> account, which you don't want. But you do want them to be able to
> administer objects within the database just as a superuser could. And
> a superuser has privileges over objects they own and objects belonging
> to other users automatically, without needing to SET ROLE.
>

+many


I encountered such issues on a cloud provider several years ago, and
blogged about the difficulties, which would have been solved very nicely
and cleanly by this proposal. It was when I understood properly how this
proposal worked, precisely as Robert states, that I became more
enthusiastic about it.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Commits

  1. Make role grant system more consistent with other privileges.

  2. Ensure that pg_auth_members.grantor is always valid.

  3. Remove the ability of a role to administer itself.

  4. Add tests of the CREATEROLE attribute

  5. Replace explicit PIN entries in pg_depend with an OID range test.

  6. Shore up ADMIN OPTION restrictions.

  7. Add pg_has_role() family of privilege inquiry functions modeled after the

  8. Align GRANT/REVOKE behavior more closely with the SQL spec, per discussion