Awesome! That never occurred to me. This is really cool.
Tom Lane wrote:
Kenneth Downs <ken@secdat.com> writes:
Perhaps a lesser form of CREATEROLE, CREATEROLE_LIMITED, who can create
roles and only grant to the roles he himself is a member of.
You can make that out of spare parts today, by granting non-superusers
execute rights on functions that create users.
regression=# create or replace function makeuser(text) returns void as $$
begin execute 'create role ' || quote_ident($1) || ' login';
end$$ language plpgsql security definer;
CREATE FUNCTION
regression=# revoke all on function makeuser(text) from public;
REVOKE
regression=# create user joe;
CREATE ROLE
regression=# grant execute on function makeuser(text) to joe;
GRANT
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> create user foo;
ERROR: permission denied to create role
regression=> select makeuser('foo');makeuser
----------
(1 row)
regression=> \c - foo
You are now connected to database "regression" as user "foo".
regression=>
regards, tom lane
