.o.
/ \ Server: marzen.de
| | Datum: 26.12.2024
| | Zeit: 16:40:33
/_______\
`o
[Startseite] [Holger] [mod_auth_pgsql_limited]
------------------------------------------------------------------------------
Some suggestions and notes about authorisation with Apache,
mod_auth_pgsql and PostgreSQL.
This version has some hints for storing encrypted passwords and limits
the number of failed attempts. It's an excerpt from a mail I sent to
the "PostgreSQL Users" mailing list.
Holger Marzen
2004-02-24
I use 2 tables and a function that automatically adds a default group to
a newly created user. You see that I use
encode(digest('mypassword', 'md5'), 'hex')
to create an encrypted password that mod_auth_pgsql accepts. The
used function are in contrib/pgcrypto of PostgreSQL source tree.
In newer versions of PostgreSQL you can use the built-in function md5()
as well.
I modified mod_auth_pgsql to write always a record to a log table, even
if the login fails. Then I added a trigger that increases the "failed"
column and that way I can limit the number of attempts. My .htaccess
looks like that:
--snip--------------------------------------------------------------
AuthName "bluebell"
AuthType Basic
deny from all
allow from 10.66.53
allow from 127.0.0.1
satisfy any
require group intern
#
Auth_PG_host localhost
Auth_PG_port 5432
Auth_PG_user www
Auth_PG_pwd secret
Auth_PG_database db1
Auth_PG_encrypted on
Auth_PG_hash_type MD5
Auth_PG_pwd_table apache_users
Auth_PG_uid_field userid
Auth_PG_pwd_field password
Auth_PG_pwd_whereclause " and failed < (select max_failed from apache_parms) "
Auth_PG_grp_table apache_groups
Auth_PG_gid_field groupid
Auth_PG_grp_whereclause " and active = TRUE "
Auth_PG_log_table apache_log
Auth_PG_log_uname_field userid
Auth_PG_log_date_field timestamp
Auth_PG_log_uri_field uri
Auth_PG_log_addrs_field ip
Auth_PG_log_pwd_field password
--snip--------------------------------------------------------------
And the changed part of mod_auth_pgsql.c is only the added line no. 747
and 765. Yes, it could be made faster if someone redesigned the whole
module, so we wouldn't need a trigger and simply increase the error
counter instead. But that would require more changes on the module.
The maximum number of failed attempts is read from the column
max_failed of the table apache_parms;
--snip--------------------------------------------------------------
736 /* if the flag is off however, keep that kind of stuff at
737 * an arms length.
738 */
739 if ((!strlen (real_pw)) || (!strlen (sent_pw)))
740 {
741 snprintf (pg_errstr, MAX_STRING_LEN,
742 "PG: user %s: Empty Password(s) Rejected", c->user);
743 ap_log_reason (pg_errstr, r->uri, r);
744 ap_note_basic_auth_failure (r);
745
746 /* -hm- 2003-07-27 */
747 pg_log_auth_user (r, sec, c->user, sent_pw);
748
749 return AUTH_REQUIRED;
750 };
751
752 if (sec->auth_pg_encrypted)
753 sent_pw = (sec->auth_pg_hash_type == AUTH_PG_HASH_TYPE_MD5) ?
754 auth_pg_md5 (sent_pw) : (char *) crypt (sent_pw, real_pw);
755
756 if ((sec->auth_pg_hash_type == AUTH_PG_HASH_TYPE_MD5 || sec->auth_pg_pwdignorecase != 0)
757 ? strcasecmp (real_pw, sent_pw) : strcmp (real_pw, sent_pw))
758 {
759 snprintf (pg_errstr, MAX_STRING_LEN,
760 "PG user %s: password mismatch", c->user);
761 ap_log_reason (pg_errstr, r->uri, r);
762 ap_note_basic_auth_failure (r);
763
764 /* -hm- 2003-07-27 */
765 pg_log_auth_user (r, sec, c->user, sent_pw);
766
767 return AUTH_REQUIRED;
768 }
--snip--------------------------------------------------------------
create table apache_users (
userid text not null
check (length(trim(userid)) > 0 and
userid ~* '^[a-z0-9_\-]+$'),
password text not null
check (length(trim(password)) >= 6)
default encode(digest('start', 'md5'), 'hex'),
name text default 'Herr/Frau Muster',
failed integer default 0,
seqno serial,
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 'kennwortaenderung'
check (length(trim(groupid)) > 0 and
groupid ~* '^[a-z0-9_\-]+$'),
active boolean default true,
seqno serial,
primary key (userid, groupid)
);
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();
grant all on apache_users to www;
grant all on apache_users_seqno_seq to www;
grant all on apache_groups to www;
grant all on apache_groups_seqno_seq to www;
create table apache_log (
userid text,
password text,
timestamp timestamp,
uri text,
ip inet,
);
grant all on apache_log to www;
grant all on apache_log_seqno_seq to www;
create function apache_users_update_f()
returns trigger
as 'declare
my_userid text;
my_password text;
my_user_pw text;
my_apache_users_rec record;
begin
my_userid := new.userid;
my_password := new.password;
select into my_apache_users_rec
password, failed
from apache_users
where userid = my_userid;
if my_password = my_apache_users_rec.password
then
if my_apache_users_rec.failed > 0
then
update apache_users
set failed = 0
where userid = my_userid;
return new; -- log successful resets
else
return NULL; -- dont log other successful logins
end if;
else
update apache_users
set failed = failed + 1
where userid = my_userid;
return new; -- log failed attempts
end if;
return new; -- just to be sure
end;'
language 'plpgsql';
create trigger apache_users_update_tr
after insert on apache_log
for each row
execute procedure apache_users_update_f();
drop table apache_parms;
create table apache_parms (
max_failed integer
);
insert into apache_parms
values (10);
grant all on apache_parms to www;
--snip--------------------------------------------------------------
------------------------------------------------------------------------------
[Startseite] [Holger] [mod_auth_pgsql_limited]