...does storing ciphertext in a database gain you much over using the database's built-in encryption?
I would think it protect data that was obtained from SQL injection or database access as long as the key remained safe?
SQLi can be used to quickly escalate access to the rest of the machine.
Form the pgcrypto docs you list:
F.25.6.3. Security Limitations
All pgcrypto functions run inside the database server. That means that
all the data and passwords move between pgcrypto and client
applications in clear text. Thus you must [either]:
-Connect locally or use SSL connections.
-Trust both system and database administrator.
If you cannot, then better do crypto inside client application.
This means that if an attacker can get into the machine, your data is exposed.
As they suggest, if this can not be ruled out you can do one of two things:
- Move the application to a separate machine so the key is not on the same machine as the database
- Encrypt the data in the client application.
The ciphertext downside being you lose the ability to query the data.
Not entirely. Here is a guide on indexing encrypted data in SQL