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

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

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

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

ההנחה בדף הזה היא שכבר יצרתם אשכול ומכונה של AlloyDB. מידע נוסף זמין במאמר יצירת מסד נתונים.

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

  1. מפעילים את הדגל לניהול מסד הנתונים parameterized_views.enabled, שטוען את ספריות התוספים הנדרשות. אתם צריכים להפעיל את הדגל הזה, גם אם צוות AlloyDB הפעיל אותו בעבר. מידע נוסף על הפעלת הדגל לניהול מסד הנתונים זמין במאמר הגדרת דגלים לניהול מסד נתונים של מכונה.

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

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

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

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

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

  1. מריצים את פקודת ה-DDL CREATE VIEW, כמו בדוגמה הבאה:

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

    בדוגמה הקודמת, התצוגה המאובטחת עם הפרמטרים מאפשרת גישה לשלוש עמודות מטבלה בשם checked_items. התצוגה מגבילה את התוצאות לשורות שבהן checked_items.customer_id תואם לפרמטר נדרש.

    משתמשים במאפיינים הבאים:

    • יוצרים את התצוגה באמצעות האפשרות security_barrier.
    • כדי להגביל את משתמשי האפליקציה כך שיוכלו לראות רק את השורות שמותר להם לגשת אליהן, מוסיפים את הפרמטרים הנדרשים בהגדרת התצוגה באמצעות התחביר $@PARAMETER_NAME. תרחיש נפוץ הוא בדיקת הערך של עמודה בסעיף WHERE באמצעות COLUMN = $@PARAMETER_NAME.
    • ‫$@PARAMETER_NAME מציין פרמטר של תצוגה עם שם. הערך שלו מתקבל כשמשתמשים ב-API של execute_parameterized_query. הדרישות לגבי פרמטרים של תצוגה בעלת שם:
      • פרמטרים של תצוגה עם שם חייבים להתחיל באות (a-z).
      • אפשר להשתמש באותיות עם סימנים דיאקריטיים, באותיות לא לטיניות ובקו תחתון (_).
      • התווים הבאים יכולים להיות אותיות, קווים תחתונים או ספרות (0-9).
      • הפרמטרים של התצוגה בעלת השם לא יכולים להכיל את התו $.
      • הפרמטרים של תצוגות מפורטות הם תלויי אותיות רישיות. לדוגמה, $@PARAMETER_NAME מתפרש באופן שונה מ-$@parameter_name.

  2. נותנים הרשאת SELECT בתצוגה לכל משתמש במסד הנתונים שמורשה לשלוח שאילתות לתצוגה.

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

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

הגדרת האבטחה של האפליקציה

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

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

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

    1. נותנים לתפקיד החדש הרשאות לתצוגות המאובטחות, שבדרך כלל כוללות הרשאות SELECT לתצוגות והרשאות USAGE לסכימות.

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

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

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

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

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

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

  • מבוסס-JSON: משתמשים ב-API הזה כדי להריץ את השאילתה בבת אחת ולהחזיר שורות JSON.

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

  • הצהרת PREPARE EXECUTE: משתמשים בה להצהרות מוכנות שאפשר להריץ כמה פעמים עם ערכי פרמטרים שונים.

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

‫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]
)

מחליפים את מה שכתוב בשדות הבאים:

  • ‫SQL_QUERY: שאילתת SQL שסעיף ה-FROM שלה מתייחס לתצוגה מאובטחת אחת או יותר עם פרמטרים.

  • ‫PARAMETER_NAMES: רשימה של שמות פרמטרים שצריך להעביר כמחרוזות.

  • ‫PARAMETER_VALUES: רשימה של ערכי פרמטרים שצריך להעביר.

    • הרשימה הזו צריכה להיות באותו גודל כמו הרשימה param_names, וסדר הערכים צריך להתאים לסדר השמות.

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

הפונקציה מחזירה טבלה של אובייקטים בפורמט JSON. כל שורה בטבלה שווה לערך ROW_TO_JSON() של שורת התוצאה של השאילתה המקורית.

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

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

