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

That's basically what I've done with my past questions on the ROLE system in
place. Since roles are global, I wanted it fine grained to the DB level so I
had to append DB_ in front of each role name and by using current_database()
inside my functions, I could hide that from the exterior.

Now I have a web administration panel that can let me administer users and
groups. Functions are synced with app permissions and given to groups only.
Then users are assigned to groups to give them their app permissions + db
function permissions. I had to create a couple extra tables to do the sync
between app permissions (view page X, do action Y) and the functions needed
for each of these app permission combos.

Users of my products will now be able to control all the users via that
panel, it also removes any superuser credentials on the app level as now the
DB users are used for the web app login too.

I'm very pleased to have learned this new database and its incorporation in
my apps and future apps.

David

On 3/10/07, Kenneth Downs <ken(at)secdat(dot)com> wrote:
>
> Awesome! That never occurred to me. This is really cool.
>
> Tom Lane wrote:
>
> Kenneth Downs <ken(at)secdat(dot)com> <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
>
>
>