パラメータ化された安全なビューを使用する

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

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

始める前に

このドキュメントでは、Cloud SQL for PostgreSQL インスタンスを作成済みであることを前提としています。

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

  1. Cloud SQL インスタンスの cloudsql.enable_parameterized_views データベース フラグを有効にします。このフラグを変更するには、データベースを再起動する必要があります。詳細については、データベース フラグを構成するをご覧ください。

  2. psql

    -- Requires cloudsql.enable_parameterized_views set to on
CREATE EXTENSION parameterized_views;

    拡張機能が作成されると、parameterized_views という名前のスキーマもシステムによって作成されます。これにより、API がそのスキーマの名前空間に含まれ、API が既存の API と競合しないようにします。

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

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

  1. security_barrier オプションを指定して CREATE VIEW DDL コマンドを実行します。

    CREATE VIEW VIEW_NAME WITH (security_barrier) AS
SELECT COLUMN_NAME, COLUMN_NAME_N
FROM TABLE_NAME ALIAS
WHERE CONDITION;

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

    • VIEW_NAME: パラメータ化されたセキュアビューの名前
    • TABLE_NAME: パラメータ化されたセキュアビューで使用するテーブルの名前
    • ALIAS: パラメータ化されたセキュアビューで使用するテーブル名のエイリアス
    • COLUMNNAME または COLUMN_NAMEN: パラメータ化されたセキュアビューで使用するテーブル列の名前

    • CONDITION: アプリケーション ユーザーがアクセスを許可された行のみを表示できるように制限するために使用される条件ステートメント。WHERE 句で $@PARAMETER_NAME 構文を使用して、必要なパラメータを追加します。一般的なユースケースは、WHERE COLUMN = $@PARAMETER_NAME を使用して列の値を確認することです。

      $@PARAMETER_NAME は名前付きビュー パラメータを示します。この値は、execute_parameterized_query API を使用するときに指定します。名前付きビュー パラメータには次の要件があります。

      • 名前付きビュー パラメータの先頭は文字（a-z）またはアンダースコア（_）にする必要があります。
      • 後続の文字には、文字、アンダースコア、数字（0-9）を使用できます。
      • 名前付きビュー パラメータでは大文字と小文字が区別されます。たとえば、$@PARAMETER_NAME は $@parameter_name とは異なる方法で解釈されます。

      名前付きビュー パラメータを使用するパラメータ化されたセキュアビューを作成する例を次に示します。

      CREATE VIEW user_specific_items WITH (security_barrier) AS
SELECT item_id, item_name, description, owner_id
FROM items t
WHERE owner_id = $@app_user_id;

  2. ビューのクエリを実行できるデータベース ユーザーに、ビューに対する SELECT を付与します。

  3. ビューで定義されたテーブルを含むスキーマに対する USAGE を、ビューのクエリを実行できるデータベース ユーザーに付与します。

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

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

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

  1. 管理者権限を持つユーザーとして、パラメータ化されたセキュアビューを作成します。これは、アプリケーションの管理オペレーションを実行する Cloud SQL データベース ユーザーです。

  2. パラメータ化されたセキュアビューに対してクエリを実行する新しいデータベース ロールを作成します。これは、アプリケーションがデータベースに接続してログインするために使用する Cloud SQL データベース ロールです。

    1. セキュアビューに対する新しいロール権限を付与します。通常、ビューに対する SELECT 権限とスキーマに対する USAGE が含まれます。
    2. このロールがアクセスできるオブジェクトを、アプリケーションに必要な公開関数とオブジェクトの最小限のセットに制限します。公開されていないスキーマとテーブルへのアクセス権を付与しないでください。

    ビューをクエリすると、アプリケーションは必要なビュー パラメータの値を提供します。この値は、アプリケーション ユーザー ID に関連付けられています。

    詳しくは、ユーザーを作成して管理するをご覧ください。

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

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

  • JSON ベース: この API を使用して、クエリをワンショットで実行し、JSON 行を返します。
  • カーソルベース: 長時間実行されるクエリや、サイズの大きいクエリで結果を一括取得する場合に、この API を使用します。
  • PREPARE .. AS RESTRICTED ステートメント: PREPARE .. AS RESTRICTED を使用してクエリプランを定義し、EXECUTE ... WITH VIEW PARAMETERS (...) を実行して、ビューの特定のパラメータで実行します。

JSON API

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

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

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

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

CURSOR API

カーソル名を使用して execute_parameterized_query() 関数を実行します。これにより、トランザクション スコープの CURSOR が作成されて返されます。

-- Must be in a transaction block
BEGIN;

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

FETCH ALL FROM CURSOR_NAME;

END;

準備済みステートメント

準備済みステートメント メソッドを使用すると、クエリプランを 1 回準備して、クエリの位置パラメータとビューの名前付きパラメータの両方に異なる値を指定して複数回実行できます。

準備済みステートメントを使用するには、次の操作を行います。

  1. 準備済みステートメントを作成します。

    -- Prepare the statement
PREPARE PREPARED_STATEMENT_NAME (QUERY_PARAM_TYPE_1, QUERY_PARAM_TYPE_N)
AS RESTRICTED SQL_QUERY;

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

    • PREPARED_STATEMENT_NAME: 準備済みステートメントの名前
    • QUERY_PARAM_TYPE_N: クエリ パラメータのデータ型（TEXT など）
    • SQL_QUERY: 指定された値を使用して準備済みステートメントの一部として実行する SQL クエリ

  2. 準備済みステートメントを実行します。

    -- Execute the statement with query parameters and view parameters
EXECUTE PREPARED_STATEMENT_NAME (QUERY_VALUE_1, QUERY_VALUE_N)
WITH VIEW PARAMETERS (PARAMETER_NAME_1 := 'PARAMETER_VALUE_1', PARAMETER_NAME_N := 'PARAMETER_VALUE_N');

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

    • PREPARED_STATEMENT_NAME: 準備済みステートメントの名前。
    • QUERY_VALUE_N: SQL クエリにパラメータとして指定する値
    • PARAMETER_NAME_N: パラメータ化された セキュアビューを作成したときに定義した名前付きビュー パラメータの名前。名前付きビュー パラメータは、テーブルの列から作成します。
    • PARAMETER_VALUE_N: 名前付きビュー パラメータの値。この値により、パラメータ化されたセキュアビューが値に関連付けられた行に制限されます。

  3. 準備済みステートメントをクリーンアップします。

    -- Cleanup
DEALLOCATE PREPARED_STATEMENT_NAME>;

次の例では、名前付きビュー パラメータ $@app_user_id を必要とする user_specific_items という名前のパラメータ化されたセキュアビューを使用します。

-- Prepare a query with a positional parameter $1 for the item_name pattern
PREPARE get_items_by_name (TEXT) AS RESTRICTED
SELECT item_id, item_name FROM user_specific_items
WHERE item_name LIKE $1;

準備済みステートメントを作成したら、`EXECUTE` を使用して準備済みステートメントを複数回実行し、クエリと名前付きビュー パラメータの両方に異なる値を割り当てることができます。

たとえば、最初のクエリの実行は次のようになります。

-- Execute for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

2 回目のクエリの実行は次のようになります。

-- Execute for user 456, looking for items like '%Book%'
EXECUTE get_items_by_name ('%Book%')
WITH VIEW PARAMETERS (app_user_id := '456');

WITH VIEW PARAMETERS 句では、準備済みクエリの位置パラメータ（%Laptop%、%Book%）とは別に、パラメータ化されたセキュアビューの名前付きビュー パラメータ（123、456）が指定されます。

最後に、準備済みステートメントをクリーンアップします。

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

クエリに適用される制限

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

  • 読み取り専用: 読み取り専用の SELECT ステートメントのみが許可されます。DML（INSERT、UPDATE、DELETE）と DDL（CREATE、ALTER）は禁止されています。
  • ネストなし: execute_parameterized_query への再帰呼び出しは禁止されています。
  • 拡張機能の制限: 新しいバックグラウンド セッションを開始する特定の拡張機能（dblink、pg_cron など）は許可されません。
  • EXPLAIN ステートメントは、クエリプランを使用した情報漏洩の可能性を防ぐために禁止されています。

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

parameterized_views.all_parameterized_views ビューを使用して、すべてのパラメータ化されたビュー（名前付きパラメータ $@... が 1 つ以上含まれているビュー）を一覧表示します。

SELECT * FROM parameterized_views.all_parameterized_views;

次のステップ