Using DN-valued attributes in search filters for back-sql

["Pierangelo Masarati" <ando@sys-net.it>]

This may e of interest to back-sql administrators.
I incidentally figured out a clean way to use DN-valued
attributes in filters.  Excuse me if this already known,
however this was not true with the examples that come
with the software, and I couldn't find any mention in
the mailing lists.

The essential problem, which I recall someone mentioned
in a posting I can't find right now, is that when filtering
for DN-valued attributes, the meta-table "ldap_entries"
is joined twice: one to procide the entry's DN and another
for the entry's DN-valued attribute.

Consider, for example, the "documentAuthor" attribute in
the examples: it requires to connect "documents" and
"persons" primary tables, "authors_docs" connection table,
and "ldap_entries" for the value, which is the DN of the
author.

The code currently detects multiple joins of the same table
and deletes the duplicates, resulting in an overconstrained
condition that yields no results.

The correct answer actually requires the double joining of
the same table, by using an alias in the attribute mapping.
Continuing with the same example, the original form of the
significant columns was:

sel_expr   = ldap_entries.dn
from_tbls  = ldap_entries,documents,authors_docs,persons
join_where = ldap_entries.keyval=documents.id AND
             ldap_entries.oc_map_id=2 AND
             authors_docs.doc_id=documents.id AND
             authors_docs.pers_id=persons.id

It must be recast in

sel_expr   = documentIdentifier.dn
from_tbls  = ldap_entries AS documentIdentifier,
             documents,authors_docs,persons
join_where = documentIdentifier.keyval=documents.id AND
             documentIdentifier.oc_map_id=2 AND
             authors_docs.doc_id=documents.id AND
             authors_docs.pers_id=persons.id

A search

ldapsearch -b "o=sql,c=RU" "documentAuthor=cn=Mitya Kovalev,o=sql,c=RU"

against Postgres default example code results in the query

SELECT DISTINCT
        ldap_entries.id,
        documents.id,
        text('document') AS objectClass,
        ldap_entries.dn
FROM
        ldap_entries,
        documents,
        authors_docs,
        persons
WHERE
        documents.id=ldap_entries.keyval AND
        ldap_entries.oc_map_id=2 AND
        upper(ldap_entries.dn) LIKE upper('%O=SQL,C=RU') AND
        ldap_entries.keyval=persons.id AND
        ldap_entries.oc_map_id=1 AND
        authors_docs.doc_id=documents.id AND
        authors_docs.pers_id=persons.id AND
        (upper(ldap_entries.dn)='CN=MITYA KOVALEV,O=SQL,C=RU')

the modified form results in

SELECT DISTINCT
        ldap_entries.id,
        documents.id,
        text('document') AS objectClass,
        ldap_entries.dn AS dn
FROM
        ldap_entries,
        documents,
        ldap_entries as documentAuthor,                          <<<
        authors_docs,
        persons
WHERE
        documents.id=ldap_entries.keyval AND
        ldap_entries.oc_map_id=2 AND
        upper(ldap_entries.dn) LIKE upper('%O=SQL,C=RU') AND
        documentAuthor.keyval=persons.id AND                     <<<
        documentAuthor.oc_map_id=1 AND                           <<<
        authors_docs.doc_id=documents.id AND
        authors_docs.pers_id=persons.id AND
        (upper(documentAuthor.dn)='CN=MITYA KOVALEV,O=SQL,C=RU') <<<

the change is very little intrusive and of great impact.
I currently applied it to the Postgres examples, because
I could test it immediately.

PS: to try this you also need to comment out the code that
decides whether to uppercase or not attribute values based
on their matching rules.  Apparently it is flawed, because
it decides not to uppercase the DN.  I currently removed it
from HEAD, until I figure out a more reliable solution.

cvs diff -u servers/slapd/back-sql/search.c -r 11.41 -r .42

for the patch.

p.

-- 
Pierangelo Masarati
mailto:pierangelo.masarati@sys-net.it