用postgresql作后台的openldap

5day

OpenLDAP-POSTGRESQL HOWTO
Gilles DAROLD, gilles __AT__ darold __DOT__ net

v1.0, 14 August 2001

v2.0, 15 Jan 2003

This document is about building an LDAP server with a PostgreSQL database as backend instead of the standard Bekerley DB. This HOWTO doesn't talk about OpenLDAP, PostgreSQL or iODBC in this document I suppose that you know all that, I just provide my personnal installation of these apps.
这篇文章讲关于用postgresql数据库代替标准的BDB作后台的ldap服务器 。这篇文章假定你对postgresql openldap iodbc都了解，所以并不会讲他们。我只提供我个人安装这些应用的过程。

1. Introduction介绍
1.1. Why a HOWTO ?为什么写这篇文章?

The previous version of this HOWTO need some patches to OpenLDAP, thank to the OpenLDAP devel team patches are no more needed and PostgreSQL backend is now fully supported at least on openldap v2.1.x.
我以前写的howto需要些OPENLDAP的补丁，感谢OPENLDAP的开发团队，现在OPENLDAP V2.1.x用postgresql作后台已经不需要补丁了。

So in this new version I will try to give all installation and configuration requirement to have OpenLDAP running with a PostgreSQL backend and a complete live example to let you see how is the work that should be done on a medium directory. This is what I have in production state in my company with a master Bekerley db backend replicated to a remote LDAP directory using PostgreSQL as backend.
所以，在这个新的版本中，我将把OPENLDAP用postgresql作后台的所有需要的安装、配置给大家，还有一个完整的活生生的例子，你可以看到要完成这个工作要作的事情。

1.2. Why a PostgreSQL backend ?为什么要用postgresql作后台?

When you start using LDAP, Bekerley DB (back-bdb) is enougth to have a powerfull directory. But when you want to link your LDAP server with some application you may need to retrieved thousand of LDAP entries as listing and you will find very poor performance using standard OpenLDAP backend.
当你开始使用ldap，bdb已经可以做成一个强大的目录。但是，当你想将你的一些应用连接到ldap服务器，你可能要写入上千个ldap记录作为列表，而且你将发现用标准的ldap后台的性能非常低。

That's what I was doing when I want to load 2500 email addresses into an address book webmail application. Loading these addresses with standard LDAP take a minute, loading the same data directly from a postgreSQL database with an SQL query takes 2 seconds. My choice was done...
那就是当我要装载2500个email地址到webmail的地址簿时作的事情。用标准ldap装载这些地址花了一些时间，从postgresql装载这些同样的数据只用了2秒。我的选择就是这样。

Also a database is more easy to manage than a bdb file but if you plan to use LDAP SQL backend in a complete replacement to bdb and do all your queries on the LDAP server with this type of backend you will experienced very poor performance du to the LDAP data model port to RDBMS.
数据库比bdb文件更容易管理，但是，根据我的经验，如果你计划用sql完全代替bdb，因为ldap数据模型来自RDBMS，所以你对这种ldap服务器作查询时性能较差。

For example querying localy some user on a high speed server:
例如，在高速服务器上查询本地的某用户：

ldapsearch -b "ou=people,dc=samse,dc=fr" "uid=*"

        Query of 2930 users in LDAP/DBD-Backend takes 1.7 seconds
        在bdb后台的ldap上查询2930个用户用了1.7秒.
        Query of 2930 users in LDAP/PostgreSQL-Backend takes 10 seconds
        在postgresql后台的ldap上查询2930个用户用了10秒.

psql pg_ldap -c "select * from people;"

        Query of 2930 users in PostgreSQL takes 0.5 seconds
        在postgresql上查询2930个用户用了0.5秒.   

An other interest to do that is when you already have all information into an existing database and you want to keep only one referential. So building LDAP on your existing database is the best way to not have to create a heavy replication process between your database and the LDAP server.
另外一个让人感兴趣的是当你已经拥有你个现存数据库的所有信息，你只想保留一个有参考内容的（??）。所以，为了你的数据库和ldap服务器间不建立沉重的复制过程，在现存的数据库上建立ldap是最好的办法。

Implementing an OpenLDAP SQL backend is not so difficult, the only painfull is on the LDAP schema mapping to your database. It can really takes hours depending on your schema...
建立sql后台的openldap并不复杂，唯一的麻烦是将ldap的schma映射到你的数据库。根据你的schema，那可能真的会用几个小时... ...

So in resume you can plan to have a SQL backend in this tree conditions:
假定你计划完成这个sql后台的ldap数据库是在这3个环境里：

        - You want to base your LDAP server to an existing RDBMS.
        －你想将你的ldap服务器放到现存的RDBMS上面。
        - You need to load thousand of entries at a time.
        －你很少的时间里需要装载上千的数据项。
        - You have time to map you database and develop your own tools.
        －你有时间映射你的数据库和开发工具到你自己的工具。

But remember that if you just want to improve performances of your LDAP directory you're on the wrong way...
但记住：如果你只希望增加你的ldap目录的性能，你走错路了...

1.3. Want to know more ?想知道更多?

There a better description of the difference between LDAP back-dbd and a SQL backend at : Directories vs. Relational Database Management.
更好的讲解sql后台的ldap和bdb后台的ldap不同之处的文章在：Directories vs. Relational Database Management.

You may also take a look at the servers/slapd/back-sql/docs where you can find documents about the concept of the implementation of a SQL backend into OpenLDAP. I suppose you already done that.
你可能也要看看servers/slapd/back-sql/docs，在这力你能发现关于安装sql后台的openldap的概念。我假定你已经看了。

2. Installation安装

I recommand OpenLDAP 2.1.12 or more, PostgreSQL 7.3.1 or more, iODBC 3.0.6 or more. These are the versions that I've tested and turn in production state.
我建议用openldap2.1.12或更高版本，postgresql7.3.1或更高版本，iODBC3.06或更高版本。这些是我已经测试通过并转入实用状态的版本。

2.1. PostgreSQL

PostgreSQL can be found at http://www.postgresql.org/.
下载postgresql的地方是：http://www.postgresql.org/.

