Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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

Does anyone have any good ideas on how to handle this (I hope I made


		Jed S. Walker


pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group