SourceForge Logo


Store your UNIX user accounts in MySQL

** Download Current Version: 1.5 (Sep 03 2005) **



Current Release
CVS Snapshot
Debian Packages


Sample Configs

Support Forums

The MySQL Database


You can design the database to be relatively simple, or extremely complex. For the most part, the stucture of the database is up to you; The groups piece is more complex and less flexible, however. Take a look at the sample configuration. There are MySQL statements to execute in order to retrieve the data needed for various system calls. The query names mostly match the system calls themselves. The system call, getpwnam(), asks the system to supply various information for the username provided - uid, gid, gecos, home directory, shell, etc. getpwuid() does the same thing, except it looks the user up via uid instead of username. You can use the man pages to see what each one does. You won't find memsbygid or gidsbymem, however. I'll cover that in more detail shortly.

Query Requirements - passwd/shadow

Each SQL query must return data in the right order - that's it. You can design your database pretty much however you want - just make sure you can create a query that will return the necessary data in the right order. You can gather from the sample getpwnam query that the data must be returned as follows (Linux): username, password, uid, gid, gecos, homedir, shell. You'll note the example has password set to 'x'. This is to protect user passwords (even though they're returned encrypted) from non-root users. The shadow database (getspnam, getspent) will return the user's password. Programs understand that systems with a shadow database (Linux and Solaris both have it) need to call getspnam if they need to retrieve a user's password. You'll also note the WHERE clause contains a '%1$s'. This '%1$s' is where the system will insert the username being searched for. For getpwuid, this is a '%1$u' because we're searching based on a number (%1$u) rather than a username/string (%s). It's important to keep these correct, else things will start crashing. Here's an example of a much more complex getpwnam query:

SELECT LOWER(services.username), 'x', uid, gid, CONCAT_WS(' ', first_name, middle_initial, last_name), service_defs.homedir, FROM services LEFT JOIN customer USING(cust_num) LEFT JOIN service_defs ON WHERE services.username='%1$s' AND expire > CURDATE() AND suspended='N' AND services.hostname='' LIMIT 1

Query Requirements - groups

The design for groups is a little more limited. Since MySQL is a relational DB, it's not possible to directly implement a many-to-many relationship, which "groups" needs. We need to be able to get all the members of a group by the gid, and we need to get all gid's a user is a member of. To do this, we need two tables: one to hold group information (group name, group password, group id), and the other to hold a list of username-gid mappings. A group lookup requires two queries - one to fetch the group itself (getgrnam, getgrgid, getgrent), and another to fetch the group's members (memsbygid). Traditionally, systems would have to scan the entire group database to figure out all the groups a user was a member of. Now, we have the initgroups function. This directly asks the subsystem for a list of gid's a user is a member of. This is why we need the 'gidsbymem' SQL query.

Connectivity to Your MySQL Servers

Connection Persistence

libnss-mysql maintains a persistent connection for any process that performs a lookup. This is necessary for performance and resource reasons. You should take note that if you have 100 concurrent processes that perform user lookups, you'll have at least 100 concurrent connections to your MySQL server. Sometimes these processes stick around for a while, and only perform a lookup at the start (IE sshd) - so you may ask how to time out these unnecessary lingering connections. You need to instruct your MySQL server to close idle connections after, say, 60 seconds (instead of the default 28,800). To do so, edit/create the file '/etc/my.cnf' and add:
And restart your MySQL server.

Connection Flavors

Connectivity comes in two forms: UNIX sockets or TCP/IP. Sockets is preferred as it's faster, uses fewer resources, and you're not likely to run out of available TCP/IP sockets on a heavily (and I mean heavily) loaded system. You also won't have to worry about encrypting your MySQL connections. If you have a multiple-server environment, you could replicate your database (optionally over an encrypted session) to each client, and point the client at the local replica. Or point all your clients at one MySQL server. You could use a tunnel for encryption, or use MySQL 4.x to use native SSL.

Multiple Servers/Failover

libnss-mysql no longer (as of v0.9) supports multiple servers or failover. The code was cumbersome and buggy - I'd just as soon let a load balancer (whether it's a piece of hardware or a specially-configured linux box) do the job.

libnss-mysql Configuration Files


There are two libnss-mysql configuration files: /etc/libnss-mysql.cfg and /etc/libnss-mysql-root.cfg. These are read once for each process that makes a lookup for a user in MySQL. If you have a daemon that looks up usernames (IE radius), it will not see changes to this file until you restart it. This is especially important to note if you're running NSCD. Please see the NSCD section below

The Files

Both files are necessary if you want to properly implement shadow support. The main file (/etc/libnss-mysql.cfg) generally specifies all the queries and non-root MySQL server information. The root file (/etc/libnss-mysql-root.cfg) can contain any subset of the above file, although generally it only contains the username and password to connect with for 'root' access.

File Format

Comments begin with a "#" at the beginning of the line. Lines may be continued by using a "\" as the last character of the line. Each line contains a key/value pair like so:
      key = value
