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
– Sami Kuhmonen, Commented Jun 9, 2020 at 6: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
– Syed Waqas, Commented Jun 9, 2020 at 6:29

Techie · Accepted Answer · 2020-06-09 06:50:50Z

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 9, 2020 at 6:50

Techie

45.2k45 gold badges164 silver badges248 bronze badges

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

3 Comments

Syed Waqas Over a year ago

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 Over a year ago

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?

Techie Over a year ago

'LOWER' can be removed because I just added it. I'm not sure about the execution cycle.

jjanes · Accepted Answer · 2020-06-09 12:09:13Z

1

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?

answered Jun 9, 2020 at 12:09

jjanes

45k5 gold badges39 silver badges48 bronze badges

Collectives™ on Stack Overflow

PostgreSql: How to query using encrypted columns

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related