1

I have a JSONB object containing lots of elements and nested objects, and some of the elements are to be encrypted, e.g.:

jsonb_obj = '{"obj1" : {"obj2": {"firstname":"john", "surname":"smith": "bankNo":"Q|%^$ME%T^$"SS"}}}

So only the element bankNo is encrypted. When traversing the entire object I can encrypt using

select jsonb_pretty(jsonb_object_agg(key, pgp_sym_encrypt( CAST(value as text), 'the key'))) as data"

to ensure that the value only gets encrypted in the object. But when decrypting I keep getting the error "wrong key or corrupt data". My question is can individual elements of a JSONB object be encrypted separately and if so how should I do it? Or must encryption take place at a column level?

3
  • How are you performing the decryption? – Ian Barwick Oct 7 '20 at 0:34
  • I perform the encryption by traversing through the object, writing the key and value to a second table and encrypting the value only. So I have a CTE which recursively steps through the object and encrypts the value using SELECT jsonb_pretty(jsonb_object_agg(key, pgp_sym_encrypt( CAST(value as text), 'the key'))) as data". I would have thought decrpyting would be SELECT CAST(value::text as bytea), 'the key') as decrypted_data FROM tmp_table – user172940 Oct 7 '20 at 7:50
  • 1
    I've managed to decrpyt my data by removing the jsonb_pretty function when writing to the temp table. So to answer my own question you can encrypt individual elements of a JSONB structure, but its much quicker to do this at a column level rather than individual JSONB element level – user172940 Oct 7 '20 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.