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 🙂
