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

Il 22/06/2018 19:56, Adrian Klaver ha scritto:
> On 06/22/2018 09:50 AM, Moreno Andreo wrote:
>> Il 22/06/2018 15:18, Adrian Klaver ha scritto:
>
>>> 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');
>
> Assuming the ALTER TABLE was done and then the values where encrypted, 
> that does not seem to affect anything here(More below):
>
> test=# create table pgp_alter_test(id integer, birthdate date);
> CREATE TABLE
> test=# \d pgp_alter_test
>             Table "public.pgp_alter_test"
>   Column   |  Type   | Collation | Nullable | Default
> -----------+---------+-----------+----------+---------
>  id        | integer |           |          |
>  birthdate | date    |           |          |
>
> test=# insert into pgp_alter_test values (1, '2018-06-21');
> INSERT 0 1
> test=# select * from pgp_alter_test ;
>  id | birthdate
> ----+------------
>   1 | 2018-06-21
> (1 row)
>
> test=# alter table pgp_alter_test alter column birthdate type text 
> using to_char(birthdate, 'YYYY-MM-DD');
> ALTER TABLE
>
> test=# \d pgp_alter_test
>             Table "public.pgp_alter_test"
>   Column   |  Type   | Collation | Nullable | Default
> -----------+---------+-----------+----------+---------
>  id        | integer |           |          |
>  birthdate | text    |
>
> test=# select * from pgp_alter_test ;
>  id | birthdate
>
> ----+------------
>
>   1 | 2018-06-21
>
> (1 row)
>
>
>
>
> test=# update pgp_alter_test set birthdate = 
> pgp_sym_encrypt(birthdate, 'AES_KEY') where id = 1;
> UPDATE 1
>
> test=# select * from pgp_alter_test ;
>  id |       birthdate
>
----+------------------------------------------------------------------------------------------------------------------------------------------------------------

>
>   1 | 
>
\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c

>
> (1 row)
>
>
>
>
>
>                                                              ^
>
> test=# select * from pgp_alter_test where 
> pgp_sym_decrypt(birthdate::bytea, 'AES_KEY') = '2018-06-21';
>  id |       birthdate
>
----+------------------------------------------------------------------------------------------------------------------------------------------------------------

>
>   1 | 
>
\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c

>
> (1 row)
>
> I am at a loss now. The only thing I can think of is that data itself 
> is actually corrupted. Maybe some sort of language encoding/collation 
> issue. Just not sure how to test that at the moment.

Actually, I tried it in a bunch of other fields with varying data types 
and everything went fine.
I don't know if it's as you say and I mismatched keys (and I need 
another pair of glasses) or something else. Just hoping (but being 
confident) it won't happen again.

Now trying to speed up a little some queries involving SELECTing among 
these encrypted fields, if I'm stuck I'll open a new thread.

Thanks,
Moreno.-

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