+1-757-461-3022 x124

Authenticating through LDAP

Overview

LDAP is generally used as a centralized user administration tool. By configuring xTuple to use LDAP authentication, all users can log in to xTuple with their standard usernames and passwords, without requiring any direct user administration in xTuple. This was implemented at one xTuple customer site (Willow Garage), where the customer had an existing LDAP implementation. Outlined below you can see how this was set up, including code samples.

Caveats: Some system administration skill will be required to set this up on your system. While the overall process is tested and working for us, the specific snippets below have not been tested in isolation. The exact LDAP incantations for your LDAP setting will vary.

Users

First, postgres must be configured to allow LDAP authentication. To do this, open the file /etc/postgresql/8.3/main/pg_hba.conf and add a line like this (and restart PostgreSQL):

hostssl all all 10.0.0.0/22 ldap "ldaps://ldap.willowgarage.com/ou=People,dc=willowgarage,dc=com;uid=;,ou=People,dc=willowgarage,dc=com"

The effect of this is that, when users type their username and password into the xTuple login dialog, those credentials are passed on to LDAP, which either does or does not authenticate the user. At this point the user is in PostgreSQL but not in xTuple, so they can't log in yet. In order to log in to xTuple, they have to have an xTuple-specific account. To facilitate this, we wrote a script that copies all of the LDAP users over to xTuple. When run with the --datatype ldapusers flag, it connects anonymously to the LDAP server, downloads all users, and creates matching accounts in xTuple. If the user already exists in xTuple, it skips them. If a user is in xTuple but not in LDAP, nothing happens.

Groups and permissions

Permissions are managed in two parts. First, the xTuple administrator creates groups in xTuple, and assigns privileges to the groups using Maintain Groups. So we have groups such as reporting, assembly, accounting, etc. Second, the network administrator creates identically named groups in LDAP and assigns users to those groups. In this way, users get all the privileges of their groups. This is cumulative; a user can belong to several groups and gets the sum total of all those groups' privileges. When the script is run with the flags --datatype ldapgroup --group accounting, it gets a list of users in the accounting LDAP group and adds them all to the accounting group in xTuple. The script is fairly dumb and only adds users to groups; it doesn't check to see if any existing users have been removed in LDAP.

Security

This is generally as secure as LDAP. If LDAP goes down, or if a user is disabled in LDAP, the user cannot log in to xTuple or directly to the database. There is one security hole, which is that if a user is removed from a privileged group in LDAP but still has a valid LDAP login, they will still have full privileges in xTuple until they are removed manually from that group in xTuple.

#!/usr/bin/env python
"""load a file of information into xTuple database
Requires psycopg and python-ldap
(in ubuntu, sudo apt-get install python-psycopg2python-ldap)
"""
import csv
import datetime
import ldap
import optparse
import os
import psycopg2
import re
import string
import subprocess
import sys
def loadLDAPGroup(group):
    """ Gets the list of users in an LDAP group and adds them all to a group  of the same name in xTuple """
    count_total = 0
    count_added = 0
    # get the list of LDAP users
    try:
        con = ldap.initialize('ldaps://ldap.willowgarage.com')
        dn = "ou=People,dc=willowgarage,dc=com;"
        con.simple_bind(dn)
        base_dn = 'cn=' + group + ',ou=Group,dc=willowgarage,dc=com'
        filter = '(objectclass=posixGroup)'
        attrs = ['memberUid']
        result = con.search_s(base_dn, ldap.SCOPE_SUBTREE, filter, attrs)
        uids = result[0][1].get('memberUid')
        for uid in uids:
            count_total = count_total + 1
            # try to add the user to the group, and handle errors if they already exist
            try:
                conn = connectToDB()
                sql = conn.cursor()
                statement = """ SELECT grantGroup(%s, (SELECT grp_id
                                                        FROM grp
                                                       WHERE lower(grp_name) = lower(%s))) AS result;"""
                sql.execute(statement, (uid,group))
                conn.commit()
                conn.close
                count_added = count_added + 1
            except Exception, e:
                print "Error,ldapgroup," + e.message
                sys.exit()
    except Exception, e:
        print "Error,ldapgroup," + str(e.message)
    finally:
        try:
            con.unbind()
        except ldap.LDAPError, e:
            pass
    print "Report, ldapgroup,total ldap users in group," + str(count_total) + ",added," + str(count_added)
    return
