PostgreSql: How to query using encrypted columns

Question

SCENARIO:

I have created a table:

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

Then I inserted a few records and encrypted the nickname column data using 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);

PROBLEM: Now I try to get records from the table using the encrypted nickname column as per suggested in this answer:

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

I get nothing back. Notice that I had to cast the nickname to varchar. Even if I change the column type to bytea, I am still getting nothing. Please note that my symmetric key is actually the same: AES_KEY. I did not generate it anywhere. Do I need to change the length?

My PostGreSql version is 9.6.

Note: don’t encrypt passwords in the database, hash them. Passwords should never be decryptable. — Sami Kuhmonen, Jun 09 '20 at 06:15
This is just an example. In my real project I am hashing the passwords. But there are some columns that need to be encrypted when in the database and decrypted when the app retrieves them, so 2 way encryption is required in that case. I will change the example to not use the password column. — Syed Waqas, Jun 09 '20 at 06:29

score 1 · Accepted Answer · answered Jun 09 '20 at 06:50

1

Your encrypted data columns should be defined as bytea

When you are reading the query should as follows,

SELECT 
    PGP_SYM_DECRYPT(nickname::bytea, 'AES_KEY') as name,
FROM knights WHERE ( 
    LOWER(PGP_SYM_DECRYPT(nickname::bytea, 'AES_KEY')
    LIKE LOWER('%Rigid%')
);

answered Jun 09 '20 at 06:50

Techie

44,706
42
157
243

It worked. However on a separate not, the lower keyword is not recognised on my end. Is it not supported in PostgreSql 9.6? – Syed Waqas Jun 09 '20 at 07:08
Also, do you have an idea that whether this query will decrypt all the records in the table first and then compare the given text? Or does it decrypt each record one by one and compare separately? – Syed Waqas Jun 09 '20 at 07:11
1

'LOWER' can be removed because I just added it. I'm not sure about the execution cycle. – Techie Jun 09 '20 at 07:17

score 0 · Answer 2 · answered Jun 09 '20 at 12:09

pgp_sym_encrypt uses a salt, so you do not get the same answer each time when encrypting the same value with the same password. As the accepted answer suggests, the answer is to test with decryption, not with encryption.

It seems like setting s2k-mode to 0 should produce repeatable encryption, but experimentally it does not. Perhaps the IV is still random?

PostgreSql: How to query using encrypted columns

2 Answers2