Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data - Mailing list pgsql-general

Il 22/06/2018 15:18, Adrian Klaver ha scritto:
> On 06/22/2018 01:46 AM, Moreno Andreo wrote:
>> 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.
>
> Are you sure that the entries where not encrypted with a different key 
> because I can't replicate.(More comments below):
(other replies below, inline)
I'm almost sure (you're never absolutely sure :-) ), since I kept all 
commands I entered in PgAdminIII SQL Window, and they're reported above.
On the other side, I tried the same procedure on another field and it 
succeeded.

The only difference between the 2 fields, and I don't know if it can 
make any sense, is that the field I tried now and succeeded was created 
as text, while the other field (dateofbirth) was a timestamp I ALTERed 
with the statement
alter table tbl_p alter column dateofbirth type text using 
to_char(dateofbirth, 'YYYY-MM-DD');

I'm just afraid it can happen in production....

>
> create table pgp_test(id integer, fld_1 varchar);
>
> insert  into pgp_test values (1, pgp_sym_encrypt('2018-06-21', 
> 'AES_KEY'))
>
> select * from pgp_test ;
>
>  id |         fld_1
>
----+------------------------------------------------------------------------------------------------------------------------------------------------------------

>
>   1 | 
>
\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb
>
> select * from pgp_test where pgp_sym_decrypt(fld_1::bytea, 'AES_KEY') 
> = '2018-06-21';
>
> id |       fld_1
>
----+------------------------------------------------------------------------------------------------------------------------------------------------------------

>
>   1 | 
>
\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb
>
>
> Have you looked at the entry in its encrypted state to see if it looks 
> the same as pgp_sym_encrypt('2018-06-21', 'AES_KEY')?
Yes, it seems to have the same value