Encrypting columns in PGD

Encrypting columns in PGD#

列の暗号化は、テーブルの機密フィールドをスクランブルされた読み取り不能形式の暗号テキストに変換することにより保護しますが、レコードの残りの部分は元の人間が判読可能な形式のままにします。暗号化はINSERT またはUPDATE 中にSQLレベルで発生するため、暗号文のみが書き込み先行ログWALに到達し、PGDノード全体でレプリケートされ、保存、バックアップ、および転送中の機密データが保護されたままになります。

分散クラスターでは、列暗号化は次の利点も提供します。

  • ノードの独立性 アプリケーションは、一貫した復号化のために任意のノードにキーを提供できます。

  • 攻撃面の削減 キーが平文でWALに入力されることはありません。暗号文のみが複製されます。

  • メモリ分離 セッションが終了すると、キーはクリアされます。

PGDは、次を介して列レベルの暗号化をサポートしています。

  • pgcrypto コミュニティPostgres拡張機能。EDBパッケージとしては出荷されませんが、 PostgreSQL、 EDB Postgres ExtendedPGE、およびEDB Postgres Advanced Serverで使用する場合にサポートされています。

  • DBMS_CRYPTO EPASでのみ入手可能なOracle互換パッケージ。

どちらもPGDレプリケーションと完全な互換性があります。 pgcrypto またはDBMS_CRYPTO を使用する場合、データはヒープに書き込まれる前に暗号化ファンクションを使用して変換されます。 pgcryptoは、結果の暗号文をbytea として保存し、 DBMS_CRYPTOはRAW 、BLOB 、またはCLOB を使用します。これらはすべてPGDがネイティブにレプリケートできます。

暗号化方法の比較#

列の暗号化は、PGDのデータを保護するためのいくつかのアプローチの1つです。それぞれがスタックの異なるレベルで動作し、さまざまな脅威から保護します。

Transparent Data Encryption (TDE) は、ストレージレベルでデータを暗号化するPGEおよびEPASのオプション機能であり、暗号化キーがなしではディスク上のデータベースファイルを読み取ることができません。 TDEはPGDレプリケーションと並行して透過的に動作しますが、実行中のデータベースにアクセスし、すべてのデータをプレーンテキストで照会できるDBAに対する保護はありません。

Data redaction は、ポリシーに基づいてクエリ結果の機密値をマスクするEPASの機能です。ディスク上の基になるデータは変更されないため、TDEまたは列暗号化と組み合わせて、意味のある保護を提供する必要があります。

列の暗号化はフィールドレベルで保護します。これは、上記のいずれよりも詳細です。セッションレベルの復号キーを持たないDBAは、ライブクエリーであっても、暗号文のみを参照します。トレードオフは、アプリケーションがキーを管理し、暗号化と復号化を明示的に処理する必要があることです。

Technique

Protection level

Key managed by

Protects against

Visible to DBAs

TDE

Block/storage-level

Database/KMIP

Physical disk theft or OS-level access

Yes

Data redaction

Presentation-level

Database policy

Unauthorized app users

Yes

Column encryption

Field-level

Application

Compromised DBAs or system admins

No (ciphertext only)

分散クラスターでの暗号化の計画#

PGDで列を暗号化すると、シングルノード設定では発生しない考慮事項が発生します。列の暗号化を実装する前に、インデックス作成、CPUオーバーヘッド、およびストレージとレプリケーションへの影響を考慮してください。

暗号化された列のインデックス作成と検索の処理#

pgcryptoと DBMS_CRYPTOは両方とも、デフォルトで確率的ランダム化暗号化を使用します。つまり、同じ平文を複数回暗号化すると、毎回異なる暗号文が生成されます。 pgcryptoはpgp_sym_encrypt() を介してこの動作を実現し、 DBMS_CRYPTOはCBCモードのAESを介してこれを行います。

ランダム化された暗号化には2つの重要な意味があります。

  • インデックスの非互換性 暗号テキスト列の標準のBツリーインデックスは、プレーンテキストの等価ルックアップをサポートできません。

  • クエリーの失敗 次の一般的なパターンは、pgp_sym_encrypt() への各呼び出しが列に格納されているものと一致しない異なる暗号文を生成するため、レコードが存在する場合でも結果ゼロを返します。エラーは発生しないため、失敗は存在しないレコードと間違われやすくなります。

SELECT * FROM customers
WHERE email = pgp_sym_encrypt(alice@example.com, your_key);

特にpgcryptoでは、列タイプがbytea ではなくtext である場合、Postgresは代わりに型不一致エラーを発生させます。

ERROR: operator does not exist: text = bytea

正確な値で暗号化された列を検索するには、次のいずれかの戦略を使用します。

  • 決定的暗号化 特定の平文に対して常に同じ暗号文を生成するように暗号化アルゴリズムを構成します。

  • ブラインドインデックス推奨 pgcryptoのhmac() のようなファンクションを使用して、平文の一方通行暗号ハッシュを含む追加列を保存し、代わりにその列にインデックスを付けます。クエリは検索値をハッシュし、暗号化された列ではなくインデックスと比較します。

両方のアプローチの実装の詳細については、

pgcrypto documentation または DBMS_CRYPTO reference を参照してください。

警告

どちらのアプローチも等価パターンを明らかにします。格納された値を観察できる攻撃者は、2つの行に同じデータが含まれる場合を推論でき、状態フラグや国コードのような低品種フィールドでは、平文を完全に推定できます。

CPUオーバーヘッドの考慮#

暗号化と復号化は、データベースサーバーで実行される計算量の多い操作です。ノードに十分なCPUヘッドルームがあることを確認します。 AES-NI命令セットをサポートするプロセッサーのAESなど、ハードウェアアクセラレーションを活用するアルゴリズムを使用することにより、この影響を大幅に削減できます。

ストレージとWALの成長の計画#

暗号文は元の平文より大きいため、テーブルサイズ、WALボリューム、およびPGDがノードを同期するために必要なレプリケーション帯域幅が増加します。それに応じて容量を計画します。

pgcryptoを使用したカラムの暗号化#

pgcryptoは、pgp_sym_encrypt() やpgp_sym_decrypt() などの暗号化機能を提供するコミュニティPostgres拡張機能です。 PostgreSQL、PGE、およびEPASで動作します。 ビューは透過的な復号を処理し、トリガーは書き込み時に自動暗号化を処理するため、アプリケーションは暗号化機能を直接呼び出さずに平文の読み取りおよび書き込みができます。

  1. 拡張機能を作成します。

CREATE EXTENSION IF NOT EXISTS pgcrypto;

  1. bytea として定義された暗号化列を使用してcustomers テーブルを作成します。

CREATE TABLE customers (
    customer_id   uuid    DEFAULT uuidv7() PRIMARY KEY,
    username      TEXT    NOT NULL,
    email         BYTEA   NOT NULL
);

  1. 透過的な復号化のためのビューを作成します。

-- Note: requires SET app.pgc_key = your_key to be run in the session first
CREATE OR REPLACE VIEW customers_view AS
SELECT
    customer_id,
    username,
    pgp_sym_decrypt(email, current_setting(app.pgc_key)) AS email
FROM customers;

  1. データが書き込まれる前に自動的に暗号化するトリガーを作成します。

