Примеры шифрования данных на уровне таблиц и колонок

Информация, хранящаяся в базе данных, часто представляет огромную ценность для компании. Но, к сожалению, она так же привлекательна и для злоумышленников. Именно поэтому важно задуматься о шифровании — это один из способов защитить данные от посторонних глаз.

Шифрование помогает уберечь конфиденциальную информацию: например, пароли, номера кредитных карт или персональные данные. Оно также помогает соблюдать требования разных законов, вроде GDPR или HIPAA. А если вдруг произойдёт утечка, зашифрованные данные будут гораздо менее уязвимы, что снижает потенциальный ущерб.

В PostgreSQL есть удобные функции для симметричного шифрования. С помощью pgp_sym_encrypt(data, key) вы можете зашифровать нужные данные, а потом расшифровать их с помощью pgp_sym_decrypt(encrypted_data, key), используя тот же ключ. Всё просто — и безопасно.

Пример шифрования данных

Шаг 1: Создание таблицы

Создадим таблицу users с колонкой, которая будет хранить зашифрованные номера телефонов:

-- Создание таблицы с колонкой для хранения зашифрованных данных
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    phone_encrypted BYTEA -- Здесь будут храниться зашифрованные номера телефонов
);

Шаг 2: Добавление данных с шифрованием

Теперь добавим пользователя, зашифровав его номер телефона:

-- Вставка данных с шифрованием
INSERT INTO users (username, phone_encrypted)
VALUES ('john_doe', pgp_sym_encrypt('123-456-7890', 'my_secret_key'));

Обратите внимание на использование функции pgp_sym_encrypt. my_secret_key — это наш симметричный ключ. В реальной жизни ключ должен быть сложным и тщательно защищённым.

Шаг 3: Извлечение данных с дешифрованием

Когда приходит время доступа к данным, мы можем их расшифровать:

-- Извлечение данных с дешифрованием
SELECT 
    username, 
    pgp_sym_decrypt(phone_encrypted, 'my_secret_key') AS phone
FROM users;

Если ключ верный, вы увидите исходный номер телефона.

Повышение сложности: добавление шифрования в существующую таблицу

Что если таблица уже существует и мы хотим начать шифровать данные в одной из её колонок? Рассмотрим пример.

Шаг 1: Создание новой колонки

Допустим, у нас есть таблица customers, и мы хотим зашифровать колонки с номерами кредитных карт:

-- Добавление новой колонки для зашифрованных данных
ALTER TABLE customers ADD COLUMN card_number_encrypted BYTEA;

Шаг 2: Перенос данных в зашифрованную колонку

Мы шифруем существующие данные и переносим их в новую колонку:

-- Шифрование данных и перенос в новую колонку
UPDATE customers
SET card_number_encrypted = pgp_sym_encrypt(card_number, 'my_other_secret_key');

Шаг 3: Удаление незашифрованной колонки

После успешного шифрования данных старую колонку можно удалить:

-- Удаление старой незашифрованной колонки
ALTER TABLE customers DROP COLUMN card_number;

Теперь данные защищены шифрованием, и доступ к ним возможен только при наличии ключа.

Особенности работы с зашифрованными данными

Есть несколько важных моментов при работе с зашифрованными колонками:

Тип данных:

• Зашифрованные значения хранятся в бинарном формате (BYTEA), а не в читаемом виде.
• При запросах необходимо использовать функции дешифрования.

Поиск и фильтрация:

• Нельзя напрямую искать строки по зашифрованным данным, например:

  SELECT * FROM users WHERE phone_encrypted = '123-456-7890'; -- НЕ СРАБОТАЕТ!

• Вместо этого можно дешифровать данные для выборки:

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

Производительность:

Шифрование и дешифрование данных могут замедлять запросы. Используйте их только там, где это необходимо.

Реальный сценарий: защита паролей

Хранение паролей — одна из наиболее распространённых задач шифрования. Вместо того, чтобы хранить пароли в открытом виде (плохая идея), их нужно хэшировать.

Хэширование пароля с использованием pgcrypto

Мы будем использовать функцию crypt() для безопасного хэширования паролей:

-- Хэширование пароля при вставке записи
INSERT INTO users (username, phone_encrypted)
VALUES ('alice', crypt('my_secure_password', gen_salt('bf')));

Здесь gen_salt('bf') создаёт соль для хэширования пароля.

Чтобы проверить пароль, сравним его хэш:

-- Сравнение хэшированного пароля
SELECT username
FROM users
WHERE crypt('my_secure_password', phone_encrypted) = phone_encrypted;

Советы по безопасности

1. Храните ключи отдельно:

Никогда не сохраняйте симметричные ключи в одной базе данных с зашифрованными данными.

2. Используйте сложные ключи:

Простые ключи, такие как "123", могут быть легко угаданы.

3. Регулярно обновляйте ключи:

Для предотвращения утечек данных рекомендуется периодически изменять ключи, при этом перешифровывая данные.

2

Задача

SQL SELF, 48 уровень, 2 лекция

Недоступна

Обновление таблицы для шифрования существующих данных

Обновление таблицы для шифрования существующих данных

Открыть