SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

[swestner]

Und warum nicht einfach der DB das Verschlüsseln / Entschlüsseln überlassen?

https://www.postgresql.org/docs/8.1/...n-options.html

Wir machen das mit Firebird so und das funktioniert alles wunderbar...

[UntoterGeist]

Die Funktion wird doch auch von der DB ausgeführt. Die benutzt man wie jede andere Funktion, die von der DB zur Verfügung gestellt wird. pg_sym_decrypt ist eine Funktion von pgcrypto. pgcrypto ist ein offizielles ExtensionModul von postgres und standardmäßig bei der Installation dabei. Steht auch in deinem Link drin.

Zitat:

The /contrib function library pgcrypto allows certain fields to be stored encrypted. This is useful if only some of the data is sensitive. The client supplies the decryption key and the data is decrypted on the server and then sent to the client.

[Blup]

Eine Suchspalte ist bei der Suche über mehrere Spalten oder Subtabellen auch eine Möglichkeit der Optimierung.

Aber das war nicht mein Vorschlag.
Ich würde alle Daten der relevanten Spalten als Ergebnis noch einmal speichern.
Einmal abfragen und entschlüsseln:

Code:

Select id, name, vorname from patienten

Dann eine zusätzliche Tabelle mit einem Blobfeld und nur einem Datensatz wo das Ergebnis der Abfrage z.B. als CSV wieder verschlüsselt abgelegt wird.
Dieses Blobfeld muss bei jeder Änderung der orginalen Tabelle aktualisiert werden, in der selben Transaktion die auch den Patienten speichert.
(lesen, entschlüsssln, geänderte Zeile austauschen, verschlüsseln, speichern)
Im Prinzip eine materialisierte View.
Auf diese Weise braucht der Schlüssel den Client nie verlassen.
Bei 15.000 Patienten sollte dieser Blob etwa 1MB groß sein, da dürfte das Entschlüsseln kaum spürbar verzögern.

[Uwe Raabe]

Ich konnte jetzt keine Aussage dazu finden, aber das hast du sicher schon versucht?

SQL-Code:

SELECT "id","name","vorname" FROM "schema"."personen" WHERE "name" ILIKE 'Albert%';

[UntoterGeist]

@Blup
Achso war das gemeint, jetzt versteh ich deinen Ansatz.

@Uwe
Die relevanten Spalten haben den Datentyp bytea und sind durch die DB verschlüsselt worden. Man kann also nicht direkt auf die Spalten ILike anwenden. Man muss die Spalte schon vor dem Vergleich entschlüsseln lassen. Sonst sucht man (wenn man das in Text umcastet) nur in Datensalat herum. Die Datenbank macht das also nicht automatisch, kann sie auch garnicht, weil sie den Schlüssel dann nicht hat. Bei Oracle ist der Masterkey z.B. in der Datenbank hinterlegt. An dem Rechenaufwand ändert sich für Oracle dadurch aber auch nichts.

Da AES nicht homomorph ist kann man keine Suchoperationen auf den Verschlüsselten Text durchführen. Homomorphe Verschlüsselung ist aber glaube auch weniger sicher, weil die Strukturen erhalten bleiben. So wird das jedenfalls beschrieben. Bei diesen Verschlüsselungen könnte man den Suchtext verschlüsseln und das Ergebnis direktin den Spalten suchen.

Deshalb muss man das so schreiben:

Code:

SELECT
"id",
pgp_sym_decrypt("name",'geheimerSchlüssel') AS name,
pgp_sym_decrypt("vorname",'geheimerSchlüssel') AS vorname

FROM "schema"."personen"

WHERE pgp_sym_decrypt("name",'geheimerSchlüssel') ILIKE 'Albert%';

Postgres ist übrigens so schlau, das Ergebnis einer Spalte die schon entschlüsselt wurde zwischen zu speichern. Deshalb kostet der folgende Query nicht wirklich mehr Zeit als der vorige.

Code:

SELECT
"id",
pgp_sym_decrypt("name",'geheimerSchlüssel') AS name,
pgp_sym_decrypt("vorname",'geheimerSchlüssel') AS vorname

FROM "schema"."personen" WHERE pgp_sym_decrypt("name",'geheimerSchlüssel') ILIKE 'Albert%'
OR "schema"."personen" WHERE pgp_sym_decrypt("name",'geheimerSchlüssel') ILIKE 'Peter%'
OR "schema"."personen" WHERE pgp_sym_decrypt("name",'geheimerSchlüssel') ILIKE 'Daniel%';

[UntoterGeist]

Hab eine Lösung gefunden =D

Auch wenn ich das vorher doof fand. Das Hashen von Teilstrings, was ich erwähnt hatte, nennt sich BlindIndexing. So an sich ist das immer noch doof. Man muss den Strings schon ein Salt mitgeben. Sonst ist "abc" leicht raus zu finden. Das ermitteln geht ganz automatisch mit Rainbowtables. Und dann wüsste jeder zumindest die ersten Buchstaben. Mit Salt wird aber "blablablubabc" daraus. Das kann man bis zu den ersten 3, 4 Buchstaben machen. Zwar gibts dann in der DB noch viele Übereinstimmungen an Hashs, aber was sie bedeuten lässt sich nicht mehr erraten. Suchen tut man dann über eine Vorslektion auf die Hashs. In Kombi mit der Suchspalte, die alles enthält wonach gesucht werden kann, erhält man ein gutes Ergebnis.

[jobo]