גישה מאובטחת לנתוני אפליקציות ושליטה בהם באמצעות תצוגות מאובטחות עם פרמטרים

במדריך הזה מוסבר איך להשתמש בתצוגות מאובטחות עם פרמטרים ב-Cloud SQL ל-PostgreSQL כדי להגביל את גישת המשתמשים לתצוגות עם פרמטרים באמצעות Cloud SQL Studio או psql.

מטרות

  • יצירה של תצוגות פרמטריות מאובטחות עם פרמטרים בעלי שם.
  • יוצרים את תפקיד מסד הנתונים שבו האפליקציה משתמשת כדי להתחבר למסד הנתונים ולגשת לתצוגות מאובטחות עם פרמטרים.
  • מעניקים את הרשאות התפקיד החדש לתצוגות המאובטחות ומבטלים את הגישה לטבלאות הבסיס.
  • מתחברים באמצעות התפקיד החדש ומוודאים שאי אפשר לגשת לטבלאות המוגבלות.
  • מריצים שאילתות בתצוגה המאובטחת שמכילה פרמטרים באמצעות הפונקציה execute_parameterized_query.

הכנת הסביבה

  1. מפעילים את הדגל cloudsql.enable_parameterized_views של מסד הנתונים במכונת Cloud SQL. כדי שהשינוי הזה יחול, צריך להפעיל מחדש את מסד הנתונים.

  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.

המאמרים הבאים