Safeguarding User Data in Django (Part 2): Database Encryption/Decryption

This article will delve into the technics for data encryption/decryption in databases.

Please check the source code on GitHub (pgcrypto branches).

adapted from [link]

Introduction

In the previous section, we discussed the importance of data encryption and techniques for doing this in the programming language (Python). In the current section, we will focus on the data encryption in the database. We will use an extension for doing so, called Pgcrypto.

Pgcrypto is a popular extension for the PostgreSQL database system that provides cryptographic functions and data encryption capabilities. It is designed to enhance the security features of PostgreSQL and enable users to encrypt sensitive data, such as passwords, credit card numbers, and other confidential information.

One of the key features of pgcrypto is its wide range of cryptographic functions. It supports various encryption algorithms, including symmetric-key encryption, asymmetric-key encryption, and hash functions. This allows users to choose the most appropriate algorithm for their specific security needs. Whether it’s encrypting data at rest or securing data transmission over the network, pgcrypto provides a robust set of tools to address different cryptographic requirements.

Symmetric-key algorithms are algorithms for cryptography that use the same cryptographic keys for both the encryption of plaintext and the decryption of ciphertext. (link)

link

With pgcrypto, users can easily encrypt and decrypt data within the PostgreSQL database. It offers functions to encrypt data using symmetric-key algorithms like AES (Advanced Encryption Standard) and DES (Data Encryption Standard). The encrypted data can then be stored securely in the database, protecting it from unauthorized access. Similarly, pgcrypto provides functions for decrypting the encrypted data when needed, allowing authorized users to retrieve and use the original information.

Furthermore, pgcrypto also supports asymmetric-key encryption, commonly known as public-key encryption. This encryption method uses a pair of keys: a public key for encrypting the data and a private key for decrypting it. This approach is particularly useful for scenarios where multiple users or systems need to securely exchange information. Pgcrypto integrates these asymmetric encryption capabilities, allowing users to generate key pairs, encrypt data with public keys, and decrypt it with the corresponding private keys.

Public-key cryptography, or asymmetric cryptography, is the field of cryptographic systems that use pairs of related keys. Each key pair consists of a public key and a corresponding private key. (link)

link

In addition to encryption, pgcrypto offers a variety of hash functions for generating message digests or digital signatures. Hash functions such as SHA-256 and MD5 can be used to ensure data integrity and verify the authenticity of the information. These functions compute a unique hash value for a given input, making it practically impossible to reverse-engineer the original data from the hash. By comparing hash values, users can verify that the data has not been tampered with and detect any unauthorized modifications.

A hash function is any function that can be used to map data of arbitrary size to fixed-size values, though there are some hash functions that support variable length output. The values returned by a hash function are called hash values, hash codes, digests, or simply hashes. The values are usually used to index a fixed-size table called a hash table. Use of a hash function to index a hash table is called hashing or scatter storage addressing. (link)

link

In summary, pgcrypto is a powerful extension for PostgreSQL that brings robust cryptographic capabilities to the database system. It empowers users to encrypt sensitive data, choose from various encryption algorithms, and leverage hash functions for data integrity and authentication. With pgcrypto, PostgreSQL becomes a more secure platform for storing and managing confidential information, helping organizations protect their data and meet their security requirements.

How to secure Django models with pgcrypto?

When it comes to using pgcrypto with Django, there are a few Python packages that can help simplify the integration process. One such package is django-pgcrypto-fields, which provides custom model fields for encrypting and decrypting data using pgcrypto within Django's ORM. This package allows you to define encrypted fields in your Django models, specifying the encryption algorithm and key. It seamlessly handles the encryption and decryption operations, making it easy to work with encrypted data while maintaining the convenience of Django's ORM.

Encryption/Decryption takes place in the Database

pgcrypto Installation

Django will automatically create the extension on the first migration. Even though you can create the extension using PSQL or PgAdmin, connecting to the DB and executing:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

You can install django-pgcrypto-fields by:

$ pip install django-pgcrypto-fields

Symmetric Key Encryption

Here is the Django model design with symmetric key encrypted fields

