I need to hash some passwords with salt on postgresql, and I haven't been able to find any relevant documentation on how to get that done.
So how can I hash passwords (with some salts) in postgresql?
+6
A:
You should use pgcrypto to get access to sha256 which is a member of the sha2 family. Keep in mind sha0,sha1 md4, and md5 are very broken and should never be used for password hashes.
The following is a good method of hashing passwords:
digest("global salt"||"password"||primary_key, "sha256")
The salt should be a large randomly generated value. This salt should be protected, because the hashes cannot be broken until the salt is recovered. If you are storing the salt in the database then it can be obtained along with the password hash using sql injection. Concatenating the primary key is used to prevent 2 people from having the same password hash even if they have the same password. Of course this system could be improved, but this is much better than most systems I have seen.
Generally it is best to do hashing in your application before it hits the database. This is because querys can show up in logs, and if the database server was owned then they could enable logging to get clear text passwords.
Rook
2010-04-15 16:33:16
Yep, I was a bit late. Deleted my answer, as you were first and more detailed ;).
T Duncan Smith
2010-04-15 16:36:55
@T Duncan Smith thanks man, i gave you some points for being a good SO member.
Rook
2010-04-15 16:46:05
Hmmm, the logging issue is a good point, I suppose, but for practical reasons I want to be able to run a sql statement to un-personalize the passwords (along with other personal information) in order to publish a cleaned database.
Tchalvak
2010-04-15 18:23:06
Err, could you clarify the statement "The salt should be a large randomly generated value"? Would I add that random value salt via the traditional means of string concatenation?Does: `update account set pswhash = crypt('global salt' || 'new password' || 'user created date', gen_salt('sha256')) where account_id = 5` or something like that actually make sense for creating the initial hash?Or am I missing something about the crypt() function?
Tchalvak
2010-04-15 18:27:04
Yes, use string concatenation of the random value, also use the digest function. `pswhash = digest('global salt'||"password"||primary_key, "sha256")` Using the id makes sure the resulting hash is unqie even if 2 people both use the same password and create an account on the same day. You could make some improvements to the salt generation, but this is a lot better than most implementations i have seen.
Rook
2010-04-15 19:04:04
Yeah, I tried getting access to pgcrypto, but installing the (postgresql-contrib) package that it's supposed to be in doesn't seem to allow access, how do you get access to digest()?
Tchalvak
2010-04-15 20:26:55
Ah, looks like I have to actually run some sql from the module before the functions are available, not just run the install.
Tchalvak
2010-04-15 21:20:29
+1
A:
Examples and documentation on: http://www.postgresql.org/docs/8.3/static/pgcrypto.html
UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));
SELECT pswhash = crypt('entered password', pswhash) FROM ... ;
Matej Puntar
2010-04-15 16:35:59
Yeah, pgcrypto looks like what I'm looking for, but I'm having a hard time figuring out the usage, is the example usage saying that I don't have to hardcode my own salt into the hash?I.e. I do no longer have to provide my own salt data like: `update account set pswhash = crypt('global salt' || 'new password' || 'user created date', gen_salt('sha256')) where account_id = 5` ? or is salting still a manual process?
Tchalvak
2010-04-15 18:13:34