Full_Name: Manny Version: 2.4.23 OS: RHEL6 URL: ftp://ftp.openldap.org/incoming/ Submission from: (NULL) (193.171.77.1) Hi there. I'm posting this into the ITS as I didn't get a response on the mailing list after 1 week. I'm using the latest stable release of openldap, with back-sql and postgresql as a backend. I have an sssd which uses this openldap server for ID providing and authentication. A recent update in this sssd changed the filter used to retrieve groupids of users, which surfaced what seems to be a bug in backsql. From my investigation it seems to me that when constructing the search query, openldap tries to use the UPPER function on every criteria in the WHERE clause, no matter which type it is. This causes an error in postgresql, as the gidNumber that is supposed to be filtered is of type "bigint". I could simply remove "upper_func" from my slapd.conf, but I need it for other queries. Changing the field gidNumber in the database to text would also be a workaround, but I'd rather prefer this fixed in backsql. Anyways, here is the important part of my slapd.conf: #----------------------------------------------- database sql suffix "dc=mydomain,dc=at" rootdn "cn=Manager,dc=mydomain,dc=at" rootpw mysecret dbname mydbname dbuser mydbuser dbpasswd myuserpw subtree_cond "UPPER(ldap_entries.dn) LIKE UPPER('%'||?)" insentry_stmt "insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values ((select max(id)+1 from ldap_entries),?,?,?,?)" upper_func "upper" upper_needs_cast "yes" strcast_func "text" concat_pattern "?||?" has_ldapinfo_dn_ru no sizelimit -1 #----------------------------------------------- The error can be reproduced in my case when running this command: #----------------------------------------------- ldapsearch -H ldaps://127.0.0.1/ -b ou=groups,ou=myserver,dc=mydomain,dc=at -D "uid=myuser,ou=users,ou=myserver,dc=mydomain,DC=at" '(gidNumber=512)' -W #----------------------------------------------- Which results in this output: #----------------------------------------------- # extended LDIF # # LDAPv3 # base <ou=groups,ou=myserver,dc=mydomain,dc=at> with scope subtree # filter: (gidNumber=512) # requesting: ALL # # search result search: 2 result: 80 Other (e.g., implementation specific) error #----------------------------------------------- Here is the interesting part of the level 255 log output: #----------------------------------------------- <==backsql_oc_get_candidates(): 0 ==>backsql_oc_get_candidates(): oc="sambaDomain" ==>backsql_srch_query() ==>backsql_process_filter() <==backsql_process_filter() succeeded <==backsql_srch_query() returns SELECT DISTINCT ldap_entries.id,samba_domain_ldap.id,text('sambaDomain') AS objectClass,ldap_entries.dn AS dn FROM ldap_entries,samba_domain_ldap WHERE samba_domain_ldap.id=ldap_entries.keyval AND ldap_entries.oc_map_id=? AND UPPER(ldap_entries.dn) LIKE UPPER('%'||?) AND 7=7 Constructed query: SELECT DISTINCT ldap_entries.id,samba_domain_ldap.id,text('sambaDomain') AS objectClass,ldap_entries.dn AS dn FROM ldap_entries,samba_domain_ldap WHERE samba_domain_ldap.id=ldap_entries.keyval AND ldap_entries.oc_map_id=? AND UPPER(ldap_entries.dn) LIKE UPPER('%'||?) AND 7=7 id: '6' (sub)dn: "%OU=GROUPS,OU=MYSERVER,DC=MYDOMAIN,DC=AT" <==backsql_oc_get_candidates(): 0 ==>backsql_oc_get_candidates(): oc="inetOrgPerson" ==>backsql_srch_query() ==>backsql_process_filter() ==>backsql_process_filter_attr(gidNumber) <==backsql_process_filter_attr(gidNumber) <==backsql_process_filter() succeeded <==backsql_srch_query() returns SELECT DISTINCT ldap_entries.id,OS_USER.id,text('inetOrgPerson') AS objectClass,ldap_entries.dn AS dn FROM ldap_entries,OS_USER WHERE OS_USER.id=ldap_entries.keyval AND ldap_entries.oc_map_id=? AND UPPER(ldap_entries.dn) LIKE UPPER('%'||?) AND (upper(OS_USER.gidnumber)='512') Constructed query: SELECT DISTINCT ldap_entries.id,OS_USER.id,text('inetOrgPerson') AS objectClass,ldap_entries.dn AS dn FROM ldap_entries,OS_USER WHERE OS_USER.id=ldap_entries.keyval AND ldap_entries.oc_map_id=? AND UPPER(ldap_entries.dn) LIKE UPPER('%'||?) AND (upper(OS_USER.gidnumber)='512') id: '8' (sub)dn: "%OU=GROUPS,OU=MYSERVER,DC=MYDOMAIN,DC=AT" backsql_oc_get_candidates(): error executing query Return code: -1 nativeErrCode=7 SQLengineState=S1000 msg="[unixODBC]ERROR: function upper(bigint) does not exist at character 288;#012Error while executing the query" #----------------------------------------------- Thanks a lot
> Full_Name: Manny > Version: 2.4.23 > OS: RHEL6 > URL: ftp://ftp.openldap.org/incoming/ > Submission from: (NULL) (193.171.77.1) > > > Hi there. > > I'm posting this into the ITS as I didn't get a response on the mailing > list > after 1 week. > > I'm using the latest stable release of openldap, with back-sql and > postgresql as a backend. > I have an sssd which uses this openldap server for ID providing and > authentication. > A recent update in this sssd changed the filter used to retrieve > groupids of users, which surfaced what seems to be a bug in backsql. > >>From my investigation it seems to me that when constructing the search > query, openldap tries to use the UPPER function on every criteria in > the WHERE clause, no matter which type it is. This causes an error in > postgresql, as the gidNumber that is supposed to be filtered is of > type "bigint". I don't recall seeing your message in openldap-technical, which it belongs to. In any case, there should be a field in ldap_at_mappings that tells how and when an attribute value needs to be uppercased. However, I can't check right now whether it works as intended, and back-sql is unmaintained. p.
I'm sorry, I send my issue to the wrong mailinglist at first (to the bugs list) and then tried to send it to technical twice. It doesnt seem to go through. Do you have any idea which column it is that defines this behaviour? I can't seem to find it..
changed notes changed state Open to Suspended
not (yet) a bug
changed state Suspended to Open moved from Incoming to Software Bugs
patches welcome