0

I have a postgres table with a jsonb column, the purpose is to insert flexible documents to then make some searches

Let's begin with the table and some demonstrative data:

create table my_table (id serial primary key, label text, document jsonb);

insert into my_table (label, document) values ('white', '{"name": "john", "lastname": "doe", "eyes": "brown", "birth": "07/08/1979 19:12:55", "pet": "dinosour", "wife": "872", "cat": "no"}');
insert into my_table (label, document) values ('lemon', '{"name": "jane", "lastname": "doe", "birth": "07/08/1978 19:12:55", "cat": "yes"}');
insert into my_table (label, document) values ('white', '{"name": "peter", "eyes": "blue", "birth": "07/08/1980 19:12:55", "pet": "worm", "dog": "yes"}');
insert into my_table (label, document) values ('cyanide', '{"name": "peter", "lastname": "doe", "doormat": "yes"}');

When the data starts to flow in some indexes are being created (this is why the column type is jsonb), for example:

create index on my_table (((document->>'name')::text)) WHERE (document->>'name') is not null and label = 'white';
create index on my_table (((document->>'lastname')::text)) WHERE (document->>'lastname') is not null and label = 'lemon';
create index on my_table (((document->>'doormat')::text)) WHERE (document->>'doormat') is not null and label = 'cyanide';

At this point everything is working as expected, we are able to perform nice searches with good performance, for example: 

select * from my_table
where to_timestamp(document->>'birth', 'DD/MM/YYYY HH24:MI:SS')  
    between to_timestamp('07/08/1979 00:00:00', 'DD/MM/YYYY HH24:MI:SS') 
    and     to_timestamp('07/08/1979 23:59:59', 'DD/MM/YYYY HH24:MI:SS');

select * from my_table where document->>'cat' = 'yes' or document->>'eyes' = 'brown';

The PROBLEM is that now we need to encrypt that information in the database as I usually do with pgcrypto but I can't find a way to encrypt a jsonb type without losing all the indexing power or the field's searching capabilities pushing me back to a very primitive status.

we have found too little information about pgcrypto with jsonb types, for instance: Postgresql - encrypt jsonb data and the pgcrypto documentation don't say anything about jsonb types https://www.postgresql.org/docs/10/static/pgcrypto.html

Is there a way that we could do something like pgp_sym_decrypt(jsonb, 'secret') ? or another mechanism where we can encrypt the data without losing at least the field searching capability ? (we can sacrifice the indexing)

Thanks in advance,

William Añez
  • 730
  • 6
  • 25
  • why do you want to encrypt it? any index on the decrypted value would expose the cleartext data. – Jasen May 13 '18 at 02:53

1 Answers1

0

Thank you @Jasen, your comment got me thinking..., as I said that we can sacrifice the indexing then it shouldn't be no problem on storing the encrypted data as bytea and casting it back to jsonb when needed, using something like: 

... where (pgp_sym_decrypt(document, 'secret')::jsonb)->>'name' = 'peter';
William Añez
  • 730
  • 6
  • 25