AlloyDB パラメータ化されたセキュアなビューを使用してアプリケーションデータのセキュリティを管理する

このドキュメントでは、AlloyDB for PostgreSQL でパラメータ化されたセキュアビューを使用する方法について説明します。これにより、アプリケーション固有の名前付きパラメータ（アプリケーションユーザー認証情報など）に基づいてデータアクセスを制限できます。パラメータ化されたセキュアビューは、PostgreSQL ビューの機能を拡張することで、セキュリティとアクセス制御を強化します。また、これらのビューは、実行されるクエリにさまざまな制限を自動的に適用することで、アプリケーションから信頼できないクエリを実行するリスクを軽減します。

詳細については、パラメータ化されたセキュアビューの概要とパラメータ化されたセキュアビューのチュートリアルをご覧ください。

始める前に

このドキュメントでは、AlloyDB クラスタとインスタンスを作成していることを前提としています。詳細については、データベースを作成するをご覧ください。

パラメータ化されたセキュアビューを使用するには、次の操作を行う必要があります。

パラメータ化されたセキュアビューへのアクセスをリクエストし、有効化の確認が届くまで待ってから開始します。
AlloyDB チームが parameterized_views.enabled データベースフラグを有効にして、必要な拡張ライブラリを読み込むまで待ちます。開始する前に、このデータベースフラグを有効にする必要があります。

AlloyDB チームが parameterized_views.enabled データベースフラグを有効にすると、データベースが再起動され、これらの変更が有効になります。
AlloyDB Studio または psql を使用して、パラメータ化されたビューが作成されているデータベースに parameterized_views 拡張機能を作成します。
```
-- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;
```
拡張機能が作成されると、parameterized_views という名前のスキーマもシステムによって作成されます。これにより、API がそのスキーマの名前空間に含まれ、API が既存の API と競合しないようにします。

パラメータ化されたセキュアビューを作成する

パラメータ化されたセキュアビューを作成する手順は次のとおりです。

次の例に示すように、CREATE VIEW DDL コマンドを実行します。
```
CREATE VIEW secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM checked_items t
WHERE customer_id = $@app_end_userid;
```
上記の例では、パラメータ化されたセキュアビューにより、/users/checked_items/ という名前のテーブルの 3 つの列にアクセスできます。このビューでは、/users.id/checked_items.customer_id/ が必須パラメータと一致する行に結果が制限されます。

次の属性を使用します。
- security_barrier オプションを使用してビューを作成します。
- アクセスが許可されている行のみをアプリケーションユーザーが表示できるように制限するには、WHERE 句で $@PARAMETER_NAME 構文を使用して必要なパラメータを追加します。一般的なユースケースは、WHERE COLUMN = $@PARAMETER_NAME を使用して列の値を確認することです。
- $@PARAMETER_NAME は名前付きビューパラメータを示します。この値は、execute_parameterized_query API を使用するときに指定します。名前付きビューパラメータには次の要件があります。
  - 名前付きビューパラメータの先頭は文字（a-z）にする必要があります。
  - アクセント記号付きの文字やラテン文字以外の文字を使用できます。アンダースコア（_）も使用できます。
  - 後続の文字には、文字、アンダースコア、数字（0～9）を使用できます。
  - 名前付きビューパラメータに $ を含めることはできません。
  - 名前付きビューパラメータでは大文字と小文字が区別されます。たとえば、$@PARAMETER_NAME は $@parameter_name とは異なる方法で解釈されます。
ビューのクエリを実行できるデータベースユーザーに、ビューに対する SELECT を付与します。
ビューで定義されたテーブルを含むスキーマに対する USAGE を、ビューのクエリを実行できるデータベースユーザーに付与します。

詳細については、パラメータ化されたセキュアなビューを使用して、アプリケーションデータへのアクセスの保護と制御を行うをご覧ください。

アプリケーションのセキュリティを構成する

パラメータ化されたセキュアビューを使用してアプリケーションのセキュリティを構成する手順は次のとおりです。

管理者権限を持つユーザーとして、パラメータ化されたセキュアビューを作成します。このユーザーは、データベースの設定やセキュリティ管理など、アプリケーションの管理オペレーションを実行する AlloyDB データベースユーザーです。
パラメータ化されたセキュアビューに対してクエリを実行する新しいデータベースロールを作成します。これは、アプリケーションがデータベースに接続してログインし、パラメータ化されたビューに対してクエリを実行するために使用する AlloyDB データベースロールです。
1. セキュアビューに対する新しいロール権限を付与します。通常、ビューに対する SELECT 権限とスキーマに対する USAGE が含まれます。
2. このロールがアクセスできるオブジェクトを、アプリケーションに必要な公開関数とオブジェクトの最小限のセットに制限します。公開されていないスキーマとテーブルへのアクセス権を付与しないでください。
ビューをクエリすると、アプリケーションは必要なビューパラメータの値を提供します。この値は、アプリケーションユーザー ID に関連付けられています。

詳細については、データベースユーザーを作成するをご覧ください。

パラメータ化されたセキュアビューにクエリを実行する

