/   \         Server: marzen.de
    |     |        Datum:  14.07.2024
    |     |        Zeit:   01:16:37
[Startseite] [Holger] [mod_auth_pgsql]

Some suggestions and notes about authorisation with Apache,
mod_auth_pgsql and PostgreSQL.

Holger Marzen 

People often want to limit access to data on a web-server and there are
many ways to do this.  Apache offers the .htaccess files that refer to
user- and group-files. Unfortunately it is not easy to manipulate these
files through web forms, so as an alternative to that I've found it is
much easier to maintain permission tables in a database.

Some people set up user- and group- tables and code CGIs or PHP pages
(or whatever) to do the permission lookup then grant or deny access to
the desired web pages by including (or setting links) or not including
them, etc.  This protection is not bullet-proof since due to
limitations of the web hoster access is often restricted to files within
the DOCUMENT_ROOT tree.  If people guess the right URL they can happily
access every page.

Fortunately there are some modules for Apache that enable SQL-queries to
a database that are defined in .htaccess files.  You can find a list of
them at http://httpd.apache.org/docs/mod/index-bytype.html.  Search for
"auth" in that page.  These are part of the Apache distribution.  For
modules that are not part of the Apache distribution, please see
http://modules.apache.org/search and search for "auth".

I was happy to find mod_auth_pgsql, written by Giuseppe Tanzilli and
went to  http://www.giuseppetanzilli.it/mod_auth_pgsql/.  There I
downloaded the module, read its installation instructions and browsed
the documentation with samples.


Now was the time to setup my PostgreSQL tables. I think many will
agree or be happy with the following layout:

- A UserID is unique
- A user can belong to several groups
- There should be no group without corresponding UserID
- If a user is changed or deleted then the group table should be
  maintained automagically.
- If a user is created then a default group entry "all" should be
  created automagically.

And so I created the user- and group tables.  I chose not to encrypt the

-- snip -------------------------------------------------------

create table apache_users (
  userid varchar(100) not null
         check (length(trim(userid)) > 0 and
                userid ~* '^[a-z0-9_\-]+$'),
  password varchar(100) default 'start'
  primary key (userid)

create table apache_groups (
  userid  varchar(100) not null 
          references apache_users (userid)
          on update cascade
          on delete cascade,
  groupid varchar(100) not null default 'all'
          check (length(trim(groupid)) > 0 and
                 groupid ~* '^[a-z0-9_\-]+$'),
  read    boolean default true,
  write   boolean default false
  primary key (userid, groupid)

-- Pgsql has to be installed: createlang dbname plpgsql
create function apache_groups_insert_f()
returns opaque
as 'begin
    insert into apache_groups (userid)
           values (new.userid);
    return new;
language 'plpgsql';

create trigger apache_groups_insert_tr
after insert on apache_users
for each row
execute procedure apache_groups_insert_f();

-- You can set up 2 different PostgreSQL users for security reasons.
-- The one used in .htaccess only needs to have read access.
-- Don't forget to create the user "www";
grant all on apache_users to www;
grant all on apache_groups to www;

-- snip -------------------------------------------------------

The columns "read" and "write" are not really necessary, but "write"
was created for future use and "read" was made to quickly enable/disable
access without deleting groups or changing passwords.

The postmaster must be started with -i, and the web server needs access
in pg_hba.conf.  Usually Apache and PostgreSQL run on the same machine
that can be addressed with localhost.  I use

host         all     crypt

in pg_hba.conf.  Now it's time to create a .htaccess file in a directory
that has to be protected.  Be sure your Apache's httpd.conf allows using
.htaccess, e.g. by "AllowOverride all" in your directories.

AuthName "User-DB"
        AuthType basic
        Auth_PG_host localhost
        Auth_PG_port 5432
        Auth_PG_user www
        Auth_PG_pwd wwwpasswd
        Auth_PG_database db1
        Auth_PG_encrypted off
        Auth_PG_pwd_table apache_users
        Auth_PG_grp_table apache_groups
        Auth_PG_uid_field userid
        Auth_PG_pwd_field password
        Auth_PG_gid_field groupid
        Auth_PG_grp_whereclause " and read = TRUE "
                require group all

Have fun!

[Startseite] [Holger] [mod_auth_pgsql]