Обсуждение: pgp_sym_decrypt() - error 39000: wrong key or corrupt data
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' ERROR: Wrong key or corrupt data ********** Error ********** ERROR: Wrong key or corrupt data SQL state: 39000 Can't find reference anywhere... Any help would be appreciated. Thanks, Moreno.-
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? Was the data encrypted in it using the 'AES_KEY'? I can replicate the below by doing: select pgp_sym_decrypt(pgp_sym_encrypt('2018-06-21', 'AES_KEY'), 'AES'); ERROR: Wrong key or corrupt data > ERROR: Wrong key or corrupt data > ********** Error ********** > > ERROR: Wrong key or corrupt data > SQL state: 39000 > > Can't find reference anywhere... > Any help would be appreciated. > Thanks, > Moreno.- > > > -- Adrian Klaver adrian.klaver@aklaver.com
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.
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): 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')? Can you return decrypted values for other items in the table? > > > > -- Adrian Klaver adrian.klaver@aklaver.com
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
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
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 >> >> >> > >
On 06/27/2018 09:55 AM, Moreno Andreo wrote: > 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: >> >> >> 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. You might try emailing the pgcrypto author https://www.postgresql.org/docs/10/static/pgcrypto.html#id-1.11.7.35.11 In the source code I noticed that there are many: px_debug('Some text') that map to: {PXE_PGP_CORRUPT_DATA, "Wrong key or corrupt data"}. I tried running with messages set to DEBUG to see if I could get at the more specific messages. That did not work, so you might ask the author if there is a way to get at them. > > 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.- > >> >> -- Adrian Klaver adrian.klaver@aklaver.com