See the PostgreSQL HOWTO if you don't know about PostgreSQL installation. You don't need any special configuration option to have LDAP working on your database now that the PostgreSQL ODBC library is external to PostgreSQL distribution. See next chapters.
如果你不知道postgresql的安装，看看postgresql的HOWTO。要将ldap工作在数据库上并不需要特别的配置，现在postgresql发行版的ODBC库是外部扩充的。

2.2. iODBC

iODBC can be found at OpenLink ODBC Drivers. Here is what I use to install libiodbc:
在OpenLink ODBC Drivers http://www.iodbc.org/ 可以找到iODBC。这是我用的iodbc库。

        ./configure --with-iodbc-inidir=/etc
        make
        make install

2.3. PostgreSQL ODBC Driver   postgresql的ODBC驱动

The PostgreSQL ODBC driver can be found at psqlODBC - The PostgreSQL ODBC Driver. Here is what I use to install libpsqlodbc:
在psqlODBC http://gborg.postgresql.org/project/psqlodbc/projdisplay.php 可以找到postgresql的ODBC驱动。这是我安装的libsqlodbc：

        ./configure --with-iodbc --with-odbcinst=/etc
        make
        make install

If you need full ODBC support with PostgreSQL take a look at the odbc.sql file. It contains some compatibility SQL function. Here with don't need it at all.
如果你需要postgresql完全支持ODBC，看看odbc.sql文件。它包括一些兼容sql的功能。这里并不需要它。

2.4. OpenLDAP

OpenLDAP can be found at http://www.openldap.org/. You need to compile OpenLDAP with the --enable-sql option. Here is what I use to install OpenLDAP:
在http://www.openldap.org/可以找到openldap，你在编译openldap时需要用到 --enable-sql选项。这是我安装openldap时作的：


        ./configure --enable-sql --without-cyrus-sasl --disable-bdb --enable-crypt
        make depend
        make
        make install

Here options --without-cyrus-sasl and --disable-bdb are used to remove Cyrus Sasl authentication support and the use of a Berkeley DB backend that could break the OpenLDAP configuration phase.
--without-cyrus-sasl 和 --disable-bdb选项用来取消cyrus sasl认证支持。使用bdb后台将???openldap的配置???。

If you use crypt password use the --enable-crypt configuration option else you will never be allowed to authenticate with ldap.
如果你使用加密的密码，除非你从不允许用ldap在认证，用 --enable-crpt 选项，

If configure complain about sql.h not found, remember that you have to install ODBC support before.
如果sql.h没找到，记着，要先安装ODBC支持。

3. Configuration配置
3.1. iODBC
With ODBC you have to configuration file: odbc.ini and odbcinst.ini. Here are the test samples configuration files I use:
配置ODBC你必须配置odbc.ini文件和odbcinst.ini文件。这是我用来测试演示的配置文件：

3.1.1. odbc.ini

Just copy this file into the /etc directory.
只需要复制这个文件到/etc目录。

;
;  odbc.ini
;
[ODBC Data Sources]
PgSQL=PostgreSQL

[PgSQL]
Driver=/usr/local/lib/psqlodbc.so
Description=Connection to LDAP/POSTGRESQL
Servername=localhost
Port=5432
Protocol=6.4
FetchBufferSize=99
Username=test
Password=test
Database=pg_ldap
ReadOnly=no
Debug=1
CommLog=1

[ODBC]
InstallDir=/usr/local/lib

[ 本帖最后由 5day 于 2005-11-4 10:19 编辑 ]

5day

3.1.2. odbcinst.ini

Normally the installation of psqlodbc library has already created this file, if not just copy this file into the /etc directory.
正常情况下，安装psqlodbc库就已经建立了这个文件，如果没有，只需复制这个文件到/etc目录。

