Re: SUPERUSER vs CREATEUSER causes foul 'code smell'

"=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" <rdeleonp(at)gmail(dot)com> writes:
> On 8/16/07, Joshua_Kramer <josh(at)globalherald(dot)net> wrote:
>> If I want to create a role who can create other roles, but not have other
>> SUPERUSER priveleges - how can I do that?

> create role foo createrole login password 'foo';

CREATEROLE privilege is not exactly a superuser, but it implies a lot of
administrative privileges anyway --- for instance the ability to change
the password of another account. So this may not be exactly what the OP
wants. We intended CREATEROLE as a way that a DBA could do all his
routine account-management duties without being a "real" superuser (with
the attendant ability to completely destroy the system with a slipup).
Think of it as sudo that lets you do anything you want to /etc/passwd,
but not "rm -rf /"...

If you want exactly what was stated --- the ability to create new roles,
and nothing else --- the best way is to create a function that does only
that one thing, make it superuser-owned and SECURITY DEFINER, and grant
execute privilege on it to whomever you want to be able to do that.
(Don't forget to revoke the default public execute privilege.) Gateway
functions of this sort are the standard solution whenever the system's
classification of privileges isn't quite what you want.

regards, tom lane