Trusted Language Extensions for PostgreSQL を使用した操作

Trusted Language Extensions for PostgreSQL は PostgreSQL 拡張機能を構築するためのオープンソース開発キットです。これにより、高性能の PostgreSQL 拡張機能を構築し、それらを RDS for PostgreSQL DB インスタンス。PostgreSQL の Trusted Language Extensions (TLE) を使用することで、PostgreSQL の機能を拡張する文書化されたアプローチに従った PostgreSQL 拡張機能を作成できます。詳細については、PostgreSQL ドキュメントの「エクステンションへの関連オブジェクトのパッケージ化」を参照してください。

TLE の主な利点の 1 つは、PostgreSQL インスタンスの基盤となるファイルシステムへのアクセスを提供しない環境で使用できることです。以前は、新しい拡張機能をインストールするにはファイルシステムへのアクセスが必要でした。TLE では、この制約を取り払います。で実行されているものを含め、あらゆる PostgreSQL データベース用の新しい拡張機能を作成するための開発環境を提供します。RDS for PostgreSQL DB インスタンス

TLE は、TLE を使用して作成する拡張機能の危険なリソースへのアクセスを防ぐように設計されています。そのランタイム環境では、拡張機能の不具合による影響は 1 つのデータベース接続に限定されます。また、TLE では、データベース管理者が拡張機能をインストールできるユーザーをきめ細かく制御でき、拡張機能を実行するためのアクセス許可モデルも用意されています。

TLE は RDS for PostgreSQL バージョン 14.5 以上のバージョンでサポートされています。Trusted Language Extensions の開発環境とランタイムは、pg_tle PostgreSQL 拡張機能のバージョン 1.0.1 としてパッケージ化されています。JavaScript、Perl、PL/pgSQL、および SQL での拡張機能の作成をサポートしています。他の PostgreSQL 拡張機能をインストールするのと同じ方法で、 RDS for PostgreSQL DB インスタンスに pg_tle 拡張機能をインストールします。pg_tle をセットアップすると、開発者はこれを使用して TLE 拡張機能と呼ばれる新しい PostgreSQL 拡張機能を作成できます。

次のトピックでは、Trusted Language Extensions をセットアップする方法と、独自の TLE 拡張機能をセットアップする方法について説明します。

トピック

• 用語
• Trusted Language Extensions for PostgreSQL を使用するための要件
• RDS for PostgreSQL DB インスタンスに Trusted Language Extensions を設定する
• Trusted Language Extensions for PostgreSQL の概要
• RDS for PostgreSQL の TLE 拡張機能の作成
• TLE 拡張機能をデータベースから削除する
• Trusted Language Extensions for PostgreSQL のアンインストール
• TLE 拡張機能で PostgreSQL フックを使用する
• Trusted Language Extensions for PostgreSQL の関数リファレンス
• Trusted Language Extensions for PostgreSQL のフックリファレンス

用語

Trusted Language Extensions の理解を深めるために、このトピックで使用されている用語については、次の用語集を参照してください。

Trusted Language Extensions for PostgreSQL

Trusted Language Extensions for PostgreSQL は、pg_tle 拡張機能としてパッケージされているオープンソース開発キットの正式名称です。どの PostgreSQL システムでも使用できます。詳細については、GitHub の「aws/pg_tle」を参照してください。

Trusted Language Extensions

Trusted Language Extensions は、Trusted Language Extensions for PostgreSQL の省略名です。このドキュメントでは、この短縮名とその略称 (TLE) も使用されています。

信頼できる言語

信頼できる言語とは、特定のセキュリティ属性を持つプログラミング言語またはスクリプト言語です。例えば、信頼できる言語は通常、ファイルシステムへのアクセスを制限し、指定されたネットワークプロパティの使用を制限します。TLE 開発キットは、信頼できる言語をサポートするように設計されています。PostgreSQL は、信頼できる、または信頼できない拡張機能を作成するために使用される複数の異なる言語をサポートしています。例については、PostgreSQL ドキュメントの「信頼できる PL/Perl と信頼できない PL/Perl」を参照してください。Trusted Language Extensions を使用して拡張機能を作成すると、その拡張機能は本質的に信頼できる言語メカニズムを使用します。

TLE 拡張機能

TLE 拡張機能は、Trusted Language Extensions (TLE) 開発キットを使用して作成された PostgreSQL 拡張機能です。

Trusted Language Extensions for PostgreSQL を使用するための要件

TLE 開発キットをセットアップして使用するには、次の要件を使用してください。

