Up one level
Authenticating and authorizing Linux users kept in a MySQL database (libpam-mysql and libnss-mysql)
Spencer Stirling

If you are going to run a network with many users, all of whom will be logging into possibly DIFFERENT machines, then you will probably want to have a main USER database, completely independent of the /etc/passwd and /etc/shadow files that reside on each machine separately.

This can be very handy also if you have possibly SEVERAL independent servers that distribute the load or act as a failsafe in case one goes down (my current situation).

First, it's important to distinguish between authentication and authorization. Authentication asks "who are you, and how do I know that it's you?" (login and password), whereas authorization asks "now that I know you, what are you allowed to do on this machine?".

There are many methods of authentication, some more secure than others. The "standard" authentication (and authorization, for that matter) scheme is based on the "/etc/passwd" and "/etc/shadow" files. It's tried and true, but not flexible. Good for small setups, but not good for networks.

For networking, Sun invented NIS for both authentication and authorization a long time ago (to complement NFS). This is standard, but I don't think that you can feel very comfortable since security seems like an afterthought in that world. Probably my biggest turnoff is the fact that we are forced to run the RPC Portmapper "portmap", which assigns random ports to whatever process and hence is totally incompatible with our firewall (yes, you can fix it, but it's a pain).

For the best security nothing beats a Kerberos system on your network. This is an EXCELLENT system for handling authentication (but NOT authorization). For details check out the MIT Kerberos site. I had a functioning system up within a couple of hours, and I rather liked it (important tip: the KDC servers do NOT listen to themselves on LOOPBACK - so don't be fooled if you cannot get a ticket while logged into the KDC server itself. For example, my server was listening on (ethernet card) but not on I added a entry by hand in my /etc/hosts file). Unfortunately, Kerberos relies on reverse DNS lookups as a part of security, which is totally incompatible with my rogue dynamic IP network spread across the country scheme. So I was forced to ditch it.

Next, you can try LDAP. This is basically a database that can be used for both authentication AND authorization. However, I must say that the documentation for LDAP in Linux seems practically nonexistent (at least what I found... err... didn't find). I worked on it for a day and never even got a decently-functioning LDAP authentication. Pissed off, I ditched it. Somebody PLEASE write something about LDAP!

Finally, there's MySQL (the great). The documentation there is excellent, and it just works. For a 10 minute intro, try this. For something a tad more beefy, try this.

PAM Before moving on, let's talk about PAM. Basically, PAM is a nice standard interface that is meant to hide a bunch of the ugly details that come from each authentication scheme. So instead of having a telnet that knows how to talk to Kerberos directly (although there IS one), and a different one that can talk to MySQL, etc., there's telnet that can talk to PAM, and in turn there are PAM modules that can talk to Kerberos. We will strive to use PAM here, since then nothing else needs to change. If you like Kerberos, then check out the PAM module "libpam-krb5". If you like LDAP then try "libpam-ldap", etc. Here we'll be using "libpam-mysql".

Authorization comprises the second half of "logging in" - granting a given user rights (e.g. assigning a home directory). This is controlled via the NSS (Name Switch Service), which has as a configuration file

You can use LDAP again here, as well as MySQL (as well as the standard /etc/password "file scheme", as well as NIS). I'm using MySQL. Kerberos doesn't apply. Think of NSS as providing a standard interface to authorization, much like PAM does for authentication. This means that there are NSS modules that handle the nitty gritty tasks, e.g. "libnss-ldap" and "libnss-mysql". We'll use the latter below.

Installing and Configuring MySQL
You can start by installing MySQL. The Debian packages are

Once that little sweetheart is installed, try reading this page to get the main idea. It only takes 10 minutes.

Now you're ready. Since you are using MySQL for both authentication and authorization install these Debian packages

a certain amount of information can be harvested by looking at the documentation buried in
(in case you get lost here or something changes).

Start by setting up your administrator like so

mysqladmin -u root password PUTPASSWORDHERE
Now go into MySQL (as the administrator) by running
mysql -u root -p
From within MySQL you can create your new database like this
create database nss_mysql;

Before you do anything else, you'll need to set up the "nss_mysql" database tables. So log out of MySQL ("quit;") and put the following MySQL commands into a text file - say "blah.sql"

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

INSERT INTO groups VALUES (1,'users','A','x',100);


  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

GRANT select(user_name,user_id,uid,gid,realname,shell,homedir,status) on user to nss@localhost identified by 'ieopurASDF';
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 'ieopurASDF';
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 'ruASDFDER';
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 'ruASDFDER';
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.

Run this file on your MySQL by running from the command line

mysql -u root -p < blah.sql
That should set up your database with the appropriate tables. Notice the last four lines. This created two MySQL users, namely "nss" and "nss-shadow" (with passwords at the end - you should use your own nasty ones). Obviously the user "nss" is for regular queries and "nss-shadow" is for access to the password information. This scheme tries to mimic the shadow-like behavior as found in /etc/passwd+/etc/shadow. You should put some nasty passwords here (that you don't use anywhere else) because you'll be FORCED to write them in PLAINTEXT in the configuration files. Don't use your root password!!!

Now that you have a MySQL database properly configured, you'll need to set up the module that will glue NSS to it. So first, edit

and look for the lines
passwd:	compat
group:	compat
shadow:	compat
Instead of "compat" yours might say "files". Either way, this just tells NSS to first look into /etc/passwd+/etc/shadow. You can add MySQL lookup by changing the above to
passwd:	compat mysql
group:	compat mysql
shadow:	compat mysql
So now NSS is first going to the old way, and then resorting to MySQL if a user is not found (you DON'T want to store special users, like "root" in the MySQL database!!! Just the regular ones).

Now edit the two files

The first applies to the "nss" MySQL user, and the second applies to the "nss-shadow" MySQL user. You probably won't have to change much in there, except put in the passwords that you used above when you initialized your database. Now do the following:
chmod 600 /etc/nss-mysql-root.conf
This makes your "nss-shadow" information file only readable by root (a good thing). It's actually mandatory. DO NOT do the same thing for "/etc/nss-mysql.conf". This file needs to be world readable it seems (yes, your "nss" password is world readable. AHHHHH. I guess this is no more of a security problem than /etc/passwd being world readable :)).

Now be careful here. Be VERY careful. You might lock yourself out of your machine (and if the machine is remote, maybe until you can fly there. This nearly happened to ME!!!). Here's an important tip: leave a spare terminal up that is logged in as root user. That way if you make a mistake then you still have a little backdoor in.

The important files for PAM configuration can be found in

If you are a Debian user then you should direct your attention to the "common-*" files. Those are used by (nearly) all of the services. If you are not using Debian then the following still applies, but you'll need to find the right filename.

I searched around forever and experimented quite a bit, and I finally have settled on this setup. This will make everything totally transparent. You'll even be able to change passwords using the "passwd" utility. NOTE: don't be surprised if this doesn't work for you. PAM behavior seems to change from distro to distro. This is what is working on my Debian sid machine.

UPDATE: Some people have told me that they need to add options such as "table=user" and "statcolumn=status" in all of these files (at least for Debian Lenny). Also sometimes you need the option "passwdcolumn=password" in the common-auth and common-password files. Check "/usr/share/doc/libpam-mysql/Readme" to see if anything changed for you.

Make "common-auth" look like this

auth    sufficient        pam_unix.so nullok_secure
auth    required        pam_mysql.so user=nss-shadow \
  passwd=ruASDFDER db=nss_mysql usercolumn=user.user_name \

Make "common-account" look like this

account sufficient      pam_unix.so
account required        pam_mysql.so user=nss \
  passwd=ieopurASDF db=nss_mysql usercolumn=user.user_name

Make "common-session" look like this

session sufficient      pam_unix.so
session required        pam_mysql.so user=nss \
  passwd=ieopurASDF db=nss_mysql usercolumn=user.user_name

Make "common-password" look like this

password   sufficient   pam_unix.so nullok obscure min=5 max=12 md5
password    required      pam_mysql.so nullok user=nss-shadow \
  passwd=ruASDFDER db=nss_mysql usercolumn=user.user_name \
  crypt=1 md5

Now make all of these only root readable (since password for "nss-shadow" it apparently in there)

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.

Note about encryption
This section is added for completeness (you may safely skip to the next section). First consider some of the most important cryptography schemes:

Let us focus further on cryptographic hashing. The standard Unix function crypt(3) (type "man crypt 3" for details) implements several hashing algorithms (note: this is NOT crypt(1), which is a very weak Unix encryption utility). Crypt(3)'s oldest (not very strong) algorithm is a clever modification of DES. Although DES is a symmetric encryption/decryption scheme, it can be turned into a hashing function as follows: the algorithm takes an 8-character password and concatenates a 2-character salt. SALT+PASSWORD is used to generate a pair of private DES keys. One of the keys is then used to encrypt some trivial data (i.e. a string of zeros), then both keys are destroyed. The encrypted result is the "hashed password". Although this can be "decrypted" in principle, it doesn't decrypt to reveal the original password. Instead it merely decrypts to return the original string of zeros. For this algorithm crypt(3) returns a 13 character string (first 2 characters are salt, 11 are hashed result).

Crypt(3) also implements some better hash algorithms. Examples are MD5 and SHA. Another example is a modification of Blowfish to create a hashing algorithm (much like the above modified DES). (again MD5 and SHA1 are considered vulnerable now - use Blowfish or SHA2). In these modes crypt(3) will return a hashed string of the form

where the number $1$ records that (for example) MD5 was used for the hash (it will be $2a$ for Blowfish, etc).

The importance of crypt(3) is that it is used by the PAM modules for password hashing. For example, in the above common-* file configuration the system first tries the regular "pam_unix.so" module. Pam_unix.so in turn uses crypt(3) to hash the entered password, and a match in /etc/passwd and /etc/shadow is sufficient to log in.

In order to instruct pam_unix.so to create new passwords using a particular crypt(3) hashing algorithm, a modifier can be added in the "common-password" file after the pam_unix.so keyword, i.e.

pam_unix.so md5
pam_unix.so blf
where the first example is MD5 and the second is Blowfish.

In the above common-* configuration if pam_unix.so fails then the system queries the "pam_mysql.so" module ("required" means that the system won't try anything else after pam_mysql.so). Confusingly, the "crypt=1" argument directs pam_mysql.so to use crypt(3), and the extra "md5" argument tells crypt(3) to use md5 hashing. (Unfortunately pam_mysql.so is only documented using the "md5" crypt(3) directive. It should at least allow better directives such as "blf". Maybe it does?)

A further source of confusion (for me, anyway) is that pam_mysql.so can bypass crypt(3) altogether and instead pass the hashing duties to the MySQL server. For example, if we changed "crypt=1" to "crypt=3" then we would STILL get MD5 hashing (we do NOT add the "md5" modifier as above since that is a crypt(3) directive), however the MySQL server would be performing the hash. This method is not desirable, however, because plaintext passwords may be intercepted in the communication between the pam_mysql.so module and the MySQL server (for example the MySQL server may LOG the plaintext password... OUCH).

Steps to add a user
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. It would be nice to write your own script, or look for one on the Internet. I didn't find any that were worth anything, and I don't have time right now to write anything. The first step is to find the next available uid, gid, etc. So go into MySQL by running:

mysql -u root -p
and execute the command
select * from nss_mysql.user;
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 "adduser.sql":
INSERT INTO nss_mysql.groups VALUES (100,'spencer','A','x',1000);
INSERT INTO nss_mysql.user VALUES (100,'spencer','Spencer Stirling','/bin/bash','','A',1000,1000,'/home/spencer', '041406', '', '','', '', '');
INSERT INTO nss_mysql.user_group VALUES (100,100);
INSERT INTO nss_mysql.user_group VALUES (100,1);
Obviously this 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. The numbers "100" seen everywhere are INTERNAL numbers for referencing in MySQL. You should increment them with the next user (i.e. 100 ---> 101 and 1000 ---> 1001). The last statement also adds the user "spencer" to the group "users" (created in the beginning).

Now run these statements with

mysql -u root -p < adduser.sql
That will add your user (with a blank password, by the way).

Now you MUST change the password manually for your user. Since you (hopefully) have PAM working properly, you can just go through the normal routine there. So run

passwd spencer

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
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.user where user_name='spencer';
DELETE FROM nss_mysql.groups where group_name='spencer';
DELETE FROM nss_mysql.user_group where user_id='100'
Then you can delete the user's home directory (although you should give some leniency there!!!).

Replicating your database to a SLAVE server
This part is REALLY cool. It is rather easy to keep a sync'ed copy of your user database (and everything else) on a SLAVE server elsewhere. First, there's the NOT recommended way: shut down both servers, copy the database (located in /var/lib/mysql) over, make sure that ownership and permissions are correct, and start them both back up.

The safe way is to use the "mysqldump" utility that dumps the database in a standard format. Since I cannot remember the syntax right now you may use Google to find out how to backup/restore this way.

Finally, there's a much better way if you want to replicate your databases to multiple slave MySQL servers. Follow these instructions EXACTLY, except with one modification if you want to use an SSH tunnel: MySQL listens on port 3306. Since my servers are widely separated, I prefer to connect them via an SSH Tunnel. So I forward (say) port 3307 on the slave server to port 3306 on the MASTER server. Then I make the slave server listen to ITSELF (localhost) on port 3307. So the only change to that article is

    ->     MASTER_HOST=localhost,
    ->     MASTER_PORT=3307,
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;
There's nothing else to say. Keep yourself an SSH Tunnel open via a crontab script (using SSH with keychain). 'Nuff said.

This page has been visited   times since April 14, 2006