How To Encrypt a Column(Like Password) with chkpass On PostgreSQL

We may need to keep passwords in databases for some reason, and we don’t want anyone to see them, including dba.

In postgresql there are several methods that we can use for this and one of them is extension, which is named chkpass.

Chkpass is an extension. In order to use it, it is better to add contrib package during installation.

In postgresql 10, contrib packages were merged.

If you have installed Contrib packages, you can do the following operations.

If you have not installed, you can read the article “How To Install PostgreSQL Replication On Centos“.

After connecting to the database to be processed, we execute the following command.

To connect to the database, you can read the article “How To Connect To Database On PostgreSQL“.

After installing the Extension, we create a table with chkpass type columns.

We’re loading data into the table for experiment purposes.

Check the data we loaded after making insert.

Our password field has been encrypted.

Different encryption values will be generated even if each insert has the same data.

The column can also be encrypted using the pgcrypto method.

With pgcrypto method, we can also see the passwords by using the pgp_sym_decrypt method.

But no one can see the data in the chkpass column.

Only the pairing method can be used.

To explain with an example;

We can find the column of the above insert by matching it as follows.