Using schemas and groups for permissions

From: "Walker, Jed S" <Jed_Walker(at)cable(dot)comcast(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Using schemas and groups for permissions
Date: 2005-06-30 15:18:26
Message-ID: 4A59B6AA01F1874283EA66C976ED51FC46613D@COENGEXCMB01.cable.comcast.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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,

Jed S. Walker

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2005-06-30 15:34:19 Re: Using schemas and groups for permissions
Previous Message Tom Lane 2005-06-30 13:16:40 Re: Aliased table names ...oddity?