Keeping Linux Users In A MySQL Database With libpam-mysql On Ubuntu

I want to have a set of users on my Ubuntu 10.4 Lucid Lynx box managed by MySQL, rather than LDAP for a change which means delving in to the sexy world that is libpam-mysql!

As ever, the first thing that we need are packages! Remember when installing mysql-server to set a strong root MySQL password. As we’re managing user accounts in MySQL we need to really make sure everything is locked down tight!

apt-get install mysql-server libpam-mysql libnss-mysql

Configuring MySQL

We then need to auth to MySQL as root

mysql -u root -p

and create a database and some tables!


USE nss_mysql;


group_id int(11) NOT NULL auto_increment primary key,

group_name varchar(30) DEFAULT ” NOT NULL,

status char(1) DEFAULT ‘A’,

group_password varchar(64) DEFAULT ‘x’ NOT NULL,

gid int(11) NOT NULL



user_id int(11) NOT NULL auto_increment primary key,

user_name varchar(50) DEFAULT ” NOT NULL,

realname varchar(32) DEFAULT ” NOT NULL,

shell varchar(20) DEFAULT ‘/bin/sh’ NOT NULL,

password varchar(40) DEFAULT ” NOT NULL,

status char(1) DEFAULT ‘N’ NOT NULL,

uid int(11) NOT NULL,

gid int(11) DEFAULT ‘65534’ NOT NULL,

homedir varchar(32) DEFAULT ‘/bin/sh’ NOT NULL,

lastchange 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’


CREATE TABLE user_group (

user_id int(11) DEFAULT ‘0’ NOT NULL,

group_id int(11) DEFAULT ‘0’ NOT NULL


And set up 2 MySQL accounts, one for reading and one for writing. The read only account will have a password exposed on the file system, so make sure it is locked down and unique. This isn’t a security issue as all it will expose is as much as /etc/passwd does anyway.

GRANT select(user_name,user_id,uid,gid,realname,shell,homedir,status) on user to nss@localhost identified by 'buttercup';

GRANT select(group_name,group_id,gid,group_password,status) on groups to nss@localhost identified by 'ieopurASDF';

GRANT select(user_id,group_id) on user_group to nss@localhost identified by 'buttercup';

GRANT select(user_name,password,user_id,uid,gid,realname,shell,homedir,status,lastchange,min,max,warn,inact,expire) on user to 'nss-shadow'@localhost identified by 'bunnyface';

GRANT update(user_name,password,user_id,uid,gid,realname,shell,homedir,status,lastchange,min,max,warn,inact,expire) on user to 'nss-shadow'@localhost identified by 'bunnyface';


Configuring NSS

NSS (Name Service Switch) provides a common method through which system database requests can be fed. Implementations of these operations can be extended via modules. By default Ubuntu is configured to use the compat (/etc/passwd & /etc/shadow) module, but we’re going to tell it to also use the mysql module.

We are going to need to edit /etc/nsswitch.conf, look for the lines

passwd: compat

group: compat

shadow: compat

and reconfigure it to also use mysql like so

passwd: compat mysql

group: compat mysql

shadow: compat mysql

Now edit the two files with the relevant MySQL usernames and passwords. The first uses the nss user and the second uses the nss-shadow user.



Now we make the nss-shadow file only readable by root as this contains the really important credentials

chmod 600 /etc/nss-mysql-root.conf

Do not do that to nss-mysql.conf though.

Configuring PAM

PAM (Pluggable Authentication Modules) handles all the different ways you can authenticate to the system. We need to update it so it knows it can use MySQL to handle authantication!

In /etc/pam.d we must edit a series of files :


auth sufficient nullok_secure

auth sufficient user=nss-shadow passwd=bunnyface db=nss_mysql usercolumn=user.user_name crypt=1 table=user

auth requisite

auth required


account sufficient

account optional user=nss passwd=buttercup db=nss_mysql usercolumn=user_name table=user

account requisite

account required


session sufficient

session required user=nss passwd=buttercup db=nss_mysql usercolumn=user_name table=user

session requisite

session required

session required


password sufficient nullok obscure min=5 max=12 md5 debug

password sufficient nullok user=nss-shadow passwd=bunnyface db=nss_mysql usercolumn=user_name crypt=1 table=user passwdcolumn=password statcolumn=status

password requisite

password required

Now lock the files down so they are only root readable

chmod 600 common-*

Creating A User

We’re going to create a user and a group called minty! Create a minty.sql file for the user

INSERT INTO nss_mysql.groups VALUES (100,'minty','A','x',1002);

INSERT INTO nss_mysql.user VALUES (100,'minty','Minty','/bin/false','','A',1002,1002,'/home/minty', '041406', '', '','', '', '-1');

INSERT INTO nss_mysql.user_group VALUES (100,100);

Then import the sql file

mysql -u root -p < minty.sql

Create the home directory

root@crisps:~# cp -ax /etc/skel /home/minty

root@crisps:~# chown -R minty:minty /home/minty/

Set the password

passwd minty

(New) Password:

Retype (New) Password:

passwd: password updated successfully

SSH in to the server 😉

Chill:~ idimmu$ ssh minty@crisps

Warning: Permanently added 'crisps,' (RSA) to the list of known hosts.

minty@crisps's password:

Last login: Fri Aug 27 10:14:05 2010 from


et voila, libpam-mysql based user management on a Linux Ubuntu box! Next up to write a web interface to manage all that 🙂

  1. I got this error when trying to change the password. Everything is setup correctly. Please help.

    passwd: Authentication token manipulation error
    passwd: password unchanged

    1. nss-shadow can’t update password of the users. maybe you can add this:

      GRANT update(password) on user to ‘nss-shadow’@localhost identified by ‘bunnyface’;

Leave a Reply

Your email address will not be published. Required fields are marked *