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

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.
 

> 
> 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
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com