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'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&amp;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