Re: Synchronizing with backend mysql

[Joel Reed <joelwreed@gmail.com>]

kiran madala wrote:
> I was wondering if openLDAP has any connectors to synchronize the users, groups in the directory to an external database such as mysql.
Attached, please find an overlay I wrote that synchronises users and 
organizations to an external postgresql database. Perhaps you can use it 
as a starting point for whatever you need.

This is "modrelay version 0.2". I posted an earlier version here:

http://www.openldap.org/lists/openldap-software/200807/msg00002.html

jr


/**
 * modrelay.c 
 *
 * Copyright (C) 2008 Joel W. Reed
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted only as authorized by the OpenLDAP
 * Public License.
 *
 * A copy of this license is available in file LICENSE in the
 * top-level directory of the distribution or, alternatively, at
 * http://www.OpenLDAP.org/license.html.
 *
 * SEE LICENSE FOR MORE INFORMATION
 *
 * Author:	Joel W. Reed
 * Email:		joelwreed@gmail.com
 * Version: 0.2
 * Updated: 09.15.2008
 * 
 * modrelay
 *
 * This is an OpenLDAP overlay that intercepts ADD, MODIFY, and DELETE requests 
 * for posixAccount and organization type entries, and relays the changes to 
 * a postgresql database.
 */

#include "portable.h" 
#include <ac/string.h>
#include <ac/ctype.h>
#include "slap.h"
#include "ldif.h"
#include "libpq-fe.h"
#include "config.h" 

#define CONNECTION_STRING "host = 'db1.nviznit.com' dbname = 'nvizn' user = 'YOURNAME' password='YOURPWD' sslmode='prefer' connect_timeout = '10'"

static slap_overinst modrelay;
static ObjectClass *oc_user_account;
static ObjectClass *oc_organization;
static ObjectClass *oc_inetlocalmailrecipient;

typedef struct modrelay_data {
	ldap_pvt_thread_mutex_t mutex;
	PGconn* connection;
} modrelay_data;

PGconn* get_db_connection(modrelay_data *ad)
{
	// we lazily connect to the db whenever first needed
	if (ad->connection != NULL) return ad->connection;

	ad->connection = PQconnectdb(CONNECTION_STRING); 
	if (!ad->connection || (PQstatus(ad->connection) != CONNECTION_OK))
		{
			Debug( LDAP_DEBUG_ANY, "%s: failed to connect to database!\n",
						 modrelay.on_bi.bi_type, 0, 0 );
		}
	
	return ad->connection;
}

static void modrelay_process_organization(Operation* op, Entry* entry)
{
	static const char INSERT_SQL[] = "insert into organization (id, version, active, domain, description, mail_host, dn) values (nextval('organization_sequence'), 0, TRUE, $1, $2, $3, $4)";
	static const char UPDATE_SQL[] = "update organization set domain=$1, description=$2, mail_host=$3 where dn=$4";
	static int PARAM_CNT = 4;

	slap_overinst* on = (slap_overinst *)op->o_bd->bd_info;
	modrelay_data *ad = on->on_bi.bi_private;
	PGconn* connection = get_db_connection(ad);

	const char* paramValues[PARAM_CNT];
	paramValues[0] = paramValues[1] = paramValues[2] = "";
	paramValues[3] = entry->e_nname.bv_val;

	Debug( LDAP_DEBUG_TRACE, "%s: modrelay_response for add/edit organization %s\n",
				 modrelay.on_bi.bi_type, paramValues[3], 0 );

	Attribute* attr;
	for (attr = entry->e_attrs; attr; attr = attr->a_next )
		{
			if (!strcmp(attr->a_desc->ad_cname.bv_val, "o"))
				paramValues[0] = attr->a_vals[0].bv_val;
			else if (!strcmp(attr->a_desc->ad_cname.bv_val, "description"))
				paramValues[1] = attr->a_vals[0].bv_val;
			else if (!strcmp(attr->a_desc->ad_cname.bv_val, "mailHost"))
				paramValues[2] = attr->a_vals[0].bv_val;
		}

	const char* SQL = (op->o_tag == LDAP_REQ_ADD)? INSERT_SQL : UPDATE_SQL;
	PGresult* res = PQexecParams(connection, SQL, PARAM_CNT, NULL, paramValues,
															 NULL, NULL, 0);

	ExecStatusType status = PQresultStatus(res);
	if (status != PGRES_COMMAND_OK)
		{
			Debug(LDAP_DEBUG_ANY, "%s: org database error: (%s) %s\n",
						modrelay.on_bi.bi_type, PQresStatus(status), PQresultErrorMessage(res));
			Debug(LDAP_DEBUG_ANY, "%s: SQL: %s domain:%s\n",
						modrelay.on_bi.bi_type, SQL, paramValues[0]);
			Debug(LDAP_DEBUG_ANY, "%s: description:%s, mail_host:%s\n",
						modrelay.on_bi.bi_type, paramValues[1], paramValues[2]);
		}

	PQclear(res);
}

