Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data

Il 21/06/2018 23:31, Adrian Klaver ha scritto:
> On 06/21/2018 08:36 AM, Moreno Andreo wrote:
>> Hi,
>>      while playing with pgcrypto I ran into a strange issue 
>> (postgresql 9.5.3 x86 on Windows 7)
>>
>> Having a table with a field
>> dateofbirth text
>>
>> I made the following sequence of SQL commands
>> update tbl_p set dateofbirth = pgp_sym_encrypt('2018-06-21', 
>> 'AES_KEY') where codguid = '00000001-0001-0001-0001-000000000001';
>> OK
>>
>> select pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') as datanasc 
>> from tbl_p where codguid = '00000001-0001-0001-0001-000000000001'
>> '2018-06-21'
>>
>> select * from tab_paz where pgp_sym_decrypt(natoil::bytea, 'AES_KEY') 
>> = '2018-06-21'
>
> You switched gears above.
>
> What is the data type of the natoil field in table tab_paz?
Sorry, just a typo... natoil is, actually dateofbirth, so it's text.
You can read it as
select * from tbl_p where pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') 
= '2018-06-21'
>
> Was the data encrypted in it using the 'AES_KEY'?
Yes, the command sequence is exactly reported above.
If I use pgp_sym_decrypt in a SELECT statement it's OK, but if it's in a 
where clause it seems not to be working.