Introduction:

As I was wanting to set-up a cluster of web servers based on Apache2 and fcgi I realized that I didn’t want to have to create/delete/update each individual fcgi user in each web server. Therefore I decided to authenticate the fcgi users through MySQL (in fact MariaDB). I’ve done that many years back but this time it seemed very new to me anyway. I spent at least 3 days on it trying to follow some tutorials but I had to combine many of them to finally make it work. Here I don’t pretend to have found the best solution or method. So if any of you can improve on it, I would be very thankful for your contribution. So here it goes:

Configuring PAM with MySQL as authentication source instead of /etc/{passwd|shadow} on Ubuntu Server 14.04-2.
This tutorial is partly based on the following one. In fact I shortened the explanations, so for more explanations see this link:
http://www.spencerstirling.com/computergeek/mysqluser.html
and https://github.com/NigelCunningham/pam-MySQL
and http://serverfault.com/questions/608692/token-error-when-trying-to-change-password-through-pam-mysql

Install packages

Install the MariaDB server/client packages(Replacement to MySQL)
apt-get install mariadb-client mariadb-server libpam-mysql libnss-mysql-bg
Install phpmyadmin for comfortable viewing
apt-get install phpmyadmin
Install mcrypt PHP5 module
mkdir /usr/local/lib/libmcrypt
chown www-data: /usr/local/lib/libmcrypt
apt-get install mcrypt php5-mcrypt
php5enmod mcrypt
service apache2 restart

Configure the PAN auth user MariaDB Database

Start the mysql client(in fact MariaDB client but still called MySQL 😉 )
NOTE: Remember to change the {pam-user Password} to a proper password in these statement:
mysql -u root -p
Issue the following SQL commands:
create database nss_mysql;
quit;

Create an SQL file to beloaded in mysql later.
Note: The GRANT update line at the bottom. This is necessary in order for nss-shadow to be able to CHANGE anything. The nss user only has read privileges.
Filename: nss_mysql.sql
Content:
USE nss_mysql;
DROP TABLE IF EXISTS groups;
CREATE TABLE groups (
dbid int(11) NOT NULL auto_increment primary key,
name varchar(30) DEFAULT '' NOT NULL,
gid int(11) NOT NULL,
password varchar(64) DEFAULT 'x' NOT NULL,
flag char(1) DEFAULT 'A'
);
INSERT INTO groups VALUES ('users',100,'x','A');
DROP TABLE IF EXISTS users;
CREATE TABLE users (
dbid int(11) NOT NULL auto_increment primary key,
username varchar(50) DEFAULT '' NOT NULL,
gecos varchar(40) DEFAULT '' NOT NULL,
shell varchar(20) DEFAULT '/bin/sh' NOT NULL,
password varchar(60) DEFAULT '' NOT NULL,
flag char(1) DEFAULT 'N' NOT NULL,
uid int(11) NOT NULL,
gid int(11) NOT NULL,
homedir varchar(64) DEFAULT '/bin/sh' NOT NULL,
lstchg varchar(50) NOT NULL default '',
min int(11) NOT NULL default '0',
max int(11) NOT NULL default '0',
warn int(11) NOT NULL default '7',
inact int(11) NOT NULL default '-1',
expire int(11) NOT NULL default '-1'
);
DROP TABLE IF EXISTS grouplist;
CREATE TABLE grouplist (
dbid int(11) NOT NULL auto_increment primary key,
gid int(11) DEFAULT '0' NOT NULL,
uid int(11) DEFAULT '0' NOT NULL,
username varchar(50) DEFAULT '' NOT NULL
);
GRANT select(dbid,username,uid,gid,gecos,shell,homedir,flag) on users to 'nss'@localhost identified by 'ieopurASDF';
GRANT select(dbid,name,gid,password,flag) on groups to 'nss'@localhost identified by 'ieopurASDF';
GRANT select(dbid,gid,uid,username) on grouplist to 'nss'@localhost identified by 'ieopurASDF';
GRANT select(dbid,username,password,uid,gid,gecos,shell,homedir,flag,lstchg,min,max,warn,inact,expire) on users to 'nss'@localhost identified by 'ieopurASDF';
GRANT select(dbid,name,gid,password,flag) on groups to nss@localhost identified by 'ieopurASDF';
GRANT select(dbid,gid,uid,username) on grouplist to nss@localhost identified by 'ieopurASDF';
GRANT select(dbid,username,uid,gid,gecos,shell,homedir,flag) on users to 'nss-shadow'@localhost identified by 'ruASDFDER';
GRANT select(dbid,name,gid,password,flag) on groups to 'nss-shadow'@localhost identified by 'ruASDFDER';
GRANT select(dbid,gid,uid,username) on grouplist to 'nss-shadow'@localhost identified by 'ruASDFDER';
GRANT select(dbid,name,gid,password,flag) on groups to nss-shadow@localhost identified by 'ruASDFDER';
GRANT select(dbid,gid,uid,username) on grouplist to nss-shadow@localhost identified by 'ruASDFDER';
GRANT select(dbid,username,password,uid,gid,gecos,shell,homedir,flag,lstchg,min,max,warn,inact,expire) on users to 'nss-shadow'@localhost identified by 'ruASDFDER';
GRANT update(dbid,username,password,uid,gid,gecos,shell,homedir,flag,lstchg,min,max,warn,inact,expire) on users to 'nss-shadow'@localhost identified by 'ruASDFDER';
FLUSH PRIVILEGES;

