[SOLVED] PostgreSql: How to query using encrypted columns

PostgreSql: How to query using encrypted columns

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.

Solution

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%')
);