The Vault will not prevent someone who has login access to your database and the right grants (or superuser) from decrypting the data. If someone is in this position they are fully compromised and the Vault is not protection against that (nor is anything else really).
In particular if an attacker has a postgres superuser login they can essentially asct as the OS process owner, and could possibly get around the process hardening we already employ to reduce that risk, but again Vault is not designed to protect against a full superuser exploit. You must carefully guard database login access.
However, the secret data that is stored on disk, in WAL logs, and in database dumps is encrypted. This way you are ensured that your secrets are encrypted at rest. The Vault also provides using standard Postgres privilege access control (via GRANT/REVOKE) to control access to the decrypted data.
I wasn't talking just about pgsodium or the vault product but similar products in general.
I understand the point of the database client having access to to the database key and not the key to the secret vault. So in this case other secrets at the vault are essentially protected. But let's say I really have this one secret to protect in which case is the vault fairly pointless?
Is it essentially that if a client using KeyX for some purpose than a compromise of said client will essentially lead to KeyX and there's really no way to protect it?
In particular if an attacker has a postgres superuser login they can essentially asct as the OS process owner, and could possibly get around the process hardening we already employ to reduce that risk, but again Vault is not designed to protect against a full superuser exploit. You must carefully guard database login access.
However, the secret data that is stored on disk, in WAL logs, and in database dumps is encrypted. This way you are ensured that your secrets are encrypted at rest. The Vault also provides using standard Postgres privilege access control (via GRANT/REVOKE) to control access to the decrypted data.