Configuring the NSS (Linux Authorization system)

These above SQL DB structures and tables are based on the following Ububtu 14.04-2 LTS Server content of /etc/libnss-mysql.conf which listed here:
getpwnam SELECT username,'x',uid,gid,gecos,homedir,shell \
FROM users \
WHERE username='%1$s' \
LIMIT 1
getpwuid SELECT username,'x',uid,gid,gecos,homedir,shell \
FROM users \
WHERE uid='%1$u' \
LIMIT 1
getspnam SELECT username,password,lstchg,min,max,warn,inact,expire,flag \
FROM users \
WHERE username='%1$s' \
LIMIT 1
getpwent SELECT username,'x',uid,gid,gecos,homedir,shell \
FROM users
getspent SELECT username,password,lstchg,min,max,warn,inact,expire,flag \
FROM users
getgrnam SELECT name,password,gid \
FROM groups \
WHERE name='%1$s' \
LIMIT 1
getgrgid SELECT name,password,gid \
FROM groups \
WHERE gid='%1$u' \
LIMIT 1
getgrent SELECT name,password,gid \
FROM groups
memsbygid SELECT username \
FROM grouplist \
WHERE gid='%1$u'
gidsbymem SELECT gid \
FROM grouplist \
WHERE username='%1$s'

Edit /etc/nsswitch.conf and change the lines:
passwd: compat
group: compat
shadow: compat
TO:
passwd: compat mysql
group: compat mysql
shadow: compat mysql

This will tell NSS to look for the user information first in files (/etc/passwd & /etc/shadow) and if not found in these files then look in the mySQL database.
Edit the file /etc/libnss-mysql.conf and replace the proper user and password you had defined for the ‘nss’ user in the SQL file above.
Example:
Refering to the user and password in the SQL file above: …nss@localhost identified by ‘ieopurASDF’…
Change the file /etc/libnss-mysql.conf,from:
database auth
username nss-user
password userpass
TO
database nss_mysql
username nss
password ieopurASDF

Edit the file /etc/libnss-mysql-root.conf and replace the proper user and password you had defined for the nss-shadow user in the SQL file above.
Example:
Refering to the user and password in the SQL file above: …nss-shadow@localhost identified by ‘ruASDFDER’…
Change the file /etc/libnss-mysql-root.conf,from:
username nss-root
password rootpass
TO
username nss-shadow
password ruASDFDER

Change the access rights of the file /etc/libnss-mysql-root.conf
chmod 600 /etc/libnss-mysql-root.conf

Configuring the PAM Modules

Change to the directory /etc/pam.d/ where the PAM modules configurations are found.
cd /etc/pam.d
Rename the originals to keep them in case:
mv common-account common-account.orig ; touch common-account
mv common-auth common-auth.orig ; touch common-auth
mv common-session common-session.orig ; touch common-session
mv common-password common-password.orig ; touch common-password

