Searching encrypted field in Postgres

Question

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!

Simo Kivistö · Accepted Answer · 2015-03-18 14:58:12Z

8

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

edited Mar 18, 2015 at 14:58

answered Mar 18, 2015 at 14:52

Simo Kivistö

4,6033 gold badges41 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dalton001 Over a year ago

So if I wanted to do a search on data encrypted with a S2K algorithm would my only option be to setup a separate index on a secure hash of that data?

Simo Kivistö Over a year ago

Search would be something like this? select * from person where 'test' = pgp_sym_decrypt(first_name, 'password');

Collectives™ on Stack Overflow

Searching encrypted field in Postgres

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related