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