• RDS for PostgreSQL 14.5 以降のバージョンが必要です - Trusted Language Extensions は RDS for PostgreSQL バージョン 14.5 以降のリリースでのみサポートされています。

  • RDS for PostgreSQL インスタンスをアップグレードする必要がある場合は、「」「Amazon RDS の PostgreSQL DB エンジンのアップグレード」を参照してください。

  • PostgreSQL を実行している Amazon RDS DB インスタンスをまだ持っていない場合は作成できます。詳細については、「」を参照してください。RDS for PostgreSQL DB インスタンスについては、「PostgreSQL DB インスタンスを作成して PostgreSQL DB インスタンスのデータベースに接続する」を参照してください。

• rds_superuser 権限が必要です - pg_tle 拡張機能をセットアップおよび設定するには、データベースユーザーロールに rds_superuser ロールのアクセス許可が必要です。デフォルトでは、このロールは を作成する postgres ユーザーに付与されます。RDS for PostgreSQL DB インスタンス。

• カスタム DB パラメータグループが必要です – RDS for PostgreSQL DB インスタンス には、カスタム DB パラメータグループを設定する必要があります。

  • RDS for PostgreSQL DB インスタンスがカスタム DB パラメータグループで構成されていない場合は、カスタム DB パラメータグループを作成して に関連付ける必要があります。RDS for PostgreSQL DB インスタンス。ステップの簡単な概要については、「カスタム DB パラメータグループの作成と適用」を参照してください。

  • RDS for PostgreSQL DB インスタンスが、カスタム DB パラメータグループを使用して既に設定されている場合は、Trusted Language Extensions をセットアップできます。詳細については、「RDS for PostgreSQL DB インスタンスに Trusted Language Extensions を設定する」を参照してください。

カスタム DB パラメータグループの作成と適用

以下のステップを使用してカスタム DB パラメータグループを作成し、それを使用するように RDS for PostgreSQL DB インスタンスを設定します。

コンソール

カスタム DB パラメータグループを作成して、RDS for PostgreSQL DB インスタンスで使用するには

