[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Password safe web application with postgre
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Password safe web application with postgre
You could try to have a function in your application that encrypts the connection string and store it in a session variable. When you need it you decrypted from the session variables. Session variables are stored as files on the server, therefore the risk is not as high. Just a thought. Fernando. 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
Re: [GENERAL] Password safe web application with postgre*s*
In our web-based-solution (PHP) the database credentials (username and password) are encrypted and stored by PHP as session-Variables.Yes, there is the risk, they could be read by someone, who has access to the apache-sessions-directory, but this user also must have access to the php-scripts with the encrypt-functions to get the unencryption-keys and he must be able to work with these informations.But I think, this solution is much more save then storing or comitting the credentials as clear-text in cookies, hidden formular-elements or as sessions. But when you try to login to the database, somehow the credentials must be cleartext, so you cant get rid of this lack of security in my opinion.By the way, this is an *intra*net-solution, and we dont have hackers in our staff, I hope...Ludwig
Re: [GENERAL] Password safe web application with postgre
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Password safe web application with postgre*s*
[EMAIL PROTECTED] wrote: ... By the way, this is an *intra*net-solution, and we don't have hackers in our staff, I hope... Cross your fingers - most compromises come from inside the firewall. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Password safe web application with postgre
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;
}
}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Password safe web application with postgre
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Password safe web application with postgre
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Password safe web application with postgre
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
