CODEWITHSUNDEEP
postgresql
Rajesh Chandrashekar
Rajesh Chandrashekar

Reputation: 51

GDPR Encrypt existing columns in postgresql

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.

Upvotes: 0

Views: 1280

Answers (1)

Asoub
Asoub

Reputation: 2371

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).

Upvotes: 2

Related Questions