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