1. AWS Management Console にサインインし、Amazon RDS コンソール (https://console.aws.amazon.com/rds/) を開きます。

2. Amazon RDS メニューから [Parameter groups] (パラメータグループ) を選択します。

3. [パラメータグループの作成]を選択します。

4. [Parameter group details] (パラメータグループの詳細) ページで、次の情報を入力します。

   • [Parameter group family] (パラメータグループファミリー) で、 [postgres14.] を選択します

   • [Type] (タイプ) で、[DB Parameter Group] (DB パラメータグループ) を選択します。

   • [Group name] (グループ名) には、パラメータグループに操作の内容に合ったわかりやすい名前を付けます。

   • [Description] (説明) には、チームの他のメンバーが簡単に見つけられるように、わかりやすい説明を入力します。

5. [Create] (作成) を選択します。カスタム DB パラメータグループは AWS リージョン で作成されます。次のステップに従って、 RDS for PostgreSQL DB インスタンスを使用するように変更できるようになりました。

6. Amazon RDS メニューから [Databases] (データベース) を選択します。

7. 一覧から TLE で使用する RDS for PostgreSQL DB インスタンスを選択し、[Modify] (変更) を選択します。

8. DB インスタンス設定の変更ページで、追加設定セクションで [データベースオプション] (データベースオプション) を選択し、セレクターからカスタム DB パラメータグループを選択します。

9. [Continue] (続行) を選択して、変更を保存します。

10. [Apply immediately] (すぐに適用) を選択すると、引き続き RDS for PostgreSQL DB インスタンスを TLE を使用するようにセットアップできます。

Trusted Language Extensions のシステム設定を継続するには、「RDS for PostgreSQL DB インスタンスに Trusted Language Extensions を設定する」を参照してください。

DB パラメータグループについては、「DB パラメータグループを使用する」を参照してください。

AWS CLI

AWS CLI をデフォルト AWS リージョン に設定することで、CLI コマンドを使用するときに --region 引数を指定しなくても済みます。詳細については、AWS Command Line Interface ユーザーガイドの「設定の基本」を参照してください。

カスタム DB パラメータグループを作成して、RDS for PostgreSQL DB インスタンスで使用するには

1. create-db-parameter-group AWS CLI コマンドを使用して、AWS リージョン の postgres14 をベースにしたカスタム DB パラメータグループを作成してください。

   Linux、macOS、Unix の場合:

   aws rds create-db-parameter-group \
     --region aws-region \
     --db-parameter-group-name custom-params-for-pg-tle \
     --db-parameter-group-family postgres14 \
     --description "My custom DB parameter group for Trusted Language Extensions"

   Windows の場合:

   aws rds create-db-parameter-group ^
     --region aws-region ^
     --db-parameter-group-name custom-params-for-pg-tle ^
     --db-parameter-group-family postgres14 ^
     --description "My custom DB parameter group for Trusted Language Extensions"

   AWS リージョン でカスタム DB パラメータグループを使用できるため、 RDS for PostgreSQL DB インスタンスのライターインスタンスを変更してそれを使用できます。

2. modify-db-instance AWS CLI コマンドを使用して、カスタム DB パラメータグループを RDS for PostgreSQL DB インスタンス。このコマンドは、アクティブなインスタンスを直ちに再起動します。

   Linux、macOS、Unix の場合:

   aws rds modify-db-instance \
     --region aws-region \
     --db-instance-identifier your-instance-name \
     --db-parameter-group-name custom-params-for-pg-tle \
     --apply-immediately

   Windows の場合:

   aws rds modify-db-instance ^
     --region aws-region ^
     --db-instance-identifier your-instance-name ^
     --db-parameter-group-name custom-params-for-pg-tle ^
     --apply-immediately

Trusted Language Extensions のシステム設定を継続するには、「RDS for PostgreSQL DB インスタンスに Trusted Language Extensions を設定する」を参照してください。

詳細については、「パラメータグループを使用する」を参照してください。

RDS for PostgreSQL DB インスタンスに Trusted Language Extensions を設定する

以下のステップでは、 RDS for PostgreSQL DB インスタンスがカスタム DB パラメータグループに関連付けられていることを前提としています。これらの手順には、AWS Management Console または AWS CLI を使用できます。

RDS for PostgreSQL DB インスタンスで信頼できる Trusted Language Extensions をセットアップする場合、そのデータベースに対するアクセス許可を持つデータベースユーザーが使用できるように、特定のデータベースにインストールします。

コンソール

Trusted Language Extensions をセットアップするには

rds_superuser グループ (ロール) のメンバーであるアカウントを使用して、次のステップを実行します。

1. AWS Management Console にサインインし、Amazon RDS コンソール (https://console.aws.amazon.com/rds/) を開きます。

2. ナビゲーションペインで、 RDS for PostgreSQL DB インスタンスを選択します。

3. の [Configuration] (設定) タブを開きます。RDS for PostgreSQL DB インスタンス。インスタンスの詳細の中から、パラメータグループのリンクを見つけてください。

4. リンクを選択して、に関連するカスタムパラメータを開きます。RDS for PostgreSQL DB インスタンス。

5. パラメータ検索フィールドに、shared_pre を入力して shared_preload_libraries パラメータを検索します。

6. プロパティ値にアクセスするには、[Edit parameters] (パラメータの編集) を選択します。

7. [Values] (値) フィールドのリストに pg_tle を追加します。値のリスト内の項目を区切るにはカンマを使用します。

   

8. RDS for PostgreSQL DB instance を再起動して、shared_preload_libraries パラメータの変更を有効にします。

9. インスタンスが使用可能になったら、pg_tle が初期化されていることを確認します。psql を使用して RDS for PostgreSQL DB インスタンスに接続し、次のコマンドを実行します。

   SHOW shared_preload_libraries;
   shared_preload_libraries 
   --------------------------
   rdsutils,pg_tle
   (1 row) 

10. pg_tle 拡張子を初期化すると、拡張機能を作成できるようになりました。

    CREATE EXTENSION pg_tle;

    以下の psql メタコマンドを使用して、拡張機能がインストールされていることを確認できます。

    labdb=> \dx
                             List of installed extensions
      Name   | Version |   Schema   |                Description
    ---------+---------+------------+--------------------------------------------
     pg_tle  | 1.0.1   | pgtle      | Trusted-Language Extensions for PostgreSQL
     plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

11. RDS for PostgreSQL DB インスタンスのセットアップ時に作成したプライマリユーザー名に pgtle_admin ロールを付与します。デフォルトを受け入れた場合は、postgres です。

    labdb=> GRANT pgtle_admin TO postgres;
    GRANT ROLE

    次の例に示すように、psql メタコマンドを使用して、付与されたことを確認できます。出力には pgtle_admin と postgres ロールのみが表示されます。詳細については、「 rds_superuser ロールを理解する.」を参照してください。

    labdb=> \du
                              List of roles
        Role name    |           Attributes            |               Member of
    -----------------+---------------------------------+-----------------------------------
    pgtle_admin     | Cannot login                     | {}
    postgres        | Create role, Create DB          +| {rds_superuser,pgtle_admin}
                    | Password valid until infinity    |...

12. \q メタコマンドを使用して psql セッションを終了します。

    \q

TLE 拡張機能の作成を開始するには、「例: SQL を使用した信頼できる言語拡張関数の作成」を参照してください。

AWS CLI

AWS CLI をデフォルト AWS リージョン に設定することで、CLI コマンドを使用するときに --region 引数を指定しなくても済みます。詳細については、AWS Command Line Interface ユーザーガイドの「設定の基本」を参照してください。

Trusted Language Extensions をセットアップするには

1. shared_preload_libraries パラメータに pg_tle を追加するには、modify-db-parameter-group AWS CLI コマンドを使用します。

   aws rds modify-db-parameter-group \
      --db-parameter-group-name custom-param-group-name \
      --parameters "ParameterName=shared_preload_libraries,ParameterValue=pg_tle,ApplyMethod=pending-reboot" \
      --region aws-region

2. reboot-db-instance AWS CLI コマンドを使用して、を再起動し、pg_tle ライブラリを初期化します。

   aws rds reboot-db-instance \
       --db-instance-identifier your-instance \
       --region aws-region

3. インスタンスが使用可能になると、pg_tle が初期化されていることを確認できます。psql を使用して RDS for PostgreSQL DB インスタンスに接続し、次のコマンドを実行します。

   SHOW shared_preload_libraries;
   shared_preload_libraries 
   --------------------------
   rdsutils,pg_tle
   (1 row) 

   pg_tle を初期化すると、拡張機能を作成できるようになりました。

   CREATE EXTENSION pg_tle;

4. RDS for PostgreSQL DB インスタンスのセットアップ時に作成したプライマリユーザー名に pgtle_admin ロールを付与します。デフォルトを受け入れた場合は、postgres です。

   GRANT pgtle_admin TO postgres;
   GRANT ROLE

5. 以下のように psql セッションを終了します。

   labdb=> \q

TLE 拡張機能の作成を開始するには、「例: SQL を使用した信頼できる言語拡張関数の作成」を参照してください。

Trusted Language Extensions for PostgreSQL の概要

Trusted Language Extensions for PostgreSQL は PostgreSQL 拡張機能で、他の PostgreSQL 拡張機能をセットアップするのと同じ方法で RDS for PostgreSQL DB インスタンスにインストールします。pgAdmin クライアントツールのサンプルデータベースの次の画像では、pg_tle 拡張機能を構成するコンポーネントの一部を確認できます。

以下の詳細を表示できます。

1. Trusted Language Extensions (TLE) for PostgreSQL 開発キットは pg_tle 拡張機能としてパッケージ化されています。そのため、pg_tle がインストールされているデータベースで使用可能な拡張機能にそれが追加されます。

2. TLE には独自のスキーマ、pgtle があります。このスキーマには、作成した拡張機能をインストールおよび管理するためのヘルパー関数 (3) が含まれています。

3. TLE には、拡張機能のインストール、登録、管理のためのヘルパー関数が十数種類用意されています。これらの関数の詳細については、「Trusted Language Extensions for PostgreSQL の関数リファレンス」を参照してください。

pg_tle 拡張機能のその他のコンポーネントには、以下のものが含まれています。

• pgtle_admin ロール – pgtle_admin ロールは、pg_tle 拡張機能のインストール時に作成されます。この役職には特権があり、そのように扱われる必要があります。データベースユーザーに pgtle_admin ロールを付与する場合は、最小特権の原則に従うことを強くお勧めします。つまり、postgres のような新しい TLE 拡張機能の作成、インストール、管理が許可されているデータベースユーザーにのみ pgtle_admin ロールを付与します。

• pgtle.feature_info テーブル – pgtle.feature_info テーブルは保護されたテーブルで、TLE、フック、およびそれらが使用するカスタムストアドプロシージャと関数に関する情報が含まれています。pgtle_admin権限がある場合は、以下の Trusted Language Extensions の関数を使用して、テーブル内の情報を追加および更新します。

  • pgtle.register_feature

  • pgtle.register_feature_if_not_exists

  • pgtle.unregister_feature

  • pgtle.unregister_feature_if_exists

RDS for PostgreSQL の TLE 拡張機能の作成

TLE を使用して作成した拡張機能は、pg_tle 拡張機能がインストールされている任意の RDS for PostgreSQL DB インスタンスにインストールできます。pg_tle 拡張機能のスコープは、インストールされている PostgreSQL データベースに限定されます。TLE を使用して作成した拡張機能は、同じデータベースを対象としています。

さまざまな pgtle 関数を使用して、TLE 拡張機能を構成するコードをインストールします。以下の Trusted Language Extensions 関数には pgtle_admin すべてロールが必要です。

• pgtle.install_extension

• pgtle.install_update_path

• pgtle.register_feature

• pgtle.register_feature_if_not_exists

• pgtle.set_default_version

• pgtle.uninstall_extension(name)

• pgtle.uninstall_extension(name, version)

• pgtle.uninstall_extension_if_exists

• pgtle.uninstall_update_path

• pgtle.uninstall_update_path_if_exists

• pgtle.unregister_feature

• pgtle.unregister_feature_if_exists

例: SQL を使用した信頼できる言語拡張関数の作成

次の例は、さまざまな式を使用して距離を計算するためのいくつかの SQL 関数を含む pg_distance という名前の TLE 拡張機能を作成する方法を示しています。リストには、マンハッタン距離を計算する関数とユークリッド距離を計算する関数があります。これらの式の違いの詳細については、Wikipedia の「Taxicab geometry」と「Euclidean geometry」を参照してください。

RDS for PostgreSQL DB インスタンスに Trusted Language Extensions を設定する で説明されているように pg_tle 拡張機能をセットアップしていれば、この例を独自の RDS for PostgreSQL DB インスタンスで使用できます。

注記

この手順を実行するには、pgtle_admin ロールの権限が必要です。

サンプルの TLE 拡張機能を作成するには

以下の手順では、labdb という名前のサンプルデータベースを使用します。このデータベースは postgres プライマリユーザーが所有しています。postgres ロールには、pgtle_admin ロールのアクセス許可もあります。

1. psql を使用して、に接続します。RDS for PostgreSQL DB インスタンス。

   psql --host=db-instance-123456789012.aws-region.rds.amazonaws.com
   --port=5432 --username=postgres --password --dbname=labdb

2. 次のコードをコピーして psql セッションコンソールに貼り付けて、pg_distance という名前の TLE 拡張機能を作成します。

   SELECT pgtle.install_extension
   (
    'pg_distance',
    '0.1',
     'Distance functions for two points',
   $_pg_tle_$
       CREATE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int)
       RETURNS float8
       AS $$
         SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm);
       $$ LANGUAGE SQL;
   
       CREATE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8)
       RETURNS float8
       AS $$
         SELECT dist(x1, y1, x2, y2, 1);
       $$ LANGUAGE SQL;
   
       CREATE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8)
       RETURNS float8
       AS $$
         SELECT dist(x1, y1, x2, y2, 2);
       $$ LANGUAGE SQL;
   $_pg_tle_$
   );

   次のような出力が表示されます。

   install_extension
   ---------------
    t
   (1 row)

   これで、pg_distance 拡張機能を構成するアーティファクトがデータベースにインストールされました。これらのアーティファクトには、コントロールファイルと拡張機能のコードが含まれます。これらは、CREATE EXTENSION コマンドを使用して拡張機能を作成するために必要となる項目です。つまり、データベースユーザーがその関数を利用できるようにするには、やはり拡張機能を作成する必要があります。

