CODEWITHSUNDEEP
postgresqlpostgresql-9.6pgcrypto
Syed Waqas
Syed Waqas

Reputation: 2676

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.

Upvotes: 1

Views: 5999

Answers (2)

jjanes
jjanes

Reputation: 44137

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?

Upvotes: 1

Techie
Techie

Reputation: 45124

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

Upvotes: 1

Related Questions