For indexing/searching on encrypted fields we use a blind index (lots of good resources if you search for that term).
On the other hand, sorting on encrypted fields has proven to be a difficult challenge. There are some possible approaches but they lower the security of your encryption.
Blind indexes are useless when working with limited address spaces like Social Security Numbers, and even US Addresses[1]. It would take under an hour to reverse these on my current home PC.
Your advice isn't simply security theater - It's wrong and dangerous. It leads to companies treating this data, which is still sensitive, as nonsensitive and storing it insecurely, particularly when data teams export it to third-party tools.
For some kinds of data and queries, it doesn't matter if the data in the index is encrypted. For other kinds of data, you could build the index on an expression that produces decrypted or anonymized values. Sadly postgres doesn't have per-index permissions, so you can't prevent a user with access to the table from using all of it's indexes.
hn_throwaway_99|2 years ago
On the other hand, sorting on encrypted fields has proven to be a difficult challenge. There are some possible approaches but they lower the security of your encryption.
GauntletWizard|2 years ago
Your advice isn't simply security theater - It's wrong and dangerous. It leads to companies treating this data, which is still sensitive, as nonsensitive and storing it insecurely, particularly when data teams export it to third-party tools.
[1] https://www.transportation.gov/gis/national-address-database
webstrand|2 years ago