LDAP Groups + SQL Authorization

[Mike Loosbrock]

Excerpts from Alan DeKok's message of Mon Mar 22 11:48:40 -0500 2010:
> Mike Loosbrock wrote:
> 
> > I thought about getting the user's groups by fetching the multi-
> > valued 'memberOf' attribute from AD and then copying it to the
> > control list via ldap.attrmap. But I don't see any way to then
> > make rlm_sql use that attribute in an authorization query (at
> > least in any sort of useful manner).
> 
>   If it's an attribute, the SQL module can use it.  See "man unlang" for
> how attributes are addressed.
> 
>   SELECT ... from ... where %{control:My-Attr...}

You're right, though I forgot to mention I want to support multiple group
memberships. Building upon your idea, could I do something like this:

1.) Populate the usergroup table with one record for each group I want to
    support. (This lets me prioritize the groups).

2.) Use rlm_ldap to fetch group membership via the 'memberOf' AD attribute.

3.) Use ldap.attrmap to map 'memberOf' to control:My-Groups.

3.) Use a custom perl module to build a SQL query string that simply
    returns a record for each group in control:My-Groups. In pseudo-code:
      control:My-Query =
        SELECT groupname
        FROM ${usergroup_table}
        WHERE groupname IN ( '%{My-Groups[0]', '%{My-Groups[1]}', ... )
        ORDER BY priority

4.) In rlm_sql, set group_membership_query = "%{control:My-Query}".

Are steps 3 and 4 really as dirty and wrong as they look? What kind of
performance hit am I looking at?

-- 
Mike Loosbrock
Bethel University Network Services
651-638-6723