Обсуждение: Setting up a fine-grained permission system

Hi,

Our current project requires a fine-grained permission system (row-level
and possibly column-level as well). We have a pretty large (tens of
thousands) of users in the 'party' table. I'm thinking of choosing
Unix-style security for now (adding 'ugo' and 'owner' and 'group'
columns to each table which access need to be regulated), but am unsure
about the column-level permission.

Anyone has experiences to share on a similar system/requirement? Do you
do Unix-style or ACL? Is there a possibility in the medium/far future
that Postgres will have such a fine-grained permission system.

Regards,
Dave

David Garamond <lists@zara.6.isreserved.com> writes:

> Hi,
>
> Our current project requires a fine-grained permission system (row-level
> and possibly column-level as well). We have a pretty large (tens of
> thousands) of users in the 'party' table. I'm thinking of choosing
> Unix-style security for now (adding 'ugo' and 'owner' and 'group'
> columns to each table which access need to be regulated), but am unsure
> about the column-level permission.

What about creating different views of the table, containing different
columns, with appropriate ACLs?

-Doug

Hi all.

Implimenting a custom permission system is fairly easy to do with
triggers, views, and rules.

Here is my suggestion.  Put your data tables in a shadow schema and
don't give users access to them.  Then create views that select the
information from the tables that they have access to.  denied columns
could be filled in with NULLs or **** or something else.  Denied rows
could simply be omitted.  As for updating and inserting, you can do your
own permission schemes here too with triggers checking them and
providing the needed logic.

Best Wishes,
Chris Travers
Metatron Technology Consulting

David Garamond wrote:

>Hi,
>
>Our current project requires a fine-grained permission system (row-level
>and possibly column-level as well). We have a pretty large (tens of
>thousands) of users in the 'party' table. I'm thinking of choosing
>Unix-style security for now (adding 'ugo' and 'owner' and 'group'
>columns to each table which access need to be regulated), but am unsure
>about the column-level permission.
>
>Anyone has experiences to share on a similar system/requirement? Do you
>do Unix-style or ACL? Is there a possibility in the medium/far future
>that Postgres will have such a fine-grained permission system.
>
>Regards,
>Dave
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>
>
>

Dave,
Sorry to be so late in responding to this but I may have just the
solution for you.

Please check out Veil at pgfoundry.  This is an add-on to Postgres that
I think does just what you are looking for.  As the developer of this
project, I would be pleased to offer you assistance.

http://veil.projects.postgresql.org/


__
Marc

> Date: Thu, 29 Sep 2005 10:36:23 +0700
> From: David Garamond <lists@zara.6.isreserved.com>
> To: pgsql-general@postgresql.org
> Subject: Setting up a fine-grained permission system
> Message-ID: <433B6137.3070103@zara.6.isreserved.com>
>
> Hi,
>
> Our current project requires a fine-grained permission system
(row-level
> and possibly column-level as well). We have a pretty large (tens of
> thousands) of users in the 'party' table. I'm thinking of choosing
> Unix-style security for now (adding 'ugo' and 'owner' and 'group'
> columns to each table which access need to be regulated), but am
unsure
> about the column-level permission.
>
> Anyone has experiences to share on a similar system/requirement? Do
you
> do Unix-style or ACL? Is there a possibility in the medium/far future
> that Postgres will have such a fine-grained permission system.
>
> Regards,
> Dave

IMHO, Veil is very strange project. Instead of concentrating on good
support of updatable views, developers are trying to reinvent the
wheel. Actually, if restriction-and-projection views would be
updatable w/o overhead (such as creating rules), there'll no need in
such project. It's one of the major roles of views - provide mechanism
to secure the data.

Am I right?

On 13/10/05, Marc Munro <marc@bloodnok.com> wrote:
> Dave,
> Sorry to be so late in responding to this but I may have just the
> solution for you.
>
> Please check out Veil at pgfoundry.  This is an add-on to Postgres that
> I think does just what you are looking for.  As the developer of this
> project, I would be pleased to offer you assistance.
>
> http://veil.projects.postgresql.org/
>
>
> __
> Marc
>
> > Date: Thu, 29 Sep 2005 10:36:23 +0700
> > From: David Garamond <lists@zara.6.isreserved.com>
> > To: pgsql-general@postgresql.org
> > Subject: Setting up a fine-grained permission system
> > Message-ID: <433B6137.3070103@zara.6.isreserved.com>
> >
> > Hi,
> >
> > Our current project requires a fine-grained permission system
> (row-level
> > and possibly column-level as well). We have a pretty large (tens of
> > thousands) of users in the 'party' table. I'm thinking of choosing
> > Unix-style security for now (adding 'ugo' and 'owner' and 'group'
> > columns to each table which access need to be regulated), but am
> unsure
> > about the column-level permission.
> >
> > Anyone has experiences to share on a similar system/requirement? Do
> you
> > do Unix-style or ACL? Is there a possibility in the medium/far future
> > that Postgres will have such a fine-grained permission system.
> >
> > Regards,
> > Dave
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQBDTqpmUBr6u+c2wkERAsIvAJ4lCkYF+L9mYCqs8sPLOjEPsCn/OQCfTXUA
> TjtCjjbIrG4907a2tLHfKE8=
> =PSLJ
> -----END PGP SIGNATURE-----
>
>
>


--
Best regards,
Nikolay