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

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

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

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

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