Обсуждение: Password safe web application with postgre

Hello,

I have the following problem. A multiuser app has authentization and
authorization done based on pgsql.

The frontend is web based so it is stateless; it is connecting to database
on every get/post. There is also a requirement that the user is
transparently logged in for some period of time.

Tha most easy way is to store login credentials into the session. The
drawback is that session is stored in file, so the credentials are
readable. I want to avoid it.

My first step was hashing the password with the same mechanizm as pgsql
does, but I am not able to pass it to the server. I did some research with
mighty google and found reply by Tom Lane:

"No, you need to put the plain text of the password into the connInfo.
Knowing the md5 doesn't prove you know the password. "

Thus the next logical step is keeping sessions in servers memory rather
than files. Memory dump could compromise it, but this is acceptable risk.

I would like to ask you, if someone had solved this problem is some more
elegant way.

Thank you,
Bohdan

Hi Bohdan,
Is your web applications for use with PostgreSQL server administration
where you would like users to supply their login credentials for
PostgreSQL so that their actions within the db can be limited by the
fine gain privileges assigned to them?

If it is not then you may want to maybe remodel your solution so that
your uses may share a common DB login whose login details
(username,password and server host name etc) are stored/contained within
your web application hosted on the server.
Then you supply your users with other username/password which will only
be known by your web application and not the PostgreSQL login. When your
users wish to use your web application, they will login with their
username/password for the web application which your web application
should verify (by means you see fit). The web application can now login
(using the PostgreSQL credentials) to the DB on behalf of the user(s).
Using a shared login has the following advantages, you only need only
one login for all your users. Which means you only need administer one
login. And this gives you the option to use DB connection pooling (this
is an application solution). Creating connections is an expensive
process and should be done only when necessary.

Allan.

Bohdan Linda wrote:
> Hello,
>
> I have the following problem. A multiuser app has authentization and
> authorization done based on pgsql.
>
> The frontend is web based so it is stateless; it is connecting to database
> on every get/post. There is also a requirement that the user is
> transparently logged in for some period of time.
>
> Tha most easy way is to store login credentials into the session. The
> drawback is that session is stored in file, so the credentials are
> readable. I want to avoid it.
>
> My first step was hashing the password with the same mechanizm as pgsql
> does, but I am not able to pass it to the server. I did some research with
> mighty google and found reply by Tom Lane:
>
> "No, you need to put the plain text of the password into the connInfo.
> Knowing the md5 doesn't prove you know the password. "
>
> Thus the next logical step is keeping sessions in servers memory rather
> than files. Memory dump could compromise it, but this is acceptable risk.
>
> I would like to ask you, if someone had solved this problem is some more
> elegant way.
>
> Thank you,
> Bohdan
>
>

Hello,

I have the following problem. A multiuser app has authentization and
authorization done based on pgsql.

The frontend is web based so it is stateless; it is connecting to database
on every get/post. There is also a requirement that the user is
transparently logged in for some period of time.

Tha most easy way is to store login credentials into the session. The
drawback is that session is stored in file, so the credentials are
readable. I want to avoid it. 

My first step was hashing the password with the same mechanizm as pgsql
does, but I am not able to pass it to the server. I did some research with
mighty google and found reply by Tom Lane:

"No, you need to put the plain text of the password into the connInfo.
Knowing the md5 doesn't prove you know the password. "

Thus the next logical step is keeping sessions in servers memory rather
than files. Memory dump could compromise it, but this is acceptable risk.

I would like to ask you, if someone had solved this problem is some more
elegant way.

Thank you,
Bohdan 
 

Bohdan Linda wrote:
> Hello,
>
> I have the following problem. A multiuser app has authentization and
> authorization done based on pgsql.
>
> The frontend is web based so it is stateless; it is connecting to database
> on every get/post. There is also a requirement that the user is
> transparently logged in for some period of time.
>
> Tha most easy way is to store login credentials into the session. The
> drawback is that session is stored in file, so the credentials are
> readable. I want to avoid it.

Don't store login info in the session - just the user's ID and whatever
other session data is appropriate for your application. The basic idea is:

1. User makes a request.

2. Server looks for the session cookie (require cookies - storing
session info in the URI means that links to off-site locations will leak
the session ID via the referrer information).

2a. If it exists, grab the user's ID from the session data and use it
for authorization.

2b. If it doesn't exist or if it exists but the session is no longer
valid, route user to login page. The user enters their username and
password. The server authenticates the information and establishes a
session tied to a cookie. The value of the cookie must be non-guessable
or your app is vulnerable - Google around and you'll find some papers
about some major websites that have stupidly stored login data in the
cookie. Base the cookie vaue on a good random number generator. The MD5
of a long random number is often used - I'm not a crypto guy so I can't
pass judgment on how "random" that is.

 From here on, the browser/server is just passing that random token back
and forth. It contains no username or password info. Since it is the
temporary pass to the system, it still needs to be protected. That's why
cookie-based sessions are preferred to URI based ones and HTTPS is
preferred to HTTP. And avoid the mistake of having a login that sits on
an HTTP page but posts to an HTTPS page. It's vulnerable. One of my
banks still does this so I always just click "login" which fails but
takes me to the HTTPS login page where I do my actual login.

