LDAP Foreign Data Wrapper for PostgreSQL 9.2

Synopsis

A PostgreSQL's Foreign Data Wrapper (FDW) to query LDAP servers.

Warnings

  • USE WITH CAUTION: It is not production ready but you could test it in your test server and help us to improve it;
  • it not implements limits yet, so if you do a select * from ldap_table it will fetch all records from LDAP server. Some LDAP servers limits this to 500;
  • by now it only supports two columns: dn and object_body where the former is populated with the DN and the last with the LDAP entry converted to a Hstore compatible format;

Description

LDAP Foreign Data Wrapper for PostgreSQL 9.2.

Usage

Create the extension in your database:

CREATE EXTENSION ldap_fdw;

Then create a foreign server to connect to your LDAP server:

CREATE SERVER ldap_myldap_server
FOREIGN DATA WRAPPER ldap_fdw
OPTIONS ( address 'myldap_server_address', port '389');

Create user mapping:

CREATE USER MAPPING FOR current_user
SERVER ldap_myldap_server
OPTIONS (user_dn 'cn=SomeUser,dc=example,dc=net', password 'the_user_password');

Finally create a foreign table with a base DN pointing to some OU:

CREATE FOREIGN TABLE ldap_people (
   dn text,
   object_body text
)
SERVER ldap_myldap_server
OPTIONS (base_dn 'OU=people,DC=example,DC=net');

And voila!

> SELECT * FROM ldap_people;

                        dn                         |                          object_body                           
---------------------------------------------------+----------------------------------------------------------------
 cn=Dickson Guedes,ou=people,dc=example,dc=net     | cn => "Dickson Guedes",                                       +
                                                   | gidNumber => "500",                                           +
                                                   | homeDirectory => "/home/users/guedes",                        +
                                                   | sn => "Guedes",                                               +
                                                   | loginShell => "/bin/sh",                                      +
                                                   | objectClass => "{\"inetOrgPerson\",\"posixAccount\",\"top\"}",+
                                                   | userPassword => "ldap",                                       +
                                                   | uidNumber => "1000",                                          +
                                                   | uid => "guedes",                                              +
                                                   | givenName => "{\"Dickson, Guedes\",\"Gueduxo\"}",             +
                                                   | 
 cn=John Smith,ou=people,dc=example,dc=net         | cn => "John Smith",                                           +
                                                   | givenName => "John",                                          +
                                                   | gidNumber => "500",                                           +
                                                   | homeDirectory => "/home/users/jsmith",                        +
                                                   | sn => "Smith",                                                +
                                                   | loginShell => "/bin/sh",                                      +
                                                   | objectClass => "{\"inetOrgPerson\",\"posixAccount\",\"top\"}",+
                                                   | userPassword => "{SSHA}y0GfklAHS9AEDz87AdQ+UAQi3bGlfqXt",     +
                                                   | uidNumber => "1001",                                          +
                                                   | uid => "jsmith",                                              +
                                                   |

If you use a filter on dn column ldap_fdw will convert it to LDAP dialect and will send it to the server and this one will reply less entries. By now, only dn has this behaviour.

See:

> SELECT object_body FROM ldap_people WHERE dn = 'cn=Dickson Guedes';

                          object_body                           
----------------------------------------------------------------
 cn => "Dickson Guedes",                                       +
 gidNumber => "500",                                           +
 homeDirectory => "/home/users/guedes",                        +
 sn => "Guedes",                                               +
 loginShell => "/bin/sh",                                      +
 objectClass => "{\"inetOrgPerson\",\"posixAccount\",\"top\"}",+
 userPassword => "ldap",                                       +
 uidNumber => "1000",                                          +
 uid => "guedes",                                              +
 givenName => "{\"Dickson, Guedes\",\"Gueduxo\"}",             +

(1 row)

Retrieving specific attributes

You can choose a list of attributes to retrieve, just add an option attributes to FOREIGN TABLE's options with a comma-separeted attributes like this:

ALTER FOREIGN TABLE ldap_people
OPTIONS ( ADD attributes 'gidNumber,uidNumber,homeDirectory' );

SELECT * FROM ldap_people WHERE dn = 'cn=John Smith';

                     dn                      |              object_body               
---------------------------------------------+----------------------------------------
 cn=John Smith,ou=people,dc=guedesoft,dc=net | gidNumber => "500",                   +
                                             | uidNumber => "1001",                  +
                                             | homeDirectory => "/home/users/jsmith",+
                                             | 
(1 row)

Integration with Hstore

Well, you could do better than just retrieve that object_body! What about using Hstore?

See:

> CREATE EXTENSION hstore;
> WITH
    _hstore as (
       SELECT hstore(object_body) as h
       FROM ldap_people
     )
     SELECT h -> 'cn' as cn,
            h -> 'objectClass' as object_class,
            h -> 'homeDirectory' as home
     FROM _hstore;

        cn        |              object_class              |         home         
------------------+----------------------------------------+----------------------
 Dickson Guedes   | {"inetOrgPerson","posixAccount","top"} | /home/users/guedes
 John Smith       | {"inetOrgPerson","posixAccount","top"} | /home/users/jsmith

Integration with pgcrypto

Your application doesn't know how to query a LDAP server? But it know SELECT? What about check if some user's password is correct using your PostgreSQL server as a proxy?

Yes, you can! See:

> CREATE EXTENSION pgcrypto;
> WITH
 _hstore as
 (
    SELECT hstore(object_body) as h
    FROM ldap_people
    WHERE dn = 'cn=John Smith'
 ),
 _user_pass as
 (
    SELECT  substr(decode(substr( h -> 'userPassword' , 7 ), 'base64'), 21) as salt,
            h -> 'userPassword' as encrypted_password
    FROM _hstore
),
_generated_pass as
(
    SELECT '{SSHA}' || encode(digest( 'TheUserPassword123' || salt, 'sha1') || salt, 'base64') as password,
           encrypted_password
    FROM _user_pass
)
SELECT password = encrypted_password   as password_match
FROM  _generated_pass;

 password_match 
----------------
 t
(1 row)

Well too verbose, but you could use a function, no? :)

Support

Copyright and License

This software is released under the PostgreSQL Licence.

Copyright (c) 2011-2013 Dickson S. Guedes.