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!

CREATE DATABASE nss_mysql;

USE nss_mysql;

CREATE TABLE groups (

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

);

CREATE TABLE user (

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';

FLUSH PRIVILEGES;

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.

/etc/nss-mysql.conf

/etc/nss-mysql-root.conf

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 :

common-auth

auth sufficient pam_unix.so nullok_secure

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

auth requisite pam_deny.so

auth required pam_permit.so

common-account

account sufficient pam_unix.so

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

account requisite pam_deny.so

account required pam_permit.so

common-session

session sufficient pam_unix.so

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

session requisite pam_deny.so

session required pam_permit.so

session required pam_unix.so

common-password

password sufficient pam_unix.so nullok obscure min=5 max=12 md5 debug

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

password requisite pam_deny.so

password required pam_permit.so

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,192.168.0.111' (RSA) to the list of known hosts.

minty@crisps's password:

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

minty@crisps:~$

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