Postgresql – Database encryption and key management with pg_crypto

encryptionpostgresql

I am looking at the possibility of having to store root passwords for virtual machines in reversibly encrypted format in a database. The reason is that local logins will sometimes be required to machines as much as they are discouraged and we need to be able to look up root passwords. Access of course would be heavily restricted to a few admins. These would be unused except when the vm is inaccessible over the network. Synchronizing users and passwords in this regard poses similar problems, namely that if the network is down, how does the sudoer log in with the guaranteed most recent password?

What is the best way to plan for keys to be rotated? Should I just use a passphrase that every admin has? This raises some red flags in my mind. Is there a way to securely manage keys such that admins can set their own passwords? Maybe use public key encryption and encrypt a symmetric key with each admin's public key?

Here I am looking just for general outlines of a solution regarding key management of encrypted sensitive data. If I can't figure out how to implement from that level I can ask more questions.

Best Answer

Note that I am not a crypto systems or security expert. This is what I've seen done and it makes sense, but I cannot claim it doesn't have security issues beyond what I outline below. As always, get a proper security audit if it's important.

In the following, "credential" can refer to ssh keys, passwords, or whatever needs storing.

The typical model is to use a couple of levels of crypto.

  • For each user, generate and store a public/private keypair

  • Store the public part of the user's key in the clear in the user's record

  • Store the private part of the user's key encrypted to the user's passphrase in the user's record.

  • Encrypt each credential to a symmetric key that's unique to that credential

  • Encrypt each credential's symmetric key to the public key of every user who should have access to that key, and store the resulting encrypted material in a user_credentials mapping table.

Essentially, you maintain a keyring, much like the gpg keyring where the public part is in cleartext and the private part is encrypted to a passphrase. You should make sure the passphrase selected by each user is strong.

Now you can generate a symmetric key for each encrypted credential. Encrypt this key to every user's public key and store it in a user-credentials join table. Never store this symmetric key unencrypted, and use a different symmetric key for each credential.

You land up with something like this

[user]
user_id
username
login_password_salt
login_password_hash
public_key_text
private_key_sym_encrypted_to_passphrase

[user_credential]
user_id
credential_id
credential_key_encrypted_to_user_public_key

[credential]
credential_id
credential_data_encrypted_to_credential_key

There are quite a few advantages to doing things this way:

  • If a dump of your database is leaked your credentials are still safe, or as safe as your passphrases and key strengths.

  • Since you have a different symmetric key for each credential you can control which users have access to which credentials

  • Any user with access to a given credential can give another user access to it; they just decrypt it using their key and encrypt it to the other user's public key. You don't need to get everybody together to add credentials or add new users.

  • Because you're encrypting each credential to a symmetric key, and then encrypting that key to the user's key, you can change the stored credential without having to re-encrypt it to each user's key. It just makes updating stored credentials more convenient.

However, if the database host is compromised, an attacker can easily extract the passwords any given user has access to as soon as that user logs in. They capture the user's passphrase by wrapping functions or by enabled detailed logging and searching the logs. Then they can decrypt passwords in a stolen dump or from the live DB. A compromised database host is no danger until a user logs in to use it but then all is lost.

This means that the host of the database is security critical. Leaking the dump isn't fatal to security, but someone being able to modify the code running on the DB is a fatal security breach.

In addition to using your credentials management DB to extract ssh key material, passwords, etc when you need it, you can also use it as an authentication proxy where it can auth against a service without your user having to ever be able to learn the credential used. For example, your app might decrypt an ssh key, add it to an ssh agent, and use the decypted key to log in to a server via the agent without the user ever being able to see and access the key directly.

Related Question