Due to the insane cost of bandwidth (compared to the rest of the developed world) in Australia, I've recently decided to move some of our hosting clients to Linode. This means they can move more data more cheaply and I don't need to come up with (and administer) a bandwidth accounting system for my Australian based web VM.

We pretty much exclusively use Drupal for hosting clients, so to make management a bit easier I decided to use Ægir on the new Linode. Installation was a relative breeze, after a quick google to find out how to specify that I didn't want to use Apache and wanted to use a separate server as dedicated MySQL host.

The problem (there is always a problem) arose when I needed to give a hosting client access to their Drupal installation, so they could manage themes and site-specific modules. Just adding an account and providing SSH access was out of the question, as all sites are stored under a single system user. Anyone logging in with permissions to edit their own Drupal can then also edit all other sites and even the Ægir installation itself.

FTP would be a solution, as FTP accounts can be chrooted (locked into a specific directory) quite easily, but I didn't want to have to manage a list of FTP accounts separate from the Drupals in Ægir. That isn't the lazysysadmin way.

After a bit of thought I remembered that on an older web host, I had happily used libnss-mysql and libpam-mysql, which integrate accounts defined in a (any) MySQL database with the Linux system. The trick is to get MySQL to cough up the account information in the correct format, so the system can parse it as if these accounts were normal system users.

Ægir allows me to assign Site nodes to Client nodes and allows these Client nodes to be owned by Drupal users. In addition, there are Platform nodes which define in which directories on disk all sites are stored. Between those content types, there is enough information to create the account entries used by the system. It just requires a MySQL query which joins 4 or 5 tables in the Ægir database. And libpam-mysql is able to authenticate against MD5 hashes, which is how Drupal 6 stores user passwords.

To avoid a giant and unreadable configuration file for libnss-mysql I decided to embed these queries as views. That means I embed some logic in the database itself and in return I can configure really simple SELECT queries in the libnss-mysql and libpam-mysql conguration files.

The logic embedded in the views ensures that people using accounts for Drupal users marked as blocked or Ægir sites marked as disabled or deleted are not able to login and that these accounts as listed as such when displayed via the standard Linux account administration tools.

After you install libnss-mysql-bg (which is the package name on Debian) you'll have to configure /etc/libnss-mysql.cfg and /etc/libnss-mysql-root.cfg. The latter exists to allow the account tools to use different credentials on the MySQL server in case they need to run UPDATE or DELETE queries. You won't be doing that, so all you need is to make sure that if there is anything in that file, it's commented out and won't interfere.

It's views Jim, but not 6.x-3.0

First up, create a MySQL user that is allowed to select data from your Ægir database. Connect to MySQL as administrator and run the following query. Adjust it, so it uses the correct host and database and a somewhat safer password.

GRANT SELECT ON aegir.* TO libnss@localhost IDENTIFIED BY 'secret';

Next, switch to the Ægir database to add the views that libnss-mysql will use. First off, the view nss_getpwnam that will return standard account inormation, such as username, home directory and shell.

You'll see I used 10000 as the uid and gid in this query. That's because my aegir system user has these user and group ID. The virtual users from MySQL need to have these same user and group IDs, so that when they login via FTP, they are the owner of their home directory and able to write files.

You should change these numbers in this query to match the user and group IDs for the aegir user on your system!

CREATE VIEW `nss_getpwnam` AS
SELECT
DISTINCT LCASE(`n`.`title`) AS `username`,
'x' AS `password`,
10000 AS `uid`,
10000 AS `gid`,
CONCAT(`nc`.`title`, ',,,') AS `gecos`,
CONCAT_WS('/', `p`.`publish_path`, 'sites', `n`.`title`) AS `homedir`,
IF (s.status = 1, '/bin/true', '/bin/false') AS `shell`
FROM `hosting_site` `s`
LEFT JOIN `node` `n`
ON (`s`.`vid` = `n`.`vid`)
LEFT JOIN `hosting_client_user` `c`
ON ((`s`.`client` = `c`.`client`) AND (`c`.`user` <> 1))
LEFT JOIN `node` `nc`
ON (`nc`.`nid` = `s`.`client`)
LEFT JOIN `users` `u`
ON ((`u`.`uid` = `c`.`user`) AND (`u`.`status` <> 0))
LEFT JOIN `hosting_platform` `p`
ON (`s`.`platform` = `p`.`nid`)
;

The username that willl be used is the title of the site node (in lowercase). There's a bit of logic there that creates a home directory for the user out of the platform path and the site name and the account shell is set to /bin/true if the site is enabled or /bin/false if it isn't. This matters, as you'll configure the FTP server to disallow logins for users who have their shell set to /bin/false. The view also disregards Ægir users 0 and 1, the guest and administrator. If you migrate the site to a different platform, the view will automatically use the new platform_path to create an updated home directory.

