How to encrypt and decrypt Table data in postgres

For encrypting and decrypting , we must use the bytea data type on the column which we implement. Bcoz bytea will use the pgcrypto method by default.

However, you will need to create the pgcrypto extension to enable these functions as they are not pre-defined in PostgreSQL/PPAS.

Example

CREATE EXTENSION pgcrypto;

CREATE TABLE userinfo (username varchar(20), password bytea);

>> Inserting the data in an encrypted format

INSERT INTO userinfo VALUES(' suman ',encrypt('111222','password','aes'));

select * from userinfo ;

>> Retrieving the data as decrypted format

SELECT decrypt(password,decode('password','escape'::text),'aes'::text) FROM userinfo;

Thanks for reading

The Young DBA