Как правильно организовать выборку зашифрованных данных PostgreSQL?

Question

[student496]

Есть таблица с выписками. В нем симметрично зашифрованы столбцы с номером, фамилией, именем, отчеством. Я на C# передаю ключ шифрования @encrkey и делаю выбор так:

SELECT s.id,
sym_dec(s.parent_iin,@encrkey) as parent_iin,
sym_dec(s.iin,@encrkey) as iin,sym_dec(s.lastname,@encrkey) as lastname,sym_dec(s.firstname,@encrkey) as firstname,sym_dec(s.middlename,@encrkey) as middlename,
s.birth_date,s.group_type,s.privilege,s.disorder,s.queue_ident,s.queue_number,s.queue_number2,s.create_date,s.create_time,s.status,s.interested,
s.interested_date,s.interested_time,s.ident_number,s.email,s.tel,s.correct_org,s.status_date,s.status_time,s.disorder_doc,s.referral_bin,c_s.*
FROM ddo.statements as s 
LEFT OUTER JOIN ddo.code_statstatus as c_s ON c_s.code=s.status
WHERE  
sym_dec(s.parent_iin,@encrkey)=@ParentIIN 
ORDER by s.create_date asc,s.create_time asc

как видно из запроса, столбцы для каждой записи расшифровываются при выборе. Когда выбрано более 2000 записей, запрос выполняется очень долго и в конце приложение выдает ошибку Max Pool Size:

Npgsql.NpgsqlException (0x80004005): The connection pool has been exhausted, either raise 'Max Pool Size' (currently 100) or 'Timeout' (currently 15 seconds) in your connection string. ---> System.TimeoutException: The operation has timed out

как правильно организовать выборку из зашифрованных данных?

Comments

[Everything_is_bad]

За это и любят такое использования шифрование в бд, практическая польза почти никакая, зато полно геморроя на ровном месте.

[mayton2019]

А ты можешь создать 2 одинаковые таблицы? С шифрованием и без.

И сравнить execution plans.

[Денис]

Попробуйте увеличить время запроса или сделать запросы с пагинацией, например по 100 записей.
Или тяните и делайте дешифровку в памяти.
Но как предлагали выше, лучше начать с плана запроса и понять, что отжирает время.

Answer 1

[Melkij]

Никак.

Ловите своих DBA, архитектора, безопасников, начальство и совместно вникаете, существует ли вообще вектор атаки, от которого вы так пытаетесь защититься.
Один постоянный универсальный ключ шифрования зашитый в приложении и открыто передающийся по сети - это больше профанация чем безопасность. И хорошо ещё, если ключ не сохраняется в логах базы вовсе в открытом виде.

Если начальство решает, что да, такое шифрование необходимо и от чего-то защищает - то это будет прямым ответом на задачу: поскольку необходимо шифрование parent_iin, то быстрый поиск по этому полю невозможен.

Единственное возможное исключение: если ваша функция шифрования обязуется быть immutable в терминах postgresql, т.е. на один и тот же ключ и исходные данные генерировать всегда один и тот же бинарно идентичный результат. Тогда делаете индекс по parent_iin и ищете по условию where parent_iin = функция_шифрования(данные, ключ) то есть по зашифрованному представлению данных.