CREATE OR REPLACE FUNCTION encrypt_customer_pii_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF current_setting(app.pgc_key, true) IS NULL OR current_setting(app.pgc_key) =  THEN
        RAISE EXCEPTION Encryption key not set. Please run SET app.pgc_key = ...;
    END IF;

    IF (TG_OP = INSERT OR NEW.email != OLD.email) THEN
        NEW.email := pgp_sym_encrypt(
            convert_from(NEW.email, utf8),
            current_setting(app.pgc_key),
            cipher-algo=aes256
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_encrypt_customer_pii
BEFORE INSERT OR UPDATE ON customers
FOR EACH ROW EXECUTE FUNCTION encrypt_customer_pii_trigger();

  1. ビューとトリガーを配置したら、セッションキーを設定し、通常のようにテーブルと対話します。

-- 1. Initialize session key
SET app.pgc_key = your_secret_key;

-- 2. Trigger handles encryption automatically
INSERT INTO customers (username, email) VALUES (dolores, dolores@example.com);

生のcustomers テーブルを照会すると、 email 列が暗号文を格納するのに対し、 customers_view は復号化された平文を返すことがわかります。

SELECT customer_id, username, email FROM customers;
__OUTPUT__
 customer_id                         | username | email
- ------------------------------------+----------+------------------------------------------------
 01966e42-1af7-7a2a-b4b9-9cf8e0d3c2a | dolores  | \xc30d04070302e4a7f23b8c12d956aa...
(1 row)

SELECT customer_id, username, email FROM customers_view;
__OUTPUT__
 customer_id                         | username | email
- ------------------------------------+----------+---------------------
 01966e42-1af7-7a2a-b4b9-9cf8e0d3c2a | dolores  | dolores@example.com
(1 row)

DBMS_CRYPTOを使用した列の暗号化#

DBMS_CRYPTO は、EPASでのみ使用可能なOracle互換パッケージです。 Oracle DBMS_CRYPTO APIをミラーする暗号化および復号化機能を提供します。 pgcryptoと同様に、ビューは透過的な復号化を処理し、トリガーは書き込み時に自動暗号化を処理します。

  1. RAW として定義された暗号化列を使用してcustomers テーブルを作成します。

CREATE TABLE customers (
    customer_id   uuid     DEFAULT uuidv7() PRIMARY KEY,
    username      TEXT     NOT NULL,
    email         RAW(128)
);

  1. 透過的な復号化のためのビューを作成します。

CREATE OR REPLACE VIEW customers_view AS
SELECT
    customer_id,
    username,
    UTL_I18N.RAW_TO_CHAR(
        DBMS_CRYPTO.DECRYPT(
            src => email,
            typ => 258 + 256 + 4096, -- AES256 + CBC + PKCS7
            key => UTL_I18N.STRING_TO_RAW(current_setting(app.pgc_key), AL32UTF8)
        ),
        AL32UTF8
    ) AS email
FROM customers;

  1. データが書き込まれる前に自動的に暗号化するトリガーを作成します。

CREATE OR REPLACE FUNCTION epas_encrypt_customer_pii_trigger()
RETURNS TRIGGER AS $$
DECLARE
    l_typ INTEGER := 258 + 256 + 4096; -- AES256 + CBC + PKCS7
    l_key RAW(32) := UTL_I18N.STRING_TO_RAW(current_setting(app.pgc_key), AL32UTF8);
BEGIN
    IF (TG_OP = INSERT OR NEW.email IS DISTINCT FROM OLD.email) THEN
        NEW.email := DBMS_CRYPTO.ENCRYPT(
            src => UTL_I18N.STRING_TO_RAW(convert_from(NEW.email, utf8), AL32UTF8),
            typ => l_typ,
            key => l_key
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_epas_encrypt_customer_pii
BEFORE INSERT OR UPDATE ON customers
FOR EACH ROW EXECUTE FUNCTION epas_encrypt_customer_pii_trigger();

  1. ビューとトリガーを配置したら、セッションキーを設定し、通常のようにテーブルと対話します。

-- 1. Initialize session key
SET app.pgc_key = my_secure_32_byte_key_1234;

-- 2. Trigger handles encryption automatically
INSERT INTO customers (username, email) VALUES (dolores, dolores@example.com);

生のcustomers テーブルを照会すると、 email 列が暗号文をRAW 16進文字列として保存するのに対し、 customers_view は復号化された平文を返すことがわかります。

SELECT customer_id, username, email FROM customers;
__OUTPUT__
 customer_id                         | username | email
- ------------------------------------+----------+------------------------------------------------
 01966e42-1af7-7a2a-b4b9-9cf8e0d3c2a | dolores  | A3F2C8D14E7B0591...
(1 row)

SELECT customer_id, username, email FROM customers_view;
__OUTPUT__
 customer_id                         | username | email
- ------------------------------------+----------+---------------------
 01966e42-1af7-7a2a-b4b9-9cf8e0d3c2a | dolores  | dolores@example.com
(1 row)

暗号化キーを安全に管理する#

PGDはデータを複製しますが、キーは複製しません。つまり、すべてのノードが同じキーマテリアルにアクセスする必要があります。キーを安全に管理するには、次のプラクティスに従ってください。

  • ハードコーディングのキーを避ける キーはpg_catalog に保存され、システムカタログへのアクセスを持つすべてのユーザーに表示されるため、 SQLスクリプトまたはCREATE VIEW 定義に含めないでください。

  • セッションレベルの構成パラメーターを使用して、キーを渡します。 セッションレベルで設定すると、キーがメモリにのみ存在し、セッションが閉じるときにクリアされることを意味します。パラメーター名には、app.* 名前空間内の任意の名前を指定できます。例 app.pgc_key

SET app.pgc_key = your_ultra_secure_aes_256_key_here;

重要

構成パラメーターは、コマンドが実行された特定のノードとセッションにローカルです。 PGDはマルチノードクラスターとして動作するため、アプリケーションは、現在の接続を処理する特定のノードに`SET` コマンドが発行されていることを確認する必要があります。アプリケーションがノードを切り替える場合たとえば、接続マネージャーを介して、暗号化されたデータの読み取りまたは書き込みを試行する前に、新しいノードでパラメーターを再設定する必要があります。 !!!

  • プロダクション展開には外部キー管理サービスを使用します。 HashiCorp VaultやAWS KMSなどのサービスを使用すると、アプリケーションは実行時にキーを取得し、セッション中にPGDに提供できるため、キーがローカルに永続化されることはありません任意のPGDノード上のディスク。

目次