SQL backend SELECT problem (1=1 causes horrible performance)

[Jakob Oestergaard <joe@sysorb.com>]

Dear list,

I have set up OpenLDAP with a PostgreSQL backend (I know this is not an 
optimal backend for LDAP data, but I have very good reasons why this is 
necessary).

In my database I have the following table (and of course all necessary 
LDAP tables etc.)

inetorgperson (
 id INTEGER NOT NULL,
 displayName TEXT,
 mail VARCHAR(64),
 o VARCHAR(128),
 PRIMARY KEY (id)
);

This table holds a number of person records, that I extract via. LDAP to 
a Thunderbird e-mail client using its address-book functionality. 
Everything works, so far so good.

However, when I search in the address book (eg. type in "foo@" in the 
search bar), the OpenLDAP SQL backend will create a query like:

SELECT DISTINCT ldap_entries.id,
 inetorgperson.id,'inetOrgPerson' AS objectClass,
 ldap_entries.dn AS dn
 FROM ldap_entries,inetorgperson
 WHERE inetorgperson.id=ldap_entries.keyval
 AND ldap_entries.oc_map_id=1
 AND upper(ldap_entries.dn) LIKE '%OU=CONTACTS,DC=EVALESCO,DC=COM'
 AND (1=1 AND ((upper(inetorgperson.mail) LIKE 'FOO@%')
      OR (upper(inetorgperson.displayName) LIKE '%FOO@%')
      OR 1=1 OR 1=1)
      );

The problem here, being the "OR 1=1" entries. This causes the *full* 
inetorgperson table to be returned to OpenLDAP on every query, instead 
of only the entries matching the searched-for string.

This, in turn, causes a lot of OpenLDAP CPU usage, a lot of tiny queries 
to the PostgreSQL backend, and the end result is that it takes about 
10-15 seconds for a query to return, while the actual select above 
takes less than 100ms to execute on the PostgreSQL backend.

As I understand it, it is the following code in search.c (in the LDAP 
SQL backend code) around line 700 that inserts the "1=1" clauses:
-------------------------
case LDAP_FILTER_EXT:
ad = f->f_mra->ma_desc;
if ( f->f_mr_dnattrs ) {
	/*
	* if dn attrs filtering is requested, better return 
	* success and let test_filter() deal with candidate
	* selection; otherwise we'd need to set conditions
	* on the contents of the DN, e.g. "SELECT ... FROM
	* ldap_entries AS attributeName WHERE attributeName.dn
	* like '%attributeName=value%'"
	*/
	backsql_strfcat_x( &bsi->bsi_flt_where,
			bsi->bsi_op->o_tmpmemctx,
			"l",
			(ber_len_t)STRLENOF( "1=1" ), "1=1" );
	bsi->bsi_status = LDAP_SUCCESS;
	rc = 1;
	goto done;
}
break;
-------------------------

Is this correct?  Or are the "OR 1=1" clauses generated elsewhere?

In any case, is there a way to configure the SQL backend to *not* insert 
those clauses?

I was wondering if there was a way to remove the search filters (by 
configuring slapd - because I would rather not have to patch 
Thunderbird).

Is there anything I can do with my LDAP metadata in the database to help 
the SQL LDAP backend to avoid those?

Any suggestions will be greatly appreciated - if there was anything 
important I didn't describe, please ask.

Thank you very much,

-- 
Best regards,
   Jakob Oestergaard