/   \         Server: marzen.de
    |     |        Datum:  30.04.2017
    |     |        Zeit:   16:49:01
[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 

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


AuthName "bluebell"
AuthType Basic
deny from all
allow from 10.66.53
allow from
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


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;


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);
746     /* -hm- 2003-07-27 */
747     pg_log_auth_user (r, sec, c->user, sent_pw);
749     return AUTH_REQUIRED;
750     };
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);
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);
764     /* -hm- 2003-07-27 */
765     pg_log_auth_user (r, sec, c->user, sent_pw);
767     return AUTH_REQUIRED;
768     }


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

    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
      if my_apache_users_rec.failed > 0
        update apache_users
          set failed = 0
          where userid = my_userid;
        return new;  -- log successful resets
        return NULL; -- dont log other successful logins
      end if;
      update apache_users 
        set failed = failed + 1
        where userid = my_userid;
      return new; -- log failed attempts
    end if;
    return new; -- just to be sure
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;


[Startseite] [Holger] [mod_auth_pgsql_limited]