0

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?

1 Answer 1

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.

1
  • wonder if there is a way to securely exeucte sql in postgres? By secure, I mean a way to execute sql containing sensitive information, that would have minimal tracking from postgres (e.g. logging, or information retain in some other forms of data retention e.g. pg_stat_activity or some other catalogues that retain track of executed commands in some format)? .... (I wonder where other comments went :-| ) Mar 13, 2023 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.