Re: Implementing "access lists" (was: Must I use DISTINCT?) - Mailing list pgsql-sql

On Fri, Feb 6, 2009 at 1:11 AM, Stephan Szabo
<sszabo@megazone.bigpanda.com> wrote:
>
> On Thu, 5 Feb 2009, Michael B Allen wrote:
>
>> Please consider the following SQL
>>
>>   SELECT e.eid, e.name
>>   FROM entry e, access a
>>   WHERE e.eid = 120
>>   AND (e.ownid = 66 OR e.aid = a.aid)
>>
>> The intent is to match one entry with the eid of 120. However I would
>> like to impose an additional constraint that either e.ownid must be 66
>> or e.aid must match the aid of an entry in the access table (there's
>> actually a lot more to the query but I think this should be sufficient
>> to illustrate my problem).
>>
>> The problem is that the e.ownid is 66 and therefore the same entry is
>> returned for each access entry.
>>
>> Of course I can simply SELECT DISTINCT but that seems like an improper
>> usage of DISTINCT here.
>>
>> Is there an alternative way to write this query? I only want to select
>> from the access table for the purpose of constraining by aid.
>
> Would something like:
>  SELECT e.eid, e.name FROM entry e WHERE e.eid = 120 AND
>  (e.ownid = 66 OR e.aid in (select a.aid from access a))
> do what you wnat?

Indeed it does. Using sub-selects eliminates the duplicate entries.
Thanks Stephan.

However, now I have a deeper SQL challenge.

As I stated before, I'm ultimately trying to implement "access lists"
where I simply join on (or subselect across) a table of links that
link account and access list entries. If I only use one level of
indirection it's easy. However, I'd really like to see if I can
efficiently implement *two* levels of indirection. Meaning instead of
having an access list linked with account entries, I want to have an
access list linked with access lists that are linked with account
entries. This is much more useful because you can grant permissions to
lists of accounts. Otherwise, you have to constantly add and remove
individual accounts from access lists which would be very tedious.

This is the full "access list" query with two levels of indirection
(that uses subselects instead of joins):

SELECT e.eid, e.type, e.name, e.ownid, e.aclid
FROM foo_entry e
WHERE e.eid = 70   AND (e.ownid = 66       OR e.aclid = 66       OR e.aclid IN (SELECT a FROM foo_link WHERE data >= 10
AND(b = 66           OR b IN (SELECT a FROM foo_link WHERE b = 66)           OR b IN (SELECT b FROM foo_link WHERE a =
66)      ))       OR e.aclid IN (SELECT b FROM foo_link WHERE data >= 10 AND (a = 66           OR a IN (SELECT a FROM
foo_linkWHERE b = 66)           OR a IN (SELECT b FROM foo_link WHERE a = 66)       ))   )
 

So the above should return the foo_entry record with eid 70 but only
if the user's own account (the entry with an eid of 66) matches either
the ownid OR aclid OR the eid of an entry linked with an entry with an
eid of 66 OR linked with an entry that is linked with an entry with an
eid of 66. The 'data' field is the access level - the higher the
level, the more permission you have.

So my question is simply - can this query be reduced or optimized futher?

Another thing I'm wondering is if this query can scale. At some point
I'll just load a lot more records and find out. But I suspect there
are people that would already know if this is a hopeless exercise?

Here's the sample data again. There are only two tables:

> select eid, type, name, ownid, aclid from foo_entry;
+-----+------+---------------------------------------+-------+-------+
| eid | type | name                                  | ownid | aclid |
+-----+------+---------------------------------------+-------+-------+
|  64 |    5 | system                                |    64 |     0 |
|  66 |    5 | abaker                                |    66 |     0 |
|  67 |    5 | bcarter                               |    67 |     0 |
|  68 |    5 | cdavis                                |    68 |     0 |
|  69 |    7 | abaker@example.com                    |    66 |   114 |
|  70 |    7 | bcarter@example.com                   |    67 |   120 |
|  71 |    7 | cdavis@example.com                    |    68 |     0 |
| 113 |    6 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |    66 |     0 |
|  92 |   10 | IOPLEX Software                       |    64 |     0 |
| 114 |    8 | Small Cap Consultants                 |    66 |     0 |
| 120 |    8 | Equity Report Readers                 |    66 |     0 |

> select * from foo_link;
+-----+-----+----------+----------+------+
| a   | b   | weight_a | weight_b | data |
+-----+-----+----------+----------+------+
| 114 | 120 |    1.000 |    1.000 | 10   |
|  66 |  69 |    1.000 |    1.000 | NULL |
|  67 |  70 |    1.000 |    1.000 | NULL |
|  68 |  71 |    1.000 |    1.000 | NULL |
|  66 |  72 |    1.000 |    1.000 | NULL |
|  69 |  72 |    1.000 |    1.000 | NULL |
|  70 |  73 |    1.000 |    1.000 | NULL |
|  71 |  74 |    1.000 |    1.000 | NULL |
|  67 |  89 |    0.900 |    1.000 | NULL |
|  71 |  92 |    1.000 |    1.000 | NULL |
|  66 | 113 |    1.000 |    1.000 | NULL |
|  66 | 114 |    1.000 |    1.000 | NULL |
|  64 | 113 |    1.000 |    1.000 | NULL |
|  67 | 113 |    1.000 |    1.000 | NULL |
|  64 | 111 |    1.000 |    1.000 | NULL |
|  68 | 113 |    1.000 |    1.000 | NULL |

Mike

-- 
Michael B Allen
Java Active Directory Integration
http://www.ioplex.com/