Make common-account look like this:
account sufficient pam_mysql.so config_file=/etc/pam-mysql.conf
account required pam_unix.so

Make common-session look like this:
session sufficient pam_mysql.so config_file=/etc/pam-mysql.conf
session required pam_unix.so

Make common-auth look like this:
auth sufficient pam_mysql.so config_file=/etc/pam-mysql-shadow.conf
auth required pam_unix.so nullok_secure

Make common-password look like this:
password sufficient pam_mysql.so config_file=/etc/pam-mysql-shadow.conf
password required pam_unix.so nullok obscure sha512

Now make all of these only root readable:
chmod 600 common-*
Try to SSH in as a both a regular user AND as root – make sure that it all works.

YOU’RE NOT DONE YET!!! If you are running either an IMAP or POP3 server then (unfortunately) these “common-*”
files are not used by default. You can easily use them by (for example) changing your /etc/pam.d/imap file to look like:
@include common-auth
@include common-account
@include common-session
@include common-password

Look around in /etc/pam.d/ for any other services that may not be using common-* and update them appropriately.

PAM-mysql configuration files

We rename the already existing PAM-mysql configuration file:
mv /etc/pam-mysql.conf /etc/pam-mysql.conf.orig
Then we create the following configuration files: /etc/pam-mysql.conf and /etc/pam-mysql-shadow.conf
touch /etc/pam-mysql.conf
Content:
users.host = localhost
users.database = nss_mysql
users.db_user = nss
users.db_passwd = ieopurASDF
users.table = users
users.user_column = username
users.password_column = password
users.password_crypt = 3

Create the file /etc/pam-mysql-shadow.conf
touch /etc/pam-mysql-shadow.conf
Content:
users.host = localhost
users.database = nss_mysql
users.db_user = nss-shadow
users.db_passwd = ruASDFDER
users.table = users
users.user_column = username
users.password_column = password
users.password_crypt = 3

Steps to add the first users:(For testing purposes)

Now that you have your database functioning properly (with the special MySQL modules for NSS and PAM) it’s time to actually add a user. The steps I’ll outline here are very MANUAL.
mysql -u root -p
and execute the command:
select * from nss_mysql.users;
That will list the existing users. In our case no users exist yet, so we’ll use user_id=group_id=100 (internal MySQL designation) and uid=gid=1000 (system sees this). Put these relevant MySQL commands into a file – say addusers.sql:
INSERT INTO nss_mysql.groups VALUES (0,'spencer',1000,'x','A');
INSERT INTO nss_mysql.users VALUES (0,'spencer','Spencer Stirling','/bin/bash','','A',1000,1000,'/home/spencer','','0','0','7','-1','-1');
INSERT INTO nss_mysql.grouplist VALUES (0,1000,1000,'spencer');
INSERT INTO nss_mysql.grouplist VALUES (0,100,1000,'spencer');

INSERT INTO nss_mysql.groups VALUES (0,'sally',1001,'x','A');
INSERT INTO nss_mysql.users VALUES (0,'sally','Sally Smith','/bin/bash','','A',1001,1001,'/home/sally','','0','0','7','-1','-1');
INSERT INTO nss_mysql.grouplist VALUES (0,1001,1001,'sally');
INSERT INTO nss_mysql.grouplist VALUES (0,100,1001,'sally');

INSERT INTO nss_mysql.groups VALUES (0,'christine',1002,'x','A');
INSERT INTO nss_mysql.users VALUES (0,'christine','Christine Weber','/bin/bash','','A',1002,1002,'/home/christine','','0','0','7','-1','-1');
INSERT INTO nss_mysql.grouplist VALUES (0,1002,1002,'christine');
INSERT INTO nss_mysql.grouplist VALUES (0,100,1002,'christine');

