ביקורת ב-PostgreSQL באמצעות pgAudit

‫MySQL | PostgreSQL | SQL Server

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

לקבלת מידע כללי על תוספים של PostgreSQL ב-Cloud SQL, אפשר לעיין במאמר בנושא תוספים של PostgreSQL.

סקירה כללית

אפשר לבצע ביקורת על מסד נתונים ב-Cloud SQL ל-PostgreSQL באמצעות התוסף pgAudit בקוד פתוח.

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

התוסף pgAudit חל על פקודות ושאילתות SQL שהופעלו. לעומת זאת, כדאי להשתמש ביומני הביקורת של Cloud כדי לבדוק פעולות אדמין ותחזוקה שבוצעו במכונת Cloud SQL.

מידע נוסף על יומני ביקורת ב-Cloud SQL זמין בדף יומני ביקורת.

הגדרת ביקורת מסד נתונים ב-Cloud SQL

השלבים לרישום ביומן הביקורת באמצעות התוסף pgAudit כוללים:

הפעלת הדגל cloudsql.enable_pgaudit ב-Cloud SQL.
הפעלת פקודה ליצירת התוסף pgAudit.
הגדרת ערכים לדגל pgaudit.log.

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

הגדרת ביקורת

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

הדגל הראשוני להפעלת ביקורת

ב-Cloud SQL, משתמשים בדגלים של מסד הנתונים להרבה פעולות, כולל שינוי פרמטרים של PostgreSQL והגדרת מכונה. הדגל cloudsql.enable_pgaudit מאפשר ביקורת עבור מופע מסוים של מסד נתונים. אפשר לשנות את הערך של הדגל cloudsql.enable_pgaudit באמצעות המסוף Google Cloud או הפקודה gcloud.

משתמשים בהוראות הסטנדרטיות להגדרת דגלים כדי להפעיל את הדגל cloudsql.enable_pgaudit ולהגדיר את הערך שלו ל-on. לדוגמה, כדי להשתמש בפקודה gcloud, מציינים את הפרטים הבאים ומחליפים את שם המופע ב-[INSTANCE_NAME]:

gcloud sql instances patch [INSTANCE_NAME] --database-flags cloudsql.enable_pgaudit=on

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

מריצים את הפקודה כדי ליצור את התוסף pgAudit

אחרי שמפעילים את הדגל של מסד הנתונים, מריצים את הפקודה CREATE EXTENSION באמצעות לקוח psql תואם. הפקודה הבאה יוצרת את התוסף pgAudit לכל מסדי הנתונים במכונת Cloud SQL:

CREATE EXTENSION pgaudit;

הגדרת ערכים לדגל `pgaudit.log`

כדי להגדיר ערכים לדגל pgaudit.log, משתמשים בההוראות הרגילות להגדרת דגלים.

לדוגמה, כדי להפעיל את הביקורת לכל פעולות מסד הנתונים במופע, אפשר להשתמש בפקודה gcloud הבאה:

  gcloud sql instances patch [INSTANCE_NAME] --database-flags \
  cloudsql.enable_pgaudit=on,pgaudit.log=all

הגדרת הגדרות אחרות למסד הנתונים

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

צפייה ביומני ביקורת של מסד נתונים

כדי להציג יומני ביקורת, צריך להפעיל את יומני הביקורת Data Access בפרויקט. יומני pgAudit שנוצרו עבור מופע מסוים נשלחים אל Cloud Logging כיומני ביקורת של גישה לנתונים. המשתמשים יכולים לראות את יומני pgAudit שנוצרו דרך האפליקציה Logs Explorer.

באפליקציית Logs Explorer, אפשר לראות את יומני pgAudit על ידי בחירה במסנן היומן cloudaudit.googleapis.com/data_access.

לחלופין, אפשר להשתמש בשאילתה הבאה כדי להציג את כל יומני pgAudit עבור פרויקט Cloud SQL נתון:

resource.type="cloudsql_database"
logName="projects/<your-project-name>/logs/cloudaudit.googleapis.com%2Fdata_access"
protoPayload.request.@type="type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"

פורמט היומן של pgAudit

לכל רשומה ביומן pgAudit ביומני הביקורת Data Access יש שדות שמייצגים את המידע שנאסף לגבי שאילתה.

לדוגמה:

