.o. / \ Server: marzen.de | | Datum: 12.10.2024 | | Zeit: 05:25:16 /_______\ `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 Marzen2004-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]