3. 拡張機能を作成するには、他の拡張機能と同じように CREATE EXTENSION コマンドを使用します。他の拡張機能と同様に、データベースユーザーにはデータベース内の CREATE アクセス許可が必要です。

   CREATE EXTENSION pg_distance;

4. pg_distance TLE 拡張機能をテストするには、これを使用して 4 点間のマンハッタン距離を計算できます。

   labdb=> SELECT manhattan_dist(1, 1, 5, 5);
   8

   同じ点群間のユークリッド距離を計算するには、以下を使用できます。

   labdb=> SELECT euclidean_dist(1, 1, 5, 5);
   5.656854249492381

pg_distance 拡張機能は関数をデータベースに読み込み、データベースに対するアクセス許可を持つすべてのユーザーがその関数を利用できるようにします。

TLE 拡張機能の変更

この TLE 拡張機能にパッケージされている関数のクエリパフォーマンスを向上させるには、次の 2 つの PostgreSQL 属性を仕様に追加してください。

• IMMUTABLE – IMMUTABLE 属性により、クエリオプティマイザが最適化を使用してクエリの応答時間を改善できるようになります。詳細については、PostgreSQL ドキュメントの「関数のボラティリティカテゴリ」を参照してください。

• PARALLEL SAFE – PARALLEL SAFE属性は、PostgreSQL が関数をパラレルモードで実行できるようにするもう 1 つの属性です。詳細については、PostgreSQL のドキュメントの「CREATE FUNCTION」を参照してください。

