Mass Virtual Hosting Part One: Database-Backed User Accounts and Authentication

In situations where the creation of UNIX system accounts (for file system permissions and s/ftp authentication, etc.) should be automated or managed remotely it is possible to use MySQL to store user and group information, thanks to libnss-mysql. There is another project named NSS MySQL which provides the same functionality but for the purposes of this article we will only deal with the former, as it has been more recently updated and is the only one included in Gentoo's Portage package management system.

NSS stands for Name Service Switch, it provides a convenient way to replace or supplement the traditional /etc/passwd /etc/shadow /etc/groups system with anything from LDAP to MySQL tables. It sits at the layer below PAM (which can only handle authentication) and this allows it to transparently handle user and group lookups and modifications from unmodified system tools. For example, using ls to show a directory listing would pull the correct user and group names from the database and using passwd to change the password of an account in the database would interface with the database and not /etc/shadow.

Once you have installed libnss-mysql (emerge libnss-mysql on Gentoo - add it to your package.keywords to grab the last, CVS-based and bug-fixed release) create a database somewhere accessible to both the intended management interface and the system where account information is to be supplemented. Sharing this database across multiple libnss-mysql enabled hosts allows one to maintain a consistent authentication system across them.

# The tables ...
  name varchar(16) NOT NULL default '',
  password varchar(34) NOT NULL default 'x',
  gid int(11) NOT NULL auto_increment,
  PRIMARY KEY  (gid)

