pgAudit
تسجيل تدقيق مفتوح المصدر لـ PostgreSQL

مقدمة

توفر إضافة تدقيق PostgreSQL (pgAudit) تسجيل تدقيق تفصيلي للجلسات و/أو الكائنات عبر مرفق تسجيل PostgreSQL القياسي.

الهدف من pgAudit هو تزويد مستخدمي PostgreSQL بالقدرة على إنتاج سجلات تدقيق غالبًا ما تكون مطلوبة للامتثال للشهادات الحكومية أو المالية أو ISO.

التدقيق هو فحص رسمي لحسابات فرد أو منظمة، عادةً بواسطة هيئة مستقلة. تُسمى المعلومات التي يجمعها pgAudit بشكل صحيح مسار تدقيق أو سجل تدقيق. يُستخدم مصطلح سجل التدقيق في هذه الوثائق.

لماذا pgAudit؟

يمكن توفير تسجيل البيانات الأساسية بواسطة مرفق التسجيل القياسي مع log_statement = all. هذا مقبول للمراقبة والاستخدامات الأخرى ولكنه لا يوفر مستوى التفاصيل المطلوب عمومًا للتدقيق. لا يكفي وجود قائمة بجميع العمليات التي تم إجراؤها على قاعدة البيانات. يجب أيضًا أن يكون من الممكن العثور على عبارات معينة تهم المدقق. يظهر مرفق التسجيل القياسي ما طلبه المستخدم، بينما يركز pgAudit على تفاصيل ما حدث أثناء تلبية قاعدة البيانات للطلب.

على سبيل المثال، قد يرغب المدقق في التحقق من إنشاء جدول معين ضمن نافذة صيانة موثقة. قد يبدو هذا مهمة بسيطة لـ grep، ولكن ماذا لو تم تقديم شيء كهذا (تم إخفاؤه عمدًا):

DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

سيعطيك التسجيل القياسي هذا:

LOG:  statement: DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

يبدو أن العثور على الجدول المطلوب قد يتطلب بعض المعرفة بالكود في الحالات التي يتم فيها إنشاء الجداول ديناميكيًا. هذا ليس مثاليًا حيث سيكون من الأفضل البحث فقط عن اسم الجدول. هذا هو المكان الذي يأتي فيه pgAudit. لنفس الإدخال، سينتج هذا المخرج في السجل:

AUDIT: SESSION,33,1,FUNCTION,DO,,,"DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;"
AUDIT: SESSION,33,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)

ليس فقط كتلة DO يتم تسجيلها، ولكن العبارة الفرعية 2 تحتوي على النص الكامل لـ CREATE TABLE مع نوع العبارة ونوع الكائن والاسم المؤهل بالكامل لتسهيل عمليات البحث.

عند تسجيل عبارات SELECT و DML، يمكن تكوين pgAudit لتسجيل إدخال منفصل لكل علاقة مشار إليها في العبارة. لا حاجة للتحليل النحوي للعثور على جميع العبارات التي تلمس جدولًا معينًا. في الواقع، الهدف هو أن نص العبارة يتم توفيره بشكل أساسي للتحليل الجنائي العميق ولا ينبغي أن يكون مطلوبًا للتدقيق.

اعتبارات الاستخدام

اعتمادًا على الإعدادات، من الممكن أن يُنتج pgAudit حجمًا هائلاً من التسجيل. كن حذرًا في تحديد ما يحتاج بالضبط إلى تسجيل التدقيق في بيئتك لتجنب تسجيل الكثير.

على سبيل المثال، عند العمل في بيئة OLAP، ربما لن يكون من الحكمة تسجيل تدقيق لعمليات الإدراج في جدول حقائق كبير. سيكون حجم ملف السجل على الأرجح أكبر بعدة مرات من حجم البيانات الفعلية للإدراج لأن ملف السجل يتم التعبير عنه كنص. نظرًا لأن السجلات تُخزن عمومًا مع نظام التشغيل، فقد يؤدي ذلك إلى استنفاد مساحة القرص بسرعة كبيرة. في الحالات التي لا يمكن فيها تقييد تسجيل التدقيق لجداول معينة، تأكد من تقييم تأثير الأداء أثناء الاختبار وتخصيص مساحة كبيرة على وحدة تخزين السجل. قد يكون هذا صحيحًا أيضًا لبيئات OLTP. حتى لو لم يكن حجم الإدراج مرتفعًا، فإن تأثير الأداء لتسجيل التدقيق قد يؤثر بشكل ملحوظ على زمن الاستجابة.

لتحديد عدد العلاقات التي يتم تسجيل تدقيقها لعبارات SELECT و DML، فكر في استخدام تسجيل تدقيق الكائنات (انظر تدقيق الكائنات). يسمح تسجيل تدقيق الكائنات باختيار العلاقات التي سيتم تسجيلها مما يسمح بتقليل الحجم الإجمالي للسجل. ومع ذلك، عند إضافة علاقات جديدة، يجب إضافتها صراحةً إلى تسجيل تدقيق الكائنات. قد يكون الحل البرمجي حيث يتم استبعاد جداول معينة من التسجيل ويتم تضمين جميع الجداول الأخرى خيارًا جيدًا في هذه الحالة.

توافق إصدار PostgreSQL

يدعم pgAudit PostgreSQL 14 أو أحدث.

لدعم الوظائف الجديدة المقدمة في كل إصدار من PostgreSQL، يحتفظ pgAudit بفرع منفصل لكل إصدار رئيسي من PostgreSQL (حاليًا PostgreSQL 14 - 19) والذي سيتم صيانته بطريقة مماثلة لمشروع PostgreSQL.

بصرف النظر عن إصلاحات الأخطاء، لا يُسمح بمزيد من التطوير للفروع المستقرة. سيكون التطوير الجديد، إن وجد، بدقة للإصدار الرئيسي التالي غير المنشور من PostgreSQL.

إصدارات pgAudit تتعلق بإصدارات PostgreSQL الرئيسية على النحو التالي:

• pgAudit v19.X يهدف إلى دعم PostgreSQL 19.

• pgAudit v18.X يهدف إلى دعم PostgreSQL 18.

• pgAudit v17.X يهدف إلى دعم PostgreSQL 17.

• pgAudit v16.X يهدف إلى دعم PostgreSQL 16.

• pgAudit v1.7.X يهدف إلى دعم PostgreSQL 15.

• pgAudit v1.6.X يهدف إلى دعم PostgreSQL 14.

التجميع والتثبيت

يمكن تجميع pgAudit مقابل نسخة مثبتة من PostgreSQL مع حزم التطوير باستخدام PGXS. يجب أن تعمل التعليمات التالية على معظم أنظمة التشغيل الشبيهة بـ Unix.

استنساخ إضافة pgAudit:

git clone https://github.com/pgaudit/pgaudit.git

الانتقال إلى دليل pgAudit:

cd pgaudit

التحقق من فرع REL_19_STABLE (لاحظ أن الفرع المستقر قد لا يكون موجودًا للإصدارات غير المنشورة من PostgreSQL):

git checkout REL_19_STABLE

بناء وتثبيت pgAudit:

make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-19/bin/pg_config

يمكن العثور على تعليمات الاختبار والتطوير في test.

الإعدادات

يمكن تعديل الإعدادات فقط بواسطة مستخدم خارق (superuser). السماح للمستخدمين العاديين بتغيير إعداداتهم سيهزم الغرض من سجل التدقيق.

يمكن تحديد الإعدادات عالميًا (في postgresql.conf أو باستخدام ALTER SYSTEM ... SET)، على مستوى قاعدة البيانات (باستخدام ALTER DATABASE ... SET)، أو على مستوى الدور (باستخدام ALTER ROLE ... SET). لاحظ أن الإعدادات لا تُورث من خلال التوريث العادي للأدوار وأن SET ROLE لن يغير إعدادات pgAudit الخاصة بالمستخدم. هذا قيد من نظام الأدوار وليس متأصلًا في pgAudit.

يجب تحميل إضافة pgAudit في shared_preload_libraries. وإلا، سيتم رفع خطأ عند وقت التحميل ولن يحدث أي تسجيل تدقيق.

بالإضافة إلى ذلك، يجب استدعاء CREATE EXTENSION pgaudit قبل تعيين pgaudit.log لضمان وظائف pgAudit الصحيحة. تقوم الإضافة بتثبيت مشغلات الأحداث (event triggers) التي تضيف تدقيقًا إضافيًا لـ DDL. سيعمل pgAudit بدون تثبيت الإضافة ولكن عبارات DDL لن تحتوي على معلومات حول نوع الكائن واسمه.

إذا تم إسقاط إضافة pgaudit وتحتاج إلى إعادة إنشائها، فيجب إلغاء تعيين pgaudit.log أولاً وإلا سيتم رفع خطأ.

pgaudit.log

يحدد أي فئات من العبارات سيتم تسجيلها بواسطة تسجيل تدقيق الجلسة. القيم الممكنة هي:

• READ: SELECT و COPY عندما يكون المصدر علاقة أو استعلامًا.

• WRITE: INSERT، UPDATE، DELETE، TRUNCATE، و COPY عندما يكون الوجهة علاقة.

• FUNCTION: استدعاءات الدوال وكتل DO.

• ROLE: العبارات المتعلقة بالأدوار والصلاحيات: GRANT، REVOKE، CREATE/ALTER/DROP ROLE.

• DDL: جميع DDL غير المضمنة في فئة ROLE.

• MISC: أوامر متنوعة، مثل DISCARD، FETCH، CHECKPOINT، VACUUM، SET.

• MISC_SET: أوامر SET المتنوعة، مثل SET ROLE.

• ALL: تضمين كل ما سبق.

يمكن توفير فئات متعددة باستخدام قائمة مفصولة بفواصل ويمكن طرح الفئات عن طريق وضع علامة - قبل الفئة (انظر تسجيل تدقيق الجلسة).

القيمة الافتراضية هي none.

pgaudit.log_catalog

يحدد أن تسجيل الجلسة يجب أن يتم تمكينه في حالة أن جميع العلاقات في العبارة موجودة في pg_catalog. سيؤدي تعطيل هذا الإعداد إلى تقليل الضوضاء في السجل من أدوات مثل psql و PgAdmin التي تستعلم عن الكتالوج بشكل كبير.

القيمة الافتراضية هي on.

pgaudit.log_client

يحدد ما إذا كانت رسائل السجل ستكون مرئية لعملية العميل مثل psql. يجب ترك هذا الإعداد معطلاً بشكل عام ولكنه قد يكون مفيدًا لتصحيح الأخطاء أو لأغراض أخرى.

لاحظ أن pgaudit.log_level يتم تمكينه فقط عندما يكون pgaudit.log_client هو on.

القيمة الافتراضية هي off.

pgaudit.log_level

يحدد مستوى السجل الذي سيتم استخدامه لإدخالات السجل (راجع مستويات خطورة الرسالة للمستويات الصالحة) ولكن لاحظ أن ERROR و FATAL و PANIC غير مسموح بها). يتم استخدام هذا الإعداد لاختبار الانحدار وقد يكون مفيدًا أيضًا للمستخدمين النهائيين للاختبار أو لأغراض أخرى.

لاحظ أن pgaudit.log_level يتم تمكينه فقط عندما يكون pgaudit.log_client هو on؛ وإلا سيتم استخدام الافتراضي.

القيمة الافتراضية هي log.

pgaudit.log_parameter

يحدد أن تسجيل التدقيق يجب أن يتضمن المعلمات التي تم تمريرها مع العبارة. عند وجود المعلمات، سيتم تضمينها بتنسيق CSV بعد نص العبارة.

القيمة الافتراضية هي off.

pgaudit.log_parameter_max_size

يحدد أن قيم المعلمات الأطول من هذا الإعداد (بالبايت) لا ينبغي تسجيلها، بل يتم استبدالها بـ <long param suppressed>. يتم تعيين هذا بالبايت، وليس بالأحرف، لذلك لا يأخذ في الاعتبار الأحرف متعددة البايت في ترميز معلمات النص. هذا الإعداد ليس له تأثير إذا كان log_parameter هو off. إذا كان هذا الإعداد هو 0 (الافتراضي)، يتم تسجيل جميع المعلمات بغض النظر عن الطول.

القيمة الافتراضية هي 0.

pgaudit.log_relation

يحدد ما إذا كان تسجيل تدقيق الجلسة يجب أن ينشئ إدخال سجل منفصل لكل علاقة (TABLE، VIEW، إلخ) مشار إليها في عبارة SELECT أو DML. هذا اختصار مفيد للتسجيل الشامل دون استخدام تسجيل تدقيق الكائنات.

القيمة الافتراضية هي off.

pgaudit.log_rows

يحدد أن تسجيل التدقيق يجب أن يتضمن عدد الصفوف التي تم استردادها أو التي تأثرت بالعبارة. عند التمكين، سيتم تضمين حقل الصفوف بعد حقل المعلمة.

القيمة الافتراضية هي off.

pgaudit.log_statement

يحدد ما إذا كان التسجيل سيتضمن نص العبارة والمعلمات (إذا تم تمكينها). اعتمادًا على المتطلبات، قد لا يحتاج سجل التدقيق إلى ذلك ويجعل السجلات أقل إسهابًا.

القيمة الافتراضية هي on.

pgaudit.log_statement_once

يحدد ما إذا كان التسجيل سيتضمن نص العبارة والمعلمات مع أول إدخال سجل لتركيبة عبارة/عبارة فرعية أو مع كل إدخال. سيؤدي تمكين هذا الإعداد إلى تقليل التسجيل ولكن قد يجعل من الصعب تحديد العبارة التي أنشأت إدخال السجل، على الرغم من أن زوج العبارة/العبارة الفرعية مع معرف العملية يجب أن يكون كافيًا لتحديد نص العبارة المسجل مع إدخال سابق.

القيمة الافتراضية هي off.

pgaudit.role

يحدد الدور الرئيسي لاستخدامه في تسجيل تدقيق الكائنات. يمكن تعريف أدوار تدقيق متعددة عن طريق منحها للدور الرئيسي. يسمح هذا لمجموعات متعددة بأن تكون مسؤولة عن جوانب مختلفة من تسجيل التدقيق.

لا توجد قيمة افتراضية.

تسجيل تدقيق الجلسة

يوفر تسجيل تدقيق الجلسة سجلات مفصلة لجميع العبارات المنفذة من قبل مستخدم في الخلفية.

التكوين

يتم تمكين تسجيل الجلسة باستخدام إعداد pgaudit.log.

تمكين تسجيل الجلسة لجميع DML و DDL وتسجيل جميع العلاقات في عبارات DML:

set pgaudit.log = 'write, ddl';
set pgaudit.log_relation = on;

تمكين تسجيل الجلسة لجميع الأوامر باستثناء MISC ورفع رسائل سجل التدقيق كـ NOTICE:

set pgaudit.log = 'all, -misc';
set pgaudit.log_level = notice;

مثال

في هذا المثال، يتم استخدام تسجيل تدقيق الجلسة لتسجيل عبارات DDL و SELECT. لاحظ أن عبارة الإدراج (insert) لا يتم تسجيلها لأن فئة WRITE غير مفعلة.

SQL:

set pgaudit.log = 'read, ddl';

create table account
(
    id int,
    name text,
    password text,
    description text
);

insert into account (id, name, password, description)
             values (1, 'user1', 'HASH1', 'blah, blah');

select *
    from account;

مخرج السجل:

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account
(
    id int,
    name text,
    password text,
    description text
);,<not logged>
AUDIT: SESSION,2,1,READ,SELECT,,,select *
    from account,,<not logged>

تسجيل تدقيق الكائنات

يسجل تسجيل تدقيق الكائنات العبارات التي تؤثر على علاقة معينة. يتم دعم أوامر SELECT و INSERT و UPDATE و DELETE فقط. لا يتم تضمين TRUNCATE في تسجيل تدقيق الكائنات.

تم تصميم تسجيل تدقيق الكائنات ليكون بديلًا أدق لـ pgaudit.log = 'read, write'. على هذا النحو، قد لا يكون من المنطقي استخدامها معًا ولكن أحد السيناريوهات المحتملة هو استخدام تسجيل الجلسة لالتقاط كل عبارة ثم تكملة ذلك بتسجيل الكائنات للحصول على مزيد من التفاصيل حول علاقات محددة.

التكوين

يتم تنفيذ تسجيل التدقيق على مستوى الكائن عبر نظام الأدوار. يحدد إعداد pgaudit.role الدور الذي سيتم استخدامه لتسجيل التدقيق. سيتم تسجيل تدقيق لعلاقة (TABLE، VIEW، إلخ) عندما يكون لدور التدقيق أذونات للأمر المنفذ أو يرث الأذونات من دور آخر. يسمح هذا بوجود أدوار تدقيق متعددة بشكل فعال على الرغم من وجود دور رئيسي واحد في أي سياق.

تعيين pgaudit.role إلى auditor ومنح صلاحيات SELECT و DELETE على جدول account. سيتم الآن تسجيل أي عبارات SELECT أو DELETE على جدول account:

set pgaudit.role = 'auditor';

grant select, delete
   on public.account
   to auditor;

مثال

في هذا المثال، يتم استخدام تسجيل تدقيق الكائنات لتوضيح كيف يمكن اتباع نهج دقيق تجاه تسجيل عبارات SELECT و DML. لاحظ أن التسجيل على جدول account يتم التحكم فيه بواسطة أذونات على مستوى العمود، بينما التسجيل على جدول account_role_map هو على مستوى الجدول.

SQL:

set pgaudit.role = 'auditor';

create table account
(
    id int,
    name text,
    password text,
    description text
);

grant select (password)
   on public.account
   to auditor;

select id, name
  from account;

select password
  from account;

grant update (name, password)
   on public.account
   to auditor;

update account
   set description = 'yada, yada';

update account
   set password = 'HASH2';

create table account_role_map
(
    account_id int,
    role_id int
);

grant select
   on public.account_role_map
   to auditor;

select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id

مخرج السجل:

AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,select password
  from account,<not logged>
AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,update account
   set password = 'HASH2',<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account_role_map,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>

التنسيق

تُكتب إدخالات التدقيق إلى مرفق التسجيل القياسي وتحتوي على الأعمدة التالية بتنسيق مفصول بفواصل. المخرج هو تنسيق CSV متوافق فقط إذا تمت إزالة جزء بادئة سطر السجل من كل إدخال سجل.

• AUDIT_TYPE - SESSION أو OBJECT.

• STATEMENT_ID - معرف العبارة الفريد لهذه الجلسة. كل معرف عبارة يمثل استدعاءً للخلفية. معرفات العبارات متسلسلة حتى إذا لم يتم تسجيل بعض العبارات. قد يكون هناك إدخالات متعددة لمعرف العبارة عندما يتم تسجيل أكثر من علاقة واحدة.

• SUBSTATEMENT_ID - معرف تسلسلي لكل عبارة فرعية ضمن العبارة الرئيسية. على سبيل المثال، استدعاء دالة من استعلام. معرفات العبارات الفرعية مستمرة حتى إذا لم يتم تسجيل بعض العبارات الفرعية. قد يكون هناك إدخالات متعددة لمعرف العبارة الفرعية عندما يتم تسجيل أكثر من علاقة واحدة.

• CLASS - على سبيل المثال READ، ROLE (راجع pgaudit.log).

• COMMAND - على سبيل المثال ALTER TABLE، SELECT.