class CustomUserModel(AbstractUser):
    """
    encrypted user model
    """

    username_validator = UnicodeUsernameValidator()

    id = HashidAutoField(primary_key=True)
    username = fields.CharPGPSymmetricKeyField(
        _("username"),
        max_length=150,
        unique=True,
        help_text=_(
            "Required. 150 characters or fewer. Letters, digits and @/./+/-/_ only."
        ),
        validators=[username_validator],
        error_messages={
            "unique": _("A user with that username already exists."),
        },
    )
    first_name = fields.CharPGPSymmetricKeyField(
        _("first name"), max_length=150, blank=True)
    last_name = fields.CharPGPSymmetricKeyField(_("last name"), max_length=150, blank=True)
    email = fields.EmailPGPSymmetricKeyField(_("email address"), blank=True, unique=True)
    phone = fields.CharPGPSymmetricKeyField(_("mobile phone"), max_length=13)

    class Meta:
        verbose_name = _("user")
        verbose_name_plural = _("users")
        db_table = "encrypted_users"

Binary data allocation on Database for symmetrically encrypted fields

decrypted user data on REST API

Raw SQL queries with pgcrypto functions (symmetric algorithm)

pgp_sym_encrypt

Here are some use cases of pgp_sym_encrypt to encrypt data:

1. INSERT

insert and encrypt data

2. UPDATE

update and encrypt data

pgp_sym_decrypt

Here are some use cases of pgp_sym_decrypt to decrypt data:

3. SELECT

decrypted username field of records

4. WHERE

using pgp_sym_decrypt in WHERE clause

Asymmetric Key Encryption

At first, we need to generate secret keys. (I don't set a passphrase, it’s on your own to do so.)

$ gpg --gen-key

$ gpg --list-secret-keys

gpg: checking the trustdb
gpg: marginals needed: 3  completes needed: 1  trust model: pgp
gpg: depth: 0  valid:   1  signed:   0  trust: 0-, 0q, 0n, 0m, 0f, 2u
gpg: next trustdb check due at 2025-06-28
/home/user/.gnupg/pubring.kbx
-----------------------------
sec   rsa3072 2023-06-29 [SC] [expires: 2025-06-28]
      81F42B706CDD717DCD9928983643CCCD5AB7A085
uid           [ultimate] testname <example@example.com>
ssb   rsa3072 2023-06-29 [E] [expires: 2025-06-28]

$ gpg -a --export 'example@example.com' > public.key

$ gpg -a --export-secret-keys 'example@example.com' > private.key

Now we should place .key files in the project root directory as follow.

For executing queries from PgAdmin, I prefer to place a copy of .key files in the Postgresql data directory. You will find this directory by executing bellow query:

SELECT setting FROM pg_settings where name='data_directory';

Django model design with asymmetric key encrypted fields

class CustomUserModel(AbstractUser):
    """
    encrypted user model
    """

    username_validator = UnicodeUsernameValidator()

    id = HashidAutoField(primary_key=True)
    username = fields.CharPGPPublicKeyField(
        _("username"),
        max_length=150,
        unique=True,
        help_text=_(
            "Required. 150 characters or fewer. Letters, digits and @/./+/-/_ only."
        ),
        validators=[username_validator],
        error_messages={
            "unique": _("A user with that username already exists."),
        },
    )
    first_name = fields.CharPGPPublicKeyField(
        _("first name"), max_length=150, blank=True)
    last_name = fields.CharPGPPublicKeyField(_("last name"), max_length=150, blank=True)
    email = fields.EmailPGPPublicKeyField(_("email address"), blank=True, unique=True)
    phone = fields.CharPGPPublicKeyField(_("mobile phone"), max_length=13)

    class Meta:
        verbose_name = _("user")
        verbose_name_plural = _("users")
        db_table = "encrypted_users"

Binary data allocation on Database for asymmetrically encrypted fields

decrypted user data on REST API

Raw SQL queries with pgcrypto functions (asymmetric algorithm)

pgp_pub_encrypt

Here are some use cases of pgp_pub_encrypt to encrypt data:

1. INSERT

insert and encrypt data

2. UPDATE

update and encrypt data

pgp_pub_decrypt

Here are some use cases of pgp_pub_decrypt to decrypt data:

3. SELECT

decrypted username field of records

4. WHERE

using pgp_sym_decrypt in WHERE clause

Conclusion

The importance of avoiding storing user data in plain text format within databases cannot be overstated. Plain text storage poses significant security risks, including password vulnerability, identity theft, compliance challenges, insider threats, and amplified consequences of data breaches. To address these risks, organizations must adopt secure data storage practices. Implementing encryption, salting, access controls, regular auditing, and user education can significantly enhance data security and protect sensitive information. By prioritizing secure data storage, organizations can build trust, mitigate the risks of data breaches, and demonstrate their commitment to safeguarding user privacy in an increasingly interconnected world.