アプリケーション データへのアクセスの保護と制御を行う

パラメータ化されたセキュアビューは、アプリケーション ユーザーの ID に基づいてクエリ結果をフィルタリングすることで、行レベルのデータ セキュリティとアクセス制御を提供します。

このチュートリアルでは、Cloud SQL でパラメータ化されたセキュアビューを設定する方法、ベーステーブルへのアクセスを制限するようにデータベースのロールと権限を構成する方法、データ セキュリティを確認する方法について説明します。このドキュメントで提供されている例は、デモンストレーションのみを目的としています。

パラメータ化されたセキュアビューとは

一般的なベスト プラクティスとして、アプリケーションはデータベースへの最小限のアクセス権を持つサービス アカウントを使用して実行する必要があります。たとえば、アプリケーションがデータベースに書き込まない場合は、読み取り専用アクセス権を持つロールを使用する必要があります。これらのアクセス制御は、データベース レベルで構成する必要があります。

アプリケーションで標準のデータベース レベルのアクセス許可よりもきめ細かいセキュリティが必要な場合は、パラメータ化されたセキュアビューを使用して、ユーザーが承認されたデータのみを表示できるようにします。

パラメータ化されたセキュアビューを使用すると、主に次の 2 つのメリットがあります。

  • 動的な行レベルのセキュリティ: エンドユーザーの識別子を使用してクエリをフィルタし、クエリのフレーズに関係なく、ユーザーが承認されたデータのみにアクセスできるようにします。
  • ロール管理の簡素化: 個々のユーザーごとに個別のデータベース ロールを管理するのではなく、すべてのアプリケーション ユーザーに単一の共有データベース ロールを使用します。

目標

  • 名前付きビュー パラメータを使用して、パラメータ化されたセキュアビューを作成します。
  • アプリケーションがデータベースに接続し、パラメータ化されたセキュアビューにアクセスするために使用するデータベースロールを作成します。
  • 新しいロールにパラメータ化されたセキュアビューに対する権限を付与し、ベーステーブルへのアクセス権を取り消します。
  • 新しいロールを使用して接続し、制限付きテーブルにアクセスできないことを確認します。
  • execute_parameterized_query() 関数または QueryData API を使用して、パラメータ化されたセキュアビューに対してクエリを実行します。

費用

このドキュメントでは、課金対象である次の Google Cloudコンポーネントを使用します。

料金計算ツールを使うと、予想使用量に基づいて費用の見積もりを算出できます。

新規の Google Cloud ユーザーは無料トライアルをご利用いただける場合があります。

引き続き課金されないようにするには、このドキュメントのタスクを完了したら、作成したリソースを削除します。詳細については、クリーンアップをご覧ください。

始める前に

パラメータ化されたセキュアビューを作成する前に、次の前提条件を満たしてください。

課金と必要な API を有効にする

  1. Google Cloud コンソールでプロジェクトを選択します。

    プロジェクト セレクタに移動

  2. Google Cloud プロジェクトに対して課金が有効になっていることを確認します。

  3. Cloud SQL の作成と接続に必要な Cloud APIs を有効にします。

    1. API を有効にする

    2. [プロジェクトを確認] の手順で、[次へ] をクリックして、変更するプロジェクトの名前を確認します。

    3. [API を有効にする] の手順で、[有効にする] をクリックして、次の機能を有効にします。

      • Cloud SQL for PostgreSQL API
      • Knowledge Catalog API

データベースを作成して接続する

  1. インスタンスを作成する。
  2. インスタンスに接続してデータベースを作成します。

環境を準備する

パラメータ化されたセキュアビューでクエリを実行する準備をするには、データベース、データベース ロール、parameterized_views 拡張機能、アプリケーション スキーマを設定します。

データベース フラグを有効にする

必要な拡張機能ライブラリを読み込む cloudsql.enable_parameterized_views データベース フラグを有効にします。詳細については、インスタンスのデータベース フラグを構成するをご覧ください。

データベースを設定する

  • アプリケーション データとパラメータ化されたセキュアビュー用に database というデータベースを作成します。詳細については、データベースを作成するをご覧ください。

データベース ロール、拡張機能、アプリケーション スキーマを作成する

  1. Google Cloud コンソールで、[Cloud SQL] ページに移動します。

    Cloud SQL に移動

  2. リストからインスタンスを選択します。

  3. ナビゲーション メニューで [Cloud SQL Studio] をクリックします。

  4. postgres 認証を使用して Studio にログインします。

  5. [認証] をクリックします。[エクスプローラ] ペインに、データベースにあるオブジェクトのリストが表示されます。

  6. [新しい SQL エディタタブ] または [新しいタブ] をクリックして、新しいタブを開きます。

  7. パラメータ化されたビューを使用するには、データベースに parameterized_views 拡張機能を作成します。

    -- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;

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

  8. 組み込みの postgres ユーザーなど、スーパーユーザー権限を持つユーザーとしてログインします。

  9. パラメータ化されたセキュアビューに対してクエリを実行する新しいデータベース ロールを作成します。これは、アプリケーションがデータベースに接続してログインし、公開関数またはオブジェクトへのアクセスを最小限に制限してクエリを実行するために使用する Cloud SQL ロールです。

    CREATE ROLE psv_user WITH LOGIN PASSWORD '...';

    詳細については、CREATE USER をご覧ください。

  10. 管理者権限を持つユーザーとして接続します。

    SET role TO postgres;

  11. テーブルを含むスキーマを作成します。

    CREATE SCHEMA store;

  12. テーブルを作成してデータを挿入します。

    CREATE TABLE store.checked_items(bag_id INT,timestamp TIMESTAMP, loc_code CHAR(3), scan_type CHAR(1), location TEXT, customer_id INT);

INSERT INTO store.checked_items (bag_id, timestamp, loc_code, scan_type, location, customer_id) VALUES
(101, '2023-10-26 10:00:00', 'ABC', 'I', 'Warehouse A', 123),
(102, '2023-10-26 10:15:30', 'DEF', 'O', 'Loading Dock B', 456),
(103, '2023-10-26 10:30:45', 'GHI', 'I', 'Conveyor Belt 1', 789),
(104, '2023-10-26 11:00:00', 'JKL', 'O', 'Shipping Area C', 101),
(105, '2023-10-26 11:45:15', 'MNO', 'I', 'Sorting Station D', 202),
(106, '2023-10-26 12:00:00', 'PQR', 'O', 'Truck Bay E', 303);

パラメータ化されたセキュアビューを作成し、アクセス権限を設定する

パラメータ化されたセキュアビューを作成し、ベーステーブルとビューに適切なアクセス権を設定する手順は次のとおりです。

  1. Google Cloud コンソールで、[Cloud SQL] ページに移動します。

    Cloud SQL に移動

  2. リストからインスタンスを選択します。

  3. ナビゲーション メニューで [Cloud SQL Studio] をクリックします。

  4. Studio にログインし、postgres として database に接続します。

  5. [認証] をクリックします。[エクスプローラ] ペインに、データベースにあるオブジェクトのリストが表示されます。

  6. [新しい SQL エディタタブ] または [新しいタブ] をクリックして、新しいタブを開きます。

  7. ビューへのアクセスを制限するには、パラメータ化されたビューを作成します。

    CREATE VIEW store.secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM store.checked_items t
WHERE customer_id = $@app_end_userid;

  8. ビューへのアクセス権を付与します。

    GRANT SELECT ON store.secure_checked_items TO psv_user;

  9. ビューにアクセスするには、スキーマへのアクセス権を付与します。

    GRANT USAGE ON SCHEMA store TO psv_user;

  10. ベーステーブルへの直接アクセスを取り消します。

    REVOKE ALL PRIVILEGES ON store.checked_items FROM psv_user;

    注: パラメータ化ビューのオーナーには、ベーステーブルに対する SELECT 権限が必要です。また、パラメータ化ビューのユーザーには、ビューのスキーマに対する USAGE 権限と、ビューに対する SELECT 権限が必要です。たとえば、postgres ユーザーがパラメータ化ビューを所有している場合、ベーステーブルに対する SELECT 権限が付与されます。一方、psv_user には、store スキーマに対する USAGE 権限と、ビューに対する SELECT 権限が必要です。

  11. postgres 管理者としてログインし、IAM 認証ユーザーに psv_user ロールを付与します。

    GRANT psv_user TO "IAM_USER_EMAIL";

    IAM_USER_EMAIL は、IAM ユーザーのメールアドレスに置き換えます。

データ セキュリティを確認する

パラメータ化されたセキュアビューが指定されたビューへのアクセスを制限していることを確認するには、IAM 認証ユーザーとしてデータベースにログインします。Cloud SQL では、IAM ユーザーは割り当てられたデータベース ロールの権限を継承します。

  1. IAM 認証済みユーザーとしてデータベースにログインします。

  2. ベーステーブルにアクセスできないことを確認します。

    SELECT * FROM store.checked_items;
ERROR:  permission denied for table checked_items

  3. execute_parameterized_query 関数を使用して、パラメータ化されたセキュアビューにアクセスします。

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

    1. SQL 構文と PSV パラメータを含む QueryData リクエストを使用して、パラメータ化されたセキュアビューをクエリします。

        curl -X POST \
    "https://geminidataanalytics.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION:queryData" \
    -H "Authorization: Bearer $(gcloud auth print-access-token)" \
    -H "Content-Type: application/json; charset=utf-8" \
    -d '{
      "prompt": "Show me the checked items.",
      "context": {
        "datasource_references": {
          "cloud_sql_reference": {
            "database_reference": {
              "engine": "POSTGRESQL",
              "project_id": "PROJECT_ID",
              "region": "REGION",
              "instance_id": "INSTANCE_ID",
              "database_id": "DATABASE_ID"
            }
          }
        },
        "parameterized_secure_view_parameters": {
          "parameters": {
            "app_end_userid": "303"
          }
        }
      },
      "generation_options": {
        "generate_query_result": true,
        "generate_natural_language_answer": true,
        "generate_explanation": true
      }
    }'

      次の値を置き換えます。

      • PROJECT_ID: 実際の Google Cloud プロジェクト ID。
      • REGION: Cloud SQL for PostgreSQL インスタンスが配置されているリージョン。
      • INSTANCE_ID: Cloud SQL for PostgreSQL インスタンスの ID。
      • DATABASE_ID: Cloud SQL for PostgreSQL データベースの ID。

    クリーンアップ

    クラスタの削除

    始める前にで作成したクラスタを削除すると、作成したすべてのオブジェクトも削除されます。

    1. Google Cloud コンソールで、[Cloud SQL] ページに移動します。

      Cloud SQL に移動

    2. リストからインスタンスを選択します。

    3. [削除] をクリックします。

    4. インスタンス名を入力し [削除] をクリックして、インスタンスの削除を確定します。