Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using schemas and groups for permissions

Using schemas and groups for permissions

From
"Walker, Jed S"
Date:

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



Re: Using schemas and groups for permissions

From
Tom Lane
Date:
"Walker, Jed S" <Jed_Walker@cable.comcast.com> writes:
> ... The problem is when I try to "alter group it
> add group it2" it doesn't work.

FWIW, this will be possible in PG 8.1.  Dunno if that helps you at all,
but depending on your timeframe for deploying this, it might be an
answer.  In any case you might not want to spend much time on a stopgap.

            regards, tom lane

Re: Using schemas and groups for permissions

From
"Walker, Jed S"
Date:
When will 8.1 be released?

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, June 30, 2005 9:34 AM
To: Walker, Jed S
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Using schemas and groups for permissions

"Walker, Jed S" <Jed_Walker@cable.comcast.com> writes:
> ... The problem is when I try to "alter group it add group it2" it
> doesn't work.

FWIW, this will be possible in PG 8.1.  Dunno if that helps you at all,
but depending on your timeframe for deploying this, it might be an
answer.  In any case you might not want to spend much time on a stopgap.

            regards, tom lane

Re: Using schemas and groups for permissions

From
Tom Lane
Date:
"Walker, Jed S" <Jed_Walker@cable.comcast.com> writes:
> When will 8.1 be released?

Probably this fall sometime.  Feature freeze is Monday, but it's always
hard to predict how long beta will last.

            regards, tom lane

Re: Using schemas and groups for permissions

From
"Walker, Jed S"
Date:
Thanks!

BTW, I assume you mean assigning groups to groups will be in 8.1?

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, June 30, 2005 9:46 AM
To: Walker, Jed S
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Using schemas and groups for permissions

"Walker, Jed S" <Jed_Walker@cable.comcast.com> writes:
> When will 8.1 be released?

Probably this fall sometime.  Feature freeze is Monday, but it's always
hard to predict how long beta will last.

            regards, tom lane