I'm looking for help with a design issue. If I was working in Oracle I know how I'd handle this situation, but I'm not sure in postgres because of some differences in security.
Our application only uses procedure calls for all data retrieval and modification. We want to have certain "groups" that allow access to areas of the system. We also want "groups" that are functional groups within the user community. We want to be able to assign the "access" groups to te "user" groups. The problem is when I try to "alter group it add group it2" it doesn't work. In addition, we want the access groups to allow appropriate functions to be run and we want the GUI to be able to see what access groups the user has (through their user group) to determine what things in the GUI are visible.
My thoughts at this point are to have schemas that represent each "access" group. When appropriate these schemas would have functions in them for database access. To grant access to user groups you would grant usage on the appropriate schema.
In general I think this will work well, the only catch I see is that many "read" functions will be share across many of the "access" groups (schemas) and some write functions will be as well. I am thinking we could have dummy functions in each access schema that just contain calls to the original function in some main schema, but that could be a bit awkward.
Does anyone have any good ideas on how to handle this (I hope I made sense)?
Thanks,