Re: mod_auth_pgsql & encryption

On Mon, 22 Sep 2003, Molly Gibson wrote:

> Hi all,
> I have recently installed Apache/1.3.28 +
> mod_auth_pgsql-0.9.12
> (http://www.giuseppetanzilli.it/mod_auth_pgsql/)
>
> The only way I have been able to get it to
> successfully authenticate against my postgres (7.3.4)
> database is to turn Auth_PG_encrypted off & have
> encryption turned off in postgresql.conf. I am really
> uncomfortable with the idea of having unencrypted user
> passwords laying about, but if I try to use an
> encrypted password from the database, I get 'password
> mismatch'.
>
> I would really like to use the existing tables
> (pg_shadow, pg_group) instead of maintaining a
> separate set of tables for user logins & group
> assignments, assuming I get the encryption part
> figured out.
> Anybody have any ideas how I could go about resolving
> this or troubleshooting it further? It seems to me
> there is a difference between postgres's encryption
> and mod_auth_pgsql's encryption. Google turned up
> only a few people who'd had the same problem (no
> answers to it) and people who said they'd been using
> mod_auth_pgsql for a while with no problems. ???

I can't help you with your problem if you insist in using PostgreSQL's
system tables.

I never thought of that because I always wrote a PHP-page where a
administrator could create/delete/lock users. And I don't like the idea
that such a program needs admin privileges on the PostgreSQL side.

I always 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.

And 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. 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.

--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 };

--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,
seqno serial
);

grant all on apache_log to www;
grant all on apache_log_seqno_seq to www;

create function apache_users_update_f()
returns opaque
as 'begin
update apache_users
set failed = 0
where userid = new.userid and
password = new.password;
update apache_users
set failed = failed + 1
where userid = new.userid and
password <> new.password;
return new;
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--------------------------------------------------------------

--
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0xB5A1AFE1