By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,776 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,776 IT Pros & Developers. It's quick & easy.

mod_auth_pgsql & encryption

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a


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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #2

P: n/a
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/pk...rch=0xB5A1AFE1

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #3

P: n/a
--- Holger Marzen <ho****@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.