Next up, the view nss_getspnam that returns secure account data, such as the encrypted password and expiry information.

CREATE VIEW `nss_getspnam` AS
SELECT
DISTINCT `n`.`title` AS `username`,
IF((`s`.`status` = 1), `u`.`pass`, '*'), AS `password`,
1 AS `lstchg`,
0 AS `min`,
99999 AS `max`,
0 AS `warn`,
0 AS `inact`,
IF((`s`.`status` <> 1), 0, -(1)) AS `expire`,
0 AS `flag`
FROM `hosting_site` `s`
LEFT JOIN `node` `n`
ON (`s`.`vid` = `n`.`vid`)
LEFT JOIN `hosting_client_user` `c`
ON ((`s`.`client` = `c`.`client`) AND (`c`.`user` <> 1))
LEFT JOIN `users` `u`
ON ((`u`.`uid` = `c`.`user`) AND (`u`.`status` <> 0))
;

This again contains a little bit of logic that prevers users from logging in if their associated Drupal site isn't enabled in Ægir, by setting the encrypted password to * instead of their Drupal password and the expire field to 0 (which means it expired in January 1970).

The other two views (nss_getgrman and nss_gidsbymem) you need for libpam-nss take care of group name to ID and group membership by user mapping. In this case all you want is for these accounts to be members of the aegir group, so all they need to do is return the name 'aegir' and the gid 10000.

CREATE VIEW `nss_getgrnam` AS
SELECT
'aegir' AS `group`,
'x' AS `password`,
10000 AS `gid`
;

CREATE VIEW `nss_gidsbymem` AS
SELECT 10000 AS `gid`
;

That was easy :-)

Finally, the view that will allow libpam-mysql to check login passwords for the FTP server. This returns the Site node title as username, the associated Ægir user's Drupal password as the password and the Ægir site status as a field that allows you to reject logins for disabled sites.

CREATE VIEW `nss_mysql` AS
SELECT
DISTINCT `n`.`title` AS `username`,
IF((`s`.`status` = 1), `u`.`pass`,'*') AS `password`,
`s`.`status` AS `status`
FROM `hosting_site` `s`
LEFT JOIN `node` `n`
ON (`s`.`vid` = `n`.`vid`)
LEFT JOIN `hosting_client` `c`
ON (`s`.`client` = `c`.`nid`)
LEFT JOIN `hosting_client_user` `cu`
ON ((`s`.`client` = `cu`.`client`) and (`cu`.`user` <> 1))
LEFT JOIN `users` `u`
ON ((`u`.`uid` = `cu`.`user`) AND (`u`.`status` <> 0))
;

There is a bit of logic again that returns * as password for Ægir users that are blocked, meaning they cannot FTP to their site and access is denied for user #1.

You can have a play with these and see what account information you now have by simply selecting data from the view, for instance SELECT * FROM nss_getpwnam;

Tell Papa Linux

That's it for the work that you need to do on the MySQL server. Next, configure libnss-mysql to use these views for account information retrieval. Add the following content to /etc/libnss-mysql.cfg:

getpwnam SELECT username,'x',uid,gid,gecos,homedir,shell \
FROM nss_getpwnam \
WHERE username = '%1$s' \
LIMIT 1
getpwuid SELECT username,'x',uid,gid,gecos,homedir,shell \
FROM nss_getpwnam \
WHERE uid = '%1$u' \
LIMIT 1
getspnam SELECT username,password,lstchg,min,max,warn,inact,expire,flag \
FROM nss_getspnam \
WHERE username='%1$s' \
LIMIT 1
getpwent SELECT username,'x',uid,gid,gecos,homedir,shell \
FROM nss_getpwnam
getspent SELECT username,password,lstchg,min,max,warn,inact,expire,flag \
FROM nss_getspnam
getgrnam SELECT group,password,gid \
FROM nss_getgrnam \
WHERE group='%1$s' \
LIMIT 1
getgrgid SELECT group,password,gid \
FROM nss_getgrnam \
WHERE gid='%1$s' \
LIMIT 1
memsbygid SELECT username
FROM nss_getpwnam \
WHERE gid='%1$u'
gidsbymem SELECT gid \
FROM nss_gidsbymem

host localhost
database aegir
username libnss
password secret

And be sure to edit the final four lines to match the MySQL server credentials for the libnss user that you created earlier. The beauty of using MySQL views is that the WHERE and LIMIT clauses will just work, as if they were appended to the query defined in the view.

When this is done, you can tell your system to start using libnssl-mysql for account detail lookups. Open up /etc/nsswitch.conf and append the word mysql to the end of the passwd line.

