Summary

The web content provides a detailed guide on using the pgcrypto extension in PostgreSQL for encryption and decryption of data within the database.

Abstract

The article titled "How To Use pgcrypto Extension in Postgresql" delves into the use of the pgcrypto extension to enhance PostgreSQL with cryptographic functions. It explains that pgcrypto supports various cryptographic operations such as encryption, decryption, hashing, and digital signatures, and requires OpenSSL to be installed alongside PostgreSQL. The guide illustrates practical steps for encrypting and decrypting sensitive data, such as passwords and emails, using the pgcrypto functions pgp_sym_encrypt and pgp_sym_decrypt. It demonstrates the creation of a table, the addition of the pgcrypto extension, and the insertion of encrypted data. The article also discusses the retrieval and decryption of data, the creation of views for simplifying access to decrypted data, and the security considerations of managing secret keys within the database.

Opinions

The author emphasizes the importance of key management in real-world scenarios, suggesting that the example provided uses a simple symmetric key for demonstration purposes, but more complex key management would be necessary for production environments.
There is a concern raised about the visibility of secret keys when using views, as other users could potentially obtain the secret key details by examining the view structure.
The article suggests a best practice of storing secret keys in a separate table and using a join operation to decrypt data, which enhances security by preventing non-privileged users from accessing the keys.
The author implies that the pgcrypto extension is a powerful tool for maintaining data privacy directly within the PostgreSQL database, eliminating the need for external encryption mechanisms.

How To Use pgcrypto Extension in Postgresql

Here, we are going to explore encryption and decryption using pgcrypto in detail

What is a Pgcrypto?

The `pgcrypto` extension enhances PostgreSQL by introducing a suite of cryptographic functions directly within the database environment.
It supports a broad spectrum of cryptographic operations, such as encryption and decryption, hashing, and the creation and verification of digital signatures.
pgcrypto requires OpenSSL and won't be installed if OpenSSL support was not selected when PostgreSQL was built.

Let us see an example of using pgcrypto for password encryption:

S-1: Create a table

First, we’ll create a table named Team_india. This table will store the playername, the encrypted mobile, and an email (also encrypted for demonstration purposes).
We'll use the bytea data type for the encrypted fields to accommodate binary data resulting from the encryption process.

CREATE TABLE Team_india(
    user_id serial PRIMARY KEY,
    Playername varchar(255) NOT NULL UNIQUE,
    Player_mobileno bytea NOT NULL,
    Player_email bytea NOT NULL
);

S-2: Create an extension

CREATE EXTENSION PGCRYPTO;

Verify the extension details using \dx and function details \df

S-3: Insert Sample Data

To insert data into our Team_india table, we'll encrypt the password and email fields using the pgp_sym_encrypt function.
Assume we use a simple symmetric key for this example, though in a real-world scenario, key management practices would be more complex.

INSERT INTO Team_india ( playername, player_mobileno,player_email)
VALUES
('Dhoni', pgp_sym_encrypt('12345890', 'Team_India_Icici'), pgp_sym_encrypt('[email protected]', 'Team_India_Icici')),
('Kohli', pgp_sym_encrypt('1234567890', 'Team_India_Icici'), pgp_sym_encrypt('[email protected]', 'Team_India_Icici'));

S-4: Querying and Decrypting Data

When you need to authenticate a user or display their email, you will decrypt the data on the fly using the pgp_sym_decrypt function.
Here's how you can retrieve and decrypt the email address of a specific user:

--without decryption details

select * from Team_india
;
-[ RECORD 1 ]---+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
user_id         | 1
playername      | Dhoni
player_mobileno | \xc30d04070302a98d119eed7407fc67d23901bcedab7e27f3ce682c12f2caf1c284c9baeea88f2f71b29237d97e9f4b0b7c54e1027ffcbd81e03e90be54e0f7ca5c334081dff0fac7960d
player_email    | \xc30d040703025c05aa90e503958766d23f016dc0e02c65fc35d4ad7929033318e54a1d61033403c9493fd1b264e549e9651b08ebc5922d380ad9a8808a1cb31a248ae748a8ecdb58d48f12218e846717
-[ RECORD 2 ]---+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
user_id         | 2
playername      | Kohli
player_mobileno | \xc30d0407030270ea3745e3c4c6c37fd23b017a0da57ec482d5adb2d3dbff74752b4c38a23f425ada70dc40160d01571b13149dd27a6c0a90da74ab3633960dfb4db5277ad8f0cd8c32110ef8
player_email    | \xc30d040703029f1c48e2c47226cd7fd2430121a89f476437b01d42b04b0819aeb370c53f6d0f6f1ac09bd14a2bb577ffa9e2211113bdafa414658b0d25cd40e5507748d6143f69e5caa7bd368c77353ed384665b



--using a secret key to decryption

select playername,
pgp_sym_decrypt(player_mobileno, 'Team_India_Icici') as Mobileno,
pgp_sym_decrypt(player_email, 'Team_India_Icici') as email
from 
Team_india;

-[ RECORD 1 ]------------------
playername | Dhoni
mobileno   | 12345890
email      | mahi@gmail.com
-[ RECORD 2 ]------------------
playername | Kohli
mobileno   | 1234567890
email      | Viruksha@gmail.com

S-5: Storing Secret Key in a View

Sometimes, we might find it cumbersome to manually execute the select query every time. Thus, we can create a view based on the select decryption query.
This allows anyone with access to simply read the data without needing the secret key, just by using the view.

CREATE VIEW team_india_key AS
SELECT 
 playername,
 pgp_sym_decrypt(player_mobileno, ‘Team_India_Icici’) AS Mobileno,
 pgp_sym_decrypt(player_email, ‘Team_India_Icici’) AS email
FROM 
 Team_india;

However, a problem arises here: even if a user doesn’t have access to this view, they can see the structure of the view. This means other users can easily obtain the secret key details.

\d+ team_india_key
                                 View "public.team_india_key"
   Column   |          Type          | Collation | Nullable | Default | Storage  | Description 
------------+------------------------+-----------+----------+---------+----------+-------------
 playername | character varying(255) |           |          |         | extended | 
 mobileno   | text                   |           |          |         | extended | 
 email      | text                   |           |          |         | extended | 
View definition:
 SELECT team_india.playername,
    pgp_sym_decrypt(team_india.player_mobileno, 'Team_India_Icici'::text) AS mobileno,
    pgp_sym_decrypt(team_india.player_email, 'Team_India_Icici'::text) AS email
   FROM team_india;

To mitigate this issue, we can create a separate table to store the key and then pass it into the view. This ensures that other users cannot read the stored key details.

S-6: Create a table to store secret-key values

create table Team_india_keys (
    key_id integer NOT NULL,
    secret_key text NOT NULL,
    CONSTRAINT Team_india_keys_pk PRIMARY KEY (key_id)
    ); 


INSERT INTO Team_india_keys (key_id, secret_key) VALUES (1, 'Team_India_Icici');

S-7: Decrypt the values

We can decrypt the values like below without using them anywhere

select playername,
pgp_sym_decrypt(player_mobileno, dec_key.seckey) as Mobileno,
pgp_sym_decrypt(player_email, dec_key.seckey) as email
from                                                  
Team_india
cross join
(select secret_key as seckey from Team_india_keys) as dec_key;

 playername |  mobileno  |       email        
------------+------------+--------------------
 Dhoni      | 12345890   | mahi@gmail.com
 Kohli      | 1234567890 | Viruksha@gmail.com
(2 rows)

You can also create a view based on the above select query. This ensures that non-privileged users cannot read the secret key value unless they have access to the `Team_india_keys` table.