次の例では、pgtle.install_update_path 関数を使用してこれらの属性を各関数に追加し、pg_distance TLE 拡張機能のバージョン 0.2 を作成する方法を確認できます。この関数の詳細については、「pgtle.install_update_path」を参照してください。このタスクを実行するには、pgtle_admin ロールが必要です。

既存の TLE 拡張機能を更新してデフォルトバージョンを指定するには

1. psql または pgAdmin などの別のクライアントツールを使用して、 RDS for PostgreSQL DB インスタンスのライターインスタンスに接続します。

   psql --host=db-instance-123456789012.aws-region.rds.amazonaws.com
   --port=5432 --username=postgres --password --dbname=labdb

2. 次のコードをコピーして psql セッションコンソールに貼り付けることで、既存の TLE 拡張機能を変更します。

   SELECT pgtle.install_update_path
   (
    'pg_distance',
    '0.1',
    '0.2',
   $_pg_tle_$
       CREATE OR REPLACE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int)
       RETURNS float8
       AS $$
         SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm);
       $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
   
       CREATE OR REPLACE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8)
       RETURNS float8
       AS $$
         SELECT dist(x1, y1, x2, y2, 1);
       $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
   
       CREATE OR REPLACE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8)
       RETURNS float8
       AS $$
         SELECT dist(x1, y1, x2, y2, 2);
       $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
   $_pg_tle_$
   );

   次のようなレスポンスが表示されます。

   install_update_path
   ---------------------
    t
   (1 row)

   このバージョンの拡張機能をデフォルトバージョンにすると、データベースユーザーがデータベースで拡張機能を作成または更新するときにバージョンを指定する必要がなくなります。