Obviously the first user creation above will create:
a “spencer” group with gid=1000, then it will create a “spencer” user with uid=1000.
Then, the “spencer” user will be put into the “spencer” group.
You should increment them with the next user (i.e. 1000 —> 1001 ect.).
The last statement also adds the user “spencer” to the group “users”(gid 100) (created in the beginning).
I assigned dummy ID dbid‘ to the tables only to create unique table columns to be able to manually change the tables and values from phpmadmin.
Now run these statements with:
mysql -u root -p < adduser.sql
That will add your user (with a blank password, by the way).
You’re almost done! Now it’s time to actually create the user’s home directory. That part is very easy:
cp -ax /etc/skel /home/spencer
chown -R spencer:spencer /home/spencer

Installing pure-ftpd for pam-mysql

apt-get install pure-ftpd
The following configuration of PureFTPD will provide FTP/ and FTPS with jailed Users.
Edit the file /etc/pam.d/pure-ftpd as follows:
auth sufficient pam_mysql.so config_file=/etc/pam-mysql-shadow.conf sense=deny file=/etc/ftpusers onerr=succeed
# Uncomment next line to allow non-anonymous ftp access ONLY for users,
# listed in /etc/ftpallow
#auth required pam_listfile.so item=user sense=allow file=/etc/ftpallow onerr=fail
# Standard pam includes
@include common-account
@include common-session
@include common-auth
auth required pam_shells.so

For configuring PureFTPD, run the following commands:
echo '20000 20099' > /etc/pure-ftpd/conf/PassivePortRange
echo "yes" > /etc/pure-ftpd/conf/NoAnonymous
echo "yes" > /etc/pure-ftpd/conf/ChrootEveryone
echo "yes" > /etc/pure-ftpd/conf/IPV4Only
echo "1" > /etc/pure-ftpd/conf/TLS

Fro cresating a TLS self-signed certificate fro ProFTPD run the following commands and enter the appropriate information:
mkdir -p /etc/ssl/private/
openssl req -x509 -nodes -days 97300 -newkey rsa:2048 -keyout /etc/ssl/private/pure-ftpd.pem -out /etc/ssl/private/pure-ftpd.pem
chmod 600 /etc/ssl/private/pure-ftpd.pem
service pure-ftpd restart

More configuration for mail services

Hopefully you have everything in “/etc/skel” set up appropriately. Mail is probably the most important thing here. For example, I set up a skeleton mail and spam folder with the command:
maildirmake /etc/skel/Maildir
maildirmake /etc/skel/Maildir/.Spam

In the “/etc/skel” directory I am ALSO sure to have an appropriate “.forward” to forward all of that spam into the right folder,
and I put a barebones “.muttrc” file in there, too, so that my users can hit the ground running with Mutt.
Please see my Email Howto for more info concerning these concepts.
Don’t forget to add email ALIASES to Exim4 if necessary for your new user!!!
To delete a user (and that user’s GROUP!!!) try these MySQL commands:
DELETE FROM nss_mysql.users where username='spencer';
DELETE FROM nss_mysql.groups where name='spencer';
DELETE FROM nss_mysql.grouplist where uid='1000'

Then you can delete the user’s home directory (although you should give some leniency there!!!).
NOTE: The system commnad useradd and passwd will continue to manage the /etc/passwd and /etc/shadow
The new scripts to create and change the user’s password are below:
myadduser.sh and mypasswd.sh

BASH SCRIPTS

Necessary mysql root password(Replace the $password with a proper mysql root password)
echo "$password" > $PWD/sqlrootpw.txt
chmod 600 $PWD/sqlrootpw.txt
touch $PWD/myadduser.sh
chmod 755 $PWD/myadduser.sh

Script for adding a user

