(Category) (Category) OpenLDAP Faq-O-Matic : (Category) OpenLDAP Software FAQ : (Category) Configuration : (Category) SLAPD Configuration : (Category) Backends : (Category) The SQL backend (How do I setup/configure back-sql?)

Introduction

This note was written at the time OpenLDAP 2.2 became ready for release; back-sql was first released with OpenLDAP 2.0; when moving from 2.0 to 2.1, it was entirely rewritten. When the original author wrote it, he provided support for many RDBMSes; however, the current maintainers could actively preserve only part of them, due to lack of resources.

SQL itself should be a standard language. Since different implementors result in different behavior and supported features, unixODBC has been used in back-sql to provide a neutral interface to the RDBMSes. Unfortunately, this was not enough; as a consequence some work might be required to have a working back-sql.

Supported RDBMSes

back-sql, in OpenLDAP 2.2, is actively supported only for (in alphabetical order) IBM db2/udb, MySQL, and Postgres. There used to be provisions also for mssql, oracle and timesten, but they haven't been officially tested after transition from OpenLDAP 2.0 to 2.1, so it's up to the final user to take care of issues that may arise. If you succeed in using any of the currently unsupported RDBMSes, or any other RDBMS not listed here, please let us know, and you'll do a favor to the Open Source community if you send us any detail or changes required by the software distribution. The preferred means is by opening an ITS.

How to Use It

To use back-sql, one needs to:
  • set up the RDBMS appropriately;
  • set up the mapping meta-data appropriately;
  • set up unixODBC (or any other library providing ODBC services) appropriately.
The term "appropriately" is of course vague; however, setting up all these things requires many operations that vary from RDBMS to RDBMS and entail so many details specific to every installation (and, besides all, are not part of OpenLDAP use), so they are left to the user. Refer to your RDBMS documentation and to unixODBC documentation for details.

Useful Links

The internet is full of hints on how to setup OpenLDAP with back-sql and different RDBMS; although the quality of these documents is not always homogeneous, or they refer to outdated versions of the software, they can still be of help. Just google for back-sql, or follow any of these links: They may be outdated, no longer available or contain erroneous information; I'm not responsible for what they say, so use at own risk! No need to say that credits go to the respective Authors.

Meta-Data

Meta-data is the most important and difficult part of setting up back-sql; basically, it consists in:
  • ldap_oc_mappings: how rows of some tables are mapped to entries with a given structuralObjectClass;
  • ldap_attr_mappings: how attributeTypes of an objectClass are resolved from RDBMS data;
  • ldap_entries: what's the DN of an entry, and how the entry relates to its objectClass mapping and to its parent DN;
  • ldap_entry_objclasses: what auxiliary objectClasses an entry has;
  • ldap_referrals: what entries must be treated as referrals.

NOTE: the objectClass names used in ldap_oc_mappings and the attributeType names used in ldap_attr_mappings MUST have been already loaded in slapd's schema by loading the appropriate schema files. Example: if your meta-data defines an objectClass inetOrgPerson with an attributeType sn, you need to load core.schema for sn and inetorgperson.schema for inetOrgPerson.

NOTE: the ldap_referrals table is no longer used in OpenLDAP 2.3; referrals should be defined as user data, i.e. the URI should be stored somewhere in the database and a referral objectClass should be defined in the ldap_oc_mappings table. See the example data for pgsql as a guideline.

Test Data

If you already have a working database, filled with data, you only need to add the metadata; otherwise, you may want to try the test data that is available in $SRC/servers/slapd/back-sql/rdbms_depend/$SUPPORTED_RDBMS. You need to load: (the links refer to the Postgres version of the meta-data). After the first two sets of SQL instructions, testdb_create.sql and testdb_data.sql, are executed, you must be able to perform SQL operations on the RDBMS via its own tools. If you are not, fix this before going ahead with meta-data.

In $SRC/servers/slapd/back-sql/rdbms_depend/$SUPPORTED_RDBMS there is also a sample slapd.conf file set up for use with that RDBMS (see for instance the one for Postgres).

