Re: mod_auth_pgsql & encryption - Mailing list pgsql-general

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