使用參數化安全檢視畫面，安全地控管應用程式資料存取權

本教學課程說明如何在 PostgreSQL 適用的 Cloud SQL 中使用參數化安全檢視區塊，透過 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 的項目。

後續步驟