3. TLE 拡張機能の修正バージョン (バージョン 0.2) がデフォルトバージョンになるように指定するには、次の例に示す pgtle.set_default_version 関数を使用します。

   SELECT pgtle.set_default_version('pg_distance', '0.2');

   この関数の詳細については、「pgtle.set_default_version」を参照してください。

4. コードを配置したら、次に示すように、ALTER EXTENSION ... UPDATE コマンドを使用して、インストールされている TLE 拡張機能を通常の方法で更新できます。

   ALTER EXTENSION pg_distance UPDATE;

TLE 拡張機能をデータベースから削除する

TLE 拡張機能は、他の PostgreSQL 拡張機能の場合と同じように DROP EXTENSION コマンドを使用して削除できます。拡張機能を削除しても、拡張機能を構成するインストールファイルは削除されないため、ユーザーは拡張機能を再作成できます。拡張機能とそのインストールファイルを削除するには、次の 2 段階のプロセスを実行します。

TLE 拡張機能とそのインストールファイルを削除するには

1. psql または別のクライアントツールを使用して RDS for PostgreSQL DB インスタンスに接続します。

   psql --host=.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=dbname

2. PostgreSQL 拡張機能と同様に、この拡張機能を削除してください。

   DROP EXTENSION your-TLE-extension

   例えば、例: SQL を使用した信頼できる言語拡張関数の作成 で詳細を説明しているように pg_distance という拡張機能を作成する場合は、次のように拡張機能を削除できます。

   DROP EXTENSION pg_distance;

   次のように、拡張機能が削除されたことを確認する出力が表示されます。

   DROP EXTENSION

   この時点で、拡張機能はデータベースでアクティブではなくなります。ただし、インストールファイルとコントロールファイルはデータベースにまだ残っているため、データベースユーザーは必要に応じて拡張機能を再作成できます。

   • 拡張ファイルをそのまま残して、データベースユーザーが TLE 拡張機能を作成できるようにする場合は、ここで終了してください。

   • 拡張機能を占めるすべてのファイルを削除する場合は、次のステップに進みます。

3. 拡張機能のインストールファイルをすべて削除するには、pgtle.uninstall_extension 関数を使用してください。この関数は、拡張機能のコードとコントロールファイルをすべて削除します。

   SELECT pgtle.uninstall_extension('your-tle-extension-name');

   例えば、すべての pg_distance インストールファイルを削除するには、次のコマンドを使用します。

   SELECT pgtle.uninstall_extension('pg_distance');
    uninstall_extension
   ---------------------
    t
   (1 row)

Trusted Language Extensions for PostgreSQL のアンインストール

TLE を使用して独自の TLE 拡張機能を作成する必要がなくなった場合は、pg_tle 拡張機能を削除してすべてのアーティファクトを削除できます。このアクションには、データベース内のすべての TLE 拡張機能の削除と pgtle スキーマの削除が含まれます。

pg_tle 拡張機能とそのスキーマをデータベースから削除するには

1. psql または別のクライアントツールを使用して RDS for PostgreSQL DB インスタンスに接続します。

   psql --host=.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=dbname

2. pg_tle 拡張機能をデータベースから削除します。データベースに独自の TLE 拡張機能がまだデータベースで実行されている場合は、それらの拡張機能も削除する必要があります。そのためには、次に示すように、CASCADE キーワードを使用します。

   DROP EXTENSION pg_tle CASCADE;

   pg_tle 拡張機能がデータベースでまだ有効になっていない場合は、CASCADE キーワードを使用する必要はありません。

3. pgtle スキーマを削除します。このアクションにより、データベースからすべての管理関数が削除されます。

   DROP SCHEMA pgtle CASCADE;

   このコマンドは、プロセスが完了すると、以下を返します。

   DROP SCHEMA

   pg_tle 拡張機能、そのスキーマ、関数、およびすべてのアーティファクトが削除されます。TLE を使用して新しい拡張機能を作成するには、セットアッププロセスをもう一度実行してください。詳細については、「RDS for PostgreSQL DB インスタンスに Trusted Language Extensions を設定する」を参照してください。

TLE 拡張機能で PostgreSQL フックを使用する

フックは PostgreSQL で利用できるコールバックメカニズムで、開発者は通常のデータベースオペレーション中にカスタム関数やその他のルーチンを呼び出すことができます。TLE 開発キットは PostgreSQL フックをサポートしているため、実行時にカスタム関数を PostgreSQL の動作と統合できます。例えば、フックを使用して認証プロセスを独自のカスタムコードに関連付けたり、特定のニーズに合わせてクエリの計画と実行プロセスを変更したりできます。