השימוש ב-API הזה מגביל את גודל קבוצת התוצאות לפי הגודל בקילובייט (KB) של התוצאות ולפי מספר השורות. אפשר להגדיר את המגבלות האלה באמצעות הדגלים parameterized_views.json_results_max_size ו-parameterized_views.json_results_max_rows.

CURSOR API

ל-API הזה יש מגבלות כי הוא מצהיר על סמן לשאילתה הנתונה. לכן, השאילתה צריכה להיות תואמת לסמנים של PostgreSQL. לדוגמה, ה-API של CURSOR לא תומך בהצהרות DO או SHOW.

בנוסף, ה-API הזה לא מגביל את התוצאות לפי גודל או לפי מספר השורות שמוחזרות.

מריצים את הפונקציה execute_parameterized_query(), שיוצרת ומחזירה CURSOR ברמת הטרנזקציה, שמשמש לאחזור תוצאות השאילתה:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

מחליפים את מה שכתוב בשדות הבאים:

  • ‫SQL_QUERY: שאילתת SQL שסעיף ה-FROM שלה מתייחס לתצוגה מאובטחת אחת או יותר שמכילה פרמטרים.

  • ‫CURSOR_NAME: שם הסמן שצריך להצהיר עליו.

  • ‫PARAMETER_NAMES: רשימה של שמות פרמטרים שצריך להעביר כמחרוזות.

  • ‫PARAMETER_VALUES: רשימה של ערכי פרמטרים שצריך להעביר. הרשימה הזו צריכה להיות באותו גודל כמו הרשימה param_names, וסדר הערכים צריך להתאים לסדר השמות. סוג הערכים המדויק נגזר מהשאילתה ומההגדרה של התצוגה עם הפרמטרים. המרות סוגים מתבצעות לפי הצורך וכשאפשר, בהתאם לערך הפרמטר הנתון. אם יש חוסר התאמה בין הסוגים, תופיע שגיאה.

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

  -- Start a transaction, which is the default lifetime of a CURSOR
  BEGIN;
  -- Create a cursor called 'mycursor'
  SELECT * FROM parameterized_views.execute_parameterized_query(
   query => 'SELECT * FROM secure_checked_items',
   cursor_name => 'mycursor',
   param_names => ARRAY ['app_end_userid'],
   param_values => ARRAY ['40']
  );

  -- Fetch the results
  FETCH ALL FROM mycursor;
  -- End the transaction, which cleans up the cursor
  END;

הסמן שמוחזר הוא סמן NO SCROLL WITHOUT HOLD. אי אפשר להשתמש בסמן כדי לאחזר שורות באופן לא רציף, למשל בכיוון הפוך. אי אפשר להשתמש בסמן מחוץ לעסקה שבה הוא נוצר.

הצהרת PREPARE

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

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

ל-API הזה יש מגבלות כי ההצהרה חייבת להיות מותרת בהצהרת PREPARE, כלומר יש תמיכה רק בהצהרות SELECT ו-VALUES.

בנוסף, ה-API הזה לא מגביל את התוצאות לפי גודל או מספר השורות שמוחזרות.

כדי ליצור הצהרה מוכנה שמפנה לתצוגות עם פרמטרים, מריצים את הפקודה PREPARE .. AS RESTRICTED:

PREPARE pquery (POSITIONAL_PARAM_TYPES)
        AS RESTRICTED SQL_QUERY;
EXECUTE pquery (POSITIONAL_PARAM_VALUES)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

מחליפים את מה שכתוב בשדות הבאים:

  • ‫POSITIONAL_PARAM_TYPES: סוג נתונים אחד או יותר של פרמטרים לפי מיקום שמשמשים בשאילתת RESTRICTED.

  • ‫SQL_QUERY: שאילתת SQL שיכולה להפנות לתצוגות שמכילות פרמטרים.

  • ‫POSITIONAL_PARAM_VALUES: הערכים בפועל שמוחלפים בפרמטרים הממוקמים שמוגדרים בהצהרה PREPARE.

  • ‫VIEW_PARAM_NAME: שם הפרמטר שצפוי בתצוגות עם הפרמטרים שאליהן מפנה השאילתה RESTRICTED.

  • ‫VIEW_PARAM_VALUE: הערכים בפועל שמועברים לפרמטרים התואמים של VIEW_PARAM_NAME בתצוגות עם הפרמטרים.

