PostgreSQL persistent connection without password

[javilla]

Hello,

I use a PHP script to connect to a PostgreSQL data base server. This script is a really complicated app with a login system. It have a general data base with configuration values and a table for the users. But the important content is in other data base which depends on the user. This user is in fact a PostgreSQL user and the login system collect the user name and the password to establish the connection to the proper data base.

Anyway, there are two connections from two different users at the same time. One of them is predetermined in a configuration file with a password to connect to the "general" data base. The other is collected by the login form.

So each time the script is executed, it needs the user name and password for both connections. The first couple (user and password) is always the same and is stored in the config file but the second couple changes for every user and for security reasons can't be stored anywhere.

It's not feasible to ask for the password every time a user carries out some task. Right now I use the PHP global array $_SESSION to store the user and password but these values are saved in a file in the server what means a security risk.

Any idea to solve this?

Thanks,
Javier.

[garrettroyce]

Maybe you can encrypt the password? Or, if you don't feel safe with it on the server, use an encrypted cookie. Or, do the login verification yourself and use different login information for the server. Maybe you have the same password for all users. Then, once you verify the user's identity, you create a second connection.

[javilla]

Thank you very much garrettroyce,

I think the better choice is the encryption on the server side since the cookie encryption is probably less secure. Anyway, I'd like the application to be totally functional with disabled cookies on client.

I'd like using the users system from PostgreSQL too. This way, the aplication would be just a front-end like for the data base manager.

I've been reading about mcrypt functions for PHP. But now the question is: which algorithm can I use?. Or there is something better than mcrypt?

Thanks,
Javier.

[garrettroyce]

I've not had any luck with builtin encryption.

I think what would work is to use connection #1 to authenticate the user, then have the same password for everyone with different user names for connection #2. You can have the password stored safely in your script that way. And you can use a more secure password this way.

[javilla]

I think I got a good solution:

1- The user fill the login form which send the user name, the password and an unique ID generated by the server an included in the form within a hidden input. That ID is for register when a user is attempting to log in and avoid the risk of someone to log in when go back to just after the login page.

2- The server checks if it is waiting for someone to log in with the ID. If it is, it use the user and password to log in the database server. On success, the server generate two keys. The first one is use to encrypt the user password. Then, the ecrypted password is stored on the PHP SESSION ($_SESSION).

The second key is for the server side and is stored on the PHP session too. That is used to encrypt the first key which is send encrypted to the client on a cookie.

3- When the user carry out the rest accions after login, the server decrypt the client key stored on the cookie using the server key. Then, with the client key decrypt the password and log in the database server each time.


Benefits:

- There's no need to store md5 ecrypted passwords on a database which is locally public since the user and password for the database must be on the PHP script. That script must be readable for the http server and predictability for other users.

- There's no need to store any decrypted sensible password in the public database, a PHP script, in the PHP session or in a cookie.


That is the best way I can imagine for now. If someone can find some potential security risk or know other way, please spend some time here.


PD: That way, cookies are essential.
I use mcrypt with blowfish in CBC mode.

Thanks,
Javier.