The key is always a single word. The value may contain anything, and should not be quoted. Whitespace between key/= and =/value is allowed as spaces or tabs - as many as you need. Lines may be continued, but it's strictly required that the "\" character is the very last character in the line to be continued. Whitespace after the "\" will break it. Here's an example:
      key = some really long \
            value goes here

Main vs. Root

The root config is read after the main config and overrides any settings in the main config for operations that are done as root. This is used to connect to your MySQL server as an "unpriviledged" user when the program requesting the user information isn't running as root, and connect to your MySQL server as a "priviledged" user when the program requesting the user information is running as root. This allows you to set up different MySQL access levels - one that can read a user's password (the "priviledged" user), and one that cannot (the "unpriviledged" user). Since the main config is readable by anyone, you'll want to make sure that the "unpriviledged" user doesn't have access to any information that it doesn't absolutely need. Remember, any user can see the MySQL username/password to connect to your database with as an "unprivilidged" user. Along the same lines, it's important to make sure your root config is owned by root and mode 0600 so only root can read the "priviledged" connection information.


This section defines the SQL queries to execute in order to implement various lookups. Some of the queries take an argument (such as a username or userid) in the form of symbols such as '%1$s' and '%1$u'. You may use these symbols as many times as you like. There are 10 queries:

getpwnamFetch 'password' record for a specific username
getpwuidFetch 'password' record for a specific uid
getspnamFetch 'shadow' record for a specific username
getpwentFetch all 'password' records on the system
getspentFetch all 'shadow' records on the system
getgrnamFetch 'group' record for a particular group
getgrgidFetch 'group' record for a particular gid
getgrentFetch all 'group' records on the system
gidsbymemFetch a list of gids that a specific username is a member of
memsbygidFetch a list of usernames that a gid has

MySQL Server Setup

Settings in the 'root' configuration will take precendence when the current process is running as 'root'. This is used to give the process access to priviledged data such as the user's password. Generally speaking the 'root' config will only contain the alternate username and password to connect with. Note that settings in your /etc/my.cnf's [client] section will influence libnss-mysql! This is why many of the settings below are optional. I don't recommend putting the username/password in /etc/my.cnf, however.

Options in red are required. All others are optional
hostString MySQL server name/IP
databaseString MySQL database name
usernameString MySQL user to connect as
passwordString MySQL password to connect with
socketPathclient defaultPath to MySQL socket (if host is localhost)
portIntegerclient defaultTCP/IP port of MySQL server (fi host is not localhost)
timeoutInteger3MySQL connect timeout in seconds
compress0|10If you want to use MySQL compressed protocol, set to 1, otherwise 0
initcmdString Run this SQL statement upon connect



This is where we tell the system what sources to use for its user information. Linux and Solaris ship with an /etc/nsswitch.conf containing many lines. On Solaris and FreeBSD, we worry about 2 - 'password' and 'group'. On Linux we worry about 3 - 'password', 'shadow', and 'group'. On FreeBSD you must create /etc/nsswitch.conf. There are a multitude of possible different configurations. Essentially you want to tell the system which databases to query under what circumstances and in what order. I recommend 'files' (/etc/passwd) first, followed by 'mysql'.

The Gory Details

The relevant lines might look something like this (note that the 'shadow' line shouldn't be used except on Linux systems):
       passwd: files mysql
       shadow: files mysql
       group:  files mysql


I want to mention NSCD as it can greatly enhance the performance of your libnss-mysql installation. NSCD - Name Service Cache Daemon - caches requests made to the NSS system. For example, if you were running NSCD and you typed 'id cinergi' for the first time, the system would have to directly query the MySQL database - a slow process compared to retrieving the data from cache. Once NSCD has cached it, subsequent commands like 'id cinergi' would come from the in-memory cache that NSCD creates - a huge performance boost, especially in a multi-threaded situation. You'll want to 'man nscd' on your system for more information on how it works, tuning, etc. You may need to enable the daemon such that it's started at boot-time. You should note that NSCD does not cache shadow/password information

NOTE: Since all queries route through NSCD if you have it enabled, changes to libnss-mysql configuration files and /etc/nsswitch.conf will not be seen until you restart NSCD. If you continue having problems, restart any daemons in question or reboot the system.


Rudimentary testing has shown that /etc/passwd user access is far faster than MySQL access, even with thousands of users. Once you start going into 10's or 100's of thousands, single-user lookups are much faster on MySQL. However, complete passwd scans (like fuzzy 'finger' matching) remain faster on /etc/passwd. That said, it's not libnss-mysql's fault; libnss-mysql adds about 30% overhead to the SQL queries themselves. Querying the database simply takes more time than it does to read a relatively small file.

In multi-threaded situations, libnss-mysql performance drops noticeably. Where possible, run multi-process software or use NSCD (which is multi-threaded). Multi-platform library threading is not something I want to get into at the moment.


SF Menu
MySQL Manual
Solaris nsswitch

I love the fact your code allows me to specify the SQL to pass to mysql. That really made integration into my complex system easy. I can not tell you how much I appreciate this library, it is so flexible and a huge time saver for administrators. - Eric L. Blevins

< This site Copyright 2002-2004 Ben Goodwin >