Is there any security advantage to using pgcrypto instead of partition-level encryption?

Question

The document here: http://www.postgresql.org/docs/8.2/static/encryption-options.html describes several approaches to data encryption using postgresql.

I would like to know if there is any security advantage for encrypting certain columns using pgcrypto instead of simply encrypting the entire partition on which the database is located. It seems to me that pgcrypto is cumbersome to use (instead of just using SQL queries, without worrying that they are encrypted), so there must be a reason for it to exist. It’s just that people may not be able to manage the database server (shared hosts / etc ...), so they have to do with pgcrypto or is there a reason for security?

In my particular case, the application code and the database are on the same host, so compromising the server itself during its operation will lead to data leakage in any case (you could look for the encryption key in the code in one case or just get the data from the installed volumes in another).

Edit: I forgot to mention, also in this particular case, the data is used by the server, not the client, that is, the client cannot provide the key at run time, it must be on the server in the application code.

Answer 1

• You can provide access at the DBA SQL server level without compromising data.
• You may have separate encryption keys for different data.
• Your backups (dumps) are encrypted
• If the client provides encryption keys, the server does not need to be trusted
• If you separate the application server from the database server, the encryption keys can only be on the application server.
• You can encrypt only part of the data.
• Last but not least, for pgcrypto there is more than symmetric single-key encryption, such as asymmetric encryption, cryptographic hashing, crit-safe PRNG, password hashing.