;
;  odbcinst.ini
;
[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/local/lib/psqlodbc.so

[ODBC]
Trace=1
Debug=1
Pooling=No

3.2. OpenLDAP

You have to configure OpenLDAP to use the SQL backend, which database and some other SQL related specific option. See below:
openldap使用sql作后台时你必须配置openldap，数据库和一些其他的sql关联的特殊的选项。看看下面的：

3.2.1. slapd.conf

Just copy the file openldap-2.1.12/servers/slapd/back-sql/rdbms_depend/slapd.conf into the /usr/local/etc/openldap/ directory and change the dbname, dbuser, dbpasswd values as follow:
复制openldap-2.1.12/servers/slapd/back-sql/rdbms_depend/slapd.conf文件到/usr/local/etc/openldap/目录。改变dbname，dbuser，dbpasswd的值：

#
# See slapd.conf(5) for details on configuration options.
# This file should NOT be world readable.
#
include         /usr/local/etc/openldap/schema/core.schema
include         /usr/local/etc/openldap/schema/cosine.schema
include         /usr/local/etc/openldap/schema/inetorgperson.schema

# Define global ACLs to disable default read access.
# Define global ACLs to disable default read access.
access to *
        by self write
        by * read
access to * by dn="cn=root,o=sql,c=RU" write
defaultaccess none

# Do not enable referrals until AFTER you have a working directory
# service AND an understanding of referrals.
#referral       ldap://root.openldap.org

pidfile         /usr/local/var/slapd.pid
argsfile        /usr/local/var/slapd.args

#######################################################################
# sql database definitions
#######################################################################

database        sql
suffix         "o=sql,c=RU"
rootdn         "cn=root,o=sql,c=RU"
rootpw          secret
dbname          PgSQL
dbuser          test
dbpasswd        test
insentry_query  "insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values ((select max(id)+1 from ldap_entries),?,?,?,?)"
upper_func      "upper"
strcast_func    "text"
concat_pattern  "?||?"
has_ldapinfo_dn_ru      no

lastmod off

See "man slapd-sql" if you want to know more about the SQL related options. Also if you never take a look to the slapd.conf file begining with "man slapd.conf" should be a good choice.
如果你想了解更多的关于sql关联选项，用“man slapd-sql”.如果你从未看slapd.conf文件，从“man slapd.conf”开始是个好的选择。

3.3. PostgreSQL

There's no special configuration options to give to PostgreSQL. We just need to create the test database and the test user.
对于postgresql并没什么特别的配置选项。我们只需要建立测试数据库和测试用户。

You must su to user postgres or any other PostgreSQL superuser to perform the following task.
你必须su到postgres或者其他的postgresql的超级用户来作下面的任务：

3.3.1. Creating the test database and test user建立测试数据库和测试用户

Just run the following command to create the test database:
只需要运行下面的命令建立测试数据库：

        createdb pg_ldap

Just run the following command to create the user 'test' with password 'test':
只需运行下面的命令建立test用户用密码test：

        createuser --no-createdb --no-adduser --password test

and give the password 'test' at prompt.
在提示光标的地方输入密码“test”。

3.3.2. Creating the SQL backend for LDAP建立sql后台的ldap

To have OpenLDAP working with a SQL backend you must create the database structure and fill some information in it. All you need is to change directory to openldap-2.1.12/servers/slapd/back-sql/rdbms_depend/pgsql/ and run the following command as PostgreSQL superuser:
要将openldap 工作在sql后台上，你必须建立数据库结构并输入一些内容。你要作的所有工作就是：改变目录到openldap-2.1.12/servers/slapd/back-sql/rdbms_depend/pgsql/用postgresql超级用户运行下面的命令：

        psql pg_ldap  < backsql_create.sql

These tables are used by OpenLDAP to maintain all links between objects. This is the LDAP meta-structure.
这些表是openldap用来管理对象间所有的连接。这就是ldap的元结构。

3.3.3. Creating the test database schema建立测试数据库schema

We have now to create a schema with table representing our test LDAP objects. This can be done by using the rdbms_depend/testdb_*.sql files and running the following commands:
我们现在要用数据表建立一个schema，描述我们的ldap测试对象。使用rdbms_depend/testdb_*.sql文件并运行下面的命令可完成这个工作：


        psql -d pg_ldap < testdb_create.sql

These tables are used to create our test directory objects and their attributes.
这些数据表用来建立我们的测试目录对象和他们的属性。

3.3.4. Creating the metadata建立元数据

This part generate all links between the SQL backend and the stored object for the test database. Theses metainformation are used to translate LDAP queries to SQL queries. This part also generate all SQL function used by the metadata definition to create links between the SQL backend and the stored object for the test database and to store all attributes value.
这部分在sql后台和测试数据库中保存的对象中建立所有的连接。这些元信息用来改变ldap请求为sql请求。这部分也建立sql后台和数据库保存的对象和属性间的定义的用来连接元数据将使用的sql函数。

        psql -d pg_ldap < testdb_metadata.sql

3.3.5. Insert data for testing输入测试用的数据

This part insert some data into the test database. This can be done by saving the following SQL code into a file named testdb_data.sql and running the following command:
这部分输入一些数据到测试数据库。保存下面的sql代码到一个文件，更名为testdbdata.sql并运行下面的命令：

        psql -d pg_ldap < testdb_data.sql

3.3.6. Set grant on the database objects设置数据库对象的权限

To be able to run SQL queries onto the test database we must give the grant to user 'test'. This can be done by saving the following SQL code into a file named testdb_grant.sql and running the following command:
要在测试数据库上运行sql查询，我们必须赋予用户test权限。保存下面的sql代码到一个文件更名为testdb_grant.sql并运行下面的命令：

        psql -d pg_ldap -c "GRANT ALL ON ldap_attr_mappings,ldap_entries,ldap_entry_objclasses,ldap_oc_mappings,ldap_referrals TO test;"
        psql -d pg_ldap -c "GRANT ALL ON ldap_attr_mappings_id_seq,ldap_entries_id_seq,ldap_oc_mappings_id_seq TO test;"
        psql -d pg_ldap -c "GRANT ALL ON authors_docs,documents,institutes,persons,phones TO test;"
        psql -d pg_ldap -c "GRANT ALL ON documents_id_seq,institutes_id_seq,persons_id_seq,phones_id_seq TO test;"

4. Testing the ODBC installation测试ODBC安装

To test the ODBC installation simply run odbctest and give our DSN. Output must be as follow. If you don't have the SQL prompt you may have problem with ODBC and OpenLDAP SQL backend should not work as wanted.
要测试ODBC安装，简单的运行odbctest，给处我们的数据源。必须像下面这样输出。如果你没有SQL控制台，你的ODBC和SQL后台的OPENLDAP可能有问题，没有正常工作。

        /usr/local/bin/odbctest

        iODBC Demonstration program
        This program shows an interactive SQL processor

        Enter ODBC connect string (? shows list): DSN=PgSQL
        Driver: 07.02.0005

        SQL>

[ 本帖最后由 5day 于 2005-11-4 11:19 编辑 ]

5day

后面的我不想翻译了，太累了。
5. Testing the LDAP directory
5.1. Performing a search

To perform a search and find all entries just do the following LDAP query:


        ldapsearch -b "o=sql,c=RU" "(objectClass=*)"

This must return some result else you have miss something during the previous chapter.

To test more the SQL backend you can run the following test suite. All test are done in LDIFF format.

Something great is that you can create flat LDAP structure like in this test or more complex structure as tree.
5.2. Creating entry

To create some new entries just copy the following LDIFF code into a file and run the following command:


        ldapadd -D "cn=root,o=sql,c=RU" -w secret -f file

Note that it assume that your LDAP server is on localhost.


        # begin

        dn: cn=User Test_Add_Entry,o=sql,c=RU
        objectClass: inetOrgPerson
        sn: First Test_Add_Entry user
        cn: User Test_Add_Entry

        # end

5.3. Setting entry attribute

To set attributes of the new entries just copy the following LDIFF code into a file and run command:


        ldapmodify -D "cn=root,o=sql,c=RU" -w secret -f file

Note that it assume that your LDAP server is on localhost.



        # begin

        dn: cn=User Test_Add_Entry,o=sql,c=RU
        changetype: modify
        replace: sn
        sn: Test_Add_Entry user

        dn: cn=User Test_Add_Entry,o=sql,c=RU
        changetype: modify
        add: telephoneNumber
        telephoneNumber: 123-4567
        telephoneNumber: 765-4321

        # end

5.4. Performing a search

To perform a search and find all entries just do the following LDAP query:


        ldapsearch -b "o=sql,c=RU" "(objectClass=*)"

or just under the inetOrgPerson objectClass with cn begining with letter M:


        ldapsearch -b "o=sql,c=RU" "(&(objectClass=inetOrgPerson) (cn=M*))"

5.5. Deleting attribute

To delete attributes of the new entries just copy the following LDIFF code into a file and run command:


        ldapmodify -D "cn=root,o=sql,c=RU" -w secret -f file

This is an example with deleting an attribute with multiple values.


        # begin

        dn: cn=User Test_Add_Entry,o=sql,c=RU
        changetype: modify
        delete: telephoneNumber
        telephoneNumber: 332-2334

        # end

5.6. Deleting entry

To delete entry just copy the following LDIFF code into a file and run command:


        ldapadd -f file

Note that it assume that your LDAP server is on localhost.


        # begin

        dn: cn=User Test_Add_Entry,o=sql,c=RU
        changetype: delete

        # end

[ 本帖最后由 5day 于 2005-11-4 14:25 编辑 ]

5day

6. A live example

This is what we are running here in production state and all the things I've done to have a full working SQL backend. I hope it is complex enougth to handle most cases.

We have 20 running OpenLDAP servers with one master that received all change to the directory and replicate them to other servers. That we needed is to extract list of data very quickly to be used in our Intranet applications like email adresses book. So the best solution was to have an SQL database with all needed informations and of course without writing replication tools. A SQL backend to OpenLDAP that can be replicated from master like others LDAP servers is the solution.
6.1. The LDAP Schema

This directory has been build with six majors objectives:


        - Apache / Intranet authentication
        - Linux Pam-LDAP authentication
        - Sendmail / Cyrus Imap mailbox
        - Mail address book
        - Employees directory
        - Companies / agencies directory

The resulting LDAP structure is build as follow:


                ---------------  dc=samse, dc=fr -------------
              /           |            |             |         \
             /            |            |             |          \
        ou=company    ou=region    ou=agency    ou=people    ou=group

DN:         uid=...       uid=...      uid=...  employeeNumber=  cn=...

Ldiff dump of the LDAP structure and some dn:


# extended LDIF
#
# LDAPv3
# base <dc=samse,dc=fr> with scope sub
# filter: objectClass=*
# requesting: objectClass
#

# samse.fr
dn: dc=samse,dc=fr
objectClass: top
objectClass: dcObject
objectClass: organization

# people, samse.fr
dn: ou=people,dc=samse,dc=fr
objectClass: organizationalUnit

# group, samse.fr
dn: ou=group,dc=samse,dc=fr
objectClass: organizationalUnit

# agency, samse.fr
dn: ou=agency,dc=samse,dc=fr
objectClass: organizationalUnit

# region, samse.fr
dn: ou=region,dc=samse,dc=fr
objectClass: organizationalUnit

# company, samse.fr
dn: ou=company,dc=samse,dc=fr
objectClass: organizationalUnit

# 123456789, company, samse.fr
dn: uid=123456789,ou=company,dc=samse,dc=fr
objectClass: samseOu

# R7, region, samse.fr
dn: uid=R7,ou=region,dc=samse,dc=fr
objectClass: samseOu

# 123, agency, samse.fr
dn: uid=123,ou=agency,dc=samse,dc=fr
objectClass: samseOu

# communication, group, samse.fr
dn: cn=users,ou=group,dc=samse,dc=fr
objectClass: top
objectClass: posixGroup

# 001234, people, samse.fr
dn: employeeNumber=001234,ou=people,dc=samse,dc=fr
objectClass: top
objectClass: person
objectClass: organizationalPerson
objectClass: inetOrgPerson
objectClass: samsePerson
objectClass: posixAccount
objectClass: shadowAccount

We also have specials attributes that are not handle by any OpenLDAP standard schema. So we have made two personnal objectClass allowing the use of these attributes. As you see above these objectClass are: 'samseOu' and 'samsePerson'. They are build as follow:


#
# SAMSE directory schema items
#

# Date d'arrivee dans le groupe de la personne
attributetype ( 1.3.6.1.4.1.3814.4.1 NAME ( 'arrivalDate' ) SUP name)
# Date de depart de la personne
attributetype ( 1.3.6.1.4.1.3814.4.2 NAME ( 'leavingDate' ) SUP name)
# Groupe cotisant de la personne
attributetype ( 1.3.6.1.4.1.3814.4.3 NAME ( 'contributionGroup' ) SUP name)
# Sexe de la personne
attributetype ( 1.3.6.1.4.1.3814.4.4 NAME ( 'sexe' ) SUP name)
# Date de naissance de la personne
attributetype ( 1.3.6.1.4.1.3814.4.5 NAME ( 'birthDate' ) SUP name)
# Raccourci telephone fixe
attributetype ( 1.3.6.1.4.1.3814.4.6 NAME ( 'fixRPVNumber' ) SUP name)
# Raccourci portable
attributetype ( 1.3.6.1.4.1.3814.4.7 NAME ( 'mobileRPVNumber' ) SUP name)
# Forme jurique
attributetype ( 1.3.6.1.4.1.3814.4.8 NAME ( 'legalForm' ) SUP name)
# Capital
attributetype ( 1.3.6.1.4.1.3814.4.9 NAME ( 'capital' ) SUP name)
# Convention Collective
attributetype ( 1.3.6.1.4.1.3814.4.10 NAME ( 'collectiveConvention' ) SUP name)
# Responsable
attributetype ( 1.3.6.1.4.1.3814.4.11 NAME ( 'responsable' ) SUP name)
# Qualite du Responsable
attributetype ( 1.3.6.1.4.1.3814.4.12 NAME ( 'quality' ) SUP name)
# Assistant(e)
attributetype ( 1.3.6.1.4.1.3814.4.13 NAME ( 'assistant' ) SUP name)
# Region d'une agence
attributetype ( 1.3.6.1.4.1.3814.4.14 NAME ( 'region' ) SUP name)
# Nom de la mailbox d'un user
attributetype ( 1.3.6.1.4.1.3814.4.15 NAME ( 'mailboxName' ) SUP name)
# Abrege agence
attributetype ( 1.3.6.1.4.1.3814.4.16 NAME ( 'rpvNumber' ) SUP name)
# Numero de societe
attributetype ( 1.3.6.1.4.1.3814.4.17 NAME ( 'companyNumber' ) SUP name)
# Enseigne de l'agence
attributetype ( 1.3.6.1.4.1.3814.4.18 NAME ( 'agencyShield' ) SUP name)
# Domaine Internet de l'agence
attributetype ( 1.3.6.1.4.1.3814.4.19 NAME ( 'mailDomain' ) SUP name)
# Domaine d'activite de l'employe
attributetype ( 1.3.6.1.4.1.3814.4.20 NAME ( 'activite' ) SUP name)
# Fonction de l'employe
attributetype ( 1.3.6.1.4.1.3814.4.21 NAME ( 'fonction' ) SUP name)
# Code LIDIS de l'agence de l'employe
attributetype ( 1.3.6.1.4.1.3814.4.22 NAME ( 'codeAgence' ) SUP name)
# Code APE d'une agence
attributetype ( 1.3.6.1.4.1.3814.4.23 NAME ( 'codeAPE' ) SUP name)
# Site physique d'une agence
attributetype ( 1.3.6.1.4.1.3814.4.24 NAME ( 'realSite' ) SUP name)
# Code emploi d'un employe BAO
attributetype ( 1.3.6.1.4.1.3814.4.25 NAME ( 'codeEmploi' ) SUP name)
# Code Lidis d'une agence
attributetype ( 1.3.6.1.4.1.3814.4.26 NAME ( 'codeLidis' ) SUP name)
# Service auquel est associe une personne
attributetype ( 1.3.6.1.4.1.3814.4.27 NAME ( 'service' ) SUP name)
# Type de contrat RTT
attributetype ( 1.3.6.1.4.1.3814.4.28 NAME ( 'typeRTT' ) SUP name)

objectClass ( 1.3.6.1.4.1.3814.3.4 NAME 'samseOu'
        DESC 'SAMSE Organizational Unit Object'
        SUP organizationalUnit
        MUST ( uid )
        MAY ( legalForm $ capital $ collectiveConvention $ quality $ responsable $ region $ rpvNumber $ companyNumber $ agencyShield $ mailDomain $ codeAPE $ realSite $ codeLidis) )

objectClass ( 1.3.6.1.4.1.3814.3.5 NAME 'samsePerson'
        DESC 'SAMSE Person'
        SUP ( inetOrgPerson )
        MUST ( uid $ cn )
        MAY ( arrivalDate $ leavingDate $ contributionGroup $ sexe $ birthDate $ fixRPVNumber $ mobileRPVNumber $ assistant $ mailboxName $ activite $ fonction $ codeAgence $ codeEmploi $ service $ typeRTT) )

Note that OIDs used here are not registered and that there's no constraint defined for these attributes to simplify things.

5day

6.2. The back-sql LDAP configuration

Here is the slapd.conf file we use:


#
# See slapd.conf(5) for details on configuration options.
# This file should NOT be world readable.
#
include                /usr/local/etc/openldap/schema/core.schema
include                /usr/local/etc/openldap/schema/cosine.schema
include                /usr/local/etc/openldap/schema/inetorgperson.schema
include                /usr/local/etc/openldap/schema/nis.schema
# Our own schema
include                /usr/local/etc/openldap/schema/samse.schema

# Define global ACLs to disable default read access.
# Define global ACLs to disable default read access.
access to *
        by self write
        by * read
access to * by dn="cn=root,dc=samse,dc=fr" write
allow   bind_v2

# Do not enable referrals until AFTER you have a working directory
# service AND an understanding of referrals.
#referral       ldap://root.openldap.org

pidfile                /usr/local/var/slapd.pid
argsfile        /usr/local/var/slapd.args

#######################################################################
# sql database definitions
#######################################################################

database        sql
suffix                "dc=samse,dc=fr"
rootdn                "cn=root,dc=samse,dc=fr"
rootpw                secret
dbname                PgSQL
dbuser                test
dbpasswd        test
insentry_query        "insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values ((select max(id)+1 from ldap_entries),?,?,?,?)"
upper_func        "upper"
strcast_func        "text"
concat_pattern        "?||?"
has_ldapinfo_dn_ru        no

schemacheck        on

lastmod off


The last two lines are uncommented to allow direct loading of initial datas. After that they are removed to allow replication from the master.

The lastmod option is set to off to disable last modification timestamp recording.
6.3. Creating the PostgreSQL backend

The creation of the PostgreSQL backend is done as above in the howto. The only change is that we have now our own database schema.
6.3.1. The live database schema


BEGIN TRANSACTION;

CREATE TABLE dc_object (
        id serial NOT NULL PRIMARY KEY,
        dc varchar(80) NOT NULL UNIQUE,
        description varchar(255)
);

CREATE TABLE organizational_unit (
        id serial NOT NULL PRIMARY KEY,
        ou varchar(40) NOT NULL UNIQUE,
        description varchar(255)
);

CREATE TABLE posix_group (
        id serial NOT NULL PRIMARY KEY,
        cn varchar(255) NOT NULL UNIQUE,
        gidnumber int NOT NULL UNIQUE
);

CREATE TABLE samse_ou (
        id serial NOT NULL PRIMARY KEY,
        ou varchar(255) NOT NULL,
        uid varchar(40) NOT NULL UNIQUE,
        legalform varchar(255),
        capital varchar(255),
        collectiveconvention varchar(255),
        description varchar(255),
        responsable varchar(1024),
        quality varchar(255),
        internationalisdnnumber varchar(25),
        businesscategory varchar(80),
        telephonenumber varchar(25),
        facsimiletelephonenumber varchar(25),
        street varchar(255),
        postofficebox varchar(10),
        postalcode varchar(6),
        l varchar(255),
        region varchar(80),
        rpvnumber varchar(15),
        companynumber int,
        agencyshield varchar(80),
        maildomain varchar(80),
        codeape varchar(15),
        realsite varchar(40),
        codelidis int
);

CREATE TABLE group_member (
        group_id int NOT NULL references posix_group(id),
        memberuid varchar(6),
        UNIQUE(group_id,memberuid)
);

CREATE TABLE samse_person (
        id serial NOT NULL PRIMARY KEY,
        ou varchar(255) NOT NULL,
        employeenumber varchar(6) NOT NULL UNIQUE,
        uid varchar(10) NOT NULL,
        cn varchar(12,
        sn varchar(80),
        givenname varchar(40),
        displayname varchar(12,
        mail varchar(255),
        mailboxname varchar(80),
        title varchar(255),
        employeetype char(1),
        departmentnumber int,
        arrivaldate varchar(10),
        birthdate varchar(10),
        leavingdate varchar(10),
        contributiongroup char(2),
        sexe char(1),
        userpassword varchar(80),
        loginshell varchar(80),
        homedirectory varchar(80),
        uidnumber int,
        gidnumber int,
        shadowlastchange int,
        shadowmin int,
        shadowmax int,
        shadowwarning int,
        shadowexpire int,
        telephonenumber varchar(25),
        fixrpvnumber varchar(10),
        mobile varchar(25),
        mobilerpvnumber varchar(10),
        assistant varchar(255),
        codeemploi varchar(10),
        codeagence int,
        fonction varchar(1),
        service varchar(255),
        typeRTT varchar(10)
);

CREATE TABLE samse_activite (
        person_id int NOT NULL references samse_person(id),
        activite varchar(10)
);


END TRANSACTION;

As you can see we have two attributes that can handle multiple value. This is 'memberuid' part of the posixAccount objectClass and 'activite' part of the samsePerson objectClass. So we need to create two separate table to received these value (n to n). These tables are 'group_member' and 'samse_activite'.

5day

6.3.2. The metadata

This is the longest part of the process with the SQL function definition. I give them all so that you can copy and past what you want to match your own needs. Don't forget to thanks me a lot )


BEGIN TRANSACTION;

------------------------------------------------
-- objectClass  mappings
------------------------------------------------
-- The root directory dcObject
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return) values (1,'dcObject','dc_object','id',NULL,NULL,0);

-- The organizationalUnit objectClass
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return) values (2,'organizationalUnit','organizational_unit','id',NULL,NULL,0);

-- The samseOu objectClass
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return) values (3,'samseOu','samse_ou','id','select create_samse_ou()','select delete_samse_ou(?)',0);

