I'm attempting to query an encrypted field in postgres using "pgp_sym_encrypt". I'm running my test by setting all the first names in my table to an encrypted value:
update person set first_name = pgp_sym_encrypt('test', 'password');
Then selecting on it:
select * from person where first_name = pgp_sym_encrypt('test', 'password');
This returns no results.
If I change it to use the normal postgres encryption it will return all the rows in the table:
update person set first_name = encrypt('test', 'password', 'aes');
select * from person where first_name = encrypt('test', 'password', 'aes');
My current postgres version is: postgres (PostgreSQL) 9.4.0. The first_name field in this case is a bytea field.
Does anyone know why this is not working using "pgp_sym_encrypt"?
Thanks!
If you look at PostgreSQL Documentation (Appendix F.25. pgcrypto - F.25.3. PGP Encryption Functions):
The given password is hashed using a String2Key (S2K) algorithm. This is rather similar to crypt() algorithms — purposefully slow and with random salt — but it produces a full-length binary key.
(Emphasis mine.)
So the following gives different results every time you run it:
select pgp_sym_encrypt('test', 'password');
When testing the password use pgp_sym_decrypt
instead, it can be tested like this:
select pgp_sym_decrypt(pgp_sym_encrypt('test', 'password'), 'password');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With