パラメータ化されたセキュアビューをクエリするには、ユースケースに最も適した次のいずれかのオプションを使用します。

JSON ベース: この API を使用して、クエリをワンショットで実行し、JSON 行を返します。
カーソルベース: 長時間実行されるクエリや、サイズの大きいクエリで結果を一括取得する場合に、この API を使用します。parameterized_views 拡張機能によって提供される execute_parameterized_query 関数は、カーソル名を受け入れます。
PREPARE EXECUTE ステートメント: 別のパラメータ値で複数回実行できる準備済みステートメントに使用します。

パラメータ化されたセキュアビューに対してクエリを実行するには、parameterized_views 拡張機能で提供される execute_parameterized_query() 関数を使用します。

JSON API

この API には、指定されたクエリのカーソルを宣言するため、制限事項があります。そのため、クエリに PostgreSQL カーソルとの互換性がある必要があります。たとえば、CURSOR API は DO ステートメントと SHOW ステートメントをサポートしていません。

また、この API では、サイズや返される行数で結果を制限することもできません。

execute_parameterized_query() 関数を実行します。構文は次のとおりです。

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

次のように置き換えます。

SQL_QUERY: FROM 句が 1 つ以上のパラメータ化されたセキュアビューを参照する SQL クエリ。
PARAMETER_NAMES: 渡すパラメータ名のリスト（文字列）。
PARAMETER_VALUES: 渡すパラメータ値のリスト。
- このリストは param_names リストと同じサイズで、値の順序が名前の順序と一致している必要があります。
- 値の正確な型は、クエリとパラメータ化されたビュー定義から推測されます。型変換は、必要に応じて、指定されたパラメータ値に対して可能な場合に行われます。型が一致しない場合、エラーがスローされます。

この関数は、JSON オブジェクトのテーブルを返します。テーブルの各行は、元のクエリ結果の行の ROW_TO_JSON() 値と同等です。

次の例を使用して、パラメータ化されたセキュアビューをクエリします。

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

この API を使用すると、結果のサイズ（キロバイト（KB）単位）と行数で結果セットのサイズを制限できます。これらの上限は、parameterized_views.json_results_max_size と parameterized_views.json_results_max_rows を使用して構成できます。

CURSOR API

execute_parameterized_query() 関数を実行します。この関数は、クエリ結果の取得に使用するトランザクションスコープの CURSOR を作成して返します。

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

次のように置き換えます。

SQL_QUERY: FROM 句が 1 つ以上のパラメータ化されたセキュアビューを参照する SQL クエリ。
CURSOR_NAME: 宣言するカーソルの名前。
PARAMETER_NAMES: 渡すパラメータ名のリスト（文字列）。
PARAMETER_VALUES: 渡すパラメータ値のリスト。このリストは param_names リストと同じサイズで、値の順序が名前の順序と一致している必要があります。値の正確な型は、クエリとパラメータ化されたビュー定義から推測されます。型変換は、必要に応じて、指定されたパラメータ値に対して可能な場合に行われます。型が一致しない場合、エラーがスローされます。

次の例を使用して、パラメータ化されたセキュアビューをクエリします。

  -- start a transaction as the that is the default lifetime of a CURSOR
  BEGIN;
  -- create a cursor called 'mycursor'
  SELECT * FROM parameterized_views.execute_parameterized_query(
   query => 'SELECT * FROM secure_checked_items',
   cursor_name => 'mycursor'
   param_names => ARRAY ['app_end_userid'],
   param_values => ARRAY ['40']
  );

  -- then, to actually fetch the results
  FETCH ALL FROM mycursor;
  -- end the transaction, which will clean up the cursor
  END;

返されるカーソルは NO SCROLL カーソル WITHOUT HOLD です。カーソルを使用して、後方方向など、連続していない順序で行を取得することはできません。カーソルは、作成したトランザクションの外部では使用できません。

PREPARE ステートメント

PREPARE .. AS RESTRICTED コマンドを使用して、パラメータ化されたビューを参照する準備済みステートメントを作成します。これらの準備済みステートメントは位置パラメータをサポートし、実行時にさまざまな制限を適用します。詳細については、セキュリティメカニズムをご覧ください。

この機能は、PREPARE と EXECUTE commands を拡張して、名前付きビューパラメータをサポートします。準備済みステートメントを使用すると、ステートメントの実行時に発生する解析、分析、書き換えのオーバーヘッドを回避できます。これにより、特に頻繁に実行されるクエリや複雑なクエリでパフォーマンスが大幅に向上します。準備済みステートメントは、後で実行するためにパラメータ化された SQL ステートメントを事前にコンパイルして保存することで、パフォーマンスを最適化できるサーバーサイドオブジェクトです。

この API には制限があります。ステートメントが PREPARE ステートメントで許可されている必要があるため、SELECT ステートメントと VALUES ステートメントのみがサポートされます。

また、この API では、サイズや返される行数で結果を制限することもできません。

パラメータ化されたビューを参照する準備済みステートメントを作成するには、PREPARE .. AS RESTRICTED コマンドを実行します。

