Thread: Re: [pgsql-general] Daily digest v1.6578 (20 messages)
On Sat, 2006-04-11 at 06:13 -0400, pgsql-general-owner@postgresql.org wrote: > Date: Fri, 3 Nov 2006 23:08:47 +0100 > From: Alexander Staubo <alex@purefiction.net> > To: PgSQL General <pgsql-general@postgresql.org> > Subject: Per-row security > Message-ID: <0994F30A-3519-4C21-BDD0-29BF2D19A384@purefiction.net> > > I am designing an application which requires fine-grained role-based > security, where every logical object in the system has an ACL which > expresses the permissions allowed by roles. A fairly cursory look at your proposed model suggests that it will work, but is likely to have serious performance problems. The issue is not so much the simple queries on single views, but the complex queries your developers will almost certainly build from them. A three-table join becomes a nine-table join, and planner, optimiser and executor all have to work very hard at that point. For an alternative approach, you might want to check out Veil: http://pgfoundry.org/projects/veil This is a postgres add-on designed to help you build row-level security implementations. I believe that with some rework, you could implement your proposed security model using Veil to good effect. Veil records privileges, etc in shared and session memory and so determining whether the connected user can see row x requires no extra fetches from the database. __ Marc
Attachment
On Nov 6, 2006, at 21:00 , Marc Munro wrote: > A fairly cursory look at your proposed model suggests that it will > work, > but is likely to have serious performance problems. The issue is > not so > much the simple queries on single views, but the complex queries your > developers will almost certainly build from them. A three-table join > becomes a nine-table join, and planner, optimiser and executor all > have > to work very hard at that point. Agreed. That said, I think there are optimizations that could ameliorate the situation by reducing the number of joins at the expense of reads. For example, filtering at the row level using bit masks would potentially incur more data reads by not being able to leverage any indexes, but this overhead may not be of a concern if the ratio of negatives (rows the caller is not permitted to read) is small. > I believe that with some rework, you could implement your proposed > security model using Veil to good effect. Veil records privileges, > etc > in shared and session memory and so determining whether the connected > user can see row x requires no extra fetches from the database. I would prefer to avoid tying myself to new and unproven third-party software, but I'm interested in trying out Veil. From what I can see it essentially implements a set of generic, efficient low-level bitmap index and hash primitives that can be used to implement a security framework such as ours. I like the fact that it does not, as far as I can see, force you into a specific security model. The demo app relies on views, rules and namespacing to implement transparent security, which is not a direction I am interested in going. It does look like you can pry this apart and use the functions directly. My biggest concern is that the whole framework is pretty much undocumented on the user side, and setting up even just a test case would require a lot of source reading, testing and so forth just to determine whether fits my own application. Could you give me a brief overview of the architecture and an example of a simplified bitmap setup? Alexander.
On Nov 6, 2006, at 21:00 , Marc Munro wrote: > For an alternative approach, you might want to check out Veil: > http://pgfoundry.org/projects/veil Addendum: I took Veil to be undocumented since the source archive only comes with Doxygen scripts; I thought the small "here" link on the Veil home page pointed to the same API docs, but it's actually a lot better than that. Apologies. Will Veil work in a replicated Slony-I setup? Alexander.
On Mon, 2006-06-11 at 22:27 +0100, Alexander Staubo wrote: > On Nov 6, 2006, at 21:00 , Marc Munro wrote: > > > For an alternative approach, you might want to check out Veil: > > http://pgfoundry.org/projects/veil > > Addendum: I took Veil to be undocumented since the source archive > only comes with Doxygen scripts; I thought the small "here" link on > the Veil home page pointed to the same API docs, but it's actually a > lot better than that. Apologies. No worries. Glad you found it in the end. Note though that the online documentation at pgfoundry is identical to that shipped with the source. > Will Veil work in a replicated Slony-I setup? I can see no reason why not. The fact that the security system triggers will be placed on the secured views rather than on the underlying tables should mean that Slony has less trigger manipulation to do than might otherwise be the case. You will of course be replicating the underlying tables and not the views, so your replication user will have to have full access to the unsecured data. This is natural and should not be a concern but may be worth explicitly documenting. __ Marc
Attachment
On Mon, Nov 06, 2006 at 01:40:18PM -0800, Marc Munro wrote:
> You will of course be replicating the underlying tables and not the
> views, so your replication user will have to have full access to the
> unsecured data. This is natural and should not be a concern but may be
> worth explicitly documenting.
In Slony, the replication user has to be a superuser anyway, so it
would have access to that data no matter what.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Unfortunately reformatting the Internet is a little more painful
than reformatting your hard drive when it gets out of whack.
--Scott Morris
ajs@crankycanuck.ca (Andrew Sullivan) wrote: > On Mon, Nov 06, 2006 at 01:40:18PM -0800, Marc Munro wrote: >> You will of course be replicating the underlying tables and not the >> views, so your replication user will have to have full access to the >> unsecured data. This is natural and should not be a concern but may be >> worth explicitly documenting. > > In Slony, the replication user has to be a superuser anyway, so it > would have access to that data no matter what. When Slony-II work was ongoing, replication was taking place from a deeper level inside the DB engine such that that took place as perhaps even a "superduperuser." A replication mechanism which captured updates from transaction logs would have "effectively more than superuser access", too. -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxfinances.info/info/languages.html Rules of the Evil Overlord #71. "If I decide to test a lieutenant's loyalty and see if he/she should be made a trusted lieutenant, I will have a crack squad of marksmen standing by in case the answer is no." <http://www.eviloverlord.com/>