• OBJECT_TYPE - TABLE، INDEX، VIEW، إلخ. متاح لعبارات SELECT و DML ومعظم عبارات DDL.

• OBJECT_NAME - اسم الكائن المؤهل بالكامل (على سبيل المثال public.account). متاح لعبارات SELECT و DML ومعظم عبارات DDL.

• STATEMENT - العبارة المنفذة على الخلفية.

• PARAMETER - إذا تم تعيين pgaudit.log_parameter، فسيحتوي هذا الحقل على معلمات العبارة كـ CSV مقتبس أو <none> إذا لم تكن هناك معلمات. خلاف ذلك، الحقل هو <not logged>.

استخدم log_line_prefix لإضافة أي حقول أخرى مطلوبة لتلبية متطلبات سجل التدقيق الخاص بك. قد تكون بادئة سطر سجل نموذجية '%m %u %d [%p]: ' والتي ستوفر التاريخ/الوقت واسم المستخدم واسم قاعدة البيانات ومعرف العملية لكل سجل تدقيق.

محاذير

تسجيل التدقيق هو جهد مبذول وليس معامليًا. يكتب pgAudit إدخالات التدقيق من خلال مرفق تسجيل PostgreSQL القياسي، الذي لا يقوم بمسح كل إدخال على القرص بشكل متزامن مع المعاملة التي أنتجته، ولا ينقل أخطاء الكتابة مرة أخرى إلى الجلسة. لا يوجد ضمان بأن المعاملة الملتزمة سيكون لها إدخال سجل تدقيق مقابل. إذا تعطل الخادم أو فقد الطاقة، أو أصبح وجهة السجل غير متاحة (على سبيل المثال، امتلأت مساحة وحدة تخزين السجل) بعد الالتزام بمعاملة ولكن قبل كتابة إدخالات التدقيق الخاصة بها بشكل دائم، فقد يتم فقدان تلك الإدخالات. على العكس من ذلك، يتم تسجيل العبارة عند تنفيذها، لذلك قد يتم كتابة إدخال حتى إذا تم التراجع عن معاملتها لاحقًا.

يتم تسجيل إعادة تسمية الكائنات تحت الاسم الذي تمت إعادة تسميتها إليه. على سبيل المثال، إعادة تسمية جدول ستنتج النتيجة التالية:

ALTER TABLE test RENAME TO test2;

AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE,public.test2,ALTER TABLE test RENAME TO test2,<not logged>

من الممكن أن يتم تسجيل أمر أكثر من مرة. على سبيل المثال، عند إنشاء جدول بمفتاح أساسي محدد في وقت الإنشاء، سيتم تسجيل الفهرس للمفتاح الأساسي بشكل مستقل وسيتم عمل سجل تدقيق آخر للفهرس ضمن إدخال الإنشاء. ومع ذلك، سيتم تضمين الإدخالات المتعددة ضمن معرف عبارة واحد.

لا يتم تسجيل Autovacuum و Autoanalyze.

العبارات التي يتم تنفيذها بعد دخول المعاملة في حالة ملغاة (aborted) لن يتم تسجيل تدقيقها. ومع ذلك، سيتم تسجيل العبارة التي تسببت في الخطأ وأي عبارات لاحقة تم تنفيذها في المعاملة الملغاة كأخطاء (ERRORs) بواسطة مرفق التسجيل القياسي.

ليس من الممكن تدقيق المستخدمين الخارقين (superusers) بشكل موثوق به باستخدام pgAudit. أحد الحلول هو تقييد الوصول إلى حسابات المستخدمين الخارقين واستخدام إضافة set_user لتصعيد الصلاحيات عند الحاجة.

المؤلفون

تعتمد إضافة تدقيق PostgreSQL على مشروع pgaudit من 2ndQuadrant من تأليف Simon Riggs و Abhijit Menon-Sen و Ian Barwick وتم تقديمها كإضافة لنواة PostgreSQL. تم تطوير إضافي بواسطة David Steele من Crunchy Data.