myadduser.sh
Syntax: myadduser.sh username 'realname' shell 'password' homedir
Content:
#!/bin/bash
# Purpose: adds a user in mysqldatabase for PAM authentication/authorization
# syntax: myadduser.sh # Note: each new user will be part of the users group as well as the group bearing username
# which also will have the same GID as the UID
# It will not take note of deleted users therefore not use their free UID
# UID and GID will
# Dependencies: mySQL root password saved in $rootpwfile which should be 'chmod 600'
# ------------------------------------------------------
# Check the syntax
if [ $# -ne 5 ]; then
echo "ERROR: Wrong syntax"
echo "Usage: myadduser.sh username 'realname' shell 'password' homedir"
exit 1
fi
#
# Constants
#--------------
rootpwfile=$PWD/sqlrootpw.txt
# Get MySQL root PW
rootpw=$(head -n1 $rootpwfile | awk '{print $1}')
#
# Get the username
username=$1
realname=$2
shell=$3
password=$4
homedir=$5
#
#------- Start ---------
# First we find out the highest UID of all users
lastUID=$(echo 'select * from nss_mysql.users;' | mysql -N -u root --password="$rootpw" | rev | awk '{print $8}' | rev | sort -n | tail -n1)
# set it to 999 if no users entered yet
if [ -z $lastUID ]; then
lastUID="999"
fi
#
# Calculate the next UID and DB:user_id
newUID=$[ $lastUID + 1 ]
# Check if the user exists in the /etc/passwd increment the UID until not found
while (grep -q ":x:${newUID}:" /etc/passwd) ; do
newUID=$[ $newUID + 1 ]
done
newGID=$newUID
#
# Find out the encryption method used in this system. Not used since it's SHA512.
#crypt_method=$(grep "^ENCRYPT_METHOD" /etc/login.defs | awk '{ print $2}')
#
# Encrypt the password .... hummm tried everything but authentication is not working with SHA256/SHA512. Decided for MD5.
crypt_method='MD5'
crypt=$(echo "${username}:${password}" | chpasswd -S -c $crypt_method | cut -d: -f2)
#
# Enter the data into the Database
echo "INSERT INTO nss_mysql.groups VALUES (0,'$username',$newUID,'x','A');" | mysql -u root --password="$rootpw"
echo "INSERT INTO nss_mysql.users VALUES (0,'$username','$realname','$shell','$crypt','A',$newUID,$newGID,'$homedir','','0','0','7','-1','-1');" | mysql -u root --password="$rootpw"
echo "INSERT INTO nss_mysql.grouplist VALUES (0,$newGID,$newUID,'$username');" | mysql -u root --password="$rootpw"
echo "INSERT INTO nss_mysql.grouplist VALUES (0,100,$newUID,'$username');" | mysql -u root --password="$rootpw"
#
# Create the home directory if not existing
if ! [ -d $homedir ]; then
echo "Creating the home directory: $homedir"
cp -ax /etc/skel $homedir
chown -R ${username}:${username} $homedir
else
echo "Home directory $homedir already exists. Doing nothing"
fi
#
# Display the result o the new user's DB record
echo "########## Result ##########"
mysql -u root --password="$rootpw" -vvv -e "select * from nss_mysql.users where uid='$newUID';"
ls -ld $homedir
#
#eof

mydeluser.sh
#!/bin/bash
# Purpose: Change the password of an existing user in the MySQL database for a PAM-mysql environment.
# Syntax: mypasswd.sh username -u username [-r]
# Dependencies: mySQL root password saved in $rootpwfile which should be 'chmod 600'
# --------------------------------------------------------
# Check the syntax
function usage () {
echo "ERROR: Wrong syntax"
echo "Usage: mypasswd.sh -u username [-r]"
echo "use '-r' option to delete the user's home directory as well"
exit 1
}
#
# Constants
#--------------
rootpwfile=$PWD/sqlrootpw.txt
# Get MySQL root PW
rootpw=$(head -n1 $rootpwfile | awk '{print $1}')
#
# get the options
deldir=false
while getopts "u:r" OPTION
do
case $OPTION in
u) user=$OPTARG
;;
r) deldir="true"
;;
h|?|*)
usage
;;
esac
done
#
# Get the UID of the user
homedir=$(echo "select homedir from nss_mysql.users where username='$user';" | mysql -u root --password="$rootpw" | tail -n1)
#
# Check if the user exists
if ! [ -z $homedir ]; then
# Delete the user's DB records
echo "Deleting the user from DB"
echo "DELETE FROM nss_mysql.users where username='$user';" | mysql -u root --password="$rootpw"
echo "DELETE FROM nss_mysql.groups where name='$user';" | mysql -u root --password="$rootpw"
echo "DELETE FROM nss_mysql.grouplist where username='$user';" | mysql -u root --password="$rootpw"
# Delete the home directory -r option used.
if [ $deldir = "true" ]; then
echo "Deleting the user home directory '$homedir'"
rm -rf $homedir
fi
else
echo "ERROR: User $user not found in database: nss_mysql.users"
fi
#
# eof

mypasswd.sh
#!/bin/bash
# Purpose: Change the password of an existing user in the MySQL database for a PAM-mysql environment.
# Syntax: mypasswd.sh -u username -p ['passowrd'] -r
# Dependencies: mySQL root password saved in $rootpwfile which should be 'chmod 600'
# --------------------------------------------------------
# Constants
#--------------
rootpwfile=$PWD/sqlrootpw.txt
# Get MySQL root PW
rootpw=$(head -n1 $rootpwfile | awk '{print $1}')
#
# Check the syntax
function usage () {
echo "ERROR: Wrong syntax"
echo "Usage: mypasswd.sh -u username -p ['passowrd'] -r"
echo "use -r if the user's home directory needs to be deleted as well"
exit 1
}
#
# get the options
deldir=false
while getopts "u:p:" OPTION
do
case $OPTION in
u) user=$OPTARG
;;
p) password=$OPTARG
;;
h|?|*)
usage
;;
esac
done
#
# Check if the user exists
dbuser=$(echo "select username from nss_mysql.users where username='$user';" | mysql -u root --password="$rootpw" | tail -n1)
if ! [ -z $dbuser ]; then
# Encrypt the password .... hummm tried everything but authentication is not working with SHA256/SHA512. Decided for MD5.
crypt_method='MD5'
crypt=$(echo "${user}:${password}" | chpasswd -S -c $crypt_method | cut -d: -f2)
# change the password
echo "UPDATE nss_mysql.users SET password='$crypt' WHERE username='$user';" | mysql -u root --password="$rootpw"
else
echo "ERROR: User $user not found in database: nss_mysql.users"
fi
#
# eof

Troubleshooting libpam-mysql

Situation: As I was writing this tutorial I tried to login as a user to the system with ssh and it worked. Then a few months later after a few system updates it didn’t work. Here is the error message I was seeing in the /var/log/auth.log file:
pam_mysql - non-crypt()ish MD5 hash is not supported in this build.
I looked all over the place in Internet and found the only answer to it in here:
http://osdir.com/ml/ubuntu-bugs/2014-07/msg10370.html
Relevant Content:
1, sudo apt-get install libmysqlclient-dev libpam-dev libssl-dev
2, ln -s /usr/include/openssl/md5.h /usr/include/md5.h
3, Makefile.in#109: DEFS = @DEFS@ -I. -I$(srcdir) -I. -DHAVE_OPENSSL
4, ./configure --prefix=/usr --with-openssl | grep md5

So here is the more detailed version of this patching procedure:
apt-get install libmysqlclient-dev libpam-dev libssl-dev libnss-mysql-bg libpam-mysql
apt-get source pam-mysql
apt-get build-dep pam-mysql
ln -s /usr/include/openssl/md5.h /usr/include/md5.h

Edit the file Makefile.in and make sure the line #109 has the following content:
DEFS = @DEFS@ -I. -I$(srcdir) -I. -DHAVE_OPENSSL
Start the compiling pam-mysql:
./configure --prefix=/usr --with-openssl | grep md5
make

Create the Debian package this time called pam-mysql:
checkinstall --install=no
Give it a slightly different version number/name and create the package
eg. 0.7~RC1-MD5
– Deinstall the original package libpam-mysql:
apt-get remove libpam-mysql
– Install the newly compiled package:
dpkg install pam-mysql_0.7~RC1-MD5-1_amd64.deb

Now in the above script (mynewuser.sh) the user’s data should be written as follows:
Write the password in the database via the normal MySQL MD5 function. eg.
echo "INSERT INTO ${DB}.users VALUES (0,'$username','$realname','$shell','tempPW','A',$newUID,$newGID,'$ftphtdocs','','0','0','7','-1','-1');" | mysql --host=$DBHOST -u root --password="$rootpw"
echo "UPDATE ${DB}.users SET password = MD5('$password') WHERE uid = $newUID;" | mysql --host=$DBHOST -u root --password="$rootpw"