The session info on the server end only needs the ID of the user
associated with the session for authorization purposes. The user's name
and password need not be stored in the session - just enough info to be
able to determine access rights.

You can make some modest security improvements by storing things such as
the browser identification and IP address in the session data and
verifying it on each request but IP verification fails if the user is
behind a proxy like AOL's where each request may come from a different IP.

Cheers,
Steve

Bohdan Linda wrote:
> The frontend is web based so it is stateless; it is connecting to database
> on every get/post. There is also a requirement that the user is
> transparently logged in for some period of time.
>
> Tha most easy way is to store login credentials into the session. The
> drawback is that session is stored in file, so the credentials are
> readable. I want to avoid it.

I keep the user's login credentials in a TripleDES-encrypted,
non-persistent cookie, separate from session data.

I believe you said you were using PHP.  Here are the encrypt/decrypt
functions I use:

     function encrypt_mcrypt($str, $key = null)
     {
         $key = ($key === null) ? DEFAULT_MCRYPT_KEY : $key;

         // Note: requires libmcrypt 2.4 or greater

         $td = mcrypt_module_open(MCRYPT_TripleDES, "", MCRYPT_MODE_CFB,
"");

         $iv = mcrypt_create_iv(mcrypt_enc_get_iv_size($td), MCRYPT_RAND);

         mcrypt_generic_init($td, $key, $iv);

         $encrypted = mcrypt_generic($td, $str);

         mcrypt_generic_deinit($td);

         $encrypted  = rawurlencode($encrypted);
         $iv         = rawurlencode($iv);

         return join(",", array (md5($str), $iv, $encrypted));
     }


     function decrypt_mcrypt($enc_str, $key = null)
     {
         $key = ($key === null) ? DEFAULT_MCRYPT_KEY : $key;

         list ($hash_value, $iv, $encrypted) = explode(",", $enc_str);

         $encrypted  = rawurldecode($encrypted);
         $iv         = rawurldecode($iv);

         // Note: requires libmcrypt 2.4 or greater

         $td = mcrypt_module_open(MCRYPT_TripleDES, "", MCRYPT_MODE_CFB,
"");

         mcrypt_generic_init($td, $key, $iv);

         $plaintext = mdecrypt_generic($td, $encrypted);

         mcrypt_generic_deinit($td);

         // Compare hash values.  If not equal, return a null.

         if (md5($plaintext) != $hash_value)  {
             return null;
         }

         return $plaintext;
     }
}

Steve Crawford wrote:

> You can make some modest security improvements by storing things such as
> the browser identification and IP address in the session data and
> verifying it on each request but IP verification fails if the user is
> behind a proxy like AOL's where each request may come from a different IP.

It'll also break with IPv6 Privacy Extensions (RFC3041), especially with
fairly short connection keepalive intervals.

With Windows Vista supporting IPv6 and enabling it by default that's a
significant concern. Its resolver doesn't appear to prefer IPv6 despite
early documentation indicating that it would (eg: http://kame.org will
prefer IPv4 to IPv6 on Vista) so it's not an urgent issue, but it bears
thinking about.

It's great that PostgreSQL supports IPv6 so well, by the way. It
provides me with transparent access to databases on my testing
workstation from many of the networks I use day to day.

--
Craig Ringer

Hello,

thank you everyone for the answers. I went through and I forgot add one
thing. The web-app is frontend, thus basically PL/PGSQL launcher and all
changes are audited, so common login is unwelcome.

On Thu, May 15, 2008 at 05:40:49PM +0200, Steve Manes wrote:
> I keep the user's login credentials in a TripleDES-encrypted,
> non-persistent cookie, separate from session data.
>

This is the approach I am/will be heading to. Having the cookie with login
and password encrypted on user side, HTTPS connection, and what was said
in previous emails about not storing credentials in cookies any ideas of
weak sides?  Moreover if parts of decryption keys will be unique to the
sessions and stored in session on a server?

PS. Appologies for going slightly OT as this is becoming more general than
pgsql.

Thank you,
Bohdan

Bohdan Linda wrote:
> On Thu, May 15, 2008 at 05:40:49PM +0200, Steve Manes wrote:
>> I keep the user's login credentials in a TripleDES-encrypted,
>> non-persistent cookie, separate from session data.
>
> This is the approach I am/will be heading to. Having the cookie with login
> and password encrypted on user side, HTTPS connection, and what was said
> in previous emails about not storing credentials in cookies any ideas of
> weak sides?  Moreover if parts of decryption keys will be unique to the
> sessions and stored in session on a server?

No security is 100% and neither is my solution.  Given enough time,
interest and computer time it could be hacked.

But we used similar tamper-proof credentials security on three large,
hacker-infested community web sites which together logged up to .75
billion page views/month.  Everything else under the sun got hacked but
this encrypted cookie never was (we had watchdogs sniffing for mangled
cred cookies).  It was just too much work.