TLE 拡張機能にはフックを使用できます。フックの適用範囲がグローバルな場合、すべてのデータベースに適用されます。そのため、TLE 拡張機能がグローバルフックを使用している場合は、ユーザーがアクセスできるすべてのデータベースに TLE 拡張機能を作成する必要があります。

pg_tle 拡張機能を使用して独自の Trusted Language Extensions を構築する場合、SQL API の利用可能なフックを使用して拡張機能の関数を構築できます。すべてのフックを pg_tle に登録する必要があります。一部のフックでは、さまざまな設定パラメータを設定する必要がある場合もあります。例えば、passcode チェックフックをオン、オフ、または必須に設定できます。使用可能な pg_tle フックの特定の要件の詳細については、「Trusted Language Extensions for PostgreSQL のフックリファレンス」を参照してください。

例: PostgreSQL フックを使用する拡張機能の作成

このセクションで説明する例では、PostgreSQL フックを使用して特定の SQL のオペレーション中に入力されたパスワードをチェックし、データベースユーザーが自分のパスワードを password_check.bad_passwords テーブルに含まれるパスワードに設定できないようにします。この表には、一般的によく使用されているものの、簡単に破られてしまうパスワードの選択肢の上位 10 件が掲載されています。

この例を 、RDS for PostgreSQL DB インスタンスに設定するには、Trusted Language Extensions が既にインストールされている必要があります。詳細については、「RDS for PostgreSQL DB インスタンスに Trusted Language Extensions を設定する」を参照してください。

パスワードチェックフックの例を設定するには

1. psql を使用して、に接続します。RDS for PostgreSQL DB インスタンス。

   psql --host=db-instance-123456789012.aws-region.rds.amazonaws.com
   --port=5432 --username=postgres --password --dbname=labdb

2. パスワードチェックフックコードリスト のコードをコピーし、データベースに貼り付けます。

   SELECT pgtle.install_extension (
     'my_password_check_rules',
     '1.0',
     'Do not let users use the 10 most commonly used passwords',
   $_pgtle_$
     CREATE SCHEMA password_check;
     REVOKE ALL ON SCHEMA password_check FROM PUBLIC;
     GRANT USAGE ON SCHEMA password_check TO PUBLIC;
   
     CREATE TABLE password_check.bad_passwords (plaintext) AS
     VALUES
       ('123456'),
       ('password'),
       ('12345678'),
       ('qwerty'),
       ('123456789'),
       ('12345'),
       ('1234'),
       ('111111'),
       ('1234567'),
       ('dragon');
     CREATE UNIQUE INDEX ON password_check.bad_passwords (plaintext);
   
     CREATE FUNCTION password_check.passcheck_hook(username text, password text, password_type pgtle.password_types, valid_until timestamptz, valid_null boolean)
     RETURNS void AS $$
       DECLARE
         invalid bool := false;
       BEGIN
         IF password_type = 'PASSWORD_TYPE_MD5' THEN
           SELECT EXISTS(
             SELECT 1
             FROM password_check.bad_passwords bp
             WHERE ('md5' || md5(bp.plaintext || username)) = password
           ) INTO invalid;
           IF invalid THEN
             RAISE EXCEPTION 'Cannot use passwords from the common password dictionary';
           END IF;
         ELSIF password_type = 'PASSWORD_TYPE_PLAINTEXT' THEN
           SELECT EXISTS(
             SELECT 1
             FROM password_check.bad_passwords bp
             WHERE bp.plaintext = password
           ) INTO invalid;
           IF invalid THEN
             RAISE EXCEPTION 'Cannot use passwords from the common common password dictionary';
           END IF;
         END IF;
       END
     $$ LANGUAGE plpgsql SECURITY DEFINER;
   
     GRANT EXECUTE ON FUNCTION password_check.passcheck_hook TO PUBLIC;
   
     SELECT pgtle.register_feature('password_check.passcheck_hook', 'passcheck');
   $_pgtle_$
   );

   拡張機能がデータベースに読み込まれると、次のような出力が表示されます。

    install_extension
   -------------------
    t
   (1 row)

3. データベースに接続したままで、拡張機能を作成できるようになりました。

   CREATE EXTENSION my_password_check_rules;

4. 次の psql メタコマンドを使用して、拡張機能がデータベースに作成されたことを確認できます。

   \dx
                           List of installed extensions
             Name           | Version |   Schema   |                         Description
   -------------------------+---------+------------+-------------------------------------------------------------
    my_password_check_rules | 1.0     | public     | Prevent use of any of the top-ten most common bad passwords
    pg_tle                  | 1.0.1   | pgtle      | Trusted-Language Extensions for PostgreSQL
    plpgsql                 | 1.0     | pg_catalog | PL/pgSQL procedural language
   (3 rows)

