The following notes provide a summary of how to install and configure an Apache module that allows authenticaion of users against a PostgreSQL Database.
My configuration consisted of two VMWare FreeBSD (8.2) machines, one hosting Apache (ver. 22) Web Server and the other hosting PostgreSQL 9.0 RDBMS.
What I wanted was to provide a relatively simple to manage authentication mechanism via a plugin that did a user credential check against PostgreSQL database. The reason for this choice is that it is simpler than setting up an LDAP server and being RDBMS based means that is it very easy to provide additional registration functionality and integration with a PHP based application I was developing.
In terms of complexity this is a small step away from using .htaccess file based mechanism, but substainsialy more flexible and managable.
Not expecting to be the first person to wanted to use PostgreSQL as the store for authenticaion credentials, the first thing I did was search for existing solutions. I found the following three projects:
AuthPG - This project documents that it works with both Apache 1.3 & 2 and explicity describes configuration via httpd.conf file
Module mod_auth_pgsql - This project explicity states that it is for Apache2 and its configuration is described using .htaccess files
mod_authn_dbd - This is the Apache officially sanctioned way to authenticate against databases and uses the mod_dbd framework. I only found this later while trying to get the other two options working... so it is covered last
As my prerference was to use httpd.conf based configuration, rather than .htaccess files in each directory, I started off by attempting to install AuthPG.
NOTE: Subsequently I found that all of these options allow configuration via httpd.conf .
The AuthPG documented installation process was quite straight forward:
I am not sure why but this did not work, but the make failed to create a usaeable apache module. In order to avoid doing a full Apache compile, the build process should create a shared library which is then installed into the Apache libexec area (/usr/local/libexec/apache22 in my FreeBSD based installation). This is done by using theAPache eXtenSion tool (apxs), which I tried to invoke manually, only to get a large number of compiler errors:
# apxs -c mod_auth_pg.c
/usr/local/share/apr/build-1/libtool --silent --mode=compile cc -prefer-pic -O2 -pipe -I/usr/include -fno-strict-aliasing -O2 -pipe -I/usr/include -fno-strict-aliasing -g -I/usr/local/include -I/usr/local/include/apache22 -I/usr/local/include/apr-1 -I/usr/local/include/apr-1 -I/usr/local/include -I/usr/local/include/db42 -c -o mod_auth_pg.lo mod_auth_pg.c && touch mod_auth_pg.slo
mod_auth_pg.c:70: error: expected ')' before '*' token
mod_auth_pg.c:141: warning: initialization from incompatible pointer type
mod_auth_pg.c:142: error: expected expression before 'auth_pg_config'
mod_auth_pg.c:142: error: initializer element is not constant
mod_auth_pg.c:142: error: (near initialization for 'auth_pg_cmds[0].cmd_data')
...
...
...
This did not look like it was going to be a simple process... so I abandoned trying to use AuthPG and moved onto mod_auth_pgsql
Having previously preferred to use AuthPG, due to its configuration being managed via httpd.conf, I subsquently found the following posting which outlined using mod_auth_pgsql with configuration via httpd.conf. My reservation with using .htaccess files is that I do not like the idea of having very senstive configuration information sitting in directories from which files are servered. It is just asking for trouble in the event that something goes wrong with your HTTP configuration that opens up a potential security hole on HTTP server. Yes I know this is paranoid, but Murphy's law does have a way of imposing itself...
So onto the mod_auth_pgsql installation.
This was very simple for DSO installation (ie using Apache eXtenSion tool):
apxs -i -a -c -I /usr/local/include -L /usr/local/lib -lpq mod_auth_pgsql.c
This all executed correctly creating and installed the Apache module and updated the httpd.conf file to load this at startup.
NOTE: To check the build look for the following line in your httpd.conf file:
LoadModule auth_pgsql_module libexec/apache22/mod_auth_pgsql.so
To test this I created sample protected directory, added tables into PostgresSQL and then created Apache configuration file to include in httpd.conf.
1 | Create a sample secure information directory and put a simple index.html file (/usr/local/www/private/index.html) into this. I then created an symlink to this from my Apache WWW root directory (/usr/local/www/apache22/data):
|
2 | Create a user id/password and groups table on the PostgresSQL database and populate with some sample data:
|
3 | Finally I created an include file for my httpd.conf file to hold my protected directory information using prior example and initially using unencrypted configuration to help get things up and running:
|
This initial configuration worked and when I followed a link into or attempt to directly access the index.html file in the "private" directory I get an authentication challenge. As this is just using basic authentication, this means that there will be clear text user id and password being transmitted across the internet. This is unlikely to be acceptable for anything other than a playpen environment, where you actually expect people to have open acces and the security is simply there so give people a chance to politely indicate who they are (a bit like using mail address as password for anonymous ftp).
Additionally the passwords stored on the database are in clear text which means that this is vulnerable to trivial data theft attack.
The following section discusses alternates to providing a more secure system.
But first some additional notes.
Having spent some time testing with AuthPG and mod_auth_pqsqul working I discovered that the DB specific athentication modules are being replaced with the mod_dbd framework (this late discovery was primarily due to Google search for "apache postgresql authentication" mostly returns information on outdated modules). The mod_dbd approach uses the Apache Portable Runtime (APR) to manage the database connections. This will start to become relavent if you wish to use Digest based authentication.
As mentioned using Basic athentication has a number of drawbacks as it sends the password across the web unencrypted. This can be handled by with changing from AuthType from Basic to Digest.
The Digest authentication does not send the user id and password, rather it sends a MD5 hash value, which is created using a number of inputs including an MD5 hashed concatentations of <user id>:<realm>:<password> and the <HTTP method>, <URI> and a nonce key and some client keys and sequence counters. See Wikipedia for full details on HTTP Digest Authentication.
The result is that you can then authenticate the user without having to setup an SSL/TLS connection, which requires certificates and the processing overhead of SSL/TLS. In my case I decided that I wanted to use Digest authentication and so changed my configuration as follows:
secure-dir.conf
secure-dir.conf |
|
This failed with error:
"Digest: user `<login-user-id>' in realm `Graphica Software Authenticator' not found: /private/index.html, referer: http://<apache-host>/
As I was unable to trivially resolve this issue and others seemed to experience the same problem and I wanted to have the option of using Digest Authentication rather than using SSL to provide security for Basic Authentication, I decided to try mod_db. I suspect the problem is that to use Digest Authentication required that the password lookup returns MD5(<user>:<realm>:<password>) hashed response rather then just the unencryped password... however I have not tested to confirm this.
As noted above mod_auth_pgsql, mod_auth_mysql, mod_auth_mssql and its various RDBMS variants have now been surplanted by mod_dbd and mod_authn_dbd. The mod_dbd framework provides a generic way to connect to and generally use SQL databases from Apache and mod_authn_dbd provides the specific functions required to perform SQL based authentication.
On a Unix box the requirements for both Basic and Digest Athentication are to define an SQL string that will return a ENCRYPTED password, which can then be used to to validate the user. This is a signficant difference from mod_auth_pgsql, where you can specify how the password data is stored on the database.
As the password comparison is always against hashed or encrypted data it is importand that both Apache and PostgreSQL are using the same crypto library. Apache uses the OpenSSL crypto library, so if you are running PostgreSQL on different machine you need to make sure that this also has the OpenSSL crypto library installed and you need to install the PostgresSQL pqcrypto module.
To achieve working configuration for both Basic and Digest authentication I completed the following preliminary build steps:
1 | Rebuild Apache22 with mod_dbd included and ensure that the the Apache Portable Runtime is built with PostgreSQL support. For FreeBSD, you can build a PostgreSQL APR library by going into /usr/ports/devel/apr1 and building from there). You then need to build Apache with mod_authn_dbd and mod_dbd selected. This meant going into the Apache22 ports directory and doing a "make rmconfig" and then "make" and selecting the PostgresSQL authentication module options and mod_dbd from the menu. In order to get Apache22 to reinstall I also had to use FORCE_PKG_REGISTER option as part of make reinstall. NOTE: The APR PostgreSQL library is loaded dynamically as a result of the directive: |
2 | As I was running PostgreSQL server on a different machine from Apache, I had to build the OpenSSL crypto libraries (/usr/ports/security/openssl) on the PostgresSQL server machine, prior to installing the pgcrypto module. NOTE: Prior to doing this I had problems with password missmatch errors, which could be a result of the PostgreSQL query not returning the results that where encrypted with the same libraries as Apache. |
3 | Install the PostgreSQL pgcrypto module into the database that is being used to hold the users information. This is not required if you are directly putting encrypted data values into the tables, but if you want to create some identity and registration funtionality on top of your PostgresSQL credential repository, then you will need this module. Its installation is a multi-step process. I followed the process documented here, as it describes what is needed to build the library from the PostgresSQL server ports area. The only difference I took from this was due to using PostgreSQL version 9.0 and it mentions changing the Makefile to pickup the OpenSSL crypto library. If you have done step (2) above and installed the OpenSSL libraries in /usr/local/lib then you do not need to change the Makefile as the /usr/local libraries will get picked up automatically. To use the pgcrypto module you need to then add the SQL functions into each database that needs them. The specific steps were:
|
Next you need to update your httpd.conf file to load the mod_dbd and mod_authn_dbd modules and configure the database connection and either Basic and Digest authentication The following snippets provide examples of the configuration items:
httpd.conf (modules) |
|
httpd.conf (database) |
|
Basic |
|
Digest |
|
In this example I have used the pgcrypto module functions to generate the required encrypted password, from my plaintext password stored on the database:
AuthDBDUserPWQuery "SELECT '{SHA}'||encode(digest(password,'sha1'),'base64') FROM registered_user WHERE user_id = %s"
AuthDBUserRealmQuery "SELECT encode(digest( user_id || ':' || realm || ':' || password , 'md5'), 'hex') FROM registered_user WHERE user_id = %s AND realm = %s"
I did this as I wanted to make sure that all the libraries and configuration were working before starting to store encrypted password information directly into the database tables. I recommend that you do not store plain text passwords.
Now that you have the pgcrypto module installed you can use the example SQL to store your password data securely, without the need to resort to calling perl scripts and htpasswd utility.
The configuration for PostgreSQL based authentication, using mod_dbd/mod_athun_dbd, discssed above is typically sufficient for creating a relatively simple site, where you are operating within a single "authentication realm" and have only one application running on your site. So what happens when you have multiple applications running each of which has its own specific database?
In this case you need to start considering whether you want to have a single sign-on that operates across multiple applications or if you need to start dividing your site up into a set of logically seperate sites. This is when some current limitations of mod_dbd will start to become apparent and it is likely that you will need to resort to Apache Virtual Hosting to provide what you need.
The following diagrams illustrates some possible Application and Database scenarios, where in each case we are hosting these via single Apache Web server:
In the case of "DB Scenario 1" the Apache Web Server needs to authenticate against different credentials database, which means that the mod_dbd managed connection needs to be different depending on which application specific URL is being used. Currently there is work being done with Apache mod_dbd to introduce the concept of named Database Connection Pools, using a new <DBDPool ...>
For the case illustrated in "DB Scenario 2" only a single database connection is used, but you need to consider if you wish to have a single Authentication Realm and hence have shared sign-on (single sign-on) or have multiple realms.
To test the "DB Scenario 1" case I established a "name-based virtual host" and then configured each to point to altenate Document Root directories. To get this to work you will also need to ensure your DNS server is configured with CNAME (Alias) entries for each of the named hosts. The same effect can be achieved by using IP-based virtual hosts. In the IP case, this means that you will need to configure additional IP addresses on the hosting server as well as providing DNS name configuration for these.
Here is what was done and example set of Apache httpd.conf files to configure single host which has two seperate "Authentication Realms" ("Graphica Private" and "Protected Area"), with the credentials for these being maintained in disparate application databases.
1 | Add a new Document Root for second application and authentication realm to be hosted from:
|
In my example I configured an internal test server so it hosted the following domains (with <deadlock> being CNAME for <www> host):
NOTE: This is standard FreeBSD Apache22 install with Document Root at /usr/local/www/apache22/data |
2 | Add <Virtual Host> Directives into httpd.conf:
|
In this example the main servers <www> DocumentRoot has been defined within the main body of httpd.conf and is as per default installation. Each of the two mod_dbd managed database connections are specified within the <VirtualHost> sections. For the <deadlock> virtual host there are Document Root and Directory directives that override those specified for default server and used by <www> virtual host. The first of these makes a connection to <db-name1> and the second to <db-name2>. In both cases they are PostgreSQL databases ("DBDriver pgsql"), but they could also be specified as for different data base providers. In my configuration I have provided the Apache directory specific access information by using the "Include" directives, for each of the two database instance. See (3) "private.conf" which does lookup of from <db-name1> database that contains unencrypted passwords (it is a test database) for Authentication Realm "Graphica Private". See (4) "deadlock.conf" which does lookup of <db-name2> that contains already encryped digest data for Authentication Realm "Protected Area" |
3 | The "private.conf" Apache/mod_authn_dbd access control directive:
|
This test database does not store enrypted passwords so we need to use the pgcrypto module to encrypt the results on the fly. The Authentication Realm is "Graphica Private" and the SQL is specific to this schema. |
4 | The "deadlock.conf" Apache/mod_authn_dbd control directive:
|
This database stores encrypted data, which is stored in the digest_passwd column. The Authentication Realm is "Protected Area" and the SQL is specific to this applications schema. |
Now that you have seen an example of how virtual hosting allows you to control using alternate dbd managed database connection, you should be aware that virtual hosting is also how you establish SSL usage and the combination of Basic Authentication with SSL. The following blog provides "dummies" instructions" on configuring SSL with FreeBSD Apache, but like most things it is always useful to go the the source. I am using StartSSL to provide my signed certifiicates and their installation instructions are simple and useful.
NOTE: It is very important that you ensure that for each of the seperate database instance that you have different Authentication Realms specified in the mod_authn_dbd "AuthName", as otherwise you could end up with the situation where access will be granted to one application as a result of having authenticated against another.
Having spent 3 days on getting the basic authentication mechanisms working I would recommend mod_dbd/mod_authn_dbd, the documentation available is more comprehensive and it was quite straight forward to get both Basic and Digest authentication going.
Make sure you have OpenSSL crypto library installed on same machine as PostgreSQL server, if you are going to use pgcrypto module.
I would not use Basic athentication unless it is in combination with SSL. Given that it is relatively straight forward to establish Digest authentication then use this if you do not want to use SSL.
For the storage of passwords there are a number of options. You can store these as defined by either of the two examples above, or have them encrypted using an internal encryption scheme and then decrypt result and then reencypt them for comparison test.
Be aware that if you are storing passwords in the Apache Basic or Digest compatible formats then you cannot change from Basic to Digest Authentication scheme unless you save seperate versions of the encrypted password stored for each of the two acceptable formats.
In more complex scenarios, where user credential information are held within the Application specific databases, you will need to use Virtual Hosting to control which database to use for a given application.. The Apache Virtual hosting also provides the ability to control whether SSL hosting is required.
All materials are copyright (C) 2011 Graphica Software/Dokmai Pty Ltd
Send any questions or comments regarding this
site to info at graphica.com.au
Last Updated: Monday 11th June, 2011.