CREATE TABLE grouplist (
  rowid int(11) NOT NULL auto_increment,
  gid int(11) NOT NULL default '0',
  username char(16) NOT NULL default '',
  PRIMARY KEY  (rowid)

  username varchar(16) NOT NULL default '',
  uid int(11) NOT NULL auto_increment,
  gid int(11) NOT NULL default '5000',
  gecos varchar(128) NOT NULL default '',
  homedir varchar(255) NOT NULL default '',
  shell varchar(64) NOT NULL default '/bin/bash',
  password varchar(34) NOT NULL default 'x',
  lstchg bigint(20) NOT NULL default '1',
  min bigint(20) NOT NULL default '0',
  max bigint(20) NOT NULL default '99999',
  warn bigint(20) NOT NULL default '0',
  inact bigint(20) NOT NULL default '0',
  expire bigint(20) NOT NULL default '-1',
  flag bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (uid),
  UNIQUE KEY username (username),
  KEY uid (uid)

# The permissions ...
GRANT USAGE ON *.* TO `nss-root`@`localhost` IDENTIFIED BY 'rootpass';
GRANT USAGE ON *.* TO `nss-user`@`localhost` IDENTIFIED BY 'userpass';

GRANT Select (`username`, `uid`, `gid`, `gecos`, `homedir`, `shell`, `password`,
              `lstchg`, `min`, `max`, `warn`, `inact`, `expire`, `flag`)
             ON `auth`.`users`
             TO 'nss-root'@'localhost';
GRANT Select (`name`, `password`, `gid`)
             ON `auth`.`groups`
             TO 'nss-root'@'localhost';

GRANT Select (`username`, `uid`, `gid`, `gecos`, `homedir`, `shell`)
             ON `auth`.`users`
             TO 'nss-user'@'localhost';
GRANT Select (`name`, `password`, `gid`)
             ON `auth`.`groups`
             TO 'nss-user'@'localhost';

GRANT Select (`username`, `gid`)
             ON `auth`.`grouplist`
             TO 'nss-user'@'localhost';
GRANT Select (`username`, `gid`)
             ON `auth`.`grouplist`
             TO 'nss-root'@'localhost';

Modify the permissions section to reflect your needs. libnss-mysql uses a so-called 'user' account for routine operations and a 'root' account for privileged operations, designated by nss-user and nss-root respectively above. Next we must configure the library, edit /etc/libnss-mysql.cfg and change only the authentication information at the bottom of the file:

host        localhost
database    auth
username    nss-user
password    userpass
#socket      /var/lib/mysql/mysql.sock
#port        3306

The SELECT queries at the top of the file can be modified later to integrate into an existing user database of your own design, it is important that only the columns (under any name) specified in the original queries are returned and in that order. Next alter /etc/libnss-mysql-root.cfg to reflect the libnss-mysql 'root' user's MySQL credentials (for obvious reasons these files should have restrictive permissions):

username    nss-root
password    rootpass

libnss-mysql makes use of persistent DB connections, which can be problematic in multithreaded environments as a new connection is established for each thread. For that reason it is recommended to pass the connections through something like mysql-proxy or nsscache/nscd. The problem can also be mitigated by reducing the timeout for persistent connections (default 8 hours) on the MySQL server to something like 1 minute, however this may not be a great idea if the MySQL server is used for other purposes (web hosting etc). Now that we have configured libnss-mysql we must teach NSS to use it. Alter these lines in /etc/nsswitch.conf to reflect:

passwd: files mysql
shadow: files mysql
group:  files mysql

files means first read the traditional /etc/passwd shadow and group files, failing lookup use libnss-mysql (mysql). This lets regular system accounts which we have already created remain valid, important because we don't want to have to import root, your regular user account(s) and all of the daemon accounts. More importantly still if connectivity to the database is lost it is still possible to log in to perform administrative duties. You will note that we created the groups and user tables with an AUTO_INCREMENT set to 5000. Most systems begin creating local user accounts at UID 1000, this amount of space between file-based accounts and database-backed accounts should be enough to keep accounts from overlapping, however your situation may require adjustment. Let's create a test account and see how we did:

INSERT INTO users (username,gecos,homedir,password)
    VALUES ('test', 'Test Account', '/home/test', ENCRYPT('test'));
INSERT INTO groups (name)
    VALUES ('test');
INSERT INTO grouplist (gid,username)
    VALUES (5000,'test');

Here we have created a user named test, with the password 'test', in the group test. The gid value in the last INSERT query should be changed to reflect the UID of the new users in subsequent runs. Create /home/test and chown test:test /home/test. When you ls -lsah /home do you see test and test under user and group next to the test directory? If so congratulations, you have successfully configured libnss-mysql. You may wish to further test your configuration by logging in via ssh/sftp.

It should be easy to see how one can manipulate these queries to add, delete and manage system accounts directly from inside your web-based management interface or other applications, the potential here is limitless.

I encountered a problem setting up a new mysql (server) installation on a host that was configured to use libnss-mysql. The message goes something like:

#  /usr/bin/mysql_install_db
Installing MySQL system tables...    
100706  9:09:15 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.                                                           

It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 76800 K
bytes of memory                                                                  
Hope that's ok; if not, decrease some variables in the equation.                 

Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went   
terribly wrong...                                                      
Cannot determine thread, fp=0x841099c, backtrace may not be correct.   
Bogus stack limit or frame pointer, fp=0x841099c, stack_bottom=0xbffe0000, thread_stack=196608, aborting backtrace.
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains                                        
information that should help you find out what is causing the crash.                                               

This crash occured while the server was calling initgroups(). This is
often due to the use of a mysqld that is statically linked against glibc
and configured to use LDAP in /etc/nsswitch.conf. You will need to either
upgrade to a version of glibc that does not have this problem (2.3.4 or  
later when used with nscd), disable LDAP in your nsswitch.conf, or use a
mysqld that is not statically linked.                                    
Installation of system tables failed!                                    

Examine the logs in /var/lib/mysql for more information.
You can try to start the mysqld daemon with:            
/usr/sbin/mysqld --skip-grant &                         
and use the command line tool                           
/usr/bin/mysql to connect to the mysql                  
database and look at the grant tables:                  

shell> /usr/bin/mysql -u root mysql
mysql> show tables                 

Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in /var/lib/mysql that may be helpful.          

The latest information about MySQL is available on the web at
Please consult the MySQL manual section: 'Problems running mysql_install_db',
and the manual section that describes problems on your OS.                   
Another information source is the MySQL email archive.                       
Please check all of the above before mailing us!                             
And if you do mail us, you MUST use the /usr/bin/mysqlbug script!

Of course, I was running glibc 2.11.2 and have nothing in the way of LDAP configured. Unfortunately there is no workaround, you must run the mysql daemon on  a separate server that does not use libnss-mysql.


[...] noted in an addendum to Part One of this series on Mass Virtual Hosting: Database-backed User Accounts and Authentication I had difficulties running mysqld on machines configured to use libnss-mysql, therefore for the [...]