Column Level Encryption With pgcrypto On PostgreSQL

Engin Yilmaz

6 years ago

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.