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.
- Authentication
You have to configure a SQL statement with two variable placeholders.
The username variable will be bound to $1, the password to $2.
The statement has to return a boolean value.
Optionally it is possible to configure a SQL statement for checking group
membership ($1: username, $2: group).
No string replacement is done, all variables are bound to the placeholders
when executing postgres functions. This way no handling of special
characters in a string is needed.
- Access Logging
Every authentication access can be logged in the same database where
authentication is done.
It is possible to log timestamp ($1), username ($2),
ip address ($3), request ($4).
This page documents version 1.1.0 of mod_auth_pq_sql.
It is tested with Apache 2.4.7 and PostreSQL 9.3.4, but should work with
any current Apache 2.4.x and any 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
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.
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.
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.
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.
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.
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.
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
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.
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
AuthBasicProvider pq_sql
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
AuthBasicProvider pq_sql
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
AuthBasicProvider pq_sql
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
- This module depends on the PostgreSQL C library
libpq.
- If the SQL statements for login or group check does not return anything
or anything other than a boolean true ('t'), it is considered false and no
access is granted.
- If the SQL statements does not contain the above listed count of
placeholders, an error will be returned from the database.
DSO (dynamic shared object) installation with apxs (APache eXtenSion tool):
- untar mod_auth_pq_sql
cd ~/src
tar xfz mod_auth_pq_sql-1.1.0.tar.gz
cd mod_auth_pq_sql-1.1.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
- Version 1.1.0 (2014-11-11)
Modifications for Apache 2.4.x
Switched to authn/authz
Auth_PQ_authoritative option removed
- Version 1.0.0 (2011-08-21)
First release (based on mod_auth_pgsql.c v2.0.3)
mod_auth_pgsql.c was written by Giuseppe Tanzilli
(info@giuseppetanzilli.it).