On Thu, Aug 09, 2018 at 02:34:07PM -0600, Bear Giles wrote: > Some regulatory standards require all UII, even all PII, information be > encrypted within the database, not just on encrypted media. That's to > reduce exposure even if someone gets access to a live server, e.g., via SQL > Injection. (The perennial #1 risk for software vulnerabilities.)
My preference for dealing with SQL Injection is to not provide direct SQL access, but to use PostgREST exporting a schema that has only PG SQL functions encapsulating all supported queries. You just can't have injection with such an access layer because you don't get to send SQL to the server (because you don't get to send SQL to PostgREST). It really helps that PostgREST is written in Haskell. That said, sure, if you have SQL Injection issues, then encrypting in the database will do provided that there's no transparent way to access the data (otherwise you've gained nothing). That basically means you're doing all the crypto on the client. If you're doing all the crypto on the client, then your options for indexing are very limited indeed. To avoid offline dictionary attacks you have to index MAC'ed values, effectively. You can still do free text indexing, provided you MAC each word. MAC == message authentication code, really, it's a keyed hash function, typically HMAC, UMAC, or some such. You could also index ciphertext, provided it has an authentication tag, but you don't gain anything versus just indexing the authentication tag. > I know the government required UII encryption in its databases when I last > [...] Usually regulations are not quite as prescriptive as that, though there's always a discussion to be had with the regulators/auditors when you deviate from the norm. You're generally not precluded from having better solutions than is the norm. Nico --
