Thread: Encryption Options
Hello Friends,
We are newly moving to postgres database (yet to decide if it would be an on premise one or AWS aurora postgres). However , we want to understand what encryption / decryption techniques are available in the postgres database.
We may have some sensitive/"personal information" (like customer name, account number etc )stored in the database and thus we may need "data at rest encryption", what are the options available here?
Along with that, we want to understand, any other option to store the specific "data attribute" itself in the database by encrypting, so it won't be visible in clear text to anybody and decrypting the same while needed and what would be the performance overhead of those options?
Regards
Sud
You need to clearly define your threat model. What exactly are you defending against? What scenario do you want to avoid?
Also, your decision of on-premise or Aurora is extremely relevant to your range of options.
Cheers,
Greg
The phrases "personal information" and "data at rest encryption" strongly indicate PCI, or something similar.
On Fri, Feb 16, 2024 at 12:20 PM Greg Sabino Mullane <htamfids@gmail.com> wrote:
You need to clearly define your threat model. What exactly are you defending against? What scenario do you want to avoid?Also, your decision of on-premise or Aurora is extremely relevant to your range of options.Cheers,Greg
On Fri, Feb 16, 2024 at 1:53 AM sud <suds1434@gmail.com> wrote:
Hello Friends,We are newly moving to postgres database (yet to decide if it would be an on premise one or AWS aurora postgres). However , we want to understand what encryption / decryption techniques are available in the postgres database.We may have some sensitive/"personal information" (like customer name, account number etc )stored in the database
The problem with encrypting "account number" is that you can't JOIN or WHERE on it. That's not always necessary, though. The pgcrypto module does what it says, but requires application-level changes,
Encryption at rest can be accomplished with filesystem-level encryption, and backup encryption. (PgBackRest has that feature, using AES-256. Don't know about BarMan.)
On Fri, Feb 16, 2024 at 10:50 PM Greg Sabino Mullane <htamfids@gmail.com> wrote:
You need to clearly define your threat model. What exactly are you defending against? What scenario do you want to avoid?Also, your decision of on-premise or Aurora is extremely relevant to your range of options.
Thank you.
Yes these are Account number/PCI data and "data at rest" encryption is something management is asking to have irrespective of whether we encrypt those before storing in the database or not. And this system needs to adhere to PCI 4.0 standards , so it looks like we can't persist the PCI data as is in th database even if the 'data at rest' encryption is there, it has to be encrypted before storing into the database.
Agreed. The on-premise vs aurora will take a different approach for catering to above needs. We are currently evaluating , what would be the possible options in each of these cases? and if this would be a factor in choosing the on-premise postgres vs aurora postgres?
On Sat, Feb 17, 2024 at 12:40 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
The problem with encrypting "account number" is that you can't JOIN or WHERE on it. That's not always necessary, though. The pgcrypto module does what it says, but requires application-level changes,Encryption at rest can be accomplished with filesystem-level encryption, and backup encryption. (PgBackRest has that feature, using AES-256. Don't know about BarMan.)
Will try to verify these options. Considering these system processes 100's of millions of transactions, will these encryption add significant overhead? It would be great, if you can suggest some doc to follow, for implementing these. Not sure if the same would work for aurora too.
Regards
Sud
On Fri, Feb 16, 2024 at 4:04 PM sud <suds1434@gmail.com> wrote:
On Fri, Feb 16, 2024 at 10:50 PM Greg Sabino Mullane <htamfids@gmail.com> wrote:You need to clearly define your threat model. What exactly are you defending against? What scenario do you want to avoid?Also, your decision of on-premise or Aurora is extremely relevant to your range of options.Thank you.Yes these are Account number/PCI data and "data at rest" encryption is something management is asking to have irrespective of whether we encrypt those before storing in the database or not. And this system needs to adhere to PCI 4.0 standards , so it looks like we can't persist the PCI data as is in th database even if the 'data at rest' encryption is there, it has to be encrypted before storing into the database.Agreed. The on-premise vs aurora will take a different approach for catering to above needs. We are currently evaluating , what would be the possible options in each of these cases? and if this would be a factor in choosing the on-premise postgres vs aurora postgres?On Sat, Feb 17, 2024 at 12:40 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:The problem with encrypting "account number" is that you can't JOIN or WHERE on it. That's not always necessary, though. The pgcrypto module does what it says, but requires application-level changes,Encryption at rest can be accomplished with filesystem-level encryption, and backup encryption. (PgBackRest has that feature, using AES-256. Don't know about BarMan.)Will try to verify these options. Considering these system processes 100's of millions of transactions
Per minute? Hour? Day? Month? Year? Decade?
Continuous? In waves?
, will these encryption add significant overhead?
"Significant" is relative, and depends on the CPU.
It would be great, if you can suggest some doc to follow, for implementing these.
Google "pgcrypto".
Not sure if the same would work for aurora too.
This list avoids giving help on Aurora, since it's very different from Postgresql. AWS RDS Postgresql is much closer to vanilla Postgresql, so we can help with that.
On Fri, Feb 16, 2024 at 4:04 PM sud <suds1434@gmail.com> wrote:
Yes these are Account number/PCI data and "data at rest" encryption is something management is asking to have irrespective of whether we encrypt those before storing in the database or not. And this system needs to adhere to PCI 4.0 standards , so it looks like we can't persist the PCI data as is in th database even if the 'data at rest' encryption is there, it has to be encrypted before storing into the database.
Even with PCI rules, not all data needs to be encrypted, only very sensitive things like actual credit card numbers. If you don't have a compliance department, you may want to outsource that investigation. To someplace other than pgsql-general. :)
Agreed. The on-premise vs aurora will take a different approach for catering to above needs. We are currently evaluating , what would be the possible options in each of these cases? and if this would be a factor in choosing the on-premise postgres vs aurora postgres?
Also outside the scope of this list, but with Aurora, you pay more, and must 100% trust Amazon with all of your data. On the plus side, they (and any other managed Postgres service) remove a lot of the complexity and DBA housekeeping tasks.
On Sat, Feb 17, 2024 at 12:40 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:The problem with encrypting "account number" is that you can't JOIN or WHERE on it.
I'm hoping the OP meant "credit card number" not "primary key identifying a customer" or just generic PII. If just cc#s, no joins are needed (and certainly no WHERE clause, yikes!)
Will try to verify these options. Considering these system processes 100's of millions of transactions, will these encryption add significant overhead?
Yes, encryption will always incur overhead. However, this cost should be absorbed by your application, not the database. Encrypt and store as a blob[1] in the database. Stolen database = no keys, no access. As Ron pointed out, you should also have other levels of encryption: your backups, your WAL, and ideally OS-level as well.
[1] Generic blob of data, not a BLOB
Cheers,
Greg