כדי לכלול פרמטרים בהצהרה מוכנה, צריך לספק רשימה של סוגי נתונים בהצהרה PREPARE. בהצהרה שאתם מכינים, אתם מתייחסים לפרמטרים לפי מיקום, למשל באמצעות $1 ו-$2.

משתמשים בפקודה EXECUTE .. WITH VIEW PARAMETERS כדי להריץ הצהרה שהוכנה מראש באמצעות הפקודה PREPARE .. AS RESTRICTED. אם בהצהרת PREPARE שיצרה את ההצהרה צוינו פרמטרים של מיקום, צריך להעביר קבוצה תואמת של פרמטרים להצהרת EXECUTE. צריך להעביר את כל הפרמטרים של התצוגה עם השם שנדרשים לתצוגות עם פרמטרים בסעיף WITH VIEW PARAMETERS.

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

PREPARE pquery (timestamp) AS RESTRICTED SELECT * FROM secure_checked_items WHERE timestamp > $1;

EXECUTE pquery (current_date - 1) WITH VIEW PARAMETERS (app_end_userid = 40);
EXECUTE pquery (current_date - 30) WITH VIEW PARAMETERS (app_end_userid = 40);

הגבלות שנאכפות על שאילתות

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

  • אסור להפעיל באופן רקורסיבי ממשקי API כלשהם – execute_parameterized_query או באמצעות EXECUTE .. WITH VIEW PARAMETERS – כדי להשתמש רק בערכים שצוינו על ידי האפליקציה. ההגבלה הזו גם מונעת שימוש בשאילתה כדי לעקוף את מעטפת האבטחה של קבוצת ערכי הפרמטרים הנתונה.

  • חלק מהתוספים שמתחילים סשן חדש ברקע אסורים, כולל התוספים dblink,‏ pg_cron ו-pg_background.

  • מבני השאילתות הבאים מותרים או מוגבלים:

    • מותר להשתמש רק בהצהרות SELECT לקריאה בלבד.

    • מותר להשתמש בהצהרות SHOW, בהצהרות CALL ובהצהרות DO לקריאה בלבד.

    • אי אפשר להשתמש בפקודות DML כמו INSERT,‏ UPDATE ו-DELETE.

    • אסור להשתמש בהצהרות DDL כמו CREATE TABLE ו-ALTER TABLE.

    • אי אפשר להשתמש בסוגי הצהרות אחרים, כמו LOAD, ‏ SET, ‏ CLUSTER,‏ LOCK, ‏ CHECKPOINT ו-EXPLAIN.

  • כברירת מחדל, אסור להשתמש בהצהרות EXPLAIN כדי למנוע התקפות של ערוצים סמויים באמצעות תוכניות לביצוע שאילתות. מידע נוסף זמין במאמר ערוץ סמוי. עם זאת, כדי להריץ הצהרות EXPLAIN, כל משתמש במסד הנתונים עם הרשאות משתמש-על ב-AlloyDB יכול להגדיר את ה-GUC‏ parameterized_views.enable_explain ברמת הסשן לערך on.

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

הצגת רשימה של כל התצוגות שמכילות פרמטרים

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

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

SELECT * FROM parameterized_views.all_parameterized_views;

הפלט אמור להיראות כך:

schemaname |       viewname       | viewowner |                       definition
-----------+----------------------+-----------+---------------------------------------------------------
public     | secure_checked_items | postgres  |  SELECT checked_items.bag_id,                          +
           |                      |           |     checked_items."timestamp",                         +
           |                      |           |     checked_items.location                             +
           |                      |           |    FROM checked_items                                  +
           |                      |           |   WHERE (checked_items.customer_id = $@app_end_userid);

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

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