Re: postgres encryption

On Thu, Mar 22, 2018 at 7:19 AM, Bear Giles <bgiles(at)coyotesong(dot)com> wrote:

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

​I should add that you don't need three (or more) columns. You can use one
column with a separator, e.g., gen$base64-encoded salt$base64-encoded hash,
or even one of the 'wallets' and the like that provide (encrypted)
key-value pairs in a single base64-encoded string. I know the latter is
recommended for credit card information since it makes sure everything is
kept together and strongly encrypted. The only requirement is that the
separator not be ​one of the base-64 encoding characters: a-z, 0-9, +, and
/.

​You definitely want a random per-user salt though, not a single
application-wide salt or something based on the uid.​