Ask HN: Two specific Postgres encrypt questions
3 comments
You are missunderstanding the transport encryption(everything related to authentication and pg_hba.conf) and the encrytion of the data at rest.
You want the encryption at rest. For that you have 3 Options:
1. Encryption in the application.
2. Use of pgcrypto as documented.
3. Encryption of the partition where postgres stores the data via LUKS or similar.
For 1. and 2. it means that indices for encrypted columns become useless and are only a waste of cpu time.
2. has the problem of transitting the key at the start of the connection, so it needs to be encrypted(tls encryption).
Its a bit of an fault with the documentation since it mixes the two types of encryption up.
You want the encryption at rest. For that you have 3 Options:
1. Encryption in the application.
2. Use of pgcrypto as documented.
3. Encryption of the partition where postgres stores the data via LUKS or similar.
For 1. and 2. it means that indices for encrypted columns become useless and are only a waste of cpu time.
2. has the problem of transitting the key at the start of the connection, so it needs to be encrypted(tls encryption).
Its a bit of an fault with the documentation since it mixes the two types of encryption up.
Thank you for response.
>You are missunderstanding the transport encryption(everything related to authentication and pg_hba.conf) and the encrytion of the data at rest
Correct! My questions perhaps implied one is connected to the other whereas they are completely separate. I could have been clearer.
>it means that indices for encrypted columns become useless and are only a waste of cpu time.
are you saying that blunt-force encryption of all data-at-rest renders indexes pointless? Could you provide more context here?
Cheers
>You are missunderstanding the transport encryption(everything related to authentication and pg_hba.conf) and the encrytion of the data at rest
Correct! My questions perhaps implied one is connected to the other whereas they are completely separate. I could have been clearer.
>it means that indices for encrypted columns become useless and are only a waste of cpu time.
are you saying that blunt-force encryption of all data-at-rest renders indexes pointless? Could you provide more context here?
Cheers
When an column is encrypted you can't index the cleartext since it undermine the encryption and if you encrypt it in the application then postgres never had the cleartext. Pgcrypto encrypts at an higher level than the indexer operates, so it sees only the encrypted data.
(A) What are the recommended ways to encode Postrgres data at rest (tables+indexes+rows)? The Postgres documentation suggests options [0], but I have no familiarity with any. I prefer to not purchase something.
(B) When configuring Postgres with TLS/SSL, one needs to make private keys [1] which can lead to a prompt for a PEM pass phrase. Once all done with Postgres running, do I need to retain the PEM passkey? (The private key itself needs to be retained, of course).
[0] https://www.postgresql.org/docs/18/encryption-options.html
[1] openssl genrsa -aes256 -out <file> Enter PEM pass phrase: ...