Installing and Configuring PostgreSQL Based Authentication Module into Apache

Status: July 2020 - This is revival of an old post from 2011 and am now revisiting due to new need and will validate currency at the same time


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 consists of two FreeBSD (11.4) machines, one hosting Apache (V.24) Web Server and the other hosting PostgreSQL (9.6) 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.


As noted below 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 important 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. Build Apache with dbd

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 Apache24 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 Apache24 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: "DBDriver pgsql"within httpd.conf file (see below). To check that you have the right available APR library, look for /usr/local/lib/apr-util-1/apr_dbd_pgql.so .

2. Make sure you have same crypto library on client and server machines

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 PostreSQL pgcrypto module

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.X 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:

# cd /usr/ports/databases/<postgresqlver-server>/work/<postgresql-ver>/contrib/pgcrypto
# gmake
# gmake install /* Installs: /usr/local/lib/postgresql/pgcrypto.so & /usr/local/share/postgresql/contrib/pgcrypto.sql */
# su pgsql
> psql <my-user-database> < /usr/local/share/postgresql/contrib/pgcrypto.sq

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:

  • Apache httpd.conf (modules)
#
# Load the modules
#
LoadModule dbd_module libexec/apache22/mod_dbd.so
LoadModule authn_dbd_module libexec/apache22/mod_authn_dbd.so
# LoadModule auth_pgsql_module libexec/apache22/mod_auth_pgsql.so
  • Apache httpd.conf (database)
#
# initialise: mod_dbd
#
<IfModule dbd_module>
DBDriver pgsql
DBDParams "host=<db-host> port=5432 dbname=<db-name> user=<connect-user> password=<connect-pwd>"

#Connection Pool Management
DBDMin 1
DBDKeep 2
DBDMax 10
DBDExptime 60
</IfModule>
  • Basic Authentication
AuthName "Graphica Software Authenticator"
AuthType Basic
AuthBasicProvider dbd
AuthDBDUserPWQuery "SELECT '{SHA}'||encode(digest(password,'sha1'),'base64') FROM registered_user WHERE user_id = %s"

<LIMIT GET POST>
require valid-user
</LIMIT>
</Directory>
  • Digest Authentication
AuthName "Graphica Software Authenticator"
AuthType Digest
AuthDigestNcCheck On /* Configures Nonce returned from Browser against that provided. Required Shared Memory support */
AuthDigestDomain http://<host-url>/private
AuthDigestProvider dbd
AuthDBUserRealmQuery "SELECT encode(digest( user_id || ':' || realm || ':' || password , 'md5'), 'hex') FROM registered_user WHERE user_id = %s AND realm = %s"

<LIMIT GET POST>
require valid-user
</LIMIT>
</Directory>

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"

Authentication Realms, Virtual Hosts & Connection Pools

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:

Alternate DB Credentials Scenarios

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 ...> directive. At present this is available in Apache 2.3 and forward versions, so another way of managing the authentication has to be used.

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:
# cd /usr/local/www/apache22
# mkdir data2
# cd data2
# ln -s  deadlock
# vi index.html

In my example I configured an internal test server so it hosted the following domains (with <deadlock> being CNAME for <www> host):

  • <www>.<domain> (data)
  • <deadlock>.<domain> (data2)

NOTE: This is standard FreeBSD Apache22 install with Document Root at /usr/local/www/apache22/data

2. Add <Virtual Host> Directives into httpd.conf:

NameVirtualHost *:80
DocumentRoot "/usr/local/www/apache22/data"
...
...
...
<VirtualHost *:80>
ServerName <www>.<domain>
<IfModule dbd_module>
  DBDriver pgsql
  DBDParams "host=<pg-host> port=5432 dbname=<db-name1> user=<user> password=<passwd>"
  DBDMin 1
  DBDKeep 2
  DBDMax 10
  DBDExptime 60
</IfModule>
Include etc/apache22/secure/private.conf
</VirtualHost>

<VirtualHost *:80>
ServerName <deadlock>.<domain>
DocumentRoot /usr/local/www/apache22/data2
<Directory "/usr/local/www/apache22/data2">
  Options Indexes FollowSymLinks
  AllowOverride None
  Order allow,deny
  Allow from all
</Directory>

<IfModule dbd_module>
  DBDriver pgsql
  DBDParams "host=<pg-host> port=5432 dbname=<db-name2> user=<user> password=<passwd>"
  DBDMin 1
  DBDKeep 2
  DBDMax 10
  DBDExptime 60
</IfModule>
Include etc/apache22/secure2/deadlock.conf
</VirtualHost>

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:

<Directory "/usr/local/www/apache22/data/private">