{
  protoPayload: {
    @type: "type.googleapis.com/google.cloud.audit.AuditLog"
    methodName: "cloudsql.instances.query"
    request: {
      @type: "type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"
      auditClass: "READ"
      auditType: "SESSION"
      chunkCount: "1"
      chunkIndex: "1"
      command: "SELECT"
      database: "finance"
      databaseSessionId: 2209692
      parameter: "[not logged]"
      statement: "SELECT * FROM revenue"
      statementId: 2
      substatementId: 1
      user: "alice"
    }
  }
}

אלה תיאורים של השדות ביומני הביקורת Data Access:

auditClass. סוג ההצהרה שנרשמה ביומן. הערכים האפשריים הם READ,‏ WRITE,‏ FUNCTION,‏ ROLE,‏ DDL,‏ MISC ו-MISC_SET.
‫auditType. ‫SESSION או OBJECT.
‫chunkCount. פיצול יכול להתרחש בנתונים שסופקו בשדות parameter ו-statement. בשדה chunkCount מצוין המספר הכולל של חלקי המידע. כדאי לעיין גם בתיאור של השדה chunkIndex.
‫chunkIndex. מציין את מספר האינדקס של חלקי הנתונים בשדות parameter ו-statement (במאגר request הנוכחי). המספר הראשוני הוא 1. כדאי לעיין גם בתיאור של השדה chunkCount.
Command. לדוגמה, ALTER TABLE או SELECT.
פרמטר. התוכן של השדה הזה נקבע לפי השדה chunkIndex. אפשר לעיין בתיאור של השדה chunkIndex. אם הערך של pgaudit.log_parameter מוגדר, השדה parameter יכול להכיל את פרמטרים של ההצהרה כנתוני CSV עם מרכאות. אם אין פרמטרים, השדה הזה מכיל [none]. אחרת, השדה הזה מכיל את הערך [not logged].
statement. ההצהרה שהופעלה בקצה העורפי. התוכן של השדה chunkIndex יכול לקבוע את התוכן של השדה statement. אפשר לעיין בתיאור של השדה chunkIndex.
‫statementId. מזהה ייחודי של הדוח בסשן הזה. כל מזהה של דף פירוט מייצג קריאה לשרת. מזהי הדוחות הם עוקבים, גם אם חלק מהדוחות לא מתועדים.
‫substatementId. מזהה רציף לכל תת-דוח בתוך הדוח הראשי.

חלק מהשדות האלה מתוארים גם במסמכי התיעוד של pgAudit.

השבתת הביקורת

כדי להשבית את הביקורת במסד הנתונים, מגדירים את הערך של האפשרות cloudsql.enable_pgaudit ל-off. אפשר לשנות את הערך דרך מסוף Google Cloud או באמצעות הפקודה gcloud. כדי להשבית את הדגל cloudsql.enable_pgaudit, פועלים לפי ההוראות הרגילות להשבתת דגלים.

בנוסף, מריצים את הפקודה DROP EXTENSION באמצעות לקוח psql תואם, כדי להסיר את מצב התוסף:

DROP EXTENSION pgaudit;

התאמה אישית של יומני ביקורת של מסד נתונים ב-Cloud SQL

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

למידע נוסף על היכולות של התוסף, אפשר לעיין במסמכי התיעוד של pgAudit.

דרישה להרשאות של משתמש-על

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

הגדרת ביקורת לכל הפעולות במסד הנתונים במופע

כדי להגדיר ביקורת לכל מסדי הנתונים במופע, צריך להחיל את ההגדרות של pgAudit ברמת המערכת. אפשר להגדיר את פרמטרי הביקורת ברמת המערכת רק כדגלים של מסד נתונים דרך המסוף Google Cloud או הפקודה gcloud. לדוגמה, כדי להפעיל ביקורת על כל פעולות מסד הנתונים במופע, אפשר להשתמש בפקודה הבאה של gcloud:

  gcloud sql instances patch [INSTANCE_NAME] --database-flags \
  cloudsql.enable_pgaudit=on,pgaudit.log=all

הגדרת פעולות ספציפיות בכל מסדי הנתונים של המופע

כדי לבצע ביקורת בכל מסדי הנתונים של המופע, אפשר להשתמש במסוףGoogle Cloud או בפקודה gcloud. לדוגמה, כדי להפעיל את הביקורת רק לפעולות קריאה וכתיבה במופע, אפשר להשתמש בפקודה gcloud הבאה. בדוגמה הזו נעשה שימוש בתחביר מבוסס-רשימה כדי לציין כמה ערכים:

  gcloud sql instances patch [INSTANCE_NAME] \
  --database-flags ^:^cloudsql.enable_pgaudit=on:pgaudit.log=read,write

הפקודה מחליפה את הדגלים הקיימים במסד הנתונים.