passwd: compat mysql

This will cause your system to first check /etc/passwd for account information and then interrogate libnss-mysql (which you've now configured to ask Ægir). You can test this by running the command getent passwd, which should now give you a list of system users, followed by a list of Ægir site names.

A nice side-effect of this is that you can now cd to the site's home directory in your shell. Doing this will pop you in the correct directory under sites in the correct platform. Even tab completion works on these site-based account names.

If you have enabled sites in Ægir and you only see users from /etc/passwd you should make sure the libnss MySQL user can connect to the server and run its defined queries on the views you created.

Install FTP

Next up, install an FTP server and configure it. Exactly how you go about this depends on the FTP server you choose, but you should ensure to disable anonymous logins, force all users to be chrooted and set the umask to 002. Check the FTP server documentation for how to do this. If it doesn't come with documentation, choose a different FTP server. It also needs to be able to use PAM (Pluggable Authentication Modules) for authentication, though I think most are able to do this.

Be sure to add /bin/true to /etc/shells, as most FTP servers are configured to disallow logins for users whose shell is not listed in that file.

I chose Pure-FTPD, after running into odd problems with vsftpd (which I've used successfully elsewhere).

The umask is used to set default permissions on files and directories uploaded by users. Setting it to 002 means that anyone sharing the aegir group (which is the group all these virtual users share) is able to modify these files and directories, without having to be the aegir user. That means I can use my own system account to make changes to these files if I need to without making them inaccessible to FTP users.

Because all FTP users are locked into their own directories via the chroot, they cannot access each others sites.

Enabling PAM

The last step is to configure authentication for the FTP server, to allow users to login and upload (or download) stuff. On Linux, authentication is taken care of by PAM, which by default uses /etc/passwd and /etc/shadow. It can be made to use a MySQL server by installing the libpam-mysql or the newer libpam-mysql-bg package. On my Debian server, only the latter is available.

Configuration for PAM can be done separately for each service that uses authentication or centrally, by having each service include a common configuration file (or files). You'll create a common configuration file for MySQL authentication and then include that in the FTP specific file.

Create a file called /etc/pam.d/common-aegir with the following content:

# Use pam-mysql to grant access to Drupal Ægir clients.
auth required pam_mysql.so \
user=libnss passwd=secret host=localhost db=aegir table=nss_mysql \
usercolumn=username passwdcolumn=password crypt=3 where=status=1
account sufficient pam_mysql.so \
user=libnss passwd=secret host=localhost db=aegir table=nss_mysql \
usercolumn=username passwdcolumn=password crypt=3 where=status=1

I've broken up the lines to make them fit on screen. They should be single long lines in your file. Also make sure you change the MyQSL credentials to the ones you created earlier, or PAM won't be able authenticate users.

The important bits here are crypt=3, which tells PAM the passwords are in MD5 format (as used by Drupal) and the where=status=1 part which will prevent users from authenticating if their site is disabled in Ægir.

Finally, edit the specific PAM configuration file for the FTP server so it includes the new common-aegir file. In my case, with /etc/pam.d/pure-ftpd, that means I need to comment out or remove the existing @include directives and add in a new one:

# PAM config for pure-ftpd
# Standard behaviour for ftpd(8).
auth required pam_listfile.so item=user sense=deny file=/etc/ftpusers onerr=succeed

@include common-aegir
That's all folks

You can now login to FTP using the site name as username and the associated Ægir user's password as password. Logging in will put you in the site-specific directory under the correct platform, so you can upload modules and themes.

I've attached copies of all the files I refer to, so you can grab them without having to copy and paste everything from this post.

Caveats & TODOs

I can't help myself, I have to add some caveats to this post. Though Ægir users can now FTP to their Drupal sites, the solution isn't exactly ideal. If multiple users are associated with a single Client, the queries I use above only return the enctypted password for one of them. And it's not clear which one. The login names on FTP and on Ægir are alo not the same, which is perhaps security through obscurity, but it's also un-ideal.

In an ideal world, FTP would perhaps use the password for user #1 on a given site, to login using that site as username. However, that would mean joining a table on a database name that's defined as a field in an Ægir table that's different for each site. It can probably be done, but it also means the libnss MySQL users needs SELECT access on all databases on the system.

As-is, the system can also not cope with Ægir installs that use multiple frontend web servers. That would require some extra WHERE statements on each frontend, so only users whose site runs on that frontend are able to login there.

AttachmentSize libnss-mysql.cfg_.txt1.11 KB nsswitch.conf_.txt481 bytes common-aegir.txt378 bytes pure-ftpd.txt443 bytes libnss-views.sql_.txt1.73 KB
Tags: linuxmysqldrupalintegration