AuthName "Graphica Private"
AuthType Digest
AuthDigestNcCheck On
AuthDigestDomain http://<www>.<domain>/private
AuthDigestProvider dbd
AuthDBDUserRealmQuery "SELECT encode(digest( user_id || ':' || realm || ':' || password , 'md5'), 'hex') FROM registered_user WHERE user_id = %s AND realm = %s"

<LIMIT GET POST>
require valid-user
</LIMIT>
</Directory>

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:

<Directory /usr/local/www/apache22/data2/deadlock>
AuthName "Protected Area"
AuthType Digest
AuthDigestNcCheck On
AuthDigestDomain http://<deadlock>.<domain>/deadlock/
AuthDigestProvider dbd
AuthDBDUserRealmQuery "SELECT digest_passwd FROM dlk_users WHERE username = %s AND realm = %s"
require valid-user
</Directory>

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. There is not reason noto to use SSL now that these are automatically available via "LetsEncrypt".

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.


Summary

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.

Use SSL even with Digest authentication, as digest is vulnerable to man-in-middle attack.

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.


Historical Testing

In writing this blog some years ago, I found the following three approaches to doing authentication using PostgreSQL data as credentials source.

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 and at the time was my recommended approach.

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 .


AuthPG Report

The AuthPG documented installation process was quite straight forward:

  1. Download source,
  2. Unzip into working directory
  3. Run ./configure and then
  4. Do make & make install.

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')
...
...
...

Apache2 Module mod_auth_pgsql Report

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):

  1. Download Source
  2. Unzip ito working directory
  3. Run build/install command as root:
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):
#cd /usr/local/www
# mkdir private
# cd private
# edit index.html /* create test data file */
# chown www:www index.html
# cd /usr/local/www/apache22/data
# ln -s /usr/local/www/private private

2. Create a user id/password and groups table on the PostgresSQL database and populate with some sample data:

CREATE TABLE registered_user (
id serial PRIMARY KEY,
user_id varchar(128),
password varchar(128),
realm varchar(128), -- added for digest support
key varchar(128),
active boolean,
created timestamp,
disabled timestamp,
party_ref integer REFERENCES party_resource(id)
);

CREATE TABLE user_group (
id serial PRIMARY KEY,
group_name varchar(128),
os_mapping integer
);

CREATE TABLE group_member (
user_ref integer REFERENCES registered_user(id),
group_ref integer REFERENCES user_group(id)
);

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:

<Directory "/usr/local/www/apache22/data/private">
AuthName "My PostgresSQL Authenticator" /* Note that this is what will appear on challenge dialog from browser */
AuthType Basic
AuthBasicAuthoritative Off
Auth_PG_host <my-PostgresSQL-host>
Auth_PG_port 5432
Auth_PG_user <PostgresSQL User to Connect to DB>
Auth_PG_pwd <PostgresSQL User Password>
Auth_PG_database <The data base with tables above>
Auth_PG_pwd_table registered_user /* As per (2) above */
Auth_PG_uid_field user_id /* As per (2) above */
Auth_PG_pwd_field password /* As per (2) above */
Auth_PG_encrypted off /* Keep it simple to start... */
# Auth_PG_hash_type CRYPT
# Auth_PG_pwd_whereclause " and active = TRUE"
<LIMIT GET POST>
require valid-user
</LIMIT>
</Directory>

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.

  • On FreeBSD the mod_auth_pgsql is available within the /usr/ports/www/mod_auth_pgsql2 (for Apache2). In general I would recommend using the version from ports as it is likely be be patched and tested for FreeBSD.
  • On Linux (Debian/Ubuntu) it is possible to get the mod_auth_pgsql module using: apt-get install libapache2-mod-auth-pgsql (assuming you have Apache2 already installed...)

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 relevant if you wish to use Digest based authentication.


Adding HTTP Digest

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.

  • Changes to "secure-dir.conf":
<Directory "/usr/local/www/apache22/data/private">

AuthName "Graphica Software Authenticator"
AuthType Digest
AuthDigestDomain http://<domain>/private
AuthBasicAuthoritative Off
AuthUserFile /dev/null /* to stop getting pcfg_openfile error */

Auth_PG_host <pgsql-host>
Auth_PG_port 5432
Auth_PG_user <db-connect-user>
Auth_PG_pwd <db-connect-password>
Auth_PG_database <database>
Auth_PG_pwd_table registered_user
Auth_PG_uid_field user_id
Auth_PG_pwd_field password
Auth_PG_encrypted off /* for simplicity to get things going */
# Auth_PG_hash_type CRYPT
# Auth_PG_pwd_whereclause " and active = TRUE"

<LIMIT GET POST>
require valid-user
</LIMIT>
</Directory>

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.


References & Links:

PostgreSQL - the open s0urce RDBMS application

Apache - the traditional web server for all and sundry