Setting Up MediaWiki 1.13 with mod_auth_pgsql Authentication

Posted: September 24, 2008
Author: Scott Newman
Category: Python, PHP, PostgreSQL

I created an internal wiki for our team, and I needed to password-protect the whole thing. I didn't want users to have to authenticate themselves to Apache then get to the wiki pages and have to log in again.

I found information on using mod_auth_mysql, but nothing with PostgreSQL. Hopefully a write-up of my experiences will help someone else!

Installing mod_auth_pgsql

On Ubuntu, it was pretty easy (and that's why I like Ubuntu!):

$ sudo aptitude install libapache2-mod-auth-pgsql

Then, in Apache, I had to enable it. Ubuntu's default Apache2 setup uses that awful mods-available/mods-enabled symlink structure that I hate, so I had to find the new module in mods-available and symlink it in mods-enabled. The module is named "000_auth_pgsql.load"; I'm pretty sure it's prefixed with "000" so it loads before other mods, but I could be wrong.

Modify MediaWiki's configuration

You have to add a line to your LocalSettings.php file that prevents it from salting the MD5 hash so that the password saved is a straight MD5 hash that Apache can compare. In LocalSettings.php, add these lines at the end:

$wgPasswordSalt = false;

If you already have users created, you may need to update their passwords after changing this. I'm not 100% sure, so I did it anyway. MediaWiki prefixes this with the characters ":A:", so we have to concatenate it to the md5-generated string. I updated my Admin user like this in psql:

wikidb=# update mwuser set user_password= ':A:' || md5('testing') 
where user_name='Admin';

Creating a New Database View

Because MediaWiki adds a ":A:" to the beginning of the password hash, a simple md5 hash of the user's password won't match when it tries to compare it. The easiest solution I found was something I found on a site by Richard K Miller. (Thanks Richard, whoever you are!) I created a view in the database that drops the ":A" so that mod_auth_pgsql can authenticate against the value of the field:

wikidb=# CREATE VIEW mwuser_view AS SELECT user_id, user_name,  
substring(user_password from 4) AS user_password FROM mwuser;

Notice the difference:

wikidb=# SELECT user_name, user_password from mwuser 
where user_name='Admin';

user_name |            user_password            
-----------+-------------------------------------
Admin     | :A:ae2b1fca515949e5d54fb22b8ed95575
(1 row)

wikidb=# SELECT user_name, user_password from mwuser_view 
where user_name='Admin';

user_name |          user_password           
-----------+----------------------------------
Admin     | ae2b1fca515949e5d54fb22b8ed95575
(1 row)

Notice the passwords are the same, except in the view, the prefix ":A:" is dropped.

Setting Up Your VirtualHost

With this in place, I had to add the necessary directives to my VirtualHost:

AuthType Basic
AuthName "My Wiki"
Require valid-user

Auth_PG_host localhost
Auth_PG_port 5432
Auth_PG_database wikidb
Auth_PG_user wikiuser
Auth_PG_pwd somepassword
Auth_PG_pwd_table mwuser_view
Auth_PG_uid_field user_name
Auth_PG_pwd_field user_password
Auth_PG_authoritative on
Auth_PG_encrypted on
Auth_PG_hash_type md5

Test that it Works

Finally, you can test to make sure it works. Restart Apache, then pull up your URL. You should get the HTTP Basic Authentication dialog box, and put in the username and password from your user in MediaWiki. (I ran into a problem here not realizing that MediaWiki automatically capitalized the first letter of my username when I created it - grrr!)

Modifying MediaWiki to Pick up HTTP Authentication

Now that this is in place, I have the site password protected. This still requires my users to log in twice, so we need to tell MediaWiki to use the authentication information that the user provided to the dialog box. Luckily, someone else wrote it, and all I had to do was copy and paste it.

Get the code at this URL, or Google "MediaWiki remote user": http://www.mediawiki.org/wiki/Extension:AutomaticREMOTE_USER

Put the code in a file called "Auth_remoteuser.php" in your extensions folder. Once saved, add this to your LocalSettings.php file:

# Let it use Apache auth to login UNLESS you are creating a new user
if ($_GET['title'] != 'Special:UserLogin')
{
  require_once('extensions/Auth_remoteuser.php');
  $wgAuth = new Auth_remoteuser();
};

You'll see a big ugly hack here with the if statement around the code. I'll explain it in a minute.

Test that it All Works

Before going any further, close your browser, open it again, and make sure you are able to log in with your username and password. You should get a basic authentication dialog box - put in your credentials, and when returned to the wiki page, you should be logged in properly.

Creating New Users

This setup causes an interesting problem when you want to create new users. We've told MediaWiki that our authentication system is remote, so we've broken the process used to create new users. (The auth isn't really remote, as Apache goes into the database table and checks the username and password from MediaWiki)

To get around this problem, the lines we added to the LocalSettings.php file are wrapped in an "if" statement to check if the page being requested is the one for new user creation. If it is, it won't try to use a remote authentication system for them, and we'll be able to create a new account.

I put this line in my LocalSettings.php file to make sure that anyone can create a new account. Without it, I'd have to log in as the administrative user just to create an account, and I'm too lazy to do that. (This is a trusted environment anyway)

$wgGroupPermissions['*']['createaccount'] = true;

To create a new user, I go in with an existing user account, and go to the account creation page at "index.php?title=Special:UserLogin&type=signup". Create the new account, but don't use the "by e-mail" button!

Using the "by-email" method will create a temporary password for the user but will leave the user_password field blank. mod_auth_pgsql won't have a value in the user_password field to test, so the user won't be able to get by the HTTP autentication to get in and change their password.

Click the "Create account" button, and it will create an account and send the user a confirmation. You will have to tell the user their password, and they will not be prompted to change it.

This last part is pretty ugly, but it works because my group is small. If it were a larger setup, I'd probably modify the user creation to put the temporary password in the user_password field so they could authenticate themselves.

I hope this helps someone! Here are some URLs I used to find this info:

http://www.gossamer-threads.com/lists/wiki/mediawiki/144024
http://www.adamek.biz/md5-generator.php
http://www.gnu-darwin.org/ProgramDocuments/mod_auth_pgsql/#Download
http://www.mediawiki.org/wiki/Extension:AutomaticREMOTE_USER
http://www.mollerus.net/tom/blog/2008/09/single_signon_to_mediawiki_113_using_active_direct.html
http://www.richardkmiller.com/blog/archives/2006/05/password-protecting-mediawiki-with-mod_auth_mysql