On Thu, Mar 10, 2022 at 7:46 AM Robert Haas <robertmh...@gmail.com> wrote:
> On Wed, Mar 9, 2022 at 4:31 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > 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. > > > > 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 don't think that's entirely the wrong concept, but it doesn't make a > lot of sense in a world where the creator has to be a superuser. If > alice, bob, and charlie are superusers who take turns creating new > users, and then we let charlie go due to budget cuts, forcing alice > and bob to change the owner of all the users he created to some other > superuser as a condition of dropping his account is a waste of > everyone's time. They can do exactly the same things to every account > on the system after we change the role owner as before. > Then maybe we should just implement the idea that if a superuser would become the owner we instead substitute in the bootstrap user. Or give the DBA the choice whether they want to retain knowledge of specific roles - and thus are willing to accept the "waste of time". > But wait, I hear you cry, what about CREATEROLE? Well, CREATEROLE is > generally agreed to be broken right now, and if you don't agree with > that, consider that it can grant pg_execute_server_programs to a > newly-created account and then explain to me how it's functionally > different from superuser. CREATEROLE has long been defined as basically having "with admin option" on every role in the system. The failure to special-case the roles that grant different aspects of superuser-ness to its members doesn't make CREATEROLE itself broken, it makes the implementation of pg_execute_server_programs broken. Only superusers should be considered to have with admin option on these roles. They can delegate through the usual membership+admin mechanism to a CREATEROLE role if they desire. > The whole area needs a rethink. I believe > everyone involved in the discussion on the other threads agrees that > some reform of CREATEROLE is necessary, and more generally with the > idea that it's useful for non-superusers to be able to create roles. > As the documentation says, using SUPERUSER for day-to-day administration is contrary to good security practices. Role management is considered to be a day-to-day administration activity. I agree with this principle. It was designed to neither be a superuser nor grant superuser, so removing the ability to grant the pg_* role memberships remains consistent with its original intent. > I want that because I want mini-superusers, where alice can administer > the users that alice creates just as if she were a superuser, > including having their permissions implicitly and dropping them when > she wants them gone, but where alice cannot break out to the operating > system as a true superuser could do. CREATEROLE (once the pg_* with admin rules are fixed) + Ownership and rules restricting interfering with another role's objects (unless superuser) seems to handle this. > the bot can stand up > accounts, can grant them membership in a defined set of groups, but > cannot exercise the privileges of those accounts (or hack superuser > either). The bot should be provided a security definer procedure that encapsulates all of this rather than us trying to hack the permission system. This isn't a user permission concern, it is an unauthorized privilege escalation concern. Anyone with the bot's credentials can trivially overcome the third restriction by creating a role with the desired membership and then logging in as that role - and there is nothing the system can do to prevent that while also allowing the other two permissions. > And that's why I'm not sure it's really the right idea to say that we > don't need syntax for this admin-without-member concept. We already have this syntax in the form of CREATEROLE. But we do need a fix, just on the group side. We need a way to define a group as having no ADMINS. ALTER ROLE pg_superuser WITH [NO] ADMIN; Then adding a role membership including the WITH ADMIN OPTION can be rejected, as can the non-superuser situation. Setting WITH NO ADMIN should fail if any existing members have admin. You must be a superuser to execute WITH ADMIN (maybe WITH NO ADMIN as well...). And possibly even a new pg_* role that grants this ability (and maybe some others) for use by a backup/restore user. Or just special-case pg_* roles. The advantage of exposing this to the DBA is that they can then package pg_* roles into a custom group and still have the benefit of superuser only administration. In the special-case implementation the presence of a pg_* role in a group hierarchy would then preclude a non-superuser from having admin on the entire tree (the pg_* roles are all roots, or in the case of pg_monitor, directly emanate from a root role). David J. David J.
