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:
Show quoted text
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.
https://www.varonis.com/blog/pci-dss-requirements
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.
https://www.varonis.com/blog/pci-dss-requirementsAgreed. 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.
https://www.varonis.com/blog/pci-dss-requirements
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]Generic blob of data, not a BLOB 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