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.
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