Re: [GENERAL] Per-row security

[Gurjeet Singh]

On 11/4/06, Alexander Staubo <[EMAIL PROTECTED]> wrote:
I am designing an application which requires fine-grained role-basedsecurity, where every logical object in the system has an ACL whichexpresses the permissions allowed by roles.
Have you considered viel for Postgres ()? Here's what it says in it's introduction:
Introduction
Veil is a data security add-on for Postgres. It provides an API
allowing you to control access to data at the row, or even column,
level. Different users will be able to run the same query and see
different results. Other database vendors describe this as a Virtual
Private Database.Although it is in Alpha, you should give it a try, as this will also help the project in testing the functionality in real world apps like yours.
Regards,-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com

Re: [GENERAL] per-row security

[Christopher Browne]

[EMAIL PROTECTED] (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."


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] per-row security

[Andrew Sullivan]

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  | [EMAIL PROTECTED]
Unfortunately reformatting the Internet is a little more painful 
than reformatting your hard drive when it gets out of whack.
--Scott Morris

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] per-row security

[Marc Munro]

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




signature.asc
Description: This is a digitally signed message part

[GENERAL] Per-row security

[Alexander Staubo]

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.


Implementing this on a high level is trivial, but it must scale, and  
scale extremely well. I have some fairly good solutions, but I'm not  
altogether satisfied with them, and a few extra eyeballs would be of  
enormous help. Of particular concern are optimizations related to  
filtering queries based on read permissions.


The model is as follows. The system is divided into realms, which are  
completely isolated areas in which a specific security policy is  
enforced; pretty much analogous to Windows domains or Kerberos  
realms. Objects in the system live in these realms, and users have  
permissions on these objects based on the role they are assigned in  
the realm. For example, a user may have the admin role in one realm,  
but an ordinary user role in another; admins may modify specific  
objects, ordinary users only read.


The realm table looks a bit like this:

create table realms (
  id serial primary key,
  schema_id integer references schemas (id)
);

The types of roles that can be applied within any given realm is  
defined by the realm schema. Schemas are domain-specific; for  
example, one schema may define the roles "Guest", "Privileged",  
"Administrator". Another schema may define the roles "Contributor",  
"Editor", "Moderator".  Roles from one schema may only be applied to  
objects in realms that use this schema.


create table schemas (
  id serial primary key,
  name text
);

create table roles (
  id serial primary key,
  schema_id integer references schemas (id),
  name text
);

Now, every object of every class has a master table, something like:

create table widgets (
  id serial primary key,
  realm_id integer references realms (id),
  title text
);

create table gadgets (
  ...
);

Then we have a table of permissions expressing each role's  
permissions on every object:


create table widget_permissions (
  widget_id integer references widgets (id),
  role_id integer references roles (id),
  read boolean,
  write boolean,
  delete boolean,
  ...
);

create table gadget_permissions (
  ...
);

And finally, we have the concept of realm membership; every user can  
be a member of any number of realms, and they are granted a single  
role in each realm.


create table memberships (
  id serial primary key,
  realm_id integer references realms (id),
  user_id integer references users (id),
  role_id integer references roles (id)
);

As for quantities: There is a limited number (<10) of schemas, a  
small number (<10) of roles per schema, a large (thousands) number of  
realms, a large (<500) number realm memberships per user, and fairly  
large (1 million+) number of objects.


Finding objects that are accessible by a given user involves a couple  
of joins:


select ...
from widgets
inner join widget_permissions on widget_permissions.widget_id =  
widgets.id

  and widgets_permission.read = true
  and widgets_role_id in (
select role_id
from memberships
where memberships.realm_id = widgets.realm_id
  and memberships.user_id = :user
  )

Not too bad, but those extra joins are incurred on every single  
query. There is another downside, which is that the result set of  
this query cannot return any information about the actual permissions  
on the object; an additional query is needed for that. Since the  
application relies on such data in order to display GUI elements  
related to editing and so on, this is a significant problem.


At the moment, this system has been implemented quite successfully,  
but with little thought to performance, optimistically assuming that  
optimizations can come up later. Later, it turns out, is now. We are  
looking at one optimization which makes the permission lists more  
compact -- and integrated as part of the result set -- at the expense  
of additional joins. We know that there is a small number of roles in  
each realm; if we assume that each role has a unique number, then we  
can use it as a bit index:


create table roles (
  id serial primary key,
  schema_id integer references schemas (id),
  name text,
  bit_index integer
);

Now we can express the entire ACL as a set of attributes on each object:

create table widgets (
  id serial primary key,
  realm_id integer references realms (id),
  title text,
  read_bitmap int,
  write_bitmap int,
  delete_bitmap int,
  ...
);

Now the join becomes:

select ...
from widgets
where exists (
  select memberships.id
  from memberships
  inner join realms on realms.id = memberships.realm_id
  inner join schemas on schemas.id = realms.schema_id
  inner join roles on memberships_role.id
  where memberships.user_id = :user
and widgets.read_bitmap & (1 << roles.bit_index) != 0
)

Now we can get read_bitmap, write_bitmap etc. as part of every result  
set, and use this to introspect every object's