store password for pgcrypto

Question

When encrypting a sensitive information for a field, we may encrypt the field with pgcrypto using something like this:

INSERT INTO employees (email, password) VALUES (
'[email protected]',
crypt('mypassword', gen_salt('bf'))
);  

but, querying the column we gotta supply the password in plain text like this:

SELECT id
FROM employees
WHERE email = '[email protected]' AND password = crypt('mypassword', password).  

I'm using azure postgres, and I'm seeing no way to store this password somewhere securely that postgres provides. One thing that worries me is if full logging of queries is enabled, then the password appears as plain text in the logs, and I'm having to supply the master password everytime I want to see/query the encrypted data. I don't know if postgres has a facility to somewhere securely save the password and just fetch it on run time, like vault is for environment variables?

Answer 1

There is no way to securely store that encryption key in the database. If the database can retrieve it automatically, so can an attacker that has control over the database.

You have to store the key outside the database, perhaps in your application. But then the same problem applies, only with the application instead of the database. Also, as you correctly noted, the database administrator can log statements that contain the key.

Using pgcrypto to encrypt database contents is flawed for this latter reason. It is better to encrypt the data inside the application, so that the database never sees unencrypted data.