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