Apache2 Module mod_auth_pq_sql

PostgreSQL Authentication

This module allows user authentication (and can log authentication requests) against information stored in a PostgreSQL database.
This page documents version 1.0.0 of mod_auth_pq_sql.
It is tested with Apache 2.2.14 and PostreSQL 8.4.8, but should work with any current Apache and PostgreSQL version.

This module is based on the source of mod_auth_pgsql v2.0.3 by Giuseppe Tanzilli.
See http://www.giuseppetanzilli.it/mod_auth_pgsql2/ for more information.

Module Directives | Download | Compilation & Installation Notes | Example | Technical Notes | Changelog

Directives

Auth_PQ_dbhost

Syntax: Auth_PQ_dbhost hostname
Context: Directory, .htaccess
Override: AuthConfig
Description: Specifies the host on which the postmaster is running. It is optional, if not specified a unix socket connection will be used.
The effective uid of the server should be allowed access, otherwise a trusted user or user with password must be specified.

Auth_PQ_dbport

Syntax: Auth_PQ_dbport port_number
Context: Directory, .htaccess
Override: AuthConfig
Description: Specifies the TCP/IP port number at which the postmaster is listening for connections.

Auth_PQ_dbname

Syntax: Auth_PQ_dbname database_name
Context: Directory, .htaccess
Override: AuthConfig
Description: Specifies the name of the database to connect to.

Auth_PQ_dboptions

Syntax: Auth_PQ_dboptions option_string
Context: Directory, .htaccess
Override: AuthConfig
Description: Specifies an option string to be passed to the PostgreSQL backend process. Refer to the PostgreSQL user manual for a description of the available options.

Auth_PQ_dbuser

Syntax: Auth_PQ_dbuser username
Context: Directory, .htaccess
Override: AuthConfig
Description: Specifies the database username who access the PostgreSQL, should have privileges on all tables specified in a SELECT statements or execution privilege of any function used.
Needed if the user who make the query is differrent from the user runnig apache, or if the postmater is on a different server and you must authenticate with password.

Auth_PQ_dbpass

Syntax: Auth_PQ_dbpass password
Context: Directory, .htaccess
Override: AuthConfig
Description: Specifies the password for the user who access the PostgreSQL.
Needed if the user specified with Auth_PQ_dbuser is not trusted.

Auth_PQ_authoritative

Syntax: Auth_PQ_authoritative on | off
Context: Directory, .htaccess
Override: AuthConfig
Description: This option is on by default. Turning it off will cause low level errors such a user not being found or a simple configuration error to fall through to other authentication directives which may be defined for this area.
For example, if a parent directory has another authorization scheme and a user name is not found for the PostgreSQL scheme, the parent directory scheme will be given the chance to try and authenticate the user. Exercise caution when turning this option off. It can be a security risk. Can be used to use two authentication schemes for the same dir.

Auth_PQ_login_check_sql

Syntax: Auth_PQ_login_check_sql sql_statement
Context: Directory, .htaccess
Override: AuthConfig
Description: Specifies a SQL statement for checking a username/password combination. It has to return a boolean value.
The Statement has to contain two variable placeholders: $1 for the actual username and $2 for the password.

Auth_PQ_group_check_sql

Syntax: Auth_PQ_group_check_sql sql_statement
Context: Directory, .htaccess
Override: AuthConfig
Description: Specifies a SQL statement for checking a username/group combination. It has to return a boolean value.
The Statement has to contain two variable placeholders: $1 for the actual username and $2 for the groupname

Auth_PQ_log_check_sql

Syntax: Auth_PQ_log_check_sql sql_statement
Context: Directory, .htaccess
Override: AuthConfig
Description: Specifies a SQL statement for saving log information.
The Statement has to contain four variable placeholders: $1 for a timestamp (string), $2 for the username, $3 for the ip address, $4 for the request string.

Examples

Here are some examples from apache configuration <Directory> directive you might use to enable PostgreSQL authentication:

PostgreSQL trusted user, Apache user check only

  AuthName "My PostgreSQL Authenticator"
  AuthType basic
  Auth_PQ_dbhost localhost
  Auth_PQ_dbport 5432
  Auth_PQ_dbuser apache
  Auth_PQ_dbname auth
  Auth_PQ_login_check_sql "SELECT checkpass($1, $2)"
  Require valid-user

PostgreSQL user with password authentication, Apache user/group check

  AuthName "My PostgreSQL Authenticator"
  AuthType basic
  Auth_PQ_dbhost db-server.local.net
  Auth_PQ_dbport 13000
  Auth_PQ_dbuser www
  Auth_PQ_dbpass verySecret
  Auth_PQ_dbname wwwdb
  Auth_PQ_login_check_sql "SELECT (SELECT pwhash FROM www_users WHERE uname = $1) = sha1($2)"
  Auth_PQ_group_check_sql "SELECT checkgroup($1, $2)"
  Require group staff

PostgreSQL trusted user, Apache user/group check with logging

  AuthName "My PostgreSQL Authenticator"
  AuthType basic
  AuthType basic
  Auth_PQ_dbhost localhost
  Auth_PQ_dbport 5432
  Auth_PQ_dbuser apache
  Auth_PQ_dbname auth
  Auth_PQ_login_check_sql "SELECT checkpass($1, $2)"
  Auth_PQ_group_check_sql "SELECT checkgroup($1, $2)"
  Auth_PQ_log_sql "INSERT INTO www_log (ts, uname, ip, req) VALUES ($1, $2, $3, $4)"
  Require group admin

Technical Notes

Download

Compilation & Installation Notes

DSO (dynamic shared object) installation with apxs (APache eXtenSion tool): 
  - untar mod_auth_pq_sql  
    cd ~/src
    tar xfz mod_auth_pq_sql-1.0.0.tar.gz
    cd mod_auth_pq_sql-1.0.0
  - make & install as DSO
    make
    sudo make install
Static installation should be possible, but it is untested.
See documentation of original source: http://www.giuseppetanzilli.it/mod_auth_pgsql2/.

Changelog