Re: postgres encryption

So you got into your target's database and want to know how to extract the passwords?... :-)

First, nobody should be encrypting passwords. They should be hashed. (The sole exception is if your site is a proxy to a third site and it's now much more common to have a different type of authentication method than a stored password.) A single layer of hashing without salt isn't very strong anymore - I don't care what your hashing algorithm is - and a single layer of hashing with salt isn't particularly strong either since it's often misapplied.

Going forward with some 'best practices'

1. Use one of the standard algorithms that will perform an iterative hash with salt for 1000 times. I think I put something up on my blog (https://invariantproperties.com) some time ago with sample code, or you can use the bcrypt2 library. Reminder that doing a hash with salt correctly is easy to screw up, e.g., H(salt|password) is far weaker than H(password|salt).

2. Add generation information to your salt and hash values. It can be encoded into a single column, e.g., 1$salt$hash, or in a UDT RECORD (gen, salt, hash). You'll need to keep track of what each generation means, e.g., '1' might mean the legacy SHA-1 hash but 2 means salted SHA-512 or BCRYPT or something else.

3. If you want to prevent an attacker from creating an account and copying the salt + hash into a targeted account's entry you can add a few more columns or UDT RECORD: (uid, gen, salt, hash, hmac). The last item is a message digest (with password known to your app) on the other four entries. You would verify that before checking the hashed password - if it doesn't match someone screwed with your database and you should raise an alarm.

4. You check the generation when checking the password. If it's an older generation you can either regenerate the password entry with newer gen information. Or you could force the user to change their password before proceeding.

5. Alternately you could force the user to reauthenticate (security questions, SMS codes, etc.) if it's an older generation of the password. You might do that if you changed your algorithm 6 months ago because you think it's become too weak, or immediately if you learn that your database has been compromised.

Advanced 'best practices'

This isn't widely done but you can borrow ideas from other services. Specifically the database should never reveal any information about the password or salt. Instead you use an oracle approach - you call a UDF or stored procedure with the uid and (hashed) password and it returns a boolean value. True if it's acceptable, false otherwise. (Or it could return a small enum if you wish to communicate additional details, e.g., that the password is old and should be updated, that the password is too old and the user needs to reauthenticate via security methods or SMS code, etc.). The initial hash can be pretty basic, you're just trying to prevent someone from sniffing it off a network tap.

You also need a UDF or stored procedure that takes the uid and (hashed) password and updates its own tables.

Those UDF or stored procedures handle the salt generation, hashing, and hash generation management itself. It can write to a different schema that the application user can't see. (Use 'DEFINER' security.) It can write to an entirely separate database. With FDW it could write to an entirely separate service, e.g., an LDAP server. The key point is that someone will illicit access to the database as the application user can't ever see the authentication information. With a different database or service they won't even be able to find the authentication information even if they have unlimited access to the database. They can't see entries, they can't insert additional records, they can't copy the authentication information with a known password into the columns for the target account.

FWIW I first saw this technique with LDAP servers and it's one reason why I've become a fan of using LDAP to store the user authentication information. It's a standard protocol, there are good servers, and as long as you don't use the same database for your LDAP server the information is totally inaccessible to someone with full access to your live database or backups.

Bear