Обсуждение: 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