5. 別のターミナルセッションを開いて、AWS CLI を操作します。パスワードチェックフックを有効にするには、カスタム DB パラメータグループを変更する必要があります。そのためには、以下の例に示すように modify-db-parameter-group CLI コマンドを使用します。

   aws rds modify-db-parameter-group \
       --region aws-region \
       --db-parameter-group-name your-custom-parameter-group \
       --parameters "ParameterName=pgtle.enable_password_check,ParameterValue=on,ApplyMethod=immediate"

   パラメータが正常に有効になると、次のような出力が表示されます。

   (
       "DBParameterGroupName": "docs-lab-parameters-for-tle"
   }

   パラメータグループ設定の変更が適用されるまでには数分かかる場合があります。ただし、このパラメータは動的であるため、設定を有効にするために を再起動する必要はありません。

6. psql セッションを開き、データベースに問い合わせて password_check フックが有効になっていることを確認します。

   labdb=> SHOW pgtle.enable_password_check;
   pgtle.enable_password_check
   -----------------------------
   on
   (1 row)

パスワードチェックフックがアクティブになりました。次の例に示すように、新しいロールを作成し、不適切なパスワードを使用してテストできます。

CREATE ROLE test_role PASSWORD 'password';
ERROR:  Cannot use passwords from the common password dictionary
CONTEXT:  PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 21 at RAISE
SQL statement "SELECT password_check.passcheck_hook(
    $1::pg_catalog.text, 
    $2::pg_catalog.text, 
    $3::pgtle.password_types, 
    $4::pg_catalog.timestamptz, 
    $5::pg_catalog.bool)"

出力は、読みやすい形式にしてあります。

次の例では、pgsql インタラクティブメタコマンドの \password 動作が password_check フックの影響を受けることを示しています。

postgres=> SET password_encryption TO 'md5';
SET
postgres=> \password
Enter new password for user "postgres":*****
Enter it again:*****
ERROR:  Cannot use passwords from the common password dictionary
CONTEXT:  PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 12 at RAISE
SQL statement "SELECT password_check.passcheck_hook($1::pg_catalog.text, $2::pg_catalog.text, $3::pgtle.password_types, $4::pg_catalog.timestamptz, $5::pg_catalog.bool)"

この TLE 拡張機能モジュールを削除して、必要に応じてソースファイルをアンインストールできます。詳細については、「TLE 拡張機能をデータベースから削除する」を参照してください。

パスワードチェックフックコードリスト

ここに示すサンプルコードは、my_password_check_rules TLE 拡張機能の仕様を定義しています。このコードをコピーしてデータベースに貼り付けると、my_password_check_rules 拡張機能のコードがデータベースにロードされ、password_check フックが拡張機能で使用できるように登録されます。

SELECT pgtle.install_extension (
  'my_password_check_rules',
  '1.0',
  'Do not let users use the 10 most commonly used passwords',
$_pgtle_$
  CREATE SCHEMA password_check;
  REVOKE ALL ON SCHEMA password_check FROM PUBLIC;
  GRANT USAGE ON SCHEMA password_check TO PUBLIC;

  CREATE TABLE password_check.bad_passwords (plaintext) AS
  VALUES
    ('123456'),
    ('password'),
    ('12345678'),
    ('qwerty'),
    ('123456789'),
    ('12345'),
    ('1234'),
    ('111111'),
    ('1234567'),
    ('dragon');
  CREATE UNIQUE INDEX ON password_check.bad_passwords (plaintext);

  CREATE FUNCTION password_check.passcheck_hook(username text, password text, password_type pgtle.password_types, valid_until timestamptz, valid_null boolean)
  RETURNS void AS $$
    DECLARE
      invalid bool := false;
    BEGIN
      IF password_type = 'PASSWORD_TYPE_MD5' THEN
        SELECT EXISTS(
          SELECT 1
          FROM password_check.bad_passwords bp
          WHERE ('md5' || md5(bp.plaintext || username)) = password
        ) INTO invalid;
        IF invalid THEN
          RAISE EXCEPTION 'Cannot use passwords from the common password dictionary';
        END IF;
      ELSIF password_type = 'PASSWORD_TYPE_PLAINTEXT' THEN
        SELECT EXISTS(
          SELECT 1
          FROM password_check.bad_passwords bp
          WHERE bp.plaintext = password
        ) INTO invalid;
        IF invalid THEN
          RAISE EXCEPTION 'Cannot use passwords from the common common password dictionary';
        END IF;
      END IF;
    END
  $$ LANGUAGE plpgsql SECURITY DEFINER;

  GRANT EXECUTE ON FUNCTION password_check.passcheck_hook TO PUBLIC;

  SELECT pgtle.register_feature('password_check.passcheck_hook', 'passcheck');
$_pgtle_$
);