Auth user with postgresql

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

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

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:
https://github.com/agentzh/headers-more-nginx-module

Best regards,
Piotr S. < [email protected] >

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

That may be useful.

All the best,

f

Francis D. [email protected]

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. [email protected]:

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] https://github.com/agentzh/set-misc-nginx-module

Best regards,
Piotr S. < [email protected] >

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. [email protected]:

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. [email protected]:

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. < [email protected] >

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. < [email protected] >

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. < [email protected] >

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. [email protected]:

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 http://wiki.nginx.org/HttpSetMiscModule#Installation
is never mentioned

Thanks again

Giuseppe

2012/2/18 Piotr S. [email protected]:

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 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] http://www.postgresql.org/docs/9.1/static/pgcrypto.html

Best regards,
Piotr S. < [email protected] >

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:

http://php.net/manual/en/function.crypt.php

Once you have that information, you should be able to figure out
what you will need to do.

Cheers,
Ken

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 [email protected] [email protected]:

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. [email protected]:

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

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. < [email protected] >

21 февраля 2012, 20:45 от Giuseppe T. [email protected]:

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

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. [email protected]: