Обсуждение: mod_auth_pgsql & encryption

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 have tried:
- setting Auth_PG_pwd_table to pg_shadow,
Auth_PG_encrypted to "on";  results in "password
mismatch" error
- setting Auth_PG_pwd_table to user_auth (table I
created--docs were not clear on wether you could use
an existing table such as pg_shadow),
Auth_PG_encrypted to "on", user passwords c&pd from
pg_shadow; results in "password mismatch"
- setting Auth_PG_pwd_table to user_auth,
Auth_PG_encrypted to "on", user passwords c&pd from a
separate md5 hash program; results in "password
mismatch"
- setting Auth_PG_pwd_table to user_auth,
Auth_PG_encrypted to "off", user passwords set in
plain text; works
- setting Auth_PG_nopasswd to "on", give user a blank
password; works
- (for verification) setting Auth_PG_pwd_table back to
pg_shadow, turn encryption off in postgresql.conf, set
user password to plain text, Auth_PG_encrypted to
"off"; works

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

TIA
mol



__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

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'm personally using mod_auth_pgsql against a user table with encrypted
passwords.  To properly encrypt them I am using the contrib pgcrypto
module and something like

UPDATE myusertable
SET passwd = crypt('password', gen_salt('md5'))
WHERE userid = 1;

I don't believe you can use pg_shadow to authenticate against, but some
things to look at are:

- verify that the passwords are encrypted in pg_shadow.
- try changing the value of Auth_PG_hash_type to md5

Kris Jurka

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

--- Holger Marzen <holger@marzen.de> wrote:
> And I
> don't like the idea
> that such a program needs admin privileges on the
> PostgreSQL side.

Good point.  I am trying to be lazy. ;)

> I always use 2 tables and a function, that
> automatically adds a default
> group to a newly created user.
...
> And I modified mod_auth_pgsql to write always a
> record to a log table,
> even if the login fails.
I was wishing for that.

 My
> .htaccess looks like that:

...snip extensive examples...

WOW!  Thank you thank you thank you!  I was about
ready to give up on this.  I will go back and try
again with this.

Thanks again,
mol

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

> I'm personally using mod_auth_pgsql against a user table with
> encrypted passwords.  To properly encrypt them I am using the
> contrib pgcrypto module and something like

Hello,

Can you tell me what version of mod_auth_pgsql do you use ? And the
Apache version ?

I'm currently having problems with mod_auth_pgsql 2.0.1 that causes a
PostgreSQL denial of service (max connection is reached because
mod_auth_pgsql don't close the backend connection).

Thanks in advance.

---------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net

Quoting Bruno BAGUETTE <pgsql-ml@baguette.net>:

> > I'm personally using mod_auth_pgsql against a user table with
> > encrypted passwords.  To properly encrypt them I am using the
> > contrib pgcrypto module and something like
>
> Hello,
>
> Can you tell me what version of mod_auth_pgsql do you use ? And the
> Apache version ?
>
> I'm currently having problems with mod_auth_pgsql 2.0.1 that causes a
> PostgreSQL denial of service (max connection is reached because
> mod_auth_pgsql don't close the backend connection).
>
> Thanks in advance.
>
> ---------------------------------------
> Bruno BAGUETTE - pgsql-ml@baguette.net
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

This is slightly off the topic but in regards to using encrypted passwords.  How
does one "recover" the password in the database if at all?  I think I might be
under the false pretense that the encryption is similar to a modern *nix
password file.  Thanks.

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com