PREPARE pquery (/POSITIONAL_PARAM_TYPES/)
        AS RESTRICTED query % a query that may refer to parameterized views
EXECUTE pquery (/POSITIONAL_PARAM_VALUES/)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

次のように置き換えます。

POSITIONAL_PARAM_TYPES: RESTRICTED クエリで使用される 1 つ以上の位置パラメータ。
POSITIONAL_PARAM_VALUES: PREPARE ステートメントで定義された位置パラメータに代入される実際の値。
VIEW_PARAM_NAME: RESTRICTED クエリで参照されるパラメータ化ビューで想定されるパラメータの名前。
VIEW_PARAM_VALUE: パラメータ化されたビューの対応する viewParamName パラメータに渡される実際の値。

準備済みステートメントにパラメータを追加するには、PREPARE ステートメントにデータ型のリストを指定します。準備するステートメントで、$1 や $2 などを使用して、パラメータを位置で参照します。

EXECUTE .. WITH VIEW PARAMETERS コマンドを使用して、PREPARE .. AS RESTRICTED コマンドを使用して作成した以前に準備済みステートメントを実行します。ステートメントを作成した PREPARE ステートメントが、位置パラメータを指定した場合は、互換性のあるパラメータセットを EXECUTE ステートメントに渡す必要があります。パラメータ化されたビューで必要な名前付きビューパラメータは、WITH VIEW PARAMETERS 句で渡す必要があります。

次の例を使用して、パラメータ化されたセキュアビューをクエリします。

PREPARE pquery (timestamp) AS RESTRICTED SELECT * FROM secure_checked_items WHERE timestamp > $1;

EXECUTE pquery (current_date - 1) WITH VIEW PARAMETERS (app_end_userid = 40);
EXECUTE pquery (current_date - 30) WITH VIEW PARAMETERS (app_end_userid = 40);

クエリに適用される制限

パラメータ化されたセキュアビューにクエリを実行するで説明されているオプションを使用して実行するクエリの制限付きオペレーションのセットを次に示します。

execute_parameterized_query または EXECUTE .. WITH VIEW PARAMETERS を使用して API を再帰的に呼び出すことは禁止されているため、アプリで指定された値のみが使用されます。この制限により、特定のパラメータ値のセットのセキュリティエンベロープを回避するためにクエリが使用されることも防止されます。
dblink、pg_cron、pg_background の各拡張機能など、新しいバックグラウンドセッションを開始する拡張機能は許可されません。
次の表に、制限付きで許可されるクエリ構造を示します。
- 読み取り専用の SELECT ステートメントは許可されます。
- 読み取り専用の SHOW ステートメント、CALL ステートメント、DO ステートメントは許可されます。
- INSERT、UPDATE、DELETE などの DML ステートメントは許可されません。
- CREATE TABLE や ALTER TABLE などの DDL ステートメントは許可されません。
- LOAD、SET、CLUSTER、LOCK、CHECKPOINT、EXPLAIN などの他のステートメントタイプは許可されません。
EXPLAIN ステートメントは、クエリプランを使用した秘密チャンネル攻撃の可能性を回避するために禁止されています。詳細については、隠れチャネルをご覧ください。

すべてのパラメータ化されたビューを一覧表示する

parameterized_views 拡張機能を使用して、all_parameterized_views ビューを使用してデータベース内のすべてのパラメータ化されたビューを一覧表示します。このビューの出力は pg_views カタログビューと同じですが、all_parameterized_views には名前付きビューパラメータを持つビューのみが一覧表示されます。

パラメータ化されたビューを一覧表示するには、次の例を使用します。

postgres=# select * from parameterized_views.all_parameterized_views ;
schemaname |      viewname      | viewowner |                       definition
-----------+--------------------+-----------+---------------------------------------------------------
public     | checked_items_view | postgres  |  SELECT checked_items.bag_id,                          +
           |                    |           |     checked_items."timestamp",                         +
           |                    |           |     checked_items.location                             +
           |                    |           |    FROM checked_items                                  +
           |                    |           |   WHERE (checked_items.customer_id = $@app_end_userid);

all_parameterized_views でパラメータ化されたビューを一覧表示するには、パラメータ化されたビューの定義に名前付きビューパラメータが 1 つ以上含まれていることを確認します。

次のステップ

パラメータ化されたセキュアビューについて学習する。
パラメータ化されたセキュアなビューを使用して、アプリケーションデータへのアクセスの保護と制御を行う方法を学習する。

AlloyDB パラメータ化されたセキュアなビューを使用してアプリケーション データのセキュリティを管理する コレクションでコンテンツを整理 必要に応じて、コンテンツの保存と分類を行います。

始める前に

パラメータ化されたセキュアビューを作成する

アプリケーションのセキュリティを構成する

パラメータ化されたセキュアビューにクエリを実行する

JSON API

CURSOR API

PREPARE ステートメント

クエリに適用される制限

すべてのパラメータ化されたビューを一覧表示する

次のステップ

AlloyDB パラメータ化されたセキュアなビューを使用してアプリケーションデータのセキュリティを管理する