שימוש בתצוגות מאובטחות עם פרמטרים

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

מידע נוסף זמין בסקירה הכללית על תצוגות מאובטחות עם פרמטרים ובמדריך בנושא תצוגות מאובטחות עם פרמטרים.

לפני שמתחילים

במאמר הזה אנחנו מניחים שיצרתם מכונת Cloud SQL ל-PostgreSQL.

כדי להשתמש בתצוגות מאובטחות עם פרמטרים, צריך לבצע את הפעולות הבאות:

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

  2. משתמשים ב-Cloud SQL Studio או ב-psql כדי ליצור את התוסף parameterized_views בכל מסד נתונים שבו נוצרת תצוגה עם פרמטרים:

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

    כשיוצרים את התוסף, המערכת יוצרת גם סכימה בשם parameterized_views, כדי שממשקי ה-API ייכללו במרחב השמות של הסכימה הזו, וכדי שלא יהיה קונפליקט בין ממשקי ה-API לבין ממשקי API קיימים.

יצירת תצוגה מאובטחת עם פרמטרים

כדי ליצור תצוגה מאובטחת עם פרמטרים, פועלים לפי השלבים הבאים:

  1. מריצים את פקודת ה-DDL‏ CREATE VIEW באמצעות האפשרות security_barrier:

    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: הצהרת התנאי שמשמשת להגבלת משתמשי האפליקציה כך שהם יוכלו לראות רק את השורות שיש להם הרשאה לגשת אליהן. מוסיפים את הפרמטרים הנדרשים באמצעות התחביר $@PARAMETER_NAME בסעיף WHERE. תרחיש נפוץ לדוגמה הוא בדיקת הערך של עמודה באמצעות WHERE COLUMN = $@PARAMETER_NAME.

      ‫$@PARAMETER_NAME מציין פרמטר של תצוגה עם שם. הערך שלו מסופק כשמשתמשים ב-API‏ execute_parameterized_query. הדרישות לגבי פרמטרים של תצוגה עם שם:

      • פרמטרים של תצוגה עם שם חייבים להתחיל באות (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. להגביל את האובייקטים שהתפקיד הזה יכול לגשת אליהם לקבוצה המינימלית הנדרשת של פונקציות ואובייקטים ציבוריים שהאפליקציה צריכה. מומלץ להימנע ממתן גישה לסכימות ולטבלאות שלא מוגדרות כציבוריות.

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

    מידע נוסף מופיע במאמר בנושא יצירה וניהול של משתמשים.

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

כדי להריץ שאילתה על תצוגה מאובטחת עם פרמטרים, משתמשים באחת מהאפשרויות הבאות:

  • מבוסס על JSON: אפשר להשתמש ב-API הזה כדי להריץ את השאילתה בפעם אחת ולהחזיר שורות JSON.
  • מבוסס-סמן: משתמשים ב-API הזה כשמריצים שאילתות ארוכות או כשמריצים שאילתות גדולות ורוצים לאחזר את התוצאות בקבוצות.
  • הצהרת PREPARE .. AS RESTRICTED: משתמשים ב-PREPARE .. AS RESTRICTED כדי להגדיר את תוכנית השאילתה, ואז מריצים את EXECUTE ... WITH VIEW PARAMETERS (...) כדי להפעיל אותה עם פרמטרים ספציפיים לתצוגה.

‫API בפורמט JSON

מריצים את הפונקציה 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() עם שם סמן, שיוצרת ומחזירה סמן בהיקף טרנזקציה:

-- 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. יוצרים את ההצהרה המוכנה.

    -- 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>;

בדוגמה הבאה נעשה שימוש בתצוגה מאובטחת עם פרמטרים בשם user_specific_items, שנדרש לה פרמטר בשם $@app_user_id.

-- 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 for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

ההרצה השנייה של השאילתה:

-- 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 מציינים את הפרמטרים של התצוגה המאובטחת עם הפרמטרים (123, 456), בנפרד מהפרמטרים המיקומיים של השאילתה המוכנה (%Laptop%, %Book%).

לסיום, מנקים את ההצהרה המוכנה.

-- 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 כדי להציג רשימה של כל התצוגות עם פרמטרים (אלה שמכילות לפחות פרמטר אחד עם שם $@...).

SELECT * FROM parameterized_views.all_parameterized_views;

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