SLAPD-SQL(5)SLAPD-SQL(5)NAMEslapd-sql - SQL backend to slapd
SYNOPSIS
ETCDIR/slapd.conf
DESCRIPTION
The primary purpose of this slapd(8) backend is to PRESENT
information stored in some RDBMS as an LDAP subtree with-
out any programming (some SQL and maybe stored procedures
can't be considered programming, anyway ;).
That is, for example, when you (some ISP) have account
information you use in an RDBMS, and want to use modern
solutions that expect such information in LDAP (to authen-
ticate users, make email lookups etc.). Or you want to
synchronize or distribute information between different
sites/applications that use RDBMSes and/or LDAP. Or what-
ever else...
It is NOT designed as a general-purpose backend that uses
RDBMS instead of BerkeleyDB (as the standard BDB backend
does), though it can be used as such with several limita-
tions. You can take a look at http://www.openl-
dap.org/faq/index.cgi?file=378 (OpenLDAP FAQ-O-Matic/Gen-
eral LDAP FAQ/Directories vs. conventional databases) to
find out more on this point.
The idea (detailed below) is to use some metainformation
to translate LDAP queries to SQL queries, leaving rela-
tional schema untouched, so that old applications can con-
tinue using it without any modifications. This allows SQL
and LDAP applications to inter-operate without replica-
tion, and exchange data as needed.
The SQL backend is designed to be tunable to virtually any
relational schema without having to change source (through
that metainformation mentioned). Also, it uses ODBC to
connect to RDBMSes, and is highly configurable for SQL
dialects RDBMSes may use, so it may be used for integra-
tion and distribution of data on different RDBMSes, OSes,
hosts etc., in other words, in highly heterogeneous envi-
ronment.
This backend is experimental.
CONFIGURATION
These slapd.conf options apply to the SQL backend
database. That is, they must follow a "database sql" line
and come before any subsequent "backend" or "database"
lines. Other database options are described in the
slapd.conf(5) manual page.
dbname <datasource name>
The name of the ODBC datasource to use.
dbhost <hostname>
dbuser <username>
dbpasswd <password>
These three options are generally unneeded, because
this information is already taken from the data-
source. Use them if you need to override
datasource settings. Also, several RDBMS' drivers
tend to require explicit passing of user/password,
even if those are given in datasource (Note: dbhost
is currently ignored).
subtree_cond <SQL expression>
Specifies a where-clause template used to form a
subtree search condition (dn=".*<dn>"). It may
differ from one SQL dialect to another (see sam-
ples).
children_cond <SQL expression>
Specifies a where-clause template used to form a
children search condition (dn=".+,<dn>"). It may
differ from one SQL dialect to another (see sam-
ples).
oc_query <SQL expression>
The default is SELECT id, name, keytbl, keycol,
create_proc, delete_proc, expect_return FROM
ldap_oc_mappings
at_query <SQL expression>
The default is SELECT name, sel_expr, from_tbls,
join_where, add_proc, delete_proc, param_order,
expect_return FROM ldap_attr_mappings WHERE
oc_map_id=?
insentry_query <SQL expression>
The default is INSERT INTO ldap_entries (dn,
oc_map_id, parent, keyval) VALUES (?, ?, ?, ?)
delentry_query <SQL expression>
The default is DELETE FROM ldap_entries WHERE id=?
These four options specify SQL query templates for
loading schema mapping metainformation, adding and
deleting entries to ldap_entries, etc. All these
and subtree_cond should have the given default val-
ues. For the current value it is recommended to
look at the sources, or in the log output when
slapd starts with "-d 5" or greater. Note that the
parameter number and order must not be changed.
upper_func <SQL function name>
Specifies the name of a function that converts a
given value to uppercase. This is used for CIS
matching when the RDBMS is case sensitive.
upper_needs_cast { yes | no }
Set this directive to yes if upper_func needs an
explicit cast when applied to literal strings. The
form cast (<arg> as varchar(<max DN length>)) is
used, where <max DN length> is builtin. This is
experimental and may change in future releases.
concat_pattern <pattern>
This statement defines the pattern to be used to
concatenate strings. The pattern MUST contain two
question marks, '?', that will be replaced by the
two strings that must be concatenated. The default
value is CONCAT(?,?); a form that is known to be
highly portable is ?||?, but an explicit cast may
be required when operating on literal strings:
cast(?||? as varchar(<length>)). On some RDBMSes
the form ?+? is known to work. Carefully check
the documentation of your RDBMS or stay with the
examples for supported ones. This is experimental
and may change in future releases.
strcast_func <SQL function name>
Specifies the name of a function that converts a
given value to a string for appropriate ordering.
This is used in "SELECT DISTINCT" statements for
strongly typed RDBMSes with little implicit casting
(like PostgreSQL), when a literal string is speci-
fied. This is experimental and may change in
future releases.
has_ldapinfo_dn_ru { yes | no }
Explicitly inform the backend whether the SQL
schema has dn_ru column (dn in reverse uppercased
form) or not. Overrides automatic check (required
by PostgreSQL/unixODBC). This is experimental and
may change in future releases.
fail_if_no_mapping { yes | no }
When set to yes it forces write operations to fail
if no appropriate mapping between LDAP attributes
and SQL data is available. The default behavior is
to ignore those changes that cannot be mapped cor-
rectly. This is experimental and may change in
future releases.
METAINFORMATION USED
Almost everything mentioned later is illustrated in exam-
ples located in the servers/slapd/back-sql/rdbms_depend/
directory in the OpenLDAP source tree, and contains
scripts for generating sample database for Oracle, MS SQL
Server, mySQL and more (including PostgreSQL and IBM db2).
The first thing that one must arrange is what set of LDAP
object classes can present your RDBMS information.
The easiest way is to create an objectclass for each
entity you had in ER-diagram when designing your rela-
tional schema. Any relational schema, no matter how nor-
malized it is, was designed after some model of your
application's domain (for instance, accounts, services
etc. in ISP), and is used in terms of its entities, not
just tables of normalized schema. It means that for every
attribute of every such instance there is an effective SQL
query that loads its values.
Also you might want your object classes to conform to some
of the standard schemas like inetOrgPerson etc.
Nevertheless, when you think it out, we must define a way
to translate LDAP operation requests to (a series of) SQL
queries. Let us deal with the SEARCH operation.
Example: Let's suppose that we store information about
persons working in our organization in two tables:
PERSONS PHONES
-----------------------
id integer id integer
first_name varchar pers_id integer references persons(id)
last_name varchar phone
middle_name varchar
...
(PHONES contains telephone numbers associated with per-
sons). A person can have several numbers, then PHONES
contains several records with corresponding pers_id, or no
numbers (and no records in PHONES with such pers_id). An
LDAP objectclass to present such information could look
like this:
person
-------
MUST cn
MAY telephoneNumber $ firstName $ lastName
...
To fetch all values for cn attribute given person ID, we
construct the query:
SELECT CONCAT(persons.first_name,' ',persons.last_name)
AS cn FROM persons WHERE persons.id=?
for telephoneNumber we can use:
SELECT phones.phone AS telephoneNumber FROM persons,phones
WHERE persons.id=phones.pers_id AND persons.id=?
If we wanted to service LDAP requests with filters like
(telephoneNumber=123*), we would construct something like:
SELECT ... FROM persons,phones
WHERE persons.id=phones.pers_id
AND persons.id=?
AND phones.phone like '123%'
So, if we had information about what tables contain values
for each attribute, how to join these tables and arrange
these values, we could try to automatically generate such
statements, and translate search filters to SQL WHERE
clauses.
To store such information, we add three more tables to our
schema and fill it with data (see samples):
ldap_oc_mappings (some columns are not listed for clarity)
---------------
id=1
name="person"
keytbl="persons"
keycol="id"
This table defines a mapping between objectclass (its name
held in the "name" column), and a table that holds the
primary key for corresponding entities. For instance, in
our example, the person entity, which we are trying to
present as "person" objectclass, resides in two tables
(persons and phones), and is identified by the persons.id
column (that we will call the primary key for this
entity). Keytbl and keycol thus contain "persons" (name
of the table), and "id" (name of the column).
ldap_attr_mappings (some columns are not listed for clarity)
-----------
id=1
oc_map_id=1
name="cn"
sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
from_tbls="persons"
join_where=NULL
************
id=<n>
oc_map_id=1
name="telephoneNumber"
sel_expr="phones.phone"
from_tbls="persons,phones"
join_where="phones.pers_id=persons.id"
This table defines mappings between LDAP attributes and
SQL queries that load their values. Note that, unlike
LDAP schema, these are not attribute types - the attribute
"cn" for "person" objectclass can have its values in dif-
ferent tables than "cn" for some other objectclass, so
attribute mappings depend on objectclass mappings (unlike
attribute types in LDAP schema, which are indifferent to
objectclasses). Thus, we have oc_map_id column with link
to oc_mappings table.
Now we cut the SQL query that loads values for a given
attribute into 3 parts. First goes into sel_expr column -
this is the expression we had between SELECT and FROM key-
words, which defines WHAT to load. Next is table list -
text between FROM and WHERE keywords. It may contain
aliases for convenience (see examples). The last is part
of the where clause, which (if it exists at all) expresses
the condition for joining the table containing values with
the table containing the primary key (foreign key equality
and such). If values are in the same table as the primary
key, then this column is left NULL (as for cn attribute
above).
Having this information in parts, we are able to not only
construct queries that load attribute values by id of
entry (for this we could store SQL query as a whole), but
to construct queries that load id's of objects that corre-
spond to a given search filter (or at least part of it).
See below for examples.
ldap_entries
------------
id=1
dn=<dn you choose>
oc_map_id=...
parent=<parent record id>
keyval=<value of primary key>
This table defines mappings between DNs of entries in your
LDAP tree, and values of primary keys for corresponding
relational data. It has recursive structure (parent col-
umn references id column of the same table), which allows
you to add any tree structure(s) to your flat relational
data. Having id of objectclass mapping, we can determine
table and column for primary key, and keyval stores value
of it, thus defining the exact tuple corresponding to the
LDAP entry with this DN.
Note that such design (see exact SQL table creation query)
implies one important constraint - the key must be an
integer. But all that I know about well-designed schemas
makes me think that it's not very narrow ;) If anyone
needs support for different types for keys - he may want
to write a patch, and submit it to OpenLDAP ITS, then I'll
include it.
Also, several people complained that they don't really
need very structured trees, and they don't want to update
one more table every time they add or delete an instance
in the relational schema. Those people can use a view
instead of a real table for ldap_entries, something like
this (by Robin Elfrink):
CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval)
AS SELECT (1000000000+userid),
UPPER(CONCAT(CONCAT('cn=',gecos),',o=MyCompany,c=NL')),
1, 0, userid FROM unixusers UNION
SELECT (2000000000+groupnummer),
UPPER(CONCAT(CONCAT('cn=',groupnaam),',o=MyCompany,c=NL')),
2, 0, groupnummer FROM groups;
Typical SQL backend operation
Having metainformation loaded, the SQL backend uses these
tables to determine a set of primary keys of candidates
(depending on search scope and filter). It tries to do it
for each objectclass registered in ldap_objclasses.
Example: for our query with filter (telephoneNumber=123*)
we would get the following query generated (which loads
candidate IDs)
SELECT ldap_entries.id,persons.id, 'person' AS objectClass,
ldap_entries.dn AS dn
FROM ldap_entries,persons,phones
WHERE persons.id=ldap_entries.keyval
AND ldap_entries.objclass=?
AND ldap_entries.parent=?
AND phones.pers_id=persons.id
AND (phones.phone LIKE '123%')
(for ONELEVEL search) or "... AND dn=?" (for BASE search)
or "... AND dn LIKE '%?'" (for SUBTREE)
Then, for each candidate, we load the requested attributes
using per-attribute queries like
SELECT phones.phone AS telephoneNumber
FROM persons,phones
WHERE persons.id=? AND phones.pers_id=persons.id
Then, we use test_filter() from the frontend API to test
the entry for a full LDAP search filter match (since we
cannot effectively make sense of SYNTAX of corresponding
LDAP schema attribute, we translate the filter into the
most relaxed SQL condition to filter candidates), and send
it to the user.
ADD, DELETE, MODIFY operations are also performed on per-
attribute metainformation (add_proc etc.). In those
fields one can specify an SQL statement or stored proce-
dure call which can add, or delete given values of a given
attribute, using the given entry keyval (see examples --
mostly ORACLE and MSSQL - since there're no stored procs
in mySQL).
We just add more columns to oc_mappings and attr_mappings,
holding statements to execute (like create_proc, add_proc,
del_proc etc.), and flags governing the order of parame-
ters passed to those statements. Please see samples to
find out what are the parameters passed, and other infor-
mation on this matter - they are self-explanatory for
those familiar with concept expressed above.
common techniques (referrals, multiclassing etc.)
First of all, let's remember that among other major dif-
ferences to the complete LDAP data model, the concept
above does not directly support such things as multiple
objectclasses per entry, and referrals. Fortunately, they
are easy to adopt in this scheme. The SQL backend sug-
gests two more tables being added to the schema -
ldap_entry_objectclasses(entry_id,oc_name), and
ldap_referrals(entry_id,url).
The first contains any number of objectclass names that
corresponding entries will be found by, in addition to
that mentioned in mapping. The SQL backend automatically
adds attribute mapping for the "objectclass" attribute to
each objectclass mapping that loads values from this
table. So, you may, for instance, have a mapping for
inetOrgPerson, and use it for queries for "person" object-
class...
The second table contains any number of referrals associ-
ated with a given entry. The SQL backend automatically
adds attribute mapping for "ref" attribute to each object-
class mapping that loads values from this table. So, if
you add objectclass "referral" to this entry, and make one
or more tuples in ldap_referrals for this entry (they will
be seen as values of "ref" attribute), you will have slapd
return a referral, as described in the Administrators
Guide.
EXAMPLES
There are example SQL modules in the slapd/back-
sql/rdbms_depend/ directory in the OpenLDAP source tree.
FILES
ETCDIR/slapd.conf
default slapd configuration file
SEE ALSOslapd.conf(5), slapd(8).
OpenLDAP LDVERSION RELEASEDATE SLAPD-SQL(5)