הגדרת ביקורת למסד נתונים ספציפי

כדי להגדיר ביקורת למסד נתונים ספציפי, צריך להגדיר את הפרמטרים של pgAudit ברמת מסד הנתונים. לדוגמה, אפשר להשתמש בפקודת ה-SQL הבאה כדי להפעיל ביקורת על קריאה/כתיבה במסד נתונים בשם finance:

finance=> ALTER DATABASE finance SET pgaudit.log = 'read,write';

הגדרת ביקורת ליחס

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

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

לדוגמה, כדי להגדיר ביקורת לכל השאילתות SELECT בקשר salary במסד הנתונים employee, אפשר להשתמש בפקודות הבאות:

employee=> CREATE ROLE auditor WITH NOLOGIN;
employee=> ALTER DATABASE employee SET pgaudit.role = 'auditor';
employee=> GRANT SELECT ON salary TO auditor;

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

לדוגמה, הפקודה הבאה מגדירה שיומני הביקורת יתעדכנו רק כשמתבצעת גישה לעמודות income ו-tax_status מהקשר salary:

employee=> GRANT SELECT(income, tax_status) ON salary TO auditor;

הגדרת ביקורת למשתמש במסד נתונים

אפשר להפעיל את האודיט עבור משתמש ספציפי על ידי הגדרת הפרמטר pgaudit.log ברמה של ROLE.

לדוגמה, פקודת ה-SQL הבאה מגדירה ביקורת לכל פעולות מסד הנתונים שמבוצעות על ידי המשתמש Alice:

finance=> ALTER ROLE alice SET pgaudit.log = 'all';

טיפים לניהול ביקורת ב-Cloud SQL

כשמתאימים אישית את אופן הפעולה של הביקורת, חשוב לזכור את הנקודות הבאות:

כשמשביתים את הדגל של מסד הנתונים cloudsql.enable_pgaudit, רישום הביקורת נפסק באופן מיידי. עם זאת, ההגדרות של pgAudit שהוחלו (לדוגמה, הגדרות הפרמטר pgaudit.log) נשמרות, אלא אם הן מוסרות באופן מפורש.
הפעלה מחדש של מופע מסד הנתונים מתבצעת בכל פעם שמשנים את ערך הדגל של מסד הנתונים עבור cloudsql.enable_pgaudit.
למשתמשי מסד נתונים שנוצרו באמצעות פקודות CREATE ROLE מפורשות אין הרשאה לשנות את הגדרות הביקורת. רק משתמשי מסד נתונים שנוצרו באמצעות המסוף של Google Cloud ופקודת gcloud יכולים לשנות את הגדרות הביקורת.
כשמפעילים גם רישום ביומן ביקורת של סשנים וגם רישום ביומן ביקורת של אובייקטים, ההצהרות שקשורות לשניהם מתווספות ליומנים. רישום ביומן של סשנים ורישום ביומן של אובייקטים לא מבטלים או משנים אחד את השני.

מגבלות של התוסף pgAudit ב-Cloud SQL ל-PostgreSQL

יומני ביקורת נכתבים באופן זמני לדיסק של המופע שלהם, ותופסים מקום בדיסק לפני שהיומנים נשלחים ל-Cloud Logging. לכן, חשוב לעיין בכל המידע הבא לפני שמשתמשים בתכונה הזו:

קצב ההטמעה של היומנים הוא 4MB לשנייה. אם העומס שנוצר מהפקת היומנים חורג מקצב ההעברה, יכולות לקרות הבעיות הבאות:
- יכולה להיות צמיחה לא רצויה בשימוש בדיסק.
- יכול להיות שלא יהיה יותר מקום בכונן.
אם הפעלתם את התכונה הזו ואתם מריצים הרבה שאילתות שעומדות בקריטריונים של הביקורת, השימוש בדיסק עלול לגדול מהר מדי.
לפני שמשתמשים בתכונה הזו, כדאי לתכנן:
- הפעלת הגדלות אוטומטיות של נפח האחסון
- עוקבים אחרי השימוש הכולל בדיסק. אי אפשר לעקוב בנפרד אחרי העומס שנוצר מהפקת היומנים. משתמשים במדד cloudsql.googleapis.com/database/disk/utilization בMetrics Explorer.
- במקרה הצורך, אפשר להקטין את השימוש בדיסק על ידי הפעלת פחות שאילתות או צמצום הביקורת.
אם לא נשאר יותר מקום בדיסק, יכול להיות שיומני הביקורת של חלק מהשאילתות יאבדו.