Re: Implementing ACLs in Pure SQL? - Mailing list pgsql-sql

On Aug 29, 2008, at 5:33 PM, Michael B Allen wrote:

> Hello,
>
> I've been tinkering with SQL level access control for web
> applications. Currently I have a UNIX style uid / gid for each record
> and do a WHERE e.uid IN (10,20,30) where 10, 20 and 30 would be the  
> IDs
> of groups the user is in.
>
> However, I'm not satisfied with this model as it has all of the  
> problems
> UNIX style permissions have. In particular you can only have one  
> group on
> each record which ultimately leads you into a few quantized levels of
> privilege.
>
> It would be much better if there were a way to implement ACLs.
>
> Meaning - given a user with the following groups (again using IDs
> instead of names):
>
>  user_groups:
>    10
>    20
>    30
>
> and an ACL with groups:
>
>  acl_groups:
>    18
>    19
>    20
>    21
>
> an access control check is performed with the following pseudocode:
>
>  foreach (acl_groups as ag) {
>      foreach (user_groups as ug) {
>          if (ug == ag) {
>              return true
>        }
>    }
>  }
>  return false;
>
> Meaning, groups 18, 19, 20 and 21 are allowed to access the resource
> protected by the ACL (the database record). When group 20 is reached  
> by
> the outer loop and the inner loop finds 20 in the list of groups the
> user is in, the above example would return true to indicate that the
> particular user should be granted access to the record (i.e. the WHERE
> clause would match).
>
> There is one way to do this. Each record has an ACL field with a  
> string
> like '+18+19+20+21+':
>
>  UPDATE e SET acl_groups = '+18+19+20+21+' WHERE eid = 1001
>
> Then to perform the access check and retrieve the record you do:
>
>  SELECT * FROM e WHERE
>      (e.acl_groups LIKE '%+10+%'
>      OR e.acl_groups LIKE '%+20+%'
>      OR e.acl_groups LIKE '%+30+%')
>
>  INSERT INTO e (name, color) VALUES ('Alice', 'blue') WHERE eid = 1001
>      AND (e.acl_groups LIKE '%+10+%'
>      OR e.acl_groups LIKE '%+20+%'
>      OR e.acl_groups LIKE '%+30+%')
>
>  ... etc
>
> Using LIKE is a little inefficient but I assume it would be more  
> efficient
> than retrieving all of the records and performing the access check in
> loop within the application.
>
> Can anyone suggest a superior method?
>
> Or any other ideas regarding implementing ACLs in SQL would be greatly
> appreciated.
>
> Mike
>

Hey Mike,

currently I am underway implementing this with pure SQL, but I don't  
have groups
like unix groups.

Each group has a specific meaning. For example a group marketing,
or a group marketing admin. They are just names and it's up to the  
application to implement
what action to take.

In my case I am disabling and enabling specific objects within a adobe  
flex interface
and subgroups are handles with trees.

Then in that case a user can be member of specific groups (or sub  
groups) in and
my case it would show what application you can access, or what objects  
are allowed for this user.

Current I have a user table, a group table and an applciation table  
and a object table
Then two MM tables between user and application/object and group and  
application/object.

Then in plpgsql I resolve the correct ACL for a user.


Ries