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