-- The posixGroup objectClass
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return) values (4,'posixGroup','posix_group','id','select create_posix_group()','select delete_posix_group(?)',0);

-- The samsePerson objectClass
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return)
values (5,'samsePerson','samse_person','id','SELECT create_samse_person()','SELECT delete_samse_person(?)',0);

SELECT setval ('ldap_oc_mappings_id_seq', 5, false);

SELECT setval ('samse_ou_id_seq', 1, false);

SELECT setval ('posix_group_id_seq', 1, false);


------------------------------------------------
-- attributes mappings
------------------------------------------------
-- dcObject's attributes
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 (1,1,'dc','dc_object.dc','dc_object',NULL,NULL,NULL,3,0);

-- organizationalUnit's attributes
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 (2,2,'ou','organizational_unit.ou','organizational_unit',NULL,NULL,NULL,3,0);

-- samseOu's attributes
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 (3,3,'uid','samse_ou.uid','samse_ou',NULL,'{ call set_samse_ou_uid(?,?) }',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 (4,3,'ou','samse_ou.ou','samse_ou',NULL,'{ call set_samse_ou_ou(?,?) }',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 (5,3,'capital','samse_ou.capital','samse_ou',NULL,'{ call set_samse_ou_capital(?,?) }','{ call del_samse_ou_capital(?,?) }',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 (6,3,'legalForm','samse_ou.legalform','samse_ou',NULL,'{ call set_samse_ou_legalform(?,?) }','{ call del_samse_ou_legalform(?,?) }',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 (7,3,'collectiveConvention','samse_ou.collectiveconvention','samse_ou',NULL,'{ call set_samse_ou_collectiveconv(?,?) }','{ call del_samse_ou_collectiveconv(?,?) }',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 (8,3,'description','samse_ou.description','samse_ou',NULL,'{ call set_samse_ou_description(?,?) }','{ call del_samse_ou_description(?,?) }',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 (9,3,'responsable','samse_ou.responsable','samse_ou',NULL,'{ call set_samse_ou_responsable(?,?) }','{ call del_samse_ou_responsable(?,?) }',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 (10,3,'quality','samse_ou.quality','samse_ou',NULL,'{ call set_samse_ou_quality(?,?) }','{ call del_samse_ou_quality(?,?) }',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 (11,3,'internationaliSDNNumber','samse_ou.internationalisdnnumber','samse_ou',NULL,'{ call set_samse_ou_internationalisdn(?,?) }','{ call del_samse_ou_internationalisdn(?,?) }',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 (12,3,'businessCategory','samse_ou.businesscategory','samse_ou',NULL,'{ call set_samse_ou_businesscategory(?,?) }','{ call del_samse_ou_businesscategory(?,?) }',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 (13,3,'telephoneNumber','samse_ou.telephonenumber','samse_ou',NULL,'{ call set_samse_ou_telephonenumber(?,?) }','{ call del_samse_ou_telephonenumber(?,?) }',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 (14,3,'facsimileTelephoneNumber','samse_ou.facsimiletelephonenumber','samse_ou',NULL,'{ call set_samse_ou_fax(?,?) }','{ call del_samse_ou_fax(?,?) }',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 (15,3,'street','samse_ou.street','samse_ou',NULL,'{ call set_samse_ou_street(?,?) }','{ call del_samse_ou_street(?,?) }',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 (16,3,'postOfficeBox','samse_ou.postofficebox','samse_ou',NULL,'{ call set_samse_ou_postofficebox(?,?) }','{ call del_samse_ou_postofficebox(?,?) }',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 (17,3,'postalCode','samse_ou.postalcode','samse_ou',NULL,'{ call set_samse_ou_postalcode(?,?) }','{ call del_samse_ou_postalcode(?,?) }',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 (18,3,'l','samse_ou.l','samse_ou',NULL,'{ call set_samse_ou_l(?,?) }','{ call del_samse_ou_l(?,?) }',3,0);

5day

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 (19,3,'region','samse_ou.region','samse_ou',NULL,'{ call set_samse_ou_region(?,?) }','{ call del_samse_ou_region(?,?) }',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 (20,3,'rpvNumber','samse_ou.rpvnumber','samse_ou',NULL,'{ call set_samse_ou_rpvnumber(?,?) }','{ call del_samse_ou_rpvnumber(?,?) }',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 (21,3,'companyNumber','samse_ou.companynumber','samse_ou',NULL,'{ call set_samse_ou_companynumber(?,?) }','{ call del_samse_ou_companynumber(?,?) }',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 (22,3,'agencyShield','samse_ou.agencyshield','samse_ou',NULL,'{ call set_samse_ou_agencyshield(?,?) }','{ call del_samse_ou_agencyshield(?,?) }',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 (23,3,'mailDomain','samse_ou.maildomain','samse_ou',NULL,'{ call set_samse_ou_maildomain(?,?) }','{ call del_samse_ou_maildomain(?,?) }',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 (24,3,'codeApe','samse_ou.codeape','samse_ou',NULL,'{ call set_samse_ou_codeape(?,?) }','{ call del_samse_ou_codeape(?,?) }',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 (25,3,'realSite','samse_ou.realsite','samse_ou',NULL,'{ call set_samse_ou_realsite(?,?) }','{ call del_samse_ou_realsite(?,?) }',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 (26,3,'codeLidis','samse_ou.codelidis','samse_ou',NULL,'{ call set_samse_ou_codelidis(?,?) }','{ call del_samse_ou_codelidis(?,?) }',3,0);
SELECT setval ('ldap_attr_mappings_id_seq', 26, false);

-- posixGroup's attributes
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 (27,4,'cn','posix_group.cn','posix_group',NULL,'{ call set_posix_group_cn(?,?) }',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 (28,4,'gidnumber','posix_group.gidnumber','posix_group',NULL,'{ call set_posix_group_gidnumber(?,?) }',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 (29,4,'memberUid','group_member.memberuid','group_member,posix_group','group_member.group_id=posix_group.id','{ call set_group_member_memberuid(?,?) }','{ call del_group_member_memberuid(?,?) }',3,0);

-- samsePerson's attributes

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 (30,5,'ou','samse_person.ou','samse_person',NULL,'{ call set_samse_person_ou(?,?) }',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 (31,5,'employeeNumber','samse_person.employeenumber','samse_person',NULL,'{ call set_samse_person_employeenumber(?,?) }',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 (32,5,'uid','samse_person.uid','samse_person',NULL,'{ call set_samse_person_uid(?,?) }',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 (33,5,'cn','samse_person.cn','samse_person',NULL,'{ call set_samse_person_cn(?,?) }','{ call del_samse_person_cn(?,?) }',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 (34,5,'sn','samse_person.sn','samse_person',NULL,'{ call set_samse_person_sn(?,?) }','{ call del_samse_person_sn(?,?) }',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 (36,5,'givenName','samse_person.givenname','samse_person',NULL,'{ call set_samse_person_givenname(?,?) }','{ call del_samse_person_givenname(?,?) }',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 (37,5,'displayName','samse_person.displayname','samse_person',NULL,'{ call set_samse_person_displayname(?,?) }','{ call del_samse_person_displayname(?,?) }',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 (38,5,'mail','samse_person.mail','samse_person',NULL,'{ call set_samse_person_mail(?,?) }','{ call del_samse_person_mail(?,?) }',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 (39,5,'mailboxName','samse_person.mailboxname','samse_person',NULL,'{ call set_samse_person_mailboxname(?,?) }','{ call del_samse_person_mailboxname(?,?) }',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 (40,5,'title','samse_person.title','samse_person',NULL,'{ call set_samse_person_title(?,?) }','{ call del_samse_person_title(?,?) }',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 (41,5,'employeeType','samse_person.employeetype','samse_person',NULL,'{ call set_samse_person_employeetype(?,?) }','{ call del_samse_person_employeetype(?,?) }',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 (42,5,'departmentNumber','samse_person.departmentnumber','samse_person',NULL,'{ call set_samse_person_departmentnum(?,?) }','{ call del_samse_person_departmentnum(?,?) }',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 (43,5,'arrivalDate','samse_person.arrivaldate','samse_person',NULL,'{ call set_samse_person_arrivaldate(?,?) }','{ call del_samse_person_arrivaldate(?,?) }',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 (44,5,'birthDate','samse_person.birthdate','samse_person',NULL,'{ call set_samse_person_birthdate(?,?) }','{ call del_samse_person_birthdate(?,?) }',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 (46,5,'leavingDate','samse_person.leavingdate','samse_person',NULL,'{ call set_samse_person_leavingdate(?,?) }','{ call del_samse_person_leavingdate(?,?) }',3,0);

5day

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 (47,5,'contributionGroup','samse_person.contributiongroup','samse_person',NULL,'{ call set_samse_person_contribgroup(?,?) }','{ call del_samse_person_contribgroup(?,?) }',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 (48,5,'userPassword','samse_person.userpassword','samse_person',NULL,'{ call set_samse_person_userpassword(?,?) }','{ call del_samse_person_userpassword(?,?) }',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 (49,5,'loginShell','samse_person.loginshell','samse_person',NULL,'{ call set_samse_person_loginshell(?,?) }','{ call del_samse_person_loginshell(?,?) }',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 (50,5,'uidNumber','samse_person.uidnumber','samse_person',NULL,'{ call set_samse_person_uidnumber(?,?) }','{ call del_samse_person_uidnumber(?,?) }',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 (51,5,'gidNumber','samse_person.gidnumber','samse_person',NULL,'{ call set_samse_person_gidnumber(?,?) }','{ call del_samse_person_gidnumber(?,?) }',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 (52,5,'homeDirectory','samse_person.homedirectory','samse_person',NULL,'{ call set_samse_person_homedirectory(?,?) }','{ call del_samse_person_homedirectory(?,?) }',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 (53,5,'shadowLastChange','samse_person.shadowlastchange','samse_person',NULL,'{ call set_samse_person_shadowlastchg(?,?) }','{ call del_samse_person_shadowlastchg(?,?) }',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 (54,5,'shadowMin','samse_person.shadowmin','samse_person',NULL,'{ call set_samse_person_shadowmin(?,?) }','{ call del_samse_person_shadowmin(?,?) }',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 (55,5,'shadowMax','samse_person.shadowmax','samse_person',NULL,'{ call set_samse_person_shadowmax(?,?) }','{ call del_samse_person_shadowmax(?,?) }',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 (56,5,'shadowWarning','samse_person.shadowwarning','samse_person',NULL,'{ call set_samse_person_shadowwarning(?,?) }','{ call del_samse_person_shadowwarning(?,?) }',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 (57,5,'telephoneNumber','samse_person.telephonenumber','samse_person',NULL,'{ call set_samse_person_phonenumber(?,?) }','{ call del_samse_person_telephonenumber(?,?) }',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 (58,5,'fixRPVNumber','samse_person.fixrpvnumber','samse_person',NULL,'{ call set_samse_person_fixrpvnumber(?,?) }','{ call del_samse_person_fixrpvnumber(?,?) }',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 (59,5,'mobile','samse_person.mobile','samse_person',NULL,'{ call set_samse_person_mobile(?,?) }','{ call del_samse_person_mobile(?,?) }',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 (60,5,'mobileRPVNumber','samse_person.mobilerpvnumber','samse_person',NULL,'{ call set_samse_person_mobilerpvnum(?,?) }','{ call del_samse_person_mobilerpvnum(?,?) }',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 (61,5,'assistant','samse_person.assistant','samse_person',NULL,'{ call set_samse_person_assistant(?,?) }','{ call del_samse_person_assistant(?,?) }',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 (62,5,'sexe','samse_person.sexe','samse_person',NULL,'{ call set_samse_person_sexe(?,?) }','{ call del_samse_person_sexe(?,?) }',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 (63,5,'codeEmploi','samse_person.codeemploi','samse_person',NULL,'{ call set_samse_person_codeemploi(?,?) }','{ call del_samse_person_codeemploi(?,?) }',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 (64,5,'codeAgence','samse_person.codeagence','samse_person',NULL,'{ call set_samse_person_codeagence(?,?) }','{ call del_samse_person_codeagence(?,?) }',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 (65,5,'fonction','samse_person.fonction','samse_person',NULL,'{ call set_samse_person_fonction(?,?) }','{ call del_samse_person_fonction(?,?) }',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 (66,5,'activite','samse_activite.activite','samse_activite,samse_person','samse_activite.person_id=samse_person.id','{ call set_samse_activite_activite(?,?) }','{ call del_samse_activite_activite(?,?) }',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 (67,5,'shadowExpire','samse_person.shadowexpire','samse_person',NULL,'{ call set_samse_person_shadowexpire(?,?) }','{ call del_samse_person_shadowexpire(?,?) }',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 (68,5,'service','samse_person.service','samse_person',NULL,'{ call set_samse_person_service(?,?) }','{ call del_samse_person_service(?,?) }',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 (69,5,'typeRTT','samse_person.typertt','samse_person',NULL,'{ call set_samse_person_typertt(?,?) }','{ call del_samse_person_typertt(?,?) }',3,0);

5day

-- Entries

-- Insert the root directory dcObject
insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values (1, 'dc=samse,dc=fr',1,0,1);
insert into dc_object (id,dc) values (1,'samse');
SELECT setval ('dc_object_id_seq', 1, false);

-- Insert all organizationalUnit objects
insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values (2, 'ou=company,dc=samse,dc=fr',2,1,1);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values (3, 'ou=region,dc=samse,dc=fr',2,1,2);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values (4, 'ou=agency,dc=samse,dc=fr',2,1,3);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values (5, 'ou=people,dc=samse,dc=fr',2,1,4);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values (6, 'ou=group,dc=samse,dc=fr',2,1,5);
SELECT setval ('ldap_entries_id_seq', 6, false);

-- referrals

insert into ldap_entry_objclasses (entry_id,oc_name) values (1,'referral');

insert into ldap_referrals (entry_id,url) values (1,'ldap://localhost/');

END TRANSACTION;

5day

6.3.3. The SQL functions


-- If you don't have PL/PGSQL activated uncomment the following lines

DROP FUNCTION plpgsql_call_handler();
DROP PROCEDURAL LANGUAGE 'plpgsql';
CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';

BEGIN TRANSACTION;

---------------------------------------------------------------------------------
-- Create internal procedures for samseOu objectClass
-- SAMSE: samseOu objectClass
---------------------------------------------------------------------------------

CREATE FUNCTION create_samse_ou () RETURNS int
AS '
        SELECT setval (''samse_ou_id_seq'', (select max(id) FROM samse_ou));
        INSERT INTO samse_ou (id,ou,uid)
                VALUES (nextval(''samse_ou_id_seq''),'''','''');
        SELECT max(id) FROM samse_ou
'
LANGUAGE 'sql';

CREATE FUNCTION delete_samse_ou (int) RETURNS int
AS '
        DELETE FROM samse_ou WHERE id=CAST($1 AS INT);
        SELECT $1 AS RETURN
'
LANGUAGE 'sql';


---------------------------------------------------------------------------------
-- Create internal procedures for modifying samseOu attributes
-- SAMSE: ou=company,dc=samse,dc=fr
-- SAMSE: ou=region,dc=samse,dc=fr
-- SAMSE: ou=agency,dc=samse,dc=fr
---------------------------------------------------------------------------------

CREATE FUNCTION set_samse_ou_uid (varchar,int) RETURNS int
AS '
        UPDATE samse_ou SET uid=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_ou(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET ou=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_legalform(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET legalform=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_legalform(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET legalform=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_capital(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET capital=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_capital(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET capital=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_collectiveconv(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET collectiveconvention=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_collectiveconv(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET collectiveconvention=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_description(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET description=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_description(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET description=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';