I recently ran into a problem with NSS-MySQL – or rather I ran into a number of problems with NSS-MySQL.

  • The first was that there are two libraries with almost identical names (libnss-mysql and nss-mysql) hosted in two different locations (sourceforge.net and savannah.gnu.org respectively) which do exactly the same thing (lookup users from a MySQL database).
  • The second was that all our legacy servers that use this method of authentication had been installed from source, however the key text that told me which version to use (sourceforge or gnu) had been removed from the build documents so I didn’t know which one I was meant to be installing.
  • The third issue (and this isn’t the final issue unfortunately) was that when I realised that the version we were using was the gnu.org one, I also discovered that the only one for which maintained packages were available was the sourceforce.net version.
  • The fourth (and last for the time being) was that the configuration file formats were completely different

At this point, it wasn’t looking too good…

Working closely with a Colleague, we also identified that although the users in the MySQL database were completely different to those found in /etc/passwd, many of the uids and gids matched entries in both.  This was because although all the account names for users start at 2001 as far as the system is concerned, the database userId field starts at 1.  I love legacy systems…

After three days trying to solve this, we were starting to wonder if it would be any quicker to redesign the database and just reassign the permissions across the 25TB of data that we hold for various accounts, then, we hit the solution.

libnss-mysql (the package available from EPEL and the version that is hosted on SourceForge) has to be the version of choice going forwards as we must be able to deploy this server without any human intervention in order for the build systems based upon cucumber-vhosts/hudson that I’ve discussed elsewhere on this blog to work correctly.  This is how we fixed the queries…

NSS uses a number of functions to call out to the authentication/authorisation service (nss-mysql in our case) and retrieve information about the users. A number of these calls return a UID or lookup a UID in the database, and this is where the problem lies.

Let’s say that we have a user called “theprof” with a group of the same name.  On the file systems, all files listed as being owned by “theprof” havd a uid of 2002 and a gid of 2002.  In the database however, “theprof” has a uid of ‘2’ and a gid of ‘2’ – this is the same as the ‘daemon’ user on most systems, therefore this is never going to work.

We interrupt this blog post to bring you a handy hint:

If you do decide to user nss-mysql as an authentication/authorisation backend, please make sure to setup your auto-increment with an offset of more than 1000 (or what ever you deem appropriate!) this way you won’t run into the same issues that we did!

So, to get around the mismatch it’s easy right? You just add 2000 onto the uid and gid in all your mysql queries and you get the correct results… umm, apparently not.

Queries that retrieve uids and gids from the database based on the user/group name need to add 2000 to the query and this works fine, however the next time you do a lookup on the database based on the uid/gid (not the user/group name) you end up with the wrong information as the uid/gid is 2000 higher than it needs to be for the database.

So it’s an easy fix then – remove the 2000 when you do a lookup on the database.  Which works fine, but only if you add the 2000 on some queries and take it away on the others – and it matters which queries that you do this for.

So, here it is, the document you’ve all been after – the finished config file.  The field names should be fairly self explanatory, all you should need to do is either remove the 2000 if you have been sensible and setup the database properly from the start (see the Handy Hint above) or change the “2000” to whatever you’ve decided to number from if you’ve also inherited a legacy system like ours, update the username and password and away you go!

getpwnam    SELECT userName,'x',uid+2000 AS uid,gid+2000 AS gid,realName,homeDir,shell 
            FROM users 
            WHERE userName='%1$s' 
            LIMIT 1
getpwuid    SELECT userName,'x',uid,gid+2000 AS gid,realName,homeDir,shell 
            FROM users 
            WHERE uid='%1$u'-2000 
            LIMIT 1
getspnam    SELECT userName,password,lstchg,min,max,warn,inact,expire,flag 
            FROM users 
            WHERE userName='%1$s' 
            LIMIT 1
getpwent    SELECT userName,'x',uid+2000,gid+2000 AS gid,realName,homeDir,shell 
            FROM users
getspent    SELECT userName,password,lstchg,min,max,warn,inact,expire,flag 
            FROM users
getgrnam    SELECT groupName,password,gid+2000 AS gid 
            FROM groups 
            WHERE groupName='%1$s' 
            LIMIT 1
getgrgid    SELECT groupName,password,gid 
            FROM groups 
            WHERE gid='%1$u'-2000 
            LIMIT 1
getgrent    SELECT groupName,password,gid+2000 AS gid 
            FROM groups
memsbygid   SELECT userName 
            FROM users 
            WHERE gid='%1$u'-2000
gidsbymem   SELECT gid+2000 AS gid 
            FROM users 
            WHERE userName='%1$s'

host        localhost
database    userdb
userName    dbuser
password    password
#socket      /var/lib/mysql/mysql.sock
port        3306

Another job done… right! What’s next?