Encryption & Decryption for PostgreSQL (Command-Line)

Hello Everyone,

Today lets see how to encrypt and decrypt the data using pgcrypto extension.

Encryption and decryption are important aspects of database security. In this blog post, we will explore how to encrypt and decrypt data in PostgreSQL using command-line tools.

First, let's discuss the basics of encryption and decryption. Encryption is the process of converting plaintext data into a ciphertext format that can only be read by authorized users. Decryption is the reverse process of converting ciphertext data back into its original plaintext format.

PostgreSQL supports several encryption and decryption functions that can be used to secure your data. One of the most popular encryption methods in PostgreSQL is the pgcrypto extension, which provides several cryptographic functions that can be used to encrypt and decrypt data.

Now lets see how to encrypt and  decrypt the data at column level using pgcrypto extension.

1. First lets create a sample database named eminds using command CREATE DATABASE eminds; :-

We have created a new database.

2.Now, lets change/enter into eminds database using /c eminds  :

We have changed/entered into eminds database.

3.Now lets add pgcrypto extension to our eminds database using  CREATE EXTENSION IF NOT EXISTS pgcrypto; :- 

Here the extension is created and added to the database to check if it the extention is added or not we can use SELECT * FROM pg_extension WHERE extname = 'pgcrypto'; :-

4.Now lets create a table with some columns using CREATE TABLE emp (emp_id SERIAL PRIMARY KEY,name varchar,depart varchar);  :-

Here is the SQL command to create a table named emp with columns emp_id, name, and depart.

In this command, we are using the SERIAL data type for the emp_id column to automatically generate unique values for each new row inserted into the table. The PRIMARY KEY constraint on this column ensures that each value is unique and identifies a unique record in the table.

The name and depart columns are of type varchar, which can store variable-length character strings.

Once you execute this command, the emp table will be created with the specified columns and constraints.

NOTE:- to display the use \dt command.

5.Now lets enter few values of  data into emp table :-

This command will insert five rows of data into the emp table, with each row representing a single employee. We are specifying values for the emp_id, name, and depart columns for each row.

Note that the emp_id column is a SERIAL data type, so we do not need to provide a value for it. PostgreSQL will automatically generate a unique value for each new row inserted into the table.

Use Select * from emp; to display the values inside the emp table:

6. Now lets encrypt the data in name and depart columns using :

The SQL query provided will update the "name" and "depart" columns in the "emp" table by encrypting their values using the pgp_sym_encrypt function with the passphrase "bhanu". Specifically, it will convert the "name" and "depart" values to text data type (if they are not already text) using the ::text cast operator, and then encrypt them using the pgp_sym_encrypt function.

Note that this update will permanently encrypt the values in the "name" and "depart" columns, making them unreadable without the passphrase used for encryption. If you want to be able to decrypt these values later, you should make sure to store the passphrase securely and use the pgp_sym_decrypt function to retrieve the original values.

Use Select * from emp; to display the values inside the emp table which are encrypted :-

7. To decrypt and display the data use :-

The SQL query you provided will select and decrypt the "name" and "depart" columns from the "emp" table using the pgp_sym_decrypt function with the passphrase "bhanu". Specifically, it will select the "name" and "depart" values as bytea data type (if they are encrypted using pgp_sym_encrypt) using the ::bytea cast operator, and then decrypt them using the pgp_sym_decrypt function with the same passphrase.

This query assumes that the "name" and "depart" columns in the "emp" table have been encrypted using the pgp_sym_encrypt function with the passphrase "bhanu". If the columns have been encrypted using a different passphrase or a different encryption method, this query may not work as expected.

Also, note that the decrypted values will only be readable if the passphrase used for decryption is correct. If the passphrase is incorrect or missing, the decrypted values will be gibberish or null.

if you have encrypted the "name" and "depart" columns using the pgp_sym_encrypt function in your SQL database, then the data in those columns will be stored in encrypted format in the database.

When you query the data using the SQL statement provided earlier to decrypt the values, you will be able to see the decrypted values. However, if you view the data directly in pgAdmin or any other database management tool, the "name" and "depart" columns will be displayed in their encrypted format.

This is because the database management tool does not automatically decrypt the data in the columns for security reasons. The encrypted data will be displayed as binary or encoded characters, depending on the encoding used by the database. To view the decrypted values, you would need to execute the SQL query that includes the pgp_sym_decrypt function as shown earlier.

Thank you

Bhanu Prakash Reddy (Intern)

shield warriors,

Data Shield Team,

Enterprise Minds.