I have a jsonb column in my postgres table where I am storing json data. I want to store the data in an encrypted format and be able to query and get the plain text value. Is there a way to do it?
Add a comment
|
1 Answer
Use the pgcrypto extenstion.
create extension if not exists pgcrypto;
If you want to alter an existing jsonb column to encrypted one, change the type of the column to bytea and use a pair of the extension's encryption / decryption functions, e.g.:
create table my_table(id serial primary key, data jsonb);
insert into my_table (data) values
('{"key": "value"}');
alter table my_table
alter data type bytea
using pgp_sym_encrypt(data::text, 'secret_password');
select pgp_sym_decrypt(data, 'secret_password')
from my_table;
pgp_sym_decrypt
------------------
{"key": "value"}
(1 row)
1 Comment
Punter Vicky
Thanks @klin. I'll try!!
