Storing sensitive data in database, recommendation

Question

I'm searching for best solution to store sensitive data in database. I know that this is common problem and i have done my homework (at least this is what i think), but i wanted to ask here before i will make a decision.

Assumptions:

• Encrypted data needs to be decrypted. We are talking about SMTP credentials like username, password, host, port itp.

I was thinking about 2 concepts:

1. Encrypt data with help of passlib.totp library. To make those data a bit safer i will keep key in separate file. Then from what i can see i can use this library to decrypt data to plain text using my key.

2. The other concept was to encrypt and decrypt data during query request with help of postgres:

   insert into demo(pw) values ( encrypt( 'data', 'key', 'aes') );
   

   And:

   decrypt(pw, 'key', 'aes'), 'utf-8')
   

   Here the key will be stored also in separate file.

So my questions are:

1. What is better approach to encrypt / decrypt data, in code or in database?
2. Are there any better (stronger) libraries to use than passlib.totp -> i have no experience with that library (i'm aware that encryption / decryption is not the moste secure way of storing password -> password supposed to be hased but i need it in plain text to use users smtp gate).

Answer 1

2) The other concept was to encrypt and decrypt data during query request with help of postgres: insert into demo(pw) values ( encrypt( 'data', 'key', 'aes') ); and decrypt(pw, 'key', 'aes'), 'utf-8') Here the key will be stored also in separate file.

I wouldn't recommend that, because it's way too easy for the keys to get exposed in pg_stat_activity, the logs, etc. PostgreSQL doesn't have log masking features that would protect against that.

I strongly advise you to use app-side crypto. Use a crypto offload device if security is crucial, so key extraction isn't possible for most attackers. Or require the key to be unlocked by the admin entering a passphrase at app start, so the key is never stored unencrypted on disk - then the attacker has to steal it from memory. But even an unencrypted key file somewhere non-obvious is better than in-db crypto, IMO, since it at least separates the key from the data.