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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Kenneth Downs <ken(at)secdat(dot)com>
Cc: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>, pgsql-general(at)postgresql(dot)org
Subject: Re: HIPPA (was Re: Anyone know ...)
Date: 2007-03-10 17:23:51
Message-ID: 16934.1173547431@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kenneth Downs 2007-03-10 17:26:43 Re: HIPPA (was Re: Anyone know ...)
Previous Message Tom Lane 2007-03-10 17:14:19 Re: unary operators, precedence, grouping