Multiple objectClass definitions are allowed for an entry; however, only one can be "structural" in a back-sql sense. This means that each entry must refer to only one row of ldap_oc_mappings. This parallels with the fact that each LDAP entry has only one structuralObjectClass, which will correspond to that objectClass. Auxiliary objectClasses can be added on a per-entry basis by means of the ldap_entry_objclasses.

Multiple attributeType definitions are allowed for an entry; that is, multiple ldap_attr_mappings rows can refer to the same ldap_oc_mappings row with the same name; the resulting attribute values are honored for multivalued attributes in search filters, in search results, in compare AVAs. However, only rules according to the first instance of that attributeType are followed in add, modify and delete operations. This limitation, under certain circumstances, may be removed in the future.

New Features

back-sql, from 2.2.5, honors objectClass inheritance in search filters; so, for instance, a search for (objectClass=person) is honored also if an entry has a structuralObjectClass of inetOrgPerson (as the entries in the test data); no objectClass inheritance is supported for auxiliary objectClasses yet. From 2.2.5, also attributeType inheritance is honored in search filters.

Filter equality matches on attributeType with DN syntax can be honored by back-sql if those attributes are defined appropriately. Take the steps:

  • use the attribute name in the sel_expr field; e.g. documentAuthor.dn instead of ldap_entries.dn;
  • alias the ldap_entries table with the attribute name in the from_tbls field; e.g. ldap_entries AS documentAuthor;
  • use the attribute name in the join_where field as required, when there is need to reference the table it comes from, instead of ldap_entries; e.g. documentAuthor.keyval=persons.id AND documentAuthor.oc_map_id=1 AND ... instead of ldap_entries.keyval=persons.id AND ldap_entries.oc_map_id=1 AND ...;
The from_tbls may require more joins, and the join_where may require more conditions in order to appropriately select the required data; see the documentAuthor example in testdb_metadata.sql as a guideline.
ando@sys-net.it

Testing

OpenLDAP 2.3 provides a test suite for back-sql which is separate from the test suite for regular storage backends.

By default back-sql tests are disabled; they must be explicitly enabled, because they require some user interaction in setting up the RDBMS and feeding it with the appropriate data. To run the tests:

  • set up the desired RDBMS;
  • define the environment variable SLAPD_USE_SQL=<rdbms>, where <rdbms> is the type of RDBMS in use. This is necessary because the slapd.conf may require specific tweakings. Currently, only mysql, ibmdb2 and pgsql are supported;
  • populate the RDBMS with the data available in servers/slapd/back-sql/rdbms_depend/<rdbms>/;
  • to enable write tests, define the environment variable SLAPD_USE_SQLWRITE=yes; only ibmdb2 and pgsql currently support them. Note that, in some cases, you may need to manually restore the contents of the database using the files in servers/slapd/back-sql/rdbms_depend/<rdbms>/;
  • to run all tests, execute
            cd tests
            make sql
    
  • to run a specific test only, execute
            cd tests
            ./run sql-test<name>
    
    where <name> is the name of the test, or its leading portion, if unique. Tests are usually named using three digits and a descriptive string; to run the test, the digits suffice.

Please report bugs using the ITS.
ando@sys-net.it

"Supported" RDBMSes (routinely tested during development):
(Answer) IBM db2/udb
(Answer) MySQL
(Answer) PostgreSQL (default)


Formerly supported RDBMSes (not tested during development, but known to work at some time):

 mssql
 oracle
 timesten


(Answer) Help in supporting other RDBMS is welcome
(Answer) Need back-sql support for free?
(Answer) New Item
(Answer) New Item
(Answer) New Item

[New Answer in "The SQL backend (How do I setup/configure back-sql?)"]
Previous: (Answer) The passwd backend (How do I setup/configure back-passwd?)
Next: (Answer) config backend configuration
This document is: http://www.openldap.org/faq/index.cgi?file=978
[Search] [Appearance] [Show This Entire Category]
This is a Faq-O-Matic 2.721.test.
© Copyright 1998-2013, OpenLDAP Foundation, info@OpenLDAP.org