Во-первых, во всех моих тестах использовалась относительно простая таблица с такой схемой (имена столбцов были разными):
CREATE TABLE enctest ( id int, id_text text, id_enc bytea );
В MySQL вместо bytea использовался varbinary (64).
Идентификатор был сформирован из последовательности от 1 до 100 000. У меня было больше проблем с загрузкой в MySQL, чем в PostgreSQL. id_text был текстовым преобразованием id, а id_enc — значением id_text, зашифрованным с использованием 128-битного шифрования AES. Это было предназначено для имитации данных о продажах, состоящих из коротких строк, которые будут расшифрованы и преобразованы в числовые данные перед агрегацией.
Цель состояла в том, чтобы увидеть, как быстро различные реализации будут дешифровать все записи и объединять их в числовые типы данных. Для PostgreSQL использовался pgcrypto. Тесты проводились в режиме ANSI на MySQL, и таблицы были InnoDB.
То, что я нашел, было удивительно тревожным. В то время как MySQL был невероятно быстрым, эта скорость обеспечивалась за счет базовой проверки ошибок, а не ошибки, а расшифровка с использованием неправильного ключа иногда возвращала неверные данные, даже в традиционных режимах. Это связано с тем, что ошибки вместо предупреждений, согласно документации, преобразуются только при вставке, а не при выборе. Другими словами, MySQL также разрешает операции чтения с включенным режимом STRICT и выключен.
mysql> select sum(cast(aes_decrypt(id_enc, sha2('secret', 512)) as decimal)) FROM enctest;
+----------------------------------------------------------------+
| sum(cast(aes_decrypt(id_enc, sha2('secret', 512)) as decimal)) |
+----------------------------------------------------------------+
| 5000050000 |
+----------------------------------------------------------------+
1 row in set (0.33 sec)
Это быстро. Очень быстро. Мой аналогичный запрос в PostgreSQL занял около 200 секунд, то есть примерно в 600 раз дольше, и все время был полностью связан с процессором.
efftest=# explain (analyse, verbose, costs, buffers) select sum(pgp_sym_decrypt(testvalsym, 'mysecretpasswd')::numeric) from sumtest;
QUERY PLAN
--------------------------------------------------------------------------------
-----------------------------------------------
Aggregate (cost=7556.16..7556.17 rows=1 width=62) (actual time=217381.965..217
381.966 rows=1 loops=1)
Output: sum((pgp_sym_decrypt(testvalsym, 'mysecretpasswd'::text))::numeric)
Buffers: shared read=5556 written=4948
-> Seq Scan on public.sumtest (cost=0.00..6556.08 rows=100008 width=62) (ac
tual time=0.015..1504.897 rows=100000 loops=1)
Output: testval, testvaltext, testvalenc, testvalsym
Buffers: shared read=5556 written=4948
Total runtime: 217382.010 ms
(7 rows)
Моей первой мыслью было, что для двух реализаций разница будет на три порядка, что-то должно быть серьезно неправильно на стороне PostgreSQL. Это огромная разница. Но потом что-то произошло со мной. Что если я использую неправильный пароль?
On PostgreSQL: efftest=# explain (analyse, verbose, costs, buffers) select sum(pgp_sym_decrypt(testvalsym, 'mysecretpasswd2')::numeric) from sumtest; ERROR: Wrong key or corrupt data
На MySQL это совсем другая история:
mysql> select sum(cast(aes_decrypt(id_enc, sha2('secret2', 512)) as decimal)) FROM enctest;
+-----------------------------------------------------------------+
| sum(cast(aes_decrypt(id_enc, sha2('secret2', 512)) as decimal)) |
+-----------------------------------------------------------------+
| 1456 |
+-----------------------------------------------------------------+
1 row in set, 6335 warnings (0.34 sec)
Хм, из 100 000 строк только 6 000 (6%) дали предупреждение, и мы получили бессмысленный ответ. Спасибо, MySQL. Поэтому я попробовал некоторые другие:
mysql> select sum(cast(aes_decrypt(id_enc, sha2('s', 512)) as decimal)) FROM enctest;
+-----------------------------------------------------------+
| sum(cast(aes_decrypt(id_enc, sha2('s', 512)) as decimal)) |
+-----------------------------------------------------------+
| 1284 |
+-----------------------------------------------------------+
1 row in set, 6230 warnings (0.35 sec)
Опять 6 процентов предупреждений, бессмысленный ответ вернулся. Вау, это весело …
Как ни старайся, я не смог заставить MySQL выдавать какие-либо ошибки, и я всегда получал бессмысленные результаты с неправильным ключом. При более внимательном рассмотрении выяснилось бы, что MySQL выдает предупреждения только тогда, когда встречаются определенные редкие критерии, и не выполняет проверку данных, чтобы убедиться, что они соответствуют данным. Дальнейший обзор показал, что криптограммы в MySQL были намного короче, чем PostgreSQL, предполагая, что PostgreSQL добавлял короткие строки, чтобы криптография лучше защищала данные. Подробнее об этом позже.
Это говорит о том, что разница в производительности вполне может быть связана с дополнительными проверками работоспособности в PostgreSQL, которые MySQL пропустил для целей, связанных со скоростью. Вооружившись этими знаниями, я попробовал следующее:
efftest=# update sumtest set testvalsym = pgp_sym_encrypt(testvaltext, 'mysecretpasswd', 's2k-mode=0, s2k-digest-algo=md5'); UPDATE 100000
Запрос вернулся довольно быстро. Однако эти параметры не очень рекомендуются для производственных сред.
Я попробовал еще раз проверить данные и выполнить запросы производительности, и результаты оказались на два порядка быстрее:
efftest=# explain (analyse, verbose, costs, buffers)
select sum(pgp_sym_decrypt(testvalsym, 'mysecretpasswd2')::numeric) from sumtest;
ERROR: Wrong key or corrupt data
efftest=# update sumtest set testvalsym = pgp_sym_encrypt(testvaltext, 'mysecretpasswd', 's2k-mode=0, s2k-digest-algo=md5');
UPDATE 100000
efftest=# explain (analyse, verbose, costs, buffers) select sum(pgp_sym_decrypt(testvalsym, 'mysecretpasswd2')::numeric) from sumtest;
ERROR: Wrong key or corrupt data
efftest=# explain (analyse, verbose, costs, buffers)
select sum(pgp_sym_decrypt(testvalsym, 'mysecretpasswd')::numeric) from sumtest;
QUERY PLAN
--------------------------------------------------------------------------------
-----------------------------------------------
Aggregate (cost=13111.00..13111.01 rows=1 width=71) (actual time=1996.574..199
6.575 rows=1 loops=1)
Output: sum((pgp_sym_decrypt(testvalsym, 'mysecretpasswd'::text))::numeric)
Buffers: shared hit=778 read=10333
-> Seq Scan on public.sumtest (cost=0.00..12111.00 rows=100000 width=71) (a
ctual time=0.020..128.722 rows=100000 loops=1)
Output: testval, testvaltext, testvalenc, testvalsym
Buffers: shared hit=778 read=10333
Total runtime: 1996.617 ms
(7 rows)
Гораздо быстрее. Конечно, это происходит за счет средств безопасности.
Измененные здесь основные функции безопасности — это так называемые строковые функции ключа. PostgreSQL также предлагает несколько относительно сложных контейнеров для коротких данных, которые включают в себя такие вещи, как padding и сеансовые ключи. MySQL не предоставляет строку для управления ключами и требует, чтобы вы генерировали шестнадцатеричный ключ самостоятельно. PostgreSQL предоставляет ряд опций для генерации строки в ключ, которые позволяют использовать соленые хэши для фактического шифрования.
Одним из наиболее очевидных последствий здесь является то, что с MySQL вы должны генерировать свой соленый хеш самостоятельно, тогда как с PostgreSQL он может генерировать различный соленый хеш для каждой строки. Это очень важно для шифрования, особенно с небольшими строками, потому что это помогает помешать радужным таблицам. В сущности, для соленых ключей не существует отношения 1: 1 между комбинацией ключевой фразы / данных и криптограммой, потому что нет отношения 1: 1 между ключевой фразой и ключом. Дальнейшее тестирование показывает, что это не ответственно за разницу в производительности, но предполагает, что под поверхностью скрыты дополнительные проверки, которые опущены в MySQL.
Таким образом, учитывая, что проблема не связана с управлением ключами, проблема должна быть заполнена. Для очень коротких строк PostgreSQL управляет заполнением и контейнерами, в то время как MySQL просто шифрует короткие строки без минимального заполнения. Поскольку заполнения недостаточно, процедуры дешифрования выполняются намного быстрее, но это обходится ценой любой разумной безопасности. Кроме того, PostgreSQL предоставляет проверки данных, которые не выполняются в MySQL.
Так что это говорит нам? Я думаю, что основной урок, который я несколько раз привел домой, заключается в том, что шифрование на уровне базы данных сложно. Это особенно верно, когда речь идет о других соображениях, таких как агрегирование данных о производительности по значительным наборам. Добавьте к этому проблемы управления ключами в базе данных и тому подобное, и шифрование в базе данных, безусловно, является экспертной областью. В связи с этим подход MySQL, по-видимому, требует гораздо большей сложности для обеспечения безопасности, чем подход PostgreSQL.
Важно помнить, что короткие зашифрованные строки относительно распространены в базах данных, использующих шифрование. Одно из наиболее распространенных применений — это такие вещи, как номера кредитных карт. По причинам, указанным здесь, я бы предположил, что PostgreSQL гораздо более надежен в этих случаях.