Examples of Table-Level and Column-Level Data Encryption

The info stored in your database is often super valuable for your company. But unfortunately, it's just as attractive to hackers. That's why it's important to think about encryption—it's one way to keep your data safe from prying eyes.

Encryption helps protect sensitive stuff: like passwords, credit card numbers, or personal data. It also helps you stay compliant with laws like GDPR or HIPAA. And if a data leak ever happens, encrypted data is way less vulnerable, which means less potential damage.

PostgreSQL has some handy functions for symmetric encryption. With pgp_sym_encrypt(data, key) you can encrypt the data you need, and then decrypt it with pgp_sym_decrypt(encrypted_data, key) using the same key. It's simple—and safe.

Example: Encrypting Data

Step 1: Creating a Table

Let's make a users table with a column that'll store encrypted phone numbers:

-- Creating a table with a column for encrypted data
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    phone_encrypted BYTEA -- This is where encrypted phone numbers will be stored
);

Step 2: Adding Data with Encryption

Now let's add a user, encrypting their phone number:

-- Inserting data with encryption
INSERT INTO users (username, phone_encrypted)
VALUES ('john_doe', pgp_sym_encrypt('123-456-7890', 'my_secret_key'));

Notice how we're using the pgp_sym_encrypt function. my_secret_key is our symmetric key. In real life, your key should be complex and kept super safe.

Step 3: Retrieving Data with Decryption

When it's time to access the data, we can decrypt it:

-- Retrieving data with decryption
SELECT 
    username, 
    pgp_sym_decrypt(phone_encrypted, 'my_secret_key') AS phone
FROM users;

If the key is correct, you'll see the original phone number.

Leveling Up: Adding Encryption to an Existing Table

What if the table already exists and we want to start encrypting data in one of its columns? Let's see how that works.

Step 1: Creating a New Column

Say we have a customers table, and we want to encrypt the credit card number columns:

-- Adding a new column for encrypted data
ALTER TABLE customers ADD COLUMN card_number_encrypted BYTEA;

Step 2: Moving Data into the Encrypted Column

We encrypt the existing data and move it into the new column:

-- Encrypting data and moving it to the new column
UPDATE customers
SET card_number_encrypted = pgp_sym_encrypt(card_number, 'my_other_secret_key');

Step 3: Deleting the Unencrypted Column

After the data is safely encrypted, you can drop the old column:

-- Dropping the old unencrypted column
ALTER TABLE customers DROP COLUMN card_number;

Now your data is protected by encryption, and you can only access it if you have the key.

Working with Encrypted Data: What to Watch Out For

There are a few important things to keep in mind when working with encrypted columns:

Data Type:

• Encrypted values are stored in binary format (BYTEA), not as readable text.
• When querying, you need to use decryption functions.

Searching and Filtering:

• You can't directly search for rows by encrypted data, like this:

  SELECT * FROM users WHERE phone_encrypted = '123-456-7890'; -- WON'T WORK!

• Instead, you can decrypt the data in your query:

  SELECT *
  FROM users
  WHERE pgp_sym_decrypt(phone_encrypted, 'my_secret_key') = '123-456-7890';

Performance:

Encrypting and decrypting data can slow down your queries. Only use it where you really need it.

Real-World Scenario: Protecting Passwords

Storing passwords is one of the most common encryption tasks. Instead of keeping passwords in plain text (bad idea), you should hash them.

Password Hashing with pgcrypto

We'll use the crypt() function to safely hash passwords. First, add a dedicated column for the password hash — don't reuse phone_encrypted, which is meant for encrypted phone numbers (BYTEA):

-- Adding a dedicated column for the password hash
ALTER TABLE users ADD COLUMN password_hash TEXT;

-- Hashing a password when inserting a record
INSERT INTO users (username, password_hash)
VALUES ('alice', crypt('my_secure_password', gen_salt('bf')));

Here, gen_salt('bf') creates a salt for hashing the password.

To check a password, compare its hash:

-- Comparing a hashed password
SELECT username
FROM users
WHERE password_hash = crypt('my_secure_password', password_hash);

Security Tips

1. Store your keys separately:

Never keep your symmetric keys in the same database as your encrypted data.

2. Use strong keys:

Simple keys like "123" are way too easy to guess.

3. Rotate your keys regularly:

To prevent data leaks, it's a good idea to change your keys from time to time, re-encrypting your data as you go.