1SLAPD-SQL(5) File Formats Manual SLAPD-SQL(5)
2
6 slapd-sql - SQL backend to slapd
7
9 /etc/openldap/slapd.conf
10
12 The primary purpose of this slapd(8) backend is to PRESENT information
13 stored in some RDBMS as an LDAP subtree without any programming (some
14 SQL and maybe stored procedures can't be considered programming, anyway
15 ;).
16 That is, for example, when you (some ISP) have account information you
18 use in an RDBMS, and want to use modern solutions that expect such
19 information in LDAP (to authenticate users, make email lookups etc.).
20 Or you want to synchronize or distribute information between different
21 sites/applications that use RDBMSes and/or LDAP. Or whatever else...
22 It is NOT designed as a general-purpose backend that uses RDBMS instead
24 of BerkeleyDB (as the standard BDB backend does), though it can be used
25 as such with several limitations. You can take a look at
26 http://www.openldap.org/faq/index.cgi?file=378 (OpenLDAP
27 FAQ-O-Matic/General LDAP FAQ/Directories vs. conventional databases) to
28 find out more on this point.
29 The idea (detailed below) is to use some meta-information to translate
31 LDAP queries to SQL queries, leaving relational schema untouched, so
32 that old applications can continue using it without any modifications.
33 This allows SQL and LDAP applications to inter-operate without replica‐
34 tion, and exchange data as needed.
35 The SQL backend is designed to be tunable to virtually any relational
37 schema without having to change source (through that meta-information
38 mentioned). Also, it uses ODBC to connect to RDBMSes, and is highly
39 configurable for SQL dialects RDBMSes may use, so it may be used for
40 integration and distribution of data on different RDBMSes, OSes, hosts
41 etc., in other words, in highly heterogeneous environment.
42 This backend is experimental.
44
46 These slapd.conf options apply to the SQL backend database, which means
47 that they must follow a "database sql" line and come before any subse‐
48 quent "backend" or "database" lines. Other database options not spe‐
49 cific to this backend are described in the slapd.conf(5) manual page.
50
52 dbname <datasource name>
53 The name of the ODBC datasource to use.
54 dbhost <hostname>
56 dbpasswd <password>
57 dbuser <username>
58 The three above options are generally unneeded, because this
59 information is taken from the datasource specified by the dbname
60 directive. They allow to override datasource settings. Also,
61 several RDBMS' drivers tend to require explicit passing of
62 user/password, even if those are given in datasource (Note:
63 dbhost is currently ignored).
64
66 These options specify SQL query templates for scoping searches.
67 subtree_cond <SQL expression>
70 Specifies a where-clause template used to form a subtree search
71 condition (dn="(.+,)?<dn>$"). It may differ from one SQL
72 dialect to another (see samples). By default, it is constructed
73 based on the knowledge about how to normalize DN values (e.g.
74 "<upper_func>(ldap_entries.dn) LIKE CONCAT('%',?)"); see
75 upper_func, upper_needs_cast, concat_pattern and strcast_func in
76 "HELPER CONFIGURATION" for details.
77 children_cond <SQL expression>
80 Specifies a where-clause template used to form a children search
81 condition (dn=".+,<dn>$"). It may differ from one SQL dialect
82 to another (see samples). By default, it is constructed based
83 on the knowledge about how to normalize DN values (e.g.
84 "<upper_func>(ldap_entries.dn) LIKE CONCAT('%,',?)"); see
85 upper_func, upper_needs_cast, concat_pattern and strcast_func in
86 "HELPER CONFIGURATION" for details.
87 use_subtree_shortcut { YES | no }
90 Do not use the subtree condition when the searchBase is the
91 database suffix, and the scope is subtree; rather collect all
92 entries.
93
96 These options specify SQL query templates for loading schema mapping
97 meta-information, adding and deleting entries to ldap_entries, etc.
98 All these and subtree_cond should have the given default values. For
99 the current value it is recommended to look at the sources, or in the
100 log output when slapd starts with "-d 5" or greater. Note that the
101 parameter number and order must not be changed.
102 oc_query <SQL expression>
105 The query that is used to collect the objectClass mapping data
106 from table ldap_oc_mappings; see "METAINFORMATION USED" for
107 details. The default is "SELECT id, name, keytbl, keycol, cre‐
108 ate_proc, delete_proc, expect_return FROM ldap_oc_mappings".
109 at_query <SQL expression>
112 The query that is used to collect the attributeType mapping data
113 from table ldap_attr_mappings; see "METAINFORMATION USED" for
114 details. The default is "SELECT name, sel_expr, from_tbls,
115 join_where, add_proc, delete_proc, param_order, expect_return
116 FROM ldap_attr_mappings WHERE oc_map_id=?".
117 id_query <SQL expression>
120 The query that is used to map a DN to an entry in table
121 ldap_entries; see "METAINFORMATION USED" for details. The
122 default is "SELECT id,keyval,oc_map_id,dn FROM ldap_entries
123 WHERE <DN match expr>", where <DN match expr> is constructed
124 based on the knowledge about how to normalize DN values (e.g.
125 "dn=?" if no means to uppercase strings are available; typi‐
126 cally, "<upper_func>(dn)=?" is used); see upper_func,
127 upper_needs_cast, concat_pattern and strcast_func in "HELPER
128 CONFIGURATION" for details.
129 insentry_stmt <SQL expression>
132 The statement that is used to insert a new entry in table
133 ldap_entries; see "METAINFORMATION USED" for details. The
134 default is "INSERT INTO ldap_entries (dn, oc_map_id, parent,
135 keyval) VALUES (?, ?, ?, ?)".
136 delentry_stmt <SQL expression>
139 The statement that is used to delete an existing entry from ta‐
140 ble ldap_entries; see "METAINFORMATION USED" for details. The
141 default is "DELETE FROM ldap_entries WHERE id=?".
142 delobjclasses_stmt <SQL expression>
145 The statement that is used to delete an existing entry's ID from
146 table ldap_objclasses; see "METAINFORMATION USED" for details.
147 The default is "DELETE FROM ldap_entry_objclasses WHERE
148 entry_id=?".
149
152 These statements are used to modify the default behavior of the backend
153 according to issues of the dialect of the RDBMS. The first options
154 essentially refer to string and DN normalization when building filters.
155 LDAP normalization is more than upper- (or lower-)casing everything;
156 however, as a reasonable trade-off, for case-sensitive RDBMSes the
157 backend can be instructed to uppercase strings and DNs by providing the
158 upper_func directive. Some RDBMSes, to use functions on arbitrary data
159 types, e.g. string constants, requires a cast, which is triggered by
160 the upper_needs_cast directive. If required, a string cast function
161 can be provided as well, by using the strcast_func directive. Finally,
162 a custom string concatenation pattern may be required; it is provided
163 by the concat_pattern directive.
164 upper_func <SQL function name>
167 Specifies the name of a function that converts a given value to
168 uppercase. This is used for case insensitive matching when the
169 RDBMS is case sensitive. It may differ from one SQL dialect to
170 another (e.g. UCASE, UPPER or whatever; see samples). By
171 default, none is used, i.e. strings are not uppercased, so
172 matches may be case sensitive.
173 upper_needs_cast { NO | yes }
176 Set this directive to yes if upper_func needs an explicit cast
177 when applied to literal strings. A cast in the form CAST (<arg>
178 AS VARCHAR(<max DN length>)) is used, where <max DN length> is
179 builtin in back-sql; see macro BACKSQL_MAX_DN_LEN (currently
180 255; note that slapd's builtin limit, in macro
181 SLAP_LDAPDN_MAXLEN, is set to 8192). This is experimental and
182 may change in future releases.
183 strcast_func <SQL function name>
186 Specifies the name of a function that converts a given value to
187 a string for appropriate ordering. This is used in "SELECT DIS‐
188 TINCT" statements for strongly typed RDBMSes with little
189 implicit casting (like PostgreSQL), when a literal string is
190 specified. This is experimental and may change in future
191 releases.
192 concat_pattern <pattern>
195 This statement defines the pattern that is used to concatenate
196 strings. The pattern MUST contain two question marks, '?', that
197 will be replaced by the two strings that must be concatenated.
198 The default value is CONCAT(?,?); a form that is known to be
199 highly portable (IBM db2, PostgreSQL) is ?||?, but an explicit
200 cast may be required when operating on literal strings:
201 CAST(?||? AS VARCHAR(<length>)). On some RDBMSes (IBM db2,
202 MSSQL) the form ?+? is known to work as well. Carefully check
203 the documentation of your RDBMS or stay with the examples for
204 supported ones. This is experimental and may change in future
205 releases.
206 aliasing_keyword <string>
209 Define the aliasing keyword. Some RDBMSes use the word "AS"
210 (the default), others don't use any.
211 aliasing_quote <string>
214 Define the quoting char of the aliasing keyword. Some RDBMSes
215 don't require any (the default), others may require single or
216 double quotes.
217 has_ldapinfo_dn_ru { NO | yes }
220 Explicitly inform the backend whether the dn_ru column (DN in
221 reverse uppercased form) is present in table ldap_entries.
222 Overrides automatic check (this is required, for instance, by
223 PostgreSQL/unixODBC). This is experimental and may change in
224 future releases.
225 fail_if_no_mapping { NO | yes }
228 When set to yes it forces attribute write operations to fail if
229 no appropriate mapping between LDAP attributes and SQL data is
230 available. The default behavior is to ignore those changes that
231 cannot be mapped. It has no impact on objectClass mapping, i.e.
232 if the structuralObjectClass of an entry cannot be mapped to SQL
233 by looking up its name in ldap_oc_mappings, an add operation
234 will fail regardless of the fail_if_no_mapping switch; see sec‐
235 tion "METAINFORMATION USED" for details. This is experimental
236 and may change in future releases.
237 allow_orphans { NO | yes }
240 When set to yes orphaned entries (i.e. without the parent entry
241 in the database) can be added. This option should be used with
242 care, possibly in conjunction with some special rule on the
243 RDBMS side that dynamically creates the missing parent.
244 baseObject [ <filename> ]
247 Instructs the database to create and manage an in-memory baseOb‐
248 ject entry instead of looking for one in the RDBMS. If the
249 (optional) <filename> argument is given, the entry is read from
250 that file in LDIF(5) format; otherwise, an entry with object‐
251 Class extensibleObject is created based on the contents of the
252 RDN of the baseObject. This is particularly useful when
253 ldap_entries information is stored in a view rather than in a
254 table, and union is not supported for views, so that the view
255 can only specify one rule to compute the entry structure for one
256 objectClass. This topic is discussed further in section
257 "METAINFORMATION USED". This is experimental and may change in
258 future releases.
259 create_needs_select { NO | yes }
262 Instructs the database whether or not entry creation in table
263 ldap_entries needs a subsequent select to collect the automati‐
264 cally assigned ID, instead of being returned by a stored proce‐
265 dure.
266 fetch_attrs <attrlist>
269 fetch_all_attrs { NO | yes }
270 The first statement allows one to provide a list of attributes
271 that must always be fetched in addition to those requested by
272 any specific operation, because they are required for the proper
273 usage of the backend. For instance, all attributes used in ACLs
274 should be listed here. The second statement is a shortcut to
275 require all attributes to be always loaded. Note that the
276 dynamically generated attributes, e.g. hasSubordinates, entryDN
277 and other implementation dependent attributes are NOT generated
278 at this point, for consistency with the rest of slapd. This may
279 change in the future.
280 check_schema { YES | no }
283 Instructs the database to check schema adherence of entries
284 after modifications, and structural objectClass chain when
285 entries are built. By default it is set to yes.
286 sqllayer <name> [...]
289 Loads the layer <name> onto a stack of helpers that are used to
290 map DNs from LDAP to SQL representation and vice-versa. Subse‐
291 quent args are passed to the layer configuration routine. This
292 is highly experimental and should be used with extreme care.
293 The API of the layers is not frozen yet, so it is unpublished.
294 autocommit { NO | yes }
297 Activates autocommit; by default, it is off.
298
301 Almost everything mentioned later is illustrated in examples located in
302 the servers/slapd/back-sql/rdbms_depend/ directory in the OpenLDAP
303 source tree, and contains scripts for generating sample database for
304 Oracle, MS SQL Server, mySQL and more (including PostgreSQL and IBM
305 db2).
306 The first thing that one must arrange is what set of LDAP object
308 classes can present your RDBMS information.
309 The easiest way is to create an objectClass for each entity you had in
311 ER-diagram when designing your relational schema. Any relational
312 schema, no matter how normalized it is, was designed after some model
313 of your application's domain (for instance, accounts, services etc. in
314 ISP), and is used in terms of its entities, not just tables of normal‐
315 ized schema. It means that for every attribute of every such instance
316 there is an effective SQL query that loads its values.
317 Also you might want your object classes to conform to some of the stan‐
319 dard schemas like inetOrgPerson etc.
320 Nevertheless, when you think it out, we must define a way to translate
322 LDAP operation requests to (a series of) SQL queries. Let us deal with
323 the SEARCH operation.
324 Example: Let's suppose that we store information about persons working
326 in our organization in two tables:
327 PERSONS PHONES
329 ---------- -------------
330 id integer id integer
331 first_name varchar pers_id integer references persons(id)
332 last_name varchar phone
333 middle_name varchar
334 ...
335 (PHONES contains telephone numbers associated with persons). A person
337 can have several numbers, then PHONES contains several records with
338 corresponding pers_id, or no numbers (and no records in PHONES with
339 such pers_id). An LDAP objectclass to present such information could
340 look like this:
341 person
343 -------
344 MUST cn
345 MAY telephoneNumber $ firstName $ lastName
346 ...
347 To fetch all values for cn attribute given person ID, we construct the
349 query:
350 SELECT CONCAT(persons.first_name,' ',persons.last_name)
352 AS cn FROM persons WHERE persons.id=?
353 for telephoneNumber we can use:
355 SELECT phones.phone AS telephoneNumber FROM persons,phones
357 WHERE persons.id=phones.pers_id AND persons.id=?
358 If we wanted to service LDAP requests with filters like (telephoneNum‐
360 ber=123*), we would construct something like:
361 SELECT ... FROM persons,phones
363 WHERE persons.id=phones.pers_id
364 AND persons.id=?
365 AND phones.phone like '%1%2%3%'
366 (note how the telephoneNumber match is expanded in multiple wildcards
368 to account for interspersed ininfluential chars like spaces, dashes and
369 so; this occurs by design because telephoneNumber is defined after a
370 specially recognized syntax). So, if we had information about what
371 tables contain values for each attribute, how to join these tables and
372 arrange these values, we could try to automatically generate such
373 statements, and translate search filters to SQL WHERE clauses.
374 To store such information, we add three more tables to our schema and
376 fill it with data (see samples):
377 ldap_oc_mappings (some columns are not listed for clarity)
379 ---------------
380 id=1
381 name="person"
382 keytbl="persons"
383 keycol="id"
384 This table defines a mapping between objectclass (its name held in the
386 "name" column), and a table that holds the primary key for correspond‐
387 ing entities. For instance, in our example, the person entity, which
388 we are trying to present as "person" objectclass, resides in two tables
389 (persons and phones), and is identified by the persons.id column (that
390 we will call the primary key for this entity). Keytbl and keycol thus
391 contain "persons" (name of the table), and "id" (name of the column).
392 ldap_attr_mappings (some columns are not listed for clarity)
394 -----------
395 id=1
396 oc_map_id=1
397 name="cn"
398 sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
399 from_tbls="persons"
400 join_where=NULL
401 ************
402 id=<n>
403 oc_map_id=1
404 name="telephoneNumber"
405 sel_expr="phones.phone"
406 from_tbls="persons,phones"
407 join_where="phones.pers_id=persons.id"
408 This table defines mappings between LDAP attributes and SQL queries
410 that load their values. Note that, unlike LDAP schema, these are not
411 attribute types - the attribute "cn" for "person" objectclass can have
412 its values in different tables than "cn" for some other objectclass, so
413 attribute mappings depend on objectclass mappings (unlike attribute
414 types in LDAP schema, which are indifferent to objectclasses). Thus,
415 we have oc_map_id column with link to oc_mappings table.
416 Now we cut the SQL query that loads values for a given attribute into 3
418 parts. First goes into sel_expr column - this is the expression we had
419 between SELECT and FROM keywords, which defines WHAT to load. Next is
420 table list - text between FROM and WHERE keywords. It may contain
421 aliases for convenience (see examples). The last is part of the where
422 clause, which (if it exists at all) expresses the condition for joining
423 the table containing values with the table containing the primary key
424 (foreign key equality and such). If values are in the same table as
425 the primary key, then this column is left NULL (as for cn attribute
426 above).
427 Having this information in parts, we are able to not only construct
429 queries that load attribute values by id of entry (for this we could
430 store SQL query as a whole), but to construct queries that load id's of
431 objects that correspond to a given search filter (or at least part of
432 it). See below for examples.
433 ldap_entries
435 ------------
436 id=1
437 dn=<dn you choose>
438 oc_map_id=...
439 parent=<parent record id>
440 keyval=<value of primary key>
441 This table defines mappings between DNs of entries in your LDAP tree,
443 and values of primary keys for corresponding relational data. It has
444 recursive structure (parent column references id column of the same ta‐
445 ble), which allows you to add any tree structure(s) to your flat rela‐
446 tional data. Having id of objectclass mapping, we can determine table
447 and column for primary key, and keyval stores value of it, thus defin‐
448 ing the exact tuple corresponding to the LDAP entry with this DN.
449 Note that such design (see exact SQL table creation query) implies one
451 important constraint - the key must be an integer. But all that I know
452 about well-designed schemas makes me think that it's not very narrow ;)
453 If anyone needs support for different types for keys - he may want to
454 write a patch, and submit it to OpenLDAP ITS, then I'll include it.
455 Also, several users complained that they don't really need very struc‐
457 tured trees, and they don't want to update one more table every time
458 they add or delete an instance in the relational schema. Those people
459 can use a view instead of a real table for ldap_entries, something like
460 this (by Robin Elfrink):
461 CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval)
463 AS
464 SELECT 0, UPPER('o=MyCompany,c=NL'),
465 3, 0, 'baseObject' FROM unixusers WHERE userid='root'
466 UNION
467 SELECT (1000000000+userid),
468 UPPER(CONCAT(CONCAT('cn=',gecos),',o=MyCompany,c=NL')),
469 1, 0, userid FROM unixusers
470 UNION
471 SELECT (2000000000+groupnummer),
472 UPPER(CONCAT(CONCAT('cn=',groupnaam),',o=MyCompany,c=NL')),
473 2, 0, groupnummer FROM groups;
474 If your RDBMS does not support unions in views, only one objectClass
477 can be mapped in ldap_entries, and the baseObject cannot be created; in
478 this case, see the baseObject directive for a possible workaround.
479
482 Having meta-information loaded, the SQL backend uses these tables to
483 determine a set of primary keys of candidates (depending on search
484 scope and filter). It tries to do it for each objectclass registered
485 in ldap_objclasses.
486 Example: for our query with filter (telephoneNumber=123*) we would get
488 the following query generated (which loads candidate IDs)
489 SELECT ldap_entries.id,persons.id, 'person' AS objectClass,
491 ldap_entries.dn AS dn
492 FROM ldap_entries,persons,phones
493 WHERE persons.id=ldap_entries.keyval
494 AND ldap_entries.objclass=?
495 AND ldap_entries.parent=?
496 AND phones.pers_id=persons.id
497 AND (phones.phone LIKE '%1%2%3%')
498 (for ONELEVEL search) or "... AND dn=?" (for BASE search) or "... AND
500 dn LIKE '%?'" (for SUBTREE)
501 Then, for each candidate, we load the requested attributes using per-
503 attribute queries like
504 SELECT phones.phone AS telephoneNumber
506 FROM persons,phones
507 WHERE persons.id=? AND phones.pers_id=persons.id
508 Then, we use test_filter() from the frontend API to test the entry for
510 a full LDAP search filter match (since we cannot effectively make sense
511 of SYNTAX of corresponding LDAP schema attribute, we translate the fil‐
512 ter into the most relaxed SQL condition to filter candidates), and send
513 it to the user.
514 ADD, DELETE, MODIFY and MODRDN operations are also performed on per-
516 attribute meta-information (add_proc etc.). In those fields one can
517 specify an SQL statement or stored procedure call which can add, or
518 delete given values of a given attribute, using the given entry keyval
519 (see examples -- mostly PostgreSQL, ORACLE and MSSQL - since as of this
520 writing there are no stored procs in MySQL).
521 We just add more columns to ldap_oc_mappings and ldap_attr_mappings,
523 holding statements to execute (like create_proc, add_proc, del_proc
524 etc.), and flags governing the order of parameters passed to those
525 statements. Please see samples to find out what are the parameters
526 passed, and other information on this matter - they are self-explana‐
527 tory for those familiar with the concepts expressed above.
528
530 First of all, let's recall that among other major differences to the
531 complete LDAP data model, the above illustrated concept does not
532 directly support such features as multiple objectclasses per entry, and
533 referrals. Fortunately, they are easy to adopt in this scheme. The
534 SQL backend requires that one more table is added to the schema:
535 ldap_entry_objectclasses(entry_id,oc_name).
536 That table contains any number of objectclass names that corresponding
538 entries will possess, in addition to that mentioned in mapping. The
539 SQL backend automatically adds attribute mapping for the "objectclass"
540 attribute to each objectclass mapping that loads values from this ta‐
541 ble. So, you may, for instance, have a mapping for inetOrgPerson, and
542 use it for queries for "person" objectclass...
543 Referrals used to be implemented in a loose manner by adding an extra
545 table that allowed any entry to host a "ref" attribute, along with a
546 "referral" extra objectClass in table ldap_entry_objclasses. In the
547 current implementation, referrals are treated like any other user-
548 defined schema, since "referral" is a structural objectclass. The sug‐
549 gested practice is to define a "referral" entry in ldap_oc_mappings,
550 holding a naming attribute, e.g. "ou" or "cn", a "ref" attribute, con‐
551 taining the url; in case multiple referrals per entry are needed, a
552 separate table for urls can be created, where urls are mapped to the
553 respective entries. The use of the naming attribute usually requires
554 to add an "extensibleObject" value to ldap_entry_objclasses.
555
558 As previously stated, this backend should not be considered a replace‐
559 ment of other data storage backends, but rather a gateway to existing
560 RDBMS storages that need to be published in LDAP form.
561 The hasSubordintes operational attribute is honored by back-sql in
563 search results and in compare operations; it is partially honored also
564 in filtering. Owing to design limitations, a (brain-dead?) filter of
565 the form (!(hasSubordinates=TRUE)) will give no results instead of
566 returning all the leaf entries, because it actually expands into ...
567 AND NOT (1=1). If you need to find all the leaf entries, please use
568 (hasSubordinates=FALSE) instead.
569 A directoryString value of the form "__First___Last_" (where under‐
571 scores mean spaces, ASCII 0x20 char) corresponds to its prettified
572 counterpart "First_Last"; this is not currently honored by back-sql if
573 non-prettified data is written via RDBMS; when non-prettified data is
574 written through back-sql, the prettified values are actually used
575 instead.
576
579 When the ldap_entry_objclasses table is empty, filters on the object‐
580 Class attribute erroneously result in no candidates. A workaround con‐
581 sists in adding at least one row to that table, no matter if valid or
582 not.
583
586 The proxy cache overlay allows caching of LDAP search requests
587 (queries) in a local database. See slapo-pcache(5) for details.
588
590 There are example SQL modules in the slapd/back-sql/rdbms_depend/
591 directory in the OpenLDAP source tree.
592
594 The sql backend honors access control semantics as indicated in
595 slapd.access(5) (including the disclose access privilege when enabled
596 at compile time).
597
599 /etc/openldap/slapd.conf
600 default slapd configuration file
601
603 slapd.conf(5), slapd(8).
604OpenLDAP 2.4.47 2018/12/19 SLAPD-SQL(5)