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

Awesome! That never occurred to me. This is really cool.

Tom Lane wrote:
> Kenneth Downs <ken(at)secdat(dot)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
>