PostgreSql: как делать запросы, используя зашифрованные столбцы

СЦЕНАРИЙ:

Я создал таблицу:

Create table knights(age integer, nickname varchar(255));

Затем я вставил несколько записей и зашифровал данные столбца псевдонима с помощью pgcrypto:

insert into knights values(21, PGP_SYM_ENCRYPT('ShiningArmor','AES_KEY')::varchar);
insert into knights values(32, PGP_SYM_ENCRYPT('Rigid','AES_KEY')::varchar);

ПРОБЛЕМА: Теперь я пытаюсь получить записи из таблицы, используя зашифрованный столбец псевдонимов, как это предлагается в этот ответ:

SELECT * FROM knights WHERE nickname = pgp_sym_encrypt('Rigid', 'AES_KEY')::varchar;

Я ничего не получаю обратно. Обратите внимание, что мне пришлось преобразовать nickname в varchar. Даже если я изменю тип столбца на bytea, я все равно ничего не получу. Обратите внимание, что мой симметричный ключ на самом деле такой же: AES_KEY. Я его нигде не генерировал. Нужно ли менять длину?

Моя версия PostGreSql — 9.6.


postgresql postgresql-9.6 pgcrypto
person Syed Waqas    schedule 09.06.2020    source источник
comment
Примечание: не шифруйте пароли в базе данных, хешируйте их. Пароли никогда не должны быть расшифрованы.   -  person Sami Kuhmonen    schedule 09.06.2020
comment
Это всего лишь пример. В моем реальном проекте я хеширую пароли. Но есть некоторые столбцы, которые необходимо шифровать в базе данных и расшифровывать, когда приложение извлекает их, поэтому в этом случае требуется двустороннее шифрование. Я изменю пример, чтобы не использовать столбец пароля.   -  person Syed Waqas    schedule 09.06.2020

Ответы (2)


arrow_upward
1
arrow_downward

Ваши зашифрованные столбцы данных должны быть определены как bytea

Когда вы читаете запрос, он должен выглядеть следующим образом:

SELECT 
    PGP_SYM_DECRYPT(nickname::bytea, 'AES_KEY') as name,
FROM knights WHERE ( 
    LOWER(PGP_SYM_DECRYPT(nickname::bytea, 'AES_KEY')
    LIKE LOWER('%Rigid%')
);
person Techie    schedule 09.06.2020
comment
Это сработало. Однако по отдельности нижнее ключевое слово не распознается с моей стороны. Разве это не поддерживается в PostgreSql 9.6? - person Syed Waqas; 09.06.2020
comment
Кроме того, у вас есть идея, будет ли этот запрос сначала расшифровывать все записи в таблице, а затем сравнивать данный текст? Или он расшифровывает каждую запись одну за другой и сравнивает отдельно? - person Syed Waqas; 09.06.2020
comment
LOWER можно удалить, потому что я только что добавил его. Я не уверен в цикле выполнения. - person Techie; 09.06.2020

arrow_upward
0
arrow_downward

pgp_sym_encrypt использует соль, поэтому вы не получите один и тот же ответ каждый раз при шифровании одного и того же значения одним и тем же паролем. Как следует из принятого ответа, ответ заключается в проверке с расшифровкой, а не с шифрованием.

Кажется, что установка s2k-mode на 0 должна производить повторяемое шифрование, но экспериментально это не так. Возможно, IV все еще случайный?

person jjanes    schedule 09.06.2020