PostgreSQL Extension - pgcrypto

PostgreSQL Password column Encryption & Decryption use Master Key:

Step 1: create an extension

               pearl=# create extension pgcrypto ;

            

Step 2: create test table

               pearl=# create table test (id numeric, password text);

            

Step 3: insert the value use encryption use master key

               pearl=# insert into test values (1, pgp_sym_encrypt('0912345678'::text,'IJljCQyuuPyBvJMuTH0v'::text));

Step 4:  Select or Decrypt the data

               Decrypt the data use wrong master key:

                    pearl=# select id, pgp_sym_decrypt(password::bytea,'wedeew45ge3') as password from test where id = 1;

             Decrypt the data use correct master key

                    pearl=# select id, pgp_sym_decrypt(password::bytea,'IJljCQyuuPyBvJMuTH0v') as password from test where id = 1;

 

Step 5: update the column use encryption

                    Insert normal text

                    pearl=# insert into test values (2,'muthu');

 

 

                    Update the column

                    pearl=# update test SET password = (PGP_SYM_ENCRYPT('muthu'::text,'IJljCQyuuPyBvJMuTH0v'::text)) where id=2;