Re: Something I don't understand with the use of schemas

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Regarding CREATEROLE, I wonder why is that a role with that privilege is
> able to create other roles containing any privileges (except
> superuserness), and not just the privileges the creating role has.

The point of CREATEROLE was to allow a role to do many of the things
you'd routinely need superuser status for (create/drop roles, change
group membership, fix forgotten passwords, etc) without having the
privileges that make superuserness so dangerous, ie, the ability to
inflict random alterations on system catalogs or tables you don't own.
If we were to try to restrict CREATEROLE to the point where it has no
ability to "escalate privileges" then I think we'd just destroy the
usefulness of the concept entirely, and people would go back to using a
superuser role for day-to-day administration. Example: such a
restriction would require that you can't grant membership in a group
unless you already are a member of same. But making your day-to-day
admin role be a member of every group isn't helpful, it's just a PITA,
and arguably it makes you less secure not more so (because your admin
role thereby gets privileges it probably doesn't need).

The design assumption here is really that the user of a CREATEROLE
account is the DBA, meaning he also has access to a superuser account.
Thus the idea of CREATEROLE is not to get in his way unnecessarily, but
just to make sure that he can't accidentally break the system when he
didn't intend to.

It's possible that we shouldn't have included the restrictions against
changing superuser accounts from a CREATEROLE account; that might be
contributing to a mistaken view about what CREATEROLE is for. You don't
give out CREATEROLE to anyone you don't trust.

Lastly: there already are mechanisms within SQL for the sort of
restricted administrator role you seem to be contemplating. Give
someone group membership WITH ADMIN OPTION, and you've got yourself
a mini admin role. I'm not sure we need another level between that
and CREATEROLE.

regards, tom lane