OpenLDAP can feed LDAP clients with data using the back_sql module. Adam Williams was so kind to post some instructions in the user mailing list.

As posted by Adam Williams on the users list. Thanks Adam!

Update: Adam created an own Plone account and has updated documentation on the LDAP connection: OpenLDAP 2.1.x Back SQL Recipe!
So the text below is somewhat deprecated, use Adam's page instead!

Below is the SQL to create a connection to the OpenGroupware (PostgreSQL) database using OpenLDAP's back-sql module. I'm still tweaking it but it seems to more-or-less work at this point. It is read-only and doesn't do access control at this point. Anyone want to try it, feel free to comment.

You'll need to create an AUXILLIARY objectclass "opengroupwareentity" that requires an attribute "opengroupwareid" (numeric). And of course change the base of the DNs to match where ever you want to stick them in your Dit.

Also your DSA needs to know about evolutionperson and officeperson objectclasses.

-------------------------------------------------------------------------
-- View of "person"
-- Presents records that are contacts
-- LDAP mapping: company_id -> opengroupwareid
-- cn -> cn
-- firstname -> givenname
-- lastname -> sn
-- initials -> initials
-- birthday -> birthdate
-- uid -> uid
-- street-> street
-- zip -> postalcode
-- city -> l
-- country -> c
-- state -> st
-- postaladdress -> postaladdress
-- mobile -> mobile
-- fax -> facsimiletelephonenumber
-- phone -> telephonenumber
-- email -> mail
-- title -> title
CREATE VIEW ldap_person
(company_id, cn, firstname, lastname, initials, birthday, uid,
street, zip, city, country, state, postaladdress,
mobile, fax, phone, email, title) AS
SELECT person.company_id,
firstname || ' ' || name AS cn,
firstname,
name,
substring(firstname, 1, 1) || CASE
WHEN middlename IS NULL THEN ''
ELSE substring(middlename, 1, 1)
END ||
substring(name, 1, 1),
to_char(birthday, 'YYYY-MM-DD') as birthdate,
CASE WHEN is_account = 0 THEN NULL
ELSE login
END AS login,
-- attributes from address table
address.street, address.zip, address.zipcity,
address.country, address.state,
-- construct postaladdress
address.street || '$' || address.zipcity || ', ' ||
address.state || ' ' || address.zip || '$' || address.country
-- attributes from subordinate views
ldap_mobile_number.number,
ldap_fax_number.number,
ldap_office_number.number,
ldap_person.email,
ldap_person.title
FROM person
LEFT OUTER JOIN address
ON person.company_id = address.company_id
LEFT OUTER JOIN ldap_mobile_number
ON person.company_id = ldap_mobile_number.company_id
LEFT OUTER JOIN ldap_fax_number
ON person.company_id = ldap_fax_number.company_id
LEFT OUTER JOIN ldap_office_number
ON person.company_id = ldap_office_number.company_id
LEFT OUTER JOIN ldap_person_email
ON person.company_id = ldap_person_email.company_id
LEFT OUTER JOIN ldap_person_title
ON person.company_id = ldap_person_title.company_id
WHERE address.type = 'mailing'
AND person.company_id > 10000
AND person.is_private = 0;
---------------------------------------------------------------
-- View of "company"
-- Presents records that are enterprises
-- LDAP mapping: company_id -> opengroupwareid
-- name -> cn
-- street -> street
-- zip -> postalcode
-- city -> l
-- country -> c
-- state -> st
-- postaladdress -> postalAddress
-- phone -> telephonenumber
-- fax -> facsimiletelephonenumber
-- mobile ->
CREATE VIEW ldap_enterprise (company_id, name, url, email, street, zip, city, country, state, postaladdress, phone, fax, mobile) AS
SELECT company.company_id, company.description, company.url, company.email, address.street, address.zip, address.zipcity, address.country, address.state,
CASE WHEN name1 IS NULL THEN ''
ELSE name1 || '$'
END ||
CASE WHEN name2 IS NULL THEN ''
ELSE name2 || '$'
END ||
CASE WHEN name3 IS NULL THEN ''
ELSE name3 || '$'
END ||
street || '$' || zipcity || ', ' || state || ' ' || zip
|| '$' || country,
ldap_office_number.number,
ldap_fax_number.number,
ldap_mobile_number.number
FROM company
LEFT OUTER JOIN address
ON company.company_id = address.company_id
LEFT OUTER JOIN ldap_office_number
ON company.company_id = ldap_office_number.company_id
LEFT OUTER JOIN ldap_fax_number
ON company.company_id = ldap_fax_number.company_id
LEFT OUTER JOIN ldap_mobile_number
ON company.company_id = ldap_mobile_number.company_id
where company.is_enterprise =1 and ((is_private = 0) or (is_private is NULL))
and address.type = 'bill';
------------------------------------------------------------------------------------
-- View of "telephone"
-- presented numbers of type 01_tel -- LDAP mapping: number -> telephonenumber
--
CREATE VIEW ldap_office_number
(telephone_id, company_id, number)
AS select telephone_id, company_id, number
from telephone
where type = '01_tel'
and number is not NULL;
-----------------------------------------------------------------------------------
-- View of "telephone"
-- presented numbers of type 10_fax
-- LDAP mapping: number -> facsimiletelephonenumber
CREATE VIEW ldap_fax_number
(telephone_id, company_id, number)
AS select telephone_id, company_id, number
from telephone
where type = '10_fax'
and number is not NULL
and length(number) > 0;
---------------------------------------------------------------------------
-- View of "telephone"
-- presented numbers of type 03_tel_func
-- LDAP mapping: number -> mobile
--
CREATE VIEW ldap_mobile_number
(telephone_id, company_id, number)
AS select telephone_id, company_id, number
from telephone
where type = '03_tel_funk'
and number is not NULL
and length(number) >0;
-----------------------------------------------------------------------
-- View of "company_value"
-- Present values of type 'email1'
-- LDAP mapping: email -> mail
--
CREATE VIEW ldap_person_email
(company_id, email)
AS select company_id, value_string
from company_value
where attribute = 'email1';
-----------------------------------------------------
-- View of "company_value"
-- Present values of type 'job_title'
-- LDAP mapping: title -> title
CREATE VIEW ldap_person_title
(company_id, title)
AS select company_id, value_string
from company_value
where attribute = 'job_title';
------------- LIST OF COMPANIES A PERSON IS ASSOCIATED WITH -- View of company_assignment
-- Present names of organizations a person has been assigned to in ogo
-- LDAP Mapping: name -> o
CREATE VIEW ldap_person_company (company_id, name) AS
SELECT company_assignment.sub_company_id, company.description
FROM company, company_assignment
WHERE company.is_enterprise = 1
AND company.company_id = company_assignment.company_id;
----------------------------------------------------------------------------
--
-- BACK-SQL REQUIRED TABLE
-- CREATES BASIC objectclass MAP
--
create table ldap_oc_mappings
(
id serial not null primary key,
name varchar(64) not null,
keytbl varchar(64) not null,
keycol varchar(64) not null,
create_proc varchar(255),
delete_proc varchar(255),
expect_return int not null
);
-- Load a mapping entry into ldap_oc_mappings
-- A person object exists for every company_id key in the ldap_person
view
insert into ldap_oc_mappings
VALUES (1, 'person', 'ldap_person', 'company_id', NULL, NULL, 0);
insert into ldap_oc_mappings
VALUES (2, 'organization', 'ldap_enterprise', 'company_id', NULL, NULL,
0);
insert into ldap_oc_mappings
VALUES (3, 'groupOfUniqueNames', 'ldap_access_control', 'company_id',
NULL, NULL, 0);
insert into ldap_oc_mappings
VALUES (4, 'organizationalUnit', 'ldap_static_entries', 'id', NULL,
NULL, 0);
---- BACK-SQL TABLE USED FOR MAPPING FIELDS TO ATTRIBUTES -------
-- BACK-SQL REQUIRED TABLE
-- MAPS DATABASE FIELDS TO LDAP ATTRIBUTES
create table ldap_attr_mappings
(
id serial not null primary key,
oc_map_id integer not null references ldap_oc_mappings(id),
name varchar(255) not null,
sel_expr varchar(255) not null,
sel_expr_u varchar(255),
from_tbls varchar(255) not null,
join_where varchar(255),
add_proc varchar(255),
delete_proc varchar(255),
param_order int not null,
expect_return int not null
);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (100,1,'cn','cn','ldap_person', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (101,1,'givenname','firstname','ldap_person', NULL, NULL, NULL,
3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (102,1,'sn','lastname','ldap_person', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (103,1,'initials','initials','ldap_person', NULL, NULL, NULL, 3,
0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (104,1,'birthdate','birthday','ldap_person', NULL, NULL, NULL, 3,
0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (105,1,'uid','uid','ldap_person', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (106,1,'street','street','ldap_person', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (107,1,'l','city','ldap_person', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (108,1,'st','state','ldap_person', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (109,1,'c','country','ldap_person', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (110,1,'postalcode','zip','ldap_person', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (111,1,'postaladdress','postaladdress','ldap_person', NULL, NULL,
NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (112,1,'telephonenumber','phone','ldap_person', NULL, NULL, NULL,
3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (113,1,'facsimiletelephonenumber','fax','ldap_person', NULL,
NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (114,1,'mobile','mobile','ldap_person', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (115,1,'mail','email','ldap_person', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (116,1,'title','title','ldap_person', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (200,1,'o','name','ldap_person,ldap_person_company',
'ldap_person.company_id = ldap_person_company.company_id', NULL, NULL,
3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (300,2,'o','description','ldap_enterprise', NULL, NULL, NULL, 3,
0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (301,2,'street','street','ldap_enterprise', NULL, NULL, NULL, 3,
0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (302,2,'postalcode','zip','ldap_enterprise', NULL, NULL, NULL, 3,
0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (303,2,'l','zipcity','ldap_enterprise', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (304,2,'c','country','ldap_enterprise', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (305,2,'st','state','ldap_enterprise', NULL, NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (306,2,'postaladdress','postaladdress','ldap_enterprise', NULL,
NULL, NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (307,2,'telephonenumber','phone','ldap_enterprise', NULL, NULL,
NULL, 3, 0);
insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,
param_order,expect_return)
values (308,2,'facsimiletelephonenumber','fax','ldap_enterprise', NULL,
NULL, NULL, 3, 0);
-------- BACK SQL TABLE USED FOR MAPPING REFERRALS -------------
-- REQUIRED BACK-SQL TABLE
-- We aren't using this for anything, it just has to exist.
-- Somebody could of course use it if they wanted to generate
-- referals out of the OpenGroupware data.
create table ldap_referrals
(
entry_id integer not null,
url text not null
);
---- CUSTOM TABLE ADDED INTO THE back-sql MAPPING SCHEME ----------
-- TABLE: ldap_objectclass_list
-- We need to store a list of objectclasses to infect
CREATE TABLE ldap_objectclass_list (
oc_map_id int,
objectclass varchar(255));
insert into ldap_objectclass_list values(1, 'orgPerson');
insert into ldap_objectclass_list values(1, 'inetOrgPerson');
insert into ldap_objectclass_list values(1, 'officePerson');
insert into ldap_objectclass_list values(1, 'evolutionPerson');
insert into ldap_objectclass_list values(1, 'top');
insert into ldap_objectclass_list values(1, 'opengroupwareentity');
insert into ldap_objectclass_list values(2, 'top');
insert into ldap_objectclass_list values(2, 'opengroupwareentity');
-------------MAKE DNs AND HIERARCHY---------------------------------
-- We need to make some static entries in order to manifest a hierarchy
-- We are assuming "root" of OpenGroupware data presentation is -
-- ou=Subsystes,o=Morrison Industries,c=US
-- * Change according to your site in this and subsequent views
--
CREATE TABLE ldap_static_entries (
id int not null,
dn varchar(255),
oc int not null);
insert into ldap_static_entries (id, dn, oc)
values (10, 'ou=Contacts,ou=OpenGroupware,ou=Subsystems,o=Morrison
Industries,c=US', 4);
insert into ldap_static_entries (id, dn, oc)
values (20, 'ou=Enterprises,ou=OpenGroupware,ou=Subsystems,o=Morrison
Industries,c=US', 4);
insert into ldap_static_entries (id, dn, oc)
values (30,
'ou=AccessControl,ou=OpenGroupware,ou=Subsystems,o=Morrison
Industries,c=US', 4);
insert into ldap_static_entries (id, dn, oc)
values (40, 'ou=Documents,ou=OpenGroupware,ou=Subsystems,o=Morrison
Industries,c=US', 4);
-- oc_map_id : 1 = Person
-- oc_map_id : 2 = Organization
-- oc_map_id : 3 = Group
-- oc_map_id: 4 = Organizational Unit
CREATE VIEW ldap_entries
(id, dn, oc_map_id, parent, keyval) AS
SELECT id, dn, -- organizationalunit
4, -- parent is root
1, id
FROM ldap_static_entries
UNION SELECT company_id, 'opengroupwareid=' || company_id ||
',ou=Contacts,ou=OpenGroupware,ou=SubSystems,o=Morrison
Industries,c=US',
-- person
1,
-- parent is ou=Contacts
10,
company_id
from ldap_person
UNION SELECT company_id, 'opengroupwareid=' || company_id ||
',ou=Enterprises,ou=OpenGroupware,ou=SubSystems,o=Morrison
Industries,c=US',
-- organization
2,
-- parent is ou=Enterprises
20,
company_id
from ldap_enterprise
------------------------------------------------------------------------
--UNION SELECT company_id, -- 'opengroupwareid=' || company_id ||
',ou=AccessControl,ou=OpenGroupware,ou=SubSystems,o=Morrison
Industries,c=US',
-- -- group
-- 3,
-- -- parent is ou=AccessControl
-- 30,
-- company_id
--from ldap_access_control
----------------------------------------------------------------------------
-- REQUIRED BACK-SQL TABLE
-- This is a view instead of the table since we have no way of -- populating or updating it.
--
CREATE VIEW ldap_entry_objectclasses ( entry_id, oc_name )
AS SELECT id, objectclass
FROM ldap_entries, ldap_objectclass_list
WHERE ldap_entries.oc_map_id = ldap_objectclass_list.oc_map_id;