def loadLDAPUsers():
    """ Update xTuple's list of users with the LDAP list
Adds new users; does not revoke newly missing users (but they can't log in if they are blocked in LDAP)
"""
    count_total = 0
    count_dup = 0
    count_added = 0
    # get the list of LDAP users
    try:
        con = ldap.initialize('ldaps://ldap.willowgarage.com')
        dn = "ou=People,dc=willowgarage,dc=com;"
        con.simple_bind(dn)
        base_dn = 'ou=People,dc=willowgarage,dc=com'
        filter = '(objectclass=posixAccount)'
        attrs = ['uid','cn']
        result = con.search_s(base_dn, ldap.SCOPE_SUBTREE, filter, attrs)
        for line in result:
            count_total = count_total + 1
            uid = line[1].get('uid')[0]
            cn =  line[1].get('cn')[0]
            try:
                # Skip the user if they already exist in xTuple
                statement = """ SELECT count(usr_id)
                                  FROM usr
                                 WHERE usr_username = %s """
                conn = connectToDB()
                sql = conn.cursor()
                sql.execute(statement, (uid,))
                user_count = sql.fetchone()[0]
                conn.commit()
                conn.close
                if user_count > 0:
                    count_dup = count_dup + 1
                    if options.verbose:
                        print "Debug,ldap," + uid + ",already exists"
                    continue
                # try to add the user to PostgreSQL, and handle errors if they already exist
                try:
                    conn = connectToDB()
                    sql = conn.cursor()
                    statement = """ SELECT createUser(%s, FALSE);"""
                    sql.execute(statement, (uid,))
                    conn.commit()
                    conn.close
                except Exception, e:
                    if 'already exists' in e.message:
                        if options.verbose:
                            print "Error,ldap," + uid + ",Already exists in postgres,"
                    else:
                        print "Error,ldap," + e.message
                        sys.exit()
                # try to create a new xTuple user
                statement = """
INSERT INTO usr ( usr_username, usr_propername,  usr_email, usr_initials, usr_locale_id,  usr_agent, usr_active,  usr_window ) VALUES ( %s, %s, '', '', 3,  FALSE, TRUE,  '' );
SELECT setUserPreference(%s, 'DisableExportContents', 'f');
SELECT setUserPreference(%s, 'UseEnhancedAuthentication', 'f');
SELECT setUserPreference(%s, 'selectedSites', 'f');
SELECT setUserPreference(%s, 'ShowIMMenu', 't');
SELECT setUserPreference(%s, 'ShowPDMenu', 't');
SELECT setUserPreference(%s, 'ShowMSMenu', 't');
SELECT setUserPreference(%s, 'ShowWOMenu', 't');
SELECT setUserPreference(%s, 'ShowCRMMenu', 'f');
SELECT setUserPreference(%s, 'ShowPOMenu', 't');
"""
                conn = connectToDB()
                sql = conn.cursor()
                sql.execute(statement, (uid,cn,uid,uid,uid,uid,uid,uid,uid,uid,uid,uid))
                conn.commit()
                conn.close
                count_added = count_added + 1
                if options.verbose:
                    print "Debug,ldap," + uid + ",successfully added"
            except Exception, e:
                if 'already exists' in e.message:
                    print "Error,ldap," + uid + ",Already exists in postgres,"
                    continue
                else:
                    print "Error,ldap," + e.message
                    sys.exit()
    finally:
        try:
            con.unbind()
        except ldap.LDAPError, e:
            pass
    print "Report, ldap,total ldap users," + str(count_total) + ",added," + str(count_added) + ",duplicates," + str(count_dup)
def main():
    usage = "usage: %prog [options]"
    parser = optparse.OptionParser(usage=usage)
    parser.add_option('-g', '--group',
                      dest="ldap_group",
                      default="",
                      help="LDAP Group to import"
                      )
    parser.add_option('-t', '--datatype',
                      dest="datatype",
                      help="Type of data.  Supported: ldapgroup, ldapusers"
                      )
    parser.add_option('-v', '--verbose',
                      action="store_true",
                      dest="verbose",
                      help="Show additional debug output on stdout"
                      )
    global options
    (options, args) = parser.parse_args()
    if options.datatype == 'ldapusers':
        loadLDAPUsers()
    elif options.datatype == 'ldapgroup':
        loadLDAPGroup(options.ldap_group)
    else:
        print "No valid data type selected"
        sys.exit()
if __name__ == "__main__":
    main()
up
123 users have voted.