Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data
От | Adrian Klaver |
---|---|
Тема | Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data |
Дата | |
Msg-id | 6fc4a146-3100-e3b9-e09e-c5bc290b76a8@aklaver.com обсуждение исходный текст |
Ответ на | Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data (Moreno Andreo <moreno.andreo@evolu-s.it>) |
Ответы |
Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data
|
Список | 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
В списке pgsql-general по дате отправления:
Предыдущее
От: Moreno AndreoДата:
Сообщение: Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data
Следующее
От: Andres FreundДата:
Сообщение: Re: Can PostgreSQL create new WAL files instead of reusing old ones?