.o. / \ Server: marzen.de | | Datum: 08.12.2024 | | Zeit: 05:17:52 /_______\ `o [Startseite] [Holger] [mod_auth_pgsql] ------------------------------------------------------------------------------ Some suggestions and notes about authorisation with Apache, mod_auth_pgsql and PostgreSQL. Holger Marzen2002-03-05 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. Cool. 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 passwords. -- 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; end;' 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 127.0.0.1 255.255.255.255 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]