static void modrelay_process_user(Operation* op, Entry* entry)
{
	static const char INSERT_SQL[] = "insert into person (id, version, first_name, last_name, mail, active, dn, organization_id) values (nextval('person_sequence'), 0, $1, $2, $3, $4, $5, (select id from organization a where a.dn=$6))";
	static int INSERT_PARAM_CNT = 6;
	static const char UPDATE_SQL[] = "update person set first_name=$1, last_name=$2, mail=$3, active=$4 where dn=$5";
	static int UPDATE_PARAM_CNT = 5;

	slap_overinst* on = (slap_overinst *)op->o_bd->bd_info;
	modrelay_data *ad = on->on_bi.bi_private;
	PGconn* connection = get_db_connection(ad);

	const char* paramValues[INSERT_PARAM_CNT];
	paramValues[0] = paramValues[1] = paramValues[2] = "";
	paramValues[4] = entry->e_nname.bv_val;

	Debug( LDAP_DEBUG_TRACE, "%s: modrelay_response for add/edit user \n",
				 modrelay.on_bi.bi_type, 0, 0 );

	Attribute* attr;
	for (attr = entry->e_attrs; attr; attr = attr->a_next )
		{
			if (!strcmp(attr->a_desc->ad_cname.bv_val, "givenName"))
				paramValues[0] = attr->a_vals[0].bv_val;
			else if (!strcmp(attr->a_desc->ad_cname.bv_val, "sn"))
				paramValues[1] = attr->a_vals[0].bv_val;
			else if (!strcmp(attr->a_desc->ad_cname.bv_val, "mail"))
				paramValues[2] = attr->a_vals[0].bv_val;
			else if (!strcmp(attr->a_desc->ad_cname.bv_val, "active"))
				paramValues[3] = attr->a_vals[0].bv_val;
		}

	struct berval usersdn, orgdn;
	const char* sql;
	int param_cnt;

	if (op->o_tag == LDAP_REQ_ADD)
		{
			sql = INSERT_SQL;
			param_cnt = INSERT_PARAM_CNT;

			dnParent(&entry->e_nname, &usersdn ); 
			dnParent(&usersdn, &orgdn); 
			paramValues[5] = orgdn.bv_val;
		}
	else
		{
			sql = UPDATE_SQL;
			param_cnt = UPDATE_PARAM_CNT;
		}

	PGresult* res = PQexecParams(connection, sql, param_cnt, NULL, paramValues,
															 NULL, NULL, 0);

	ExecStatusType status = PQresultStatus(res);
	if (status != PGRES_COMMAND_OK)
		{
			Debug(LDAP_DEBUG_ANY, "%s: user database error: (%d) %s\n",
						modrelay.on_bi.bi_type, status, PQresultErrorMessage(res));
		}

	PQclear(res);
}

static int modrelay_response( Operation *op, SlapReply *rs )
{
	slap_overinst* on = (slap_overinst *)op->o_bd->bd_info;
	modrelay_data *ad = on->on_bi.bi_private;
	Entry* entry = NULL;
		
	/* if error no point in continuing */
	if ( rs->sr_err != LDAP_SUCCESS ) return SLAP_CB_CONTINUE;

	switch (op->o_tag)
		{
		case LDAP_REQ_ADD:
			entry = op->ora_e;
			break;
		case LDAP_REQ_MODIFY:	
			if ( overlay_entry_get_ov( op, &op->o_req_ndn, NULL, NULL, 0, &entry, on ) != LDAP_SUCCESS || entry == NULL ) {
				Debug( LDAP_DEBUG_ANY, "modrelay_response MODIFY cannot get entry for <%s>\n", op->o_req_dn.bv_val, 0, 0);
				return SLAP_CB_CONTINUE;
			}
			break;
		default:
			return SLAP_CB_CONTINUE;
		}

	ldap_pvt_thread_mutex_lock(&ad->mutex);

	/* is this a object class we care about */
	if (is_entry_objectclass( (entry), oc_user_account, 0))
		modrelay_process_user(op, entry);
	else if ((is_entry_objectclass( (entry), oc_organization, 0))&&
					 (is_entry_objectclass( (entry), oc_inetlocalmailrecipient, 0)))
		modrelay_process_organization(op, entry);

	ldap_pvt_thread_mutex_unlock(&ad->mutex);
 
	if (op->o_tag == LDAP_REQ_MODIFY)
		overlay_entry_release_ov( op, entry, 0, on );
		
	return SLAP_CB_CONTINUE;
}

static void modrelay_delete_organization(PGconn* connection, const char* dn)
{
	static const char SQL[] = "delete from organization where dn=$1";
	const char* paramValues[1];	paramValues[0] = dn;

  Debug( LDAP_DEBUG_TRACE, "modrelay_delete_organization %s\n", dn, 0, 0 );

	PGresult* res = PQexecParams(connection, SQL, 1, NULL, paramValues,
															 NULL, NULL, 0);

	ExecStatusType status = PQresultStatus(res);
	if (status != PGRES_COMMAND_OK)
		{
			Debug(LDAP_DEBUG_ANY, "%s: organization database error: (%d) %s\n",
						modrelay.on_bi.bi_type, status, PQresultErrorMessage(res));
			Debug(LDAP_DEBUG_ANY, "%s: SQL: %s o:%s\n",
						modrelay.on_bi.bi_type, SQL, paramValues[0]);
		}

	PQclear(res);
}

