Column Level Encryption With pgcrypto On PostgreSQL

First, if the extension is not present, it is created with the following command.

CREATE EXTENSION pgcrypto;

1	CREATE EXTENSION pgcrypto;

PGP_SYM_ENCRYPT('The value to be entered in the column:','AES_KEY')
PGP_SYM_DECRYPT(column_name::bytea,'AES_KEY')

1 2	PGP_SYM_ENCRYPT('The value to be entered in the column:','AES_KEY') PGP_SYM_DECRYPT(column_name::bytea,'AES_KEY')

We create the table with the help of the following script.

Create table den(age integer,password varchar(255));

1	Create table den(age integer,password varchar(255));

The column we want to encrypt is inserted as follows.

insert into den values(21,PGP_SYM_ENCRYPT(‘AAA’,‘AES_KEY’));

1	insert into den values(21,PGP_SYM_ENCRYPT(‘AAA’,‘AES_KEY’));

select age,pgp_sym_decrypt(password::bytea,‘AES_KEY’) from den;

1	select age,pgp_sym_decrypt(password::bytea,‘AES_KEY’) from den;

select * from den;

1	select * from den;

If requested, the password in the database can be updated as follows.

UPDATE den SET
password=(
PGP_SYM_ENCRYPT(‘sss’,‘AES_KEY’)
)WHERE age=21;

UPDATE den SET

password=(

PGP_SYM_ENCRYPT(‘sss’,‘AES_KEY’)

)WHERE age=21;

If you do not want the password to be plain text in the database, you can use the chkpass extension.

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux