Приклади шифрування даних на рівні таблиць і колонок

Інформація, що зберігається в базі даних, часто має величезну цінність для компанії. Але, на жаль, вона так само приваблива і для зловмисників. Саме тому важливо задуматися про шифрування — це один зі способів захистити дані від сторонніх очей.

Шифрування допомагає вберегти конфіденційну інформацію: наприклад, паролі, номери кредитних карток або персональні дані. Воно також допомагає дотримуватися вимог різних законів, типу 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. Регулярно оновлюй ключі:

Щоб уникнути витоків даних, рекомендується періодично змінювати ключі, при цьому перешифровуючи дані.