# Auth user with postgresql **URL:** \ **Category:** NGINX\ **Created:** [February 17, 2012, 1:40pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947 "2012-02-17T13:40:10Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![dubstep](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/cdc98d/32.png) [@dubstep](https://www.ruby-forum.com/u/dubstep)\ **Post date:** [February 17, 2012, 1:40pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/1 "2012-02-17T13:40:10Z")
Hi list I am a nginx newbie. Nginx does not ask user and password with the following configuration: … upstream database { postgres\_server 127.0.0.1 dbname=dbnginx user=nginx password=secret; } ``` server { listen 80; server_name localhost; index index.htm index.html; location =/t1 { internal; postgres_escape $user $remote_user; postgres_escape $pass $remote_passwd; postgres_pass database; postgres_query "SELECT user FROM usertable WHERE ``` user=$user AND pwd=$pass"; postgres\_rewrite no\_rows 403; postgres\_output none; } ``` location /test //don't request window for user and ``` password { auth\_basic “folder test1”; auth\_request /t1; } ``` location /test2 //o.k. request window for user and password { auth_basic "folder Test2"; auth_basic_user_file /web/test2/.passwd; } ``` * * * I did several searches on google but found nothing. Where is the mistake? Thanks for the help ``` Giuseppe ``` P.S: the database connection is ok and password is stored in MD5 follow my compilation config ./configure –prefix=/usr/local/nginx –sbin-path=/usr/local/nginx/sbin/nginx –conf-path=/etc/nginx/nginx.conf –error-log-path=/var/log/nginx/error.log –pid-path=/var/run/nginx/nginx.pid –lock-path=/var/lock/nginx.lock –http-uwsgi-temp-path=/var/tmp/nginx –http-scgi-temp-path=/var/tmp/nginx –user=nobody –group=nobody –with-ipv6 –with-http\_dav\_module –with-http\_ssl\_module –with-http\_flv\_module –with-http\_gzip\_static\_module –http-log-path=/var/log/nginx/access.log –http-client-body-temp-path=/var/tmp/nginx/client/ –http-proxy-temp-path=/var/tmp/nginx/proxy/ –http-fastcgi-temp-path=/var/tmp/nginx/fcgi/ –add-module=/home/mercurio/nginx/ngx\_http\_auth\_request\_module-a29d74804ff1 –add-module=/home/mercurio/nginx/FRiCKLE-ngx\_coolkit-cb99a0f –add-module=/home/mercurio/nginx/agentzh-nginx-eval-module-09d7728 –add-module=/home/mercurio/nginx/ngx\_postgres-0.9 ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 17, 2012, 1:50pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/2 "2012-02-17T13:50:07Z")
Hi, > Nginx does not ask user and password with the following configuration: That’s because you’re returning 403 (Forbidden) instead of 401 (Unauthorized). I should update README file, because people get confused by this 😉 > postgres\_rewrite no\_rows 403; -postgres\_rewrite no\_rows 403; +postgres\_rewrite no\_rows 401; +more\_set\_headers -s 401 ‘WWW-Authenticate: Basic realm=“Restricted”’; You’ll also need ngx\_headers\_more module for this to work: > **[GitHub - openresty/headers-more-nginx-module: Set, add, and clear arbitrary output headers in...](https://github.com/openresty/headers-more-nginx-module)** > > Set, add, and clear arbitrary output headers in NGINX http servers Best regards, Piotr S. \< [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) \> ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 17, 2012, 1:55pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/3 "2012-02-17T13:55:08Z")
On Fri, Feb 17, 2012 at 01:39:40PM +0100, Giuseppe T. wrote: Hi there, > I did several searches on google but found nothing. > Where is the mistake? > Thanks for the help This looks very like. [http://forum.nginx.org/read.php?2,220692](http://forum.nginx.org/read.php?2,220692) That may be useful. All the best, ## f Francis D. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 17, 2012, 8:14pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/4 "2012-02-17T20:14:01Z")
Hello Piotr S. and Francis D., thanks for the quick reply, The login and password is okay, but in my database passwords are stored in MD5, while the password is passed in the clear as I can solve the problem? Thanks again for your kindness and patience 2012/2/17 Francis D. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 17, 2012, 8:28pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/5 "2012-02-17T20:28:17Z")
Hi, > The login and password is okay, but in my database passwords are > stored in MD5, while the password is passed in the clear as I can > solve the problem? You can use “set\_md5” directive from ngx\_set\_misc module [1]: ``` set_md5 $remote_passwd; /* must be before postgres escape */ postgres_escape $pass $remote_passwd; ``` [1] [GitHub - openresty/set-misc-nginx-module: Various set\_xxx directives added to nginx's rewrite module (md5/sha1, sql/json quoting, and many more) · GitHub](https://github.com/agentzh/set-misc-nginx-module) Best regards, Piotr S. \< [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) \> ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 17, 2012, 9:25pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/6 "2012-02-17T21:25:21Z")
Hi, Piotr S. I followed your instructions but when I start nginx the following error: nginx: [emerg] the duplicate “remote\_passwd” variable in /etc/nginx/nginx.conf:60 … location =/t1 { internal; more\_set\_headers -s 401 ‘WWW-Authenticate: Basic realm=“Cartella test1”’; postgres\_escape $user $remote\_user; ``` set_md5 $remote_passwd; postgres_escape $pass $remote_passwd; postgres_pass database; postgres_query "SELECT user FROM usertable WHERE ``` user=$user AND pwd=$pass"; postgres\_rewrite no\_rows 401; postgres\_output none; } … How can I solve the problem? Many thanks again. Giuseppe p.s.:it is normal to immediately start nginx requires authentication to the database? 2012/2/17 Piotr S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 18, 2012, 12:29am UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/7 "2012-02-18T00:29:52Z")
Hi Hi, I have no errors, nginx starts correctly, but the password are calculated differently: password : pippo created with htpasswd h7n37SzKs.aO6 (test with auth\_basic\_user\_file is ok) created with set\_md5 0c88028bf3aa6a6a143ed846f2be1ea4 error (STATEMENT: SELECT user FROM usertable WHERE user=‘donalduck’ AND pwd=‘0c88028bf3aa6a6a143ed846f2be1ea4’) Thanks again Giuseppe 2012/2/17 Piotr S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 17, 2012, 10:05pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/8 "2012-02-17T22:05:36Z")
Hi, > nginx: [emerg] the duplicate “remote\_passwd” variable in > /etc/nginx/nginx.conf:60 > (…) Right, “$remote\_passwd” is read-only, try this instead: ``` set_md5 $md5_passwd $remote_passwd; postgres_escape $pass $md5_passwd; ``` Best regards, Piotr S. \< [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) \> ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 18, 2012, 1:06am UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/9 "2012-02-18T01:06:19Z")
Hi, > I have no errors, nginx starts correctly, but the password are > calculated differently: > > password : pippo > > created with htpasswd h7n37SzKs.aO6 (test with > auth\_basic\_user\_file is ok) > created with set\_md5 0c88028bf3aa6a6a143ed846f2be1ea4 error Uhm, and what value do you have in the database? ``` MD5("pippo") = 0c88028bf3aa6a6a143ed846f2be1ea4 ``` so it would seem that you don’t store MD5 hashes after all? Best regards, Piotr S. \< [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) \> ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 18, 2012, 3:19pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/10 "2012-02-18T15:19:21Z")
Hi, > In my database passwords are all stored in MD5 has been created using PHP. > The value in the database for user pippo is h7n37SzKs.aO6 and there > are no problems with APACHE But “h7n37SzKs.aO6” is not 128-bit value, so whatever it is, it cannot be MD5. Best regards, Piotr S. \< [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) \> ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 18, 2012, 11:49am UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/11 "2012-02-18T11:49:17Z")
Hi, In my database passwords are all stored in MD5 has been created using PHP. The value in the database for user pippo is h7n37SzKs.aO6 and there are no problems with APACHE I used the same password with python and there are no problems. I would like to use nginx but if I do not solve the problem of the passwords can not leave APACHE and PHP. Many Many thanks for your patience Giuseppe 2012/2/18 Piotr S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 18, 2012, 7:46pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/12 "2012-02-18T19:46:11Z")
Hi, First, Sorry about the time you’ve lost for my problem. Hi reason, the password is not in MD5, but rather in DES (PHP → crypt($verpas, CRYPT\_STD\_DES) What should I use instead of set\_md5 ? DES on this page [Set Misc | NGINX](http://wiki.nginx.org/HttpSetMiscModule#Installation) is never mentioned Thanks again Giuseppe 2012/2/18 Piotr S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 18, 2012, 8:12pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/13 "2012-02-18T20:12:14Z")
Hi, > Hi reason, the password is not in MD5, but rather in DES (PHP → > crypt($verpas, CRYPT\_STD\_DES) > What should I use instead of set\_md5 ? > DES on this page [GitHub - nginxinc/nginx-wiki: ARCHIVED -- Source for the now archived NGINX Wiki section of https://www.nginx.com · GitHub](http://wiki.nginx.org/HttpSetMiscModule#Installation) > is never mentioned I’m not aware of any module that would offer crypt() hashing for variables in nginx.conf. On the bright side, PostgreSQL’s crypt() [1] should help you. Could you please try: ``` postgres_query "SELECT user FROM usertable "WHERE user=$user AND pwd=crypt($pass, pwd)"; ``` [1] [PostgreSQL: Documentation: 9.1: pgcrypto](http://www.postgresql.org/docs/9.1/static/pgcrypto.html) Best regards, Piotr S. \< [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) \> ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 21, 2012, 5:02pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/14 "2012-02-21T17:02:52Z")
On Tue, Feb 21, 2012 at 12:38:15AM +0100, Giuseppe T. wrote: > Best regards, and many thanks. You need to determine what “crypt” is being used in your PHP: > **[PHP: crypt - Manual](https://www.php.net/manual/en/function.crypt.php)** > > One-way string hashing Once you have that information, you should be able to figure out what you will need to do. Cheers, Ken ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 21, 2012, 5:45pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/15 "2012-02-21T17:45:43Z")
Hi, In PHP I used crypt($pass, CRYPT\_STD\_DES) and I tried with the following statement postgres\_query “SELECT user FROM usertable WHERE user=$user AND pwd=crypt($pass, substr(pwd, 1, 2))”; but do not work, some ideas? Best regards Giuseppe 2012/2/21 removed\_email\_address@domain.invalid [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 21, 2012, 12:38am UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/16 "2012-02-21T00:38:44Z")
Hi Unfortunately the problem is partially solved. postgres\_query "SELECT user FROM usertable “WHERE user=$user AND pwd=crypt($pass, pwd)”; The crypt function in postgresql works correctly only with the password created by the htpasswd program, but do not work with passwords created by PHP. Best regards, and many thanks. 2012/2/18 Piotr S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 21, 2012, 6:03pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/17 "2012-02-21T18:03:04Z")
On Tue, Feb 21, 2012 at 05:45:20PM +0100, Giuseppe T. wrote: > Best regards > > Giuseppe Are the encrypted passwords the same? If they are, are you certain you are passing the correct password, i.e. stripping line ending correctly? Ken ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 21, 2012, 8:20pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/18 "2012-02-21T20:20:14Z")
Hi, > crypt(“test”, “XX”) generates “XXF2OrGyU2fzk” > > So you may want to use something like this: > > postgres\_query “SELECT user FROM usertable WHERE user=$user AND > pwd=crypt($pass, substr($user, 1, 2))”; Except that “pwd” used in the above snipped is not password, but the hash stored in the database and “pwd=crypt($pass, pwd)” is the correct way to verify that “$pass” would evaluate to “pwd” hash (so that the password is correct). Best regards, Piotr S. \< [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) \> ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 21, 2012, 8:12pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/19 "2012-02-21T20:12:10Z")
21 февраля 2012, 20:45 от Giuseppe T. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): > In PHP I used crypt($pass, CRYPT\_STD\_DES) and I tried with the > following statement CRYPT\_STD\_DES is just a constant that indicates whether standard DES crypt() is availlable, so you should not use it as the salt - or if you do, the salt will be “1” (or “0” if standard DES crypt() is not available). You may want to use something like this instead: if (CRYPT\_STD\_DES == 1) { $salt = substr($username, 0, 2); $encrypted\_password = crypt($password, $salt); } You should regenerate your .htpasswd file using this approach because the Apache htpasswd uses a random salt instead of the first two characters of the username, > postgres\_query “SELECT user FROM usertable WHERE user=$user AND > pwd=crypt($pass, substr(pwd, 1, 2))”; You should never use any part of whatever you’re encrypting as the salt because it greatly reduces encryption strength / entropy. By using the first two characters of the password as the salt, you’re revealing them because the salt is stored in the first two characters of the resulting crypt() hash: crypt(“test”, “te”) generates “teH0wLIpW0gyQ” crypt(“test”, “XX”) generates “XXF2OrGyU2fzk” So you may want to use something like this: postgres\_query “SELECT user FROM usertable WHERE user=$user AND pwd=crypt($pass, substr($user, 1, 2))”; Max ---
**Author:** ![Giuseppe\_T](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/e5b9ba/32.png) [@Giuseppe\_T](https://www.ruby-forum.com/u/Giuseppe_T)\ **Post date:** [February 21, 2012, 8:22pm UTC](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947/20 "2012-02-21T20:22:43Z")
Hi, The password is correct, the problem is postgresql vers. 9.0.3 not “nginx”, es: authuser=# select crypt(‘multilab’, ‘1$’), pwd from usertable where user =‘multilab’ ; crypt | pwd ---------------±-------------- 1$2NVPu8Urs82 | 1$Ln7ocLxd/.k (1 row) pwd =1$Ln7ocLxd/.k salt =1$ PHP calculated and in python crypt.crypt(‘multilab’, pwd[:2] are are correct) Best regards Giuseppe 2012/2/21 Piotr S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): [Next page](https://www.ruby-forum.com/t/auth-user-with-postgresql/216947.md?page=2)