Re: HIPPA (was Re: Anyone know ...)

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