static void modrelay_delete_person(PGconn* connection, const char* dn)
{
	static const char SQL[] = "delete from person where dn=$1";
	const char* paramValues[1];	paramValues[0] = dn;

  Debug( LDAP_DEBUG_TRACE, "modrelay_delete_person %s\n", dn, 0, 0 );

	PGresult* res = PQexecParams(connection, SQL, 1, NULL, paramValues,
															 NULL, NULL, 0);

	ExecStatusType status = PQresultStatus(res);
	if (status != PGRES_COMMAND_OK)
		{
			Debug(LDAP_DEBUG_ANY, "%s: person database error: (%d) %s\n",
						modrelay.on_bi.bi_type, status, PQresultErrorMessage(res));
			Debug(LDAP_DEBUG_ANY, "%s: SQL: %s o:%s\n",
						modrelay.on_bi.bi_type, SQL, paramValues[0]);
		}

	PQclear(res);
}

static int modrelay_delete(Operation *op, SlapReply *rs )
{
	slap_overinst* on = (slap_overinst *)op->o_bd->bd_info;
	modrelay_data *ad = on->on_bi.bi_private;
	PGconn* connection = get_db_connection(ad);
		
	Debug(LDAP_DEBUG_TRACE, "modrelay_delete dn=%s\n", op->o_req_ndn.bv_val, 0, 0);

	BackendInfo	*bi = op->o_bd->bd_info;
	op->o_bd->bd_info = (BackendInfo *)on->on_info;

	Entry	*entry = NULL;
	rs->sr_err = be_entry_get_rw(op, &op->o_req_ndn, NULL, NULL, 0, &entry );

	/* FIXME: couldn't the entry be added before deletion? */
	if ( rs->sr_err == LDAP_SUCCESS && entry != NULL ) 
		{
			if (is_entry_objectclass( (entry), oc_user_account, 0))
				{
					modrelay_delete_person(connection, op->o_req_ndn.bv_val);
				}
			else if ((is_entry_objectclass( (entry), oc_organization, 0))&&
							 (is_entry_objectclass( (entry), oc_inetlocalmailrecipient, 0)))
				{
          modrelay_delete_organization(connection, op->o_req_ndn.bv_val);
				}

			be_entry_release_r( op, entry );
			entry = NULL;
		}

	op->o_bd->bd_info = bi;
	return SLAP_CB_CONTINUE;
}

static int modrelay_db_init(BackendDB *be,	ConfigReply *cr)
{
	slap_overinst *on = (slap_overinst *)be->bd_info;
	modrelay_data *ad = ch_calloc(1, sizeof(modrelay_data));

	on->on_bi.bi_private = ad;
	ldap_pvt_thread_mutex_init( &ad->mutex );
	ad->connection = NULL;

	return 0;
}

static int modrelay_db_destroy(BackendDB *be, ConfigReply *cr)
{
	slap_overinst *on = (slap_overinst *)be->bd_info;
	modrelay_data *ad = on->on_bi.bi_private;

	ldap_pvt_thread_mutex_destroy( &ad->mutex );
	if (ad->connection != NULL) PQfinish(ad->connection);

	free( ad );
	return 0;
}

int modrelay_init() 
{
	modrelay.on_bi.bi_type = "modrelay";
	modrelay.on_bi.bi_db_init = modrelay_db_init;
	modrelay.on_bi.bi_db_destroy = modrelay_db_destroy;
	modrelay.on_response = modrelay_response;
	modrelay.on_bi.bi_op_delete = modrelay_delete;

	oc_user_account = oc_find( "userAccount" );
	if(oc_user_account == NULL) {
		Debug( LDAP_DEBUG_ANY, "%s: unable to find default ObjectClass \"userAccount\".\n",
					 modrelay.on_bi.bi_type, 0, 0 );
		return -1;
	}

	oc_organization = oc_find( "organization" );
	if(oc_organization == NULL) {
		Debug( LDAP_DEBUG_ANY, "%s: unable to find default ObjectClass \"organization\".\n",
					 modrelay.on_bi.bi_type, 0, 0 );
		return -1;
	}

	oc_inetlocalmailrecipient = oc_find( "inetLocalMailRecipient" );
	if(oc_inetlocalmailrecipient == NULL) {
		Debug( LDAP_DEBUG_ANY, "%s: unable to find default ObjectClass \"inetLocalMailRecipient\".\n",
					 modrelay.on_bi.bi_type, 0, 0 );
		return -1;
	}

	return ( overlay_register(&modrelay) );
}

int init_module( int argc, char *argv[] ) 
{
	return modrelay_init();
}