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

このチュートリアルでは、Cloud SQL for PostgreSQL でパラメータ化されたセキュアビューを使用して、Cloud SQL Studio または psql を使用してパラメータ化されたビューへのユーザー アクセスを制限する方法について説明します。

目標

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

環境を準備する

  1. Cloud SQL インスタンスの cloudsql.enable_parameterized_views データベース フラグを有効にします。このフラグを変更するには、データベースを再起動する必要があります。

  2. postgres ユーザーとしてデータベースに接続します。

    psql -U postgres

  3. データベースに parameterized_views 拡張機能を作成します。

    CREATE EXTENSION parameterized_views;

  4. クエリを実行するための新しいデータベース ロールを作成します。

    CREATE ROLE psv_user WITH LOGIN PASSWORD 'PASSWORD';

  5. アプリケーション データのスキーマとテーブルを作成します。

    CREATE SCHEMA app_schema;
CREATE TABLE app_schema.items(item_id INT, item_name TEXT, description TEXT, owner_id INT);

INSERT INTO app_schema.items (item_id, item_name, description, owner_id) VALUES
(1, 'Book', 'A great read', 123),
(2, 'Laptop', 'Work machine', 456),
(3, 'Pencil', 'For writing', 123);

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

  1. パラメータ化されたセキュアビューを作成します。

    CREATE VIEW app_schema.user_items_view WITH (security_barrier) AS
SELECT item_id, item_name, description
FROM app_schema.items
WHERE owner_id = $@current_user_id;

  2. ビューとスキーマへのアクセス権をアプリケーション ロールに付与します。

    GRANT USAGE ON SCHEMA app_schema TO psv_user;
GRANT SELECT ON app_schema.user_items_view TO psv_user;

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

    REVOKE ALL PRIVILEGES ON app_schema.items FROM psv_user;

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

  1. psv_user として接続します。

    psql -U psv_user -d postgres

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

    SELECT * FROM app_schema.items;
-- ERROR:  permission denied for table items

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

    SELECT * FROM parameterized_views.execute_parameterized_query(
  query => 'SELECT * from app_schema.user_items_view',
  param_names => ARRAY ['current_user_id'],
  param_values => ARRAY ['123']
);

    結果には、owner_id が 123 のアイテムのみが含まれます。

次のステップ