Encryption and Decryption in Postgres SQL

Question

I have to encrypt and then join 2 tables. Common column for both table is 'NAME'. When I encrypt 'Name',salary, in both tables consecutively, i should get same value for 'name' so that i can join both tables based on that encrypted 'name' value.

Thanks in advance !!!

Table : 1

Name|    Date     |  Salary
A   |  01/01/2019 |  100
A   |  01/02/2019 |  200
B   |  01/01/2019 |  300

Table : 2

Name| Date of Birth |  No of Absence
A   |  10/12/1991   |  10
B   |  10/12/1992   |  20

Answer 1

There are many ways to preform encryption. One of the way is that you can hash the data in columns NAME and Salary with a secret key.

There are a lot of hashing algorithms, including but not limited to md5, sha1, sha256. The pgcrypto module in Postgresql provides some hashing functions for you to use.

No matter which hashing algorithm you use, same value is always being hashed to a same string. For example md5 hash of A is always 7fc56270e7a70fa81a5935b72eacbe29, and that of B is always 9d5ed678fe57bcca610140957afab571. So you can join tables as usual.

Example:

CREATE TABLE table1 (
  "Name" VARCHAR(1),
  "Date" TIMESTAMP,
  "Salary" INTEGER
);

INSERT INTO table1
  ("Name", "Date", "Salary")
VALUES
  (pgp_sym_encrypt('A', 'key'), '01/01/2019', '100'),
  (pgp_sym_encrypt('A', 'key'), '01/02/2019', '200'),
  (pgp_sym_encrypt('B', 'key'), '01/01/2019', '300');


CREATE TABLE table2 (
  "Name" VARCHAR(1),
  "Date of Birth" TIMESTAMP,
  "No of Absence" INTEGER
);

INSERT INTO table2
  ("Name", "Date of Birth", "No of Absence")
VALUES
  (pgp_sym_encrypt('A', 'key'), '10/12/1991', '10'),
  (pgp_sym_encrypt('B', 'key'), '10/12/1992', '20');

SELECT pgp_sym_decrypt(table1."Name", 'key'), table1."Salary", table1."Date", pgp_sym_decrypt(table2."Name", 'key'), table2."Date of Birth", table2."No of Absence" 
    FROM table1 INNER JOIN table2 ON (table1."Name" = table2."Name");

Note that pgcrypto is installed.