MySQL-based Authentication with NSS-MySQL

Introduction

Many network environments rely today on some kind of centralized authentication server, which simplifies the duty of providing a consistent user accounts scheme across the network. Along with some type of network filesystem administrators can develop approaches to an unified work environment, where each user is not tied to a particular workstation or system. Instead each user can work on any of the systems of the network without noticing changes in their work environment.

Over the time many protocols and applications have been developed in order to provide this kind of schema. Some, like NIS are inherently insecure and should be avoided. Others, like Windows NT/2K/XP Domain controllers are tied to a particular operating system or architecture, which is a "no-no" on several networks where this OSes are not used. There are others that are secure and multi platform, but not precisely easy to use or maintain (Kerberos or NIS+ may come to the mind of the reader).

In this little document I'll try to give some insight on one solution that is relatively secure, works on a wide range of Unix-based systems and is pretty easy to setup and maintain. By no means I claim this approach to be the fastest, securest or most flexible out there, but in my humble opinion has a good compromise between all the desirable characteristics for an centralized authentication system. This document is centered around the Debian GNU/Linux operating system, but application to other Linux distributions or even other Unix systems should be pretty straightforward. At the time of this writing NSS-Mysql supports the following operating systems: Linux, FreeBSD, OpenBSD, NetBSD and Solaris

How it works

General perspective

What we are going to do is installing and configuring a plugin for libnss, Glibc's Name Service Switch (NSS) library. The NSS library provides a common interface for querying the system databases, like the password files. This library is flexible enough to accept plugins that can extend the lookup functionality to other, non file-based, databases (like MySQL).

We will use a NSS plugin that allows athentication based on a MySQL database . In the server side we will configure a MySQL server which will contain the authentication database. On the client side we will configure the NSS plugin for allowing these systems to lookup the authentication information using the MySQL database on the server.

In order to protect the authentication information that will flow across the network we will setup an SSL tunnel between the clients and the server.

Finally (and as an optional step) we will setup name caching proxies at the client machines for minimizing network traffic and thus improving the performance of the system.

Interaction with PAM

You might be wondering what all this has to do with PAM, the Pluggable Authentication Modules library. Well, it really has nothing to do with it. We will use the (default) pam_unix auth module, which relies in NSS to do the lookup in the system's authentication databases. You can think of PAM a superior layer in the authentication scheme, being NSS an abstraction of how to query the system for information.

Installing the required software

In the server we'll need the following packages (and their dependencies):

mysql-server
libnss-mysql
stunnel

In the workstation and client machines we'll need:

libnss-mysql
stunnel
inetd
ncsd (optional, improves performance)

It looks like there are two projects which provide MySQL support in NSS. The one used here is NSS-Mysql and has nothing to do with libnss-mysql. Unfortunately debian packages are confusely named (libnss-mysql & libnss-mysql-bg respectively).

Configuring the server

MySQL configuration

In first place we'll configure the authentication server. Our first duty is to configure and secure the MySQL server appropriately. This typically involves the following steps:

Securing with a password the MySQL root account
Deleting the tests users and databases
(Optional, but recommended) restricting network connections to the MySQL server port using MySQL configuration tools and/or iptables
Create a new database

I'll assume that you already know how to administer a MySQL database, so we will just move on without providing more details.

We will now create the tables of our newly created database. This is a tedious task that is also very error prone, so the libnss-mysql developers provide a small SQL script to make things easier. Note that this script also creates some sample users, so it's recommended to comment the INSERT statements in the script.

Once we have created the auth database we will have to add database users (note the plural) that will have access to the authentication data. libnss-mysql uses two different users for acceding different regions of the auth database. One of these users will have access to the ''non dangerous`` part of the auth database, that is, the one containing usernames, home directories, gid's, uid's, etc... The other user will have access to the hashed passwords of the users. If you think about it this kind of ''privilege separation`` is very similar to what happens with the files /etc/passwd and /etc/shadow. Once again, there exists an small SQL script provided by the libnss-mysql developers that contains the correct SQL GRANT statements in order to make effective this privilege separation. You'll have to modify it in order to replace the example database name with yours.

Setting up the server side stunnel

So far we have configured the database part of the setup. We will now configure the server side of the future encrypted link where we will "tunnel" the MySQL connection.

stunnel is a nice utility that allows the mentioned SSL tunneling of TCP connections. On the server we will spawn stunnel in server mode. In this mode stunnel will wait for SSL connections on an specified port and will redirect the data to another port. In a more graphical fashion:

                   PORT                                        PORT
               +<--3307<************Encrypted link************>whatever<--+
               |                                                          |
auth.mynet.com +---3306                                        3306-------+  workstN.mynet.com
                   |                                             ^
                   V                                             |
                 MYSQL                                       SQL Request

After stunnel installation you should generate an SSL certificate for it. The exact procedure is described in the stunnel documentation and it's pretty simple, so I won't duplicate it here.

The following script could be placed in the rc.N directories in order to start stunnel at boot time:

#!/bin/sh
case $1 in

start)
    echo -n "Starting Stunnel server..."
    stunnel -C RC4 -p /etc/ssl/certs/stunnel.pem -d 3307 -r localhost:3306
    echo "OK"
    ;;

stop)
    echo -n "Stopping Stunnel server..."
    killall -KILL stunnel
    echo "OK"
    ;;

default)
    echo "Usage: $0 start/stop"
    ;;
esac

What we are telling to stunnel is:

Use the RC4 cipher (-C RC4): RC4 is a stream cipher that is very fast and not very CPU intensive, which makes it perfect for our purpose. Of course you can change this to match your preferences.
Use the certificate found in /etc/ssl/certs/stunnel.pem (-p /etc/ssl/certs/stunnel.pem): Although this may be the default on some systems, it does no harm to specify it and may prevent future headaches with certificates.
Listen in the port 3307 (-d 3307): This will put stunnel in server mode, listening on the TCP port 3307.
Forward connections to port 3306, at localhost (-r localhost:3306): This will make stunnel to forward the decrypted connection to the TCP port 3306 at localhost, which is where our MySQL server is listening

Configuring the clients

Once the server is configured is time to configure all the authentication clients. The procedure is always the same in all the machines. If you want to configure the server as a client of its authentication mechanism you will also have to apply this steps on the server.

Configuring stunnel

After installing stunnel (don't forget to generate as well a certificate) we will install the client stunnel forwarder as a service in inetd. The following line at /etc/inetd.conf will do the trick:

mysql   stream  tcp     nowait  root    /usr/sbin/stunnel /usr/sbin/stunnel -c -r auth.mynet.com:3307

Restart inetd (/etc/init.d/inetd restart). If you want to test the encrypted link, use a MySQL client program to connect to the auth server (use the client machine IP address, not the auth server, IP address!)

Configuring libnss-mysql

Next step will be configuring the libnss-mysql configuration files. This library uses two configuration files: /etc/nss-mysql.conf and /etc/nss-mysql-root.conf. Both specify ways to access the authentication database, but there is a subtle difference between them: nss-mysql-root.conf tells the library how to connect to the database with the user that can have access to the hashed passwords. Thus we want this file to have read permissions only to the root user.

The files themselves are very self-documented, so you should have no problems to fill them with the appropriate values. Just remember that, from the point of view of libnss-mysql, the MySQL server is located at localhost, port 3306. This way the communication between the clients and the authentication server will be encrypted by stunnel.

It is also worth mentioning that libnss-mysql allows you to specify the tables and the columns used in the lookup, so you are not tied to a particular database layout. The defaults used in the configuration files match with the layout created with the scripts provided in the appendix of this document.

Configuring the NSS subsystem

The last part of the configuration process involves telling the NSS subsystem where to look for the system authentication databases. We will tell it to look first at the local password file (/etc/passwd and co.) and then, if the lookup was unsuccessful, query the central authentication server. This way we could use the regular flat-file databases to store authentication information for critical system users (such as root) and the MySQL for regular users.

For doing this all we have to do is modify the file /etc/nsswitch.conf so it looks like this one:

passwd:         compat mysql
group:          compat mysql
shadow:         compat mysql

hosts:          files dns
networks:       files

protocols:      db files
services:       db files
ethers:         db files
rpc:            db files

netgroup:       nis

Improving performance with ncsd

With the above setup each time the system needs to know some information from the system authentication databases it will likely have to query a MySQL server in some other location. Before this query leaves the client machine it will be encrypted using an SSL tunnel, which just adds more time. Once the information is decrypted at the server, and processed at the MySQL server the response will have to be encrypted again before it is send to the client. As you will have notice, this process is not precisely fast, and as the number of clients grow things get much worse.

Fortunately there exists a piece of software, called ncsd, which can speed this process. ncsd is an NSS catching daemon that does just that: catches nss lookups.

Installation is as easy as issuing apt-get install nscd and no configuration is needed for almost anyone, since the defaults are very reasonable.

References

nss-mysql website: http://savannah.nongnu.org/projects/nss-mysql
MySQL website: http://www.mysql.com
Stunnel website: http://www.stunnel.org

Appendix: SQL scripts for creating the database and the users

For reference here are the SQL scripts used to create the authentication database and its users. They are taken from the supplied as examples with the libnss-mysql library.

This script creates the required tables in the database.

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

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

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

This script creates the database users nss (regular database user) and nss-shadow (privileged database user).

GRANT select(user_name,user_id,uid,gid,realname,shell,homedir) on 
 nss-myql.user to nss@localhost identified by 'password';

GRANT select(group_name,group_id,gid) on nss-mysql.groups to nss@localhost
 identified by 'password';

GRANT select(user_id,group_id) on nss-mysql.user_group to nss@localhost;
 identified by 'password';

GRANT select(user_name,password,user_id) on nss-mysql.user to 'nss-shadow'@localhost
 identified by 'another_password';
 
FLUSH PRIVILEGES;