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