Configuring OpenVPN to obtain client IPs from MySQL table

client-connect.sh

#!/bin/bash

# this script queries client IP from DB and pushes to client

LOG_FILE=$0.log

# env
# CLIENT_NAME=$2
CLIENT_NAME=$common_name
CLIENT_CONFIG_FILE=$1

# MYSQL

# connection settings
MYSQL_USER_NAME=$(user)
MYSQL_USER_PWD=$(pwd)
MYSQL_DB_NAME=$(db)

MYSQL="mysql --skip-column-names --host=127.0.0.1 --user=$MYSQL_USER_NAME --password=$MYSQL_USER_PWD $MYSQL_DB_NAME"

# send query to mysql
function query()
{
        echo $1 | $MYSQL
}


# action

CLIENT_IFCONFIG=`query "select concat('ifconfig-push ', inet_ntoa(inet_aton(ip)), ' ', inet_ntoa(inet_aton(ip)-1)) as 'xxx' from iplist where name='$CLIENT_NAME'"`

if [ "$CLIENT_IFCONFIG" == "" ] ; then
        echo [ `date` ] $CLIENT_NAME : not found >> $LOG_FILE
        exit 1 # no ip for client; disconnect
fi

echo [ `date` ] $CLIENT_NAME : $CLIENT_IFCONFIG >> $LOG_FILE
echo $CLIENT_IFCONFIG > $CLIENT_CONFIG_FILE

exit 0

schema.sql

create database $(db);

use $(db);

create table iplist (
	id int(10) auto_increment,
	name text not null,
	ip varchar(16) not null,
	
	primary key (id)
);

create user '$(user)'@'localhost' identified by '$(pwd)';

	
grant select
	on $(db).*
	to '$(user)'@'localhost';

insert into iplist (name, ip) values ('client1', '10.0.0.6');

server-config

...
client-connect /etc/whatever/client-connect.sh
...