Authenticating and authorizing Linux users kept in a MySQL database (libpam-mysql and libnss-mysql)

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?".

Authentication
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 192.168.0.24 (ethernet card) but not on 127.0.0.1. I added a 192.168.0.24 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
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

/etc/nsswitch.conf

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

mysql-server
mysql-client

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

libpam-mysql
libnss-mysql

a certain amount of information can be harvested by looking at the documentation buried in

/usr/share/doc/libnss-mysql
/usr/share/doc/libpam-mysql

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

DROP TABLE IF EXISTS groups;

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

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

DROP TABLE IF EXISTS user;

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

DROP TABLE IF EXISTS user_group;

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

Note the "GRANT update" line at the bottom. This is necessary in order for nss-shadow to be able to CHANGE anything (like passwords). Thanks to Robin Williams for pointing this out. I don't know how big of a security risk it is to allow nss-shadow to update ALL of those entries - probably only the "password" field is enough.

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!!!

libnss-mysql
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

/etc/nsswitch.conf

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

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

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 :)).

libpam-mysql
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 screw up the login process (lock yourself out) you'll still have a little backdoor in.

The important files for PAM configuration can be found in

/etc/pam.d

If you are a Debian user then you should direct your attention to the "common-*" files. Those are used by (nearly) everything else. 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: Perger Balazs tells me that he needed to put the option "table=user" in all of the entries below. Again, the PAM/MySQL modules seem to change conventions often. Grrrr.

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 \
  crypt=1

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

UPDATE: Mathieu Ignacio points out that you may need to also add these options to common-password: passwdcolumn=password statcolumn=status (at least for Debian/lenny). Again, PAM seems to change behavior from release to release. Thanks Mathieu.

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

chmod 600 common-*

In the future you may need to add more settings on each "pam_mysql.so" line. I only added what was necessary. For everything else the default was fine. Check "/usr/share/doc/libpam-mysql/Readme" to see if anything changed for you.

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.

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 inelegant 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.

But there's a MUCH better way. 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

mysql> CHANGE MASTER TO
    ->     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 2,787 (6 today) times since April 14, 2006