GDPR Encrypt existing columns in postgresql

Question

We have a postgresql database, as part of GDPR, we need to encrypt some of the column data, I tried searching on internet but couldn't get any concrete answer on how we can achieve this.

For example: let's suppose we have a table as below:

     Table "public.demo"
   Column   | Type | Modifiers
------------+------+-----------
 firstname  | text |
 secondname | text |
 city       | text |

and it already have data in it:

 firstname | secondname | city
-----------+------------+------
 john      | doe        | ban
 hich      | ke         | can
 val       | nuti       | syd
(3 rows)

Now, I need to encrypt only the firstname let's suppose, so basically I need to invoke a script which would encrypt the first name and while reading from java code, we can decrypt and read.

The above needs to work in case of insert queries as well.

why don't you convert into byte array. if you will used any hashing technique then you be not able to decrypt further. — Kandy
– Kandy, Commented Dec 28, 2018 at 12:41
thnx the existing table columns are mix of all, bytea, text, varchar, so the above was just an example, and would need to use encryption(aes,des etc) , not sure if we use hashing we can decrypt later , pls help — Rajesh Chandrashekar
– Rajesh Chandrashekar, Commented Dec 28, 2018 at 12:49
then use java code to encrypt or decrypt it will be more efficient to you. — Kandy
– Kandy, Commented Dec 28, 2018 at 12:51
I tried using java , to read , encrypt, update, it takes lot of time, hence checking if there is a way using postgresql, which could be leveraged — Rajesh Chandrashekar
– Rajesh Chandrashekar, Commented Dec 28, 2018 at 16:24

arso · Accepted Answer · 2018-12-28 13:26:27Z

2

If you're using JPA, you can use Postgres's pgcrypto module and then use @ColumnTransformer in your JPA entities: Tutorial here.

If you're using plain old java, the same concept applies but you'll have much more work to do server-side, as there are not "TRIGGER ON SELECT" in postgresql (you could have used it to encrypt on update/insert and decrypt on select).

answered Dec 28, 2018 at 13:26

arso

2,3671 gold badge22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

GDPR Encrypt existing columns in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related