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@secdat.com> wrote: 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
