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 in‐
19 formation in LDAP (to authenticate users, make email lookups etc.). Or
20 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 in‐
59 formation 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: db‐
63 host 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 di‐
72 alect 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 up‐
75 per_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. "<up‐
84 per_func>(ldap_entries.dn) LIKE CONCAT('%,',?)"); see up‐
85 per_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 pa‐
101 rameter 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 de‐
107 tails. 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 ldap_en‐
121 tries; see "METAINFORMATION USED" for details. The default is
122 "SELECT id,keyval,oc_map_id,dn FROM ldap_entries WHERE <DN match
123 expr>", where <DN match expr> is constructed based on the knowl‐
124 edge about how to normalize DN values (e.g. "dn=?" if no means
125 to uppercase strings are available; typically, "<up‐
126 per_func>(dn)=?" is used); see upper_func, upper_needs_cast,
127 concat_pattern and strcast_func in "HELPER CONFIGURATION" for
128 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 de‐
134 fault is "INSERT INTO ldap_entries (dn, oc_map_id, parent, key‐
135 val) 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 en‐
148 try_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 es‐
154 sentially 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 de‐
171 fault, none is used, i.e. strings are not uppercased, so matches
172 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 im‐
189 plicit casting (like PostgreSQL), when a literal string is spec‐
190 ified. This is experimental and may change in future releases.
191 concat_pattern <pattern>
194 This statement defines the pattern that is used to concatenate
195 strings. The pattern MUST contain two question marks, '?', that
196 will be replaced by the two strings that must be concatenated.
197 The default value is CONCAT(?,?); a form that is known to be
198 highly portable (IBM db2, PostgreSQL) is ?||?, but an explicit
199 cast may be required when operating on literal strings:
200 CAST(?||? AS VARCHAR(<length>)). On some RDBMSes (IBM db2,
201 MSSQL) the form ?+? is known to work as well. Carefully check
202 the documentation of your RDBMS or stay with the examples for
203 supported ones. This is experimental and may change in future
204 releases.
205 aliasing_keyword <string>
208 Define the aliasing keyword. Some RDBMSes use the word "AS"
209 (the default), others don't use any.
210 aliasing_quote <string>
213 Define the quoting char of the aliasing keyword. Some RDBMSes
214 don't require any (the default), others may require single or
215 double quotes.
216 has_ldapinfo_dn_ru { NO | yes }
219 Explicitly inform the backend whether the dn_ru column (DN in
220 reverse uppercased form) is present in table ldap_entries.
221 Overrides automatic check (this is required, for instance, by
222 PostgreSQL/unixODBC). This is experimental and may change in
223 future releases.
224 fail_if_no_mapping { NO | yes }
227 When set to yes it forces attribute write operations to fail if
228 no appropriate mapping between LDAP attributes and SQL data is
229 available. The default behavior is to ignore those changes that
230 cannot be mapped. It has no impact on objectClass mapping, i.e.
231 if the structuralObjectClass of an entry cannot be mapped to SQL
232 by looking up its name in ldap_oc_mappings, an add operation
233 will fail regardless of the fail_if_no_mapping switch; see sec‐
234 tion "METAINFORMATION USED" for details. This is experimental
235 and may change in future releases.
236 allow_orphans { NO | yes }
239 When set to yes orphaned entries (i.e. without the parent entry
240 in the database) can be added. This option should be used with
241 care, possibly in conjunction with some special rule on the
242 RDBMS side that dynamically creates the missing parent.
243 baseObject [ <filename> ]
246 Instructs the database to create and manage an in-memory baseOb‐
247 ject entry instead of looking for one in the RDBMS. If the (op‐
248 tional) <filename> argument is given, the entry is read from
249 that file in LDIF(5) format; otherwise, an entry with object‐
250 Class extensibleObject is created based on the contents of the
251 RDN of the baseObject. This is particularly useful when
252 ldap_entries information is stored in a view rather than in a
253 table, and union is not supported for views, so that the view
254 can only specify one rule to compute the entry structure for one
255 objectClass. This topic is discussed further in section
256 "METAINFORMATION USED". This is experimental and may change in
257 future releases.
258 create_needs_select { NO | yes }
261 Instructs the database whether or not entry creation in table
262 ldap_entries needs a subsequent select to collect the automati‐
263 cally assigned ID, instead of being returned by a stored proce‐
264 dure.
265 fetch_attrs <attrlist>
268 fetch_all_attrs { NO | yes }
269 The first statement allows one to provide a list of attributes
270 that must always be fetched in addition to those requested by
271 any specific operation, because they are required for the proper
272 usage of the backend. For instance, all attributes used in ACLs
273 should be listed here. The second statement is a shortcut to
274 require all attributes to be always loaded. Note that the dy‐
275 namically generated attributes, e.g. hasSubordinates, entryDN
276 and other implementation dependent attributes are NOT generated
277 at this point, for consistency with the rest of slapd. This may
278 change in the future.
279 check_schema { YES | no }
282 Instructs the database to check schema adherence of entries af‐
283 ter modifications, and structural objectClass chain when entries
284 are built. By default it is set to yes.
285 sqllayer <name> [...]
288 Loads the layer <name> onto a stack of helpers that are used to
289 map DNs from LDAP to SQL representation and vice-versa. Subse‐
290 quent args are passed to the layer configuration routine. This
291 is highly experimental and should be used with extreme care.
292 The API of the layers is not frozen yet, so it is unpublished.
293 autocommit { NO | yes }
296 Activates autocommit; by default, it is off.
297
300 Almost everything mentioned later is illustrated in examples located in
301 the servers/slapd/back-sql/rdbms_depend/ directory in the OpenLDAP
302 source tree, and contains scripts for generating sample database for
303 Oracle, MS SQL Server, mySQL and more (including PostgreSQL and IBM
304 db2).
305 The first thing that one must arrange is what set of LDAP object
307 classes can present your RDBMS information.
308 The easiest way is to create an objectClass for each entity you had in
310 ER-diagram when designing your relational schema. Any relational
311 schema, no matter how normalized it is, was designed after some model
312 of your application's domain (for instance, accounts, services etc. in
313 ISP), and is used in terms of its entities, not just tables of normal‐
314 ized schema. It means that for every attribute of every such instance
315 there is an effective SQL query that loads its values.
316 Also you might want your object classes to conform to some of the stan‐
318 dard schemas like inetOrgPerson etc.
319 Nevertheless, when you think it out, we must define a way to translate
321 LDAP operation requests to (a series of) SQL queries. Let us deal with
322 the SEARCH operation.
323 Example: Let's suppose that we store information about persons working
325 in our organization in two tables:
326 PERSONS PHONES
328 ---------- -------------
329 id integer id integer
330 first_name varchar pers_id integer references persons(id)
331 last_name varchar phone
332 middle_name varchar
333 ...
334 (PHONES contains telephone numbers associated with persons). A person
336 can have several numbers, then PHONES contains several records with
337 corresponding pers_id, or no numbers (and no records in PHONES with
338 such pers_id). An LDAP objectclass to present such information could
339 look like this:
340 person
342 -------
343 MUST cn
344 MAY telephoneNumber $ firstName $ lastName
345 ...
346 To fetch all values for cn attribute given person ID, we construct the
348 query:
349 SELECT CONCAT(persons.first_name,' ',persons.last_name)
351 AS cn FROM persons WHERE persons.id=?
352 for telephoneNumber we can use:
354 SELECT phones.phone AS telephoneNumber FROM persons,phones
356 WHERE persons.id=phones.pers_id AND persons.id=?
357 If we wanted to service LDAP requests with filters like (telephoneNum‐
359 ber=123*), we would construct something like:
360 SELECT ... FROM persons,phones
362 WHERE persons.id=phones.pers_id
363 AND persons.id=?
364 AND phones.phone like '%1%2%3%'
365 (note how the telephoneNumber match is expanded in multiple wildcards
367 to account for interspersed ininfluential chars like spaces, dashes and
368 so; this occurs by design because telephoneNumber is defined after a
369 specially recognized syntax). So, if we had information about what ta‐
370 bles contain values for each attribute, how to join these tables and
371 arrange these values, we could try to automatically generate such
372 statements, and translate search filters to SQL WHERE clauses.
373 To store such information, we add three more tables to our schema and
375 fill it with data (see samples):
376 ldap_oc_mappings (some columns are not listed for clarity)
378 ---------------
379 id=1
380 name="person"
381 keytbl="persons"
382 keycol="id"
383 This table defines a mapping between objectclass (its name held in the
385 "name" column), and a table that holds the primary key for correspond‐
386 ing entities. For instance, in our example, the person entity, which
387 we are trying to present as "person" objectclass, resides in two tables
388 (persons and phones), and is identified by the persons.id column (that
389 we will call the primary key for this entity). Keytbl and keycol thus
390 contain "persons" (name of the table), and "id" (name of the column).
391 ldap_attr_mappings (some columns are not listed for clarity)
393 -----------
394 id=1
395 oc_map_id=1
396 name="cn"
397 sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
398 from_tbls="persons"
399 join_where=NULL
400 ************
401 id=<n>
402 oc_map_id=1
403 name="telephoneNumber"
404 sel_expr="phones.phone"
405 from_tbls="persons,phones"
406 join_where="phones.pers_id=persons.id"
407 This table defines mappings between LDAP attributes and SQL queries
409 that load their values. Note that, unlike LDAP schema, these are not
410 attribute types - the attribute "cn" for "person" objectclass can have
411 its values in different tables than "cn" for some other objectclass, so
412 attribute mappings depend on objectclass mappings (unlike attribute
413 types in LDAP schema, which are indifferent to objectclasses). Thus,
414 we have oc_map_id column with link to oc_mappings table.
415 Now we cut the SQL query that loads values for a given attribute into 3
417 parts. First goes into sel_expr column - this is the expression we had
418 between SELECT and FROM keywords, which defines WHAT to load. Next is
419 table list - text between FROM and WHERE keywords. It may contain
420 aliases for convenience (see examples). The last is part of the where
421 clause, which (if it exists at all) expresses the condition for joining
422 the table containing values with the table containing the primary key
423 (foreign key equality and such). If values are in the same table as
424 the primary key, then this column is left NULL (as for cn attribute
425 above).
426 Having this information in parts, we are able to not only construct
428 queries that load attribute values by id of entry (for this we could
429 store SQL query as a whole), but to construct queries that load id's of
430 objects that correspond to a given search filter (or at least part of
431 it). See below for examples.
432 ldap_entries
434 ------------
435 id=1
436 dn=<dn you choose>
437 oc_map_id=...
438 parent=<parent record id>
439 keyval=<value of primary key>
440 This table defines mappings between DNs of entries in your LDAP tree,
442 and values of primary keys for corresponding relational data. It has
443 recursive structure (parent column references id column of the same ta‐
444 ble), which allows you to add any tree structure(s) to your flat rela‐
445 tional data. Having id of objectclass mapping, we can determine table
446 and column for primary key, and keyval stores value of it, thus defin‐
447 ing the exact tuple corresponding to the LDAP entry with this DN.
448 Note that such design (see exact SQL table creation query) implies one
450 important constraint - the key must be an integer. But all that I know
451 about well-designed schemas makes me think that it's not very narrow ;)
452 If anyone needs support for different types for keys - he may want to
453 write a patch, and submit it to OpenLDAP ITS, then I'll include it.
454 Also, several users complained that they don't really need very struc‐
456 tured trees, and they don't want to update one more table every time
457 they add or delete an instance in the relational schema. Those people
458 can use a view instead of a real table for ldap_entries, something like
459 this (by Robin Elfrink):
460 CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval)
462 AS
463 SELECT 0, UPPER('o=MyCompany,c=NL'),
464 3, 0, 'baseObject' FROM unixusers WHERE userid='root'
465 UNION
466 SELECT (1000000000+userid),
467 UPPER(CONCAT(CONCAT('cn=',gecos),',o=MyCompany,c=NL')),
468 1, 0, userid FROM unixusers
469 UNION
470 SELECT (2000000000+groupnummer),
471 UPPER(CONCAT(CONCAT('cn=',groupnaam),',o=MyCompany,c=NL')),
472 2, 0, groupnummer FROM groups;
473 If your RDBMS does not support unions in views, only one objectClass
476 can be mapped in ldap_entries, and the baseObject cannot be created; in
477 this case, see the baseObject directive for a possible workaround.
478
481 Having meta-information loaded, the SQL backend uses these tables to
482 determine a set of primary keys of candidates (depending on search
483 scope and filter). It tries to do it for each objectclass registered
484 in ldap_objclasses.
485 Example: for our query with filter (telephoneNumber=123*) we would get
487 the following query generated (which loads candidate IDs)
488 SELECT ldap_entries.id,persons.id, 'person' AS objectClass,
490 ldap_entries.dn AS dn
491 FROM ldap_entries,persons,phones
492 WHERE persons.id=ldap_entries.keyval
493 AND ldap_entries.objclass=?
494 AND ldap_entries.parent=?
495 AND phones.pers_id=persons.id
496 AND (phones.phone LIKE '%1%2%3%')
497 (for ONELEVEL search) or "... AND dn=?" (for BASE search) or "... AND
499 dn LIKE '%?'" (for SUBTREE)
500 Then, for each candidate, we load the requested attributes using per-
502 attribute queries like
503 SELECT phones.phone AS telephoneNumber
505 FROM persons,phones
506 WHERE persons.id=? AND phones.pers_id=persons.id
507 Then, we use test_filter() from the frontend API to test the entry for
509 a full LDAP search filter match (since we cannot effectively make sense
510 of SYNTAX of corresponding LDAP schema attribute, we translate the fil‐
511 ter into the most relaxed SQL condition to filter candidates), and send
512 it to the user.
513 ADD, DELETE, MODIFY and MODRDN operations are also performed on per-at‐
515 tribute meta-information (add_proc etc.). In those fields one can
516 specify an SQL statement or stored procedure call which can add, or
517 delete given values of a given attribute, using the given entry keyval
518 (see examples -- mostly PostgreSQL, ORACLE and MSSQL - since as of this
519 writing there are no stored procs in MySQL).
520 We just add more columns to ldap_oc_mappings and ldap_attr_mappings,
522 holding statements to execute (like create_proc, add_proc, del_proc
523 etc.), and flags governing the order of parameters passed to those
524 statements. Please see samples to find out what are the parameters
525 passed, and other information on this matter - they are self-explana‐
526 tory for those familiar with the concepts expressed above.
527
529 First of all, let's recall that among other major differences to the
530 complete LDAP data model, the above illustrated concept does not di‐
531 rectly support such features as multiple objectclasses per entry, and
532 referrals. Fortunately, they are easy to adopt in this scheme. The
533 SQL backend requires that one more table is added to the schema:
534 ldap_entry_objectclasses(entry_id,oc_name).
535 That table contains any number of objectclass names that corresponding
537 entries will possess, in addition to that mentioned in mapping. The
538 SQL backend automatically adds attribute mapping for the "objectclass"
539 attribute to each objectclass mapping that loads values from this ta‐
540 ble. So, you may, for instance, have a mapping for inetOrgPerson, and
541 use it for queries for "person" objectclass...
542 Referrals used to be implemented in a loose manner by adding an extra
544 table that allowed any entry to host a "ref" attribute, along with a
545 "referral" extra objectClass in table ldap_entry_objclasses. In the
546 current implementation, referrals are treated like any other user-de‐
547 fined schema, since "referral" is a structural objectclass. The sug‐
548 gested practice is to define a "referral" entry in ldap_oc_mappings,
549 holding a naming attribute, e.g. "ou" or "cn", a "ref" attribute, con‐
550 taining the url; in case multiple referrals per entry are needed, a
551 separate table for urls can be created, where urls are mapped to the
552 respective entries. The use of the naming attribute usually requires
553 to add an "extensibleObject" value to ldap_entry_objclasses.
554
557 As previously stated, this backend should not be considered a replace‐
558 ment of other data storage backends, but rather a gateway to existing
559 RDBMS storages that need to be published in LDAP form.
560 The hasSubordintes operational attribute is honored by back-sql in
562 search results and in compare operations; it is partially honored also
563 in filtering. Owing to design limitations, a (brain-dead?) filter of
564 the form (!(hasSubordinates=TRUE)) will give no results instead of re‐
565 turning all the leaf entries, because it actually expands into ... AND
566 NOT (1=1). If you need to find all the leaf entries, please use (has‐
567 Subordinates=FALSE) instead.
568 A directoryString value of the form "__First___Last_" (where under‐
570 scores mean spaces, ASCII 0x20 char) corresponds to its prettified
571 counterpart "First_Last"; this is not currently honored by back-sql if
572 non-prettified data is written via RDBMS; when non-prettified data is
573 written through back-sql, the prettified values are actually used in‐
574 stead.
575
578 When the ldap_entry_objclasses table is empty, filters on the object‐
579 Class attribute erroneously result in no candidates. A workaround con‐
580 sists in adding at least one row to that table, no matter if valid or
581 not.
582
585 The proxy cache overlay allows caching of LDAP search requests
586 (queries) in a local database. See slapo-pcache(5) for details.
587
589 There are example SQL modules in the slapd/back-sql/rdbms_depend/ di‐
590 rectory in the OpenLDAP source tree.
591
593 The sql backend honors access control semantics as indicated in
594 slapd.access(5) (including the disclose access privilege when enabled
595 at compile time).
596
598 /etc/openldap/slapd.conf
599 default slapd configuration file
600
602 slapd.conf(5), slapd(8).
603OpenLDAP 2021/06/03 SLAPD-SQL(5)