PostgreSQL: المستخدمون والأدوار وإدارة الصلاحيات في…

-- أدوار المجموعة (لا يمكنها تسجيل الدخول)
CREATE ROLE dev_team NOINHERIT;
CREATE ROLE analytics_team NOINHERIT;
CREATE ROLE ops_team NOINHERIT;
CREATE ROLE audit_team NOINHERIT;

-- مستخدمو تسجيل الدخول
-- ⚠️ لا ترمز كلمات المرور في الإنتاج؛ استخدم متغيرات البيئة أو مدير أسرار
CREATE USER alice_dev PASSWORD 'SecureP@ss1' IN ROLE dev_team;
CREATE USER bob_dev PASSWORD 'SecureP@ss2' IN ROLE dev_team;
CREATE USER charlie_analyst PASSWORD 'SecureP@ss3' IN ROLE analytics_team;
CREATE USER diana_ops PASSWORD 'SecureP@ss4' IN ROLE ops_team INHERIT;

CREATE TABLE

-- إضافة صلاحية CREATEDB لقائد فريق العمليات
ALTER ROLE diana_ops CREATEDB;

-- تعيين انتهاء صلاحية كلمة المرور
ALTER ROLE charlie_analyst VALID UNTIL '2025-12-31';

-- إعادة تسمية دور
ALTER ROLE dev_team RENAME TO engineering_team;

-- تعطيل تسجيل الدخول مؤقتاً
ALTER ROLE bob_dev NOLOGIN;

CREATE TABLE

-- فريق التطوير: CRUD كامل على جداول الأعمال
GRANT SELECT, INSERT, UPDATE, DELETE ON products TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON order_items TO dev_team;

-- فريق التحليلات: قراءة فقط
GRANT SELECT ON products TO analytics_team;
GRANT SELECT ON orders TO analytics_team;
GRANT SELECT ON customers TO analytics_team;

-- فريق التدقيق: فقط سجلات التدقيق
GRANT SELECT ON price_audit_log TO audit_team;
GRANT SELECT ON ddl_audit_log TO audit_team;

INSERT 0 1

-- فريق التطوي�� يمكنه إنشاء كائنات في مخطط dev
GRANT CREATE, USAGE ON SCHEMA dev TO dev_team;

-- فريق التحليلات يمكن�� الاستخدام لكن ليس الإنشاء في المخطط public
GRANT USAGE ON SCHEMA public TO analytics_team;

-- السماح للتحليلات بإنشاء طرق عرض مادية في مخططهم الخاص
GRANT CREATE, USAGE ON SCHEMA analytics TO analytics_team;

-- خدمة التطبيق تتصل بقاعدة البيانات
GRANT CONNECT ON DATABASE shop_db TO app_service;

CREATE TABLE

-- سحب DELETE من فريق التطوير على products
REVOKE DELETE ON products FROM dev_team;

-- سحب جميع الصلاحيات على جدول
REVOKE ALL PRIVILEGES ON orders FROM public;

-- سحب حق إنشاء المخطط
REVOKE CREATE ON SCHEMA dev FROM dev_team;

-- تتالي: سحب والمنح التابعة
REVOKE SELECT ON products FROM analytics_team CASCADE;

DELETE 2

-- جميع الجداول المستقبلية في المخطط public قابلة للقراءة من analytics_team
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO analytics_team;

-- جميع الجداول المستقبلية المنشأة بواسطة app_service قابلة للكتابة من dev_team
ALTER DEFAULT PRIVILEGES FOR ROLE app_service IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE ON TABLES TO dev_team;

-- جميع التسلسلات المستقبلية في المخطط public
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO dev_team;

-- جميع الدوال المستقبلية
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT EXECUTE ON FUNCTIONS TO analytics_team;

INSERT 0 1

SELECT
  pg_get_userbyid(defaclrole) AS grantor,
  pg_get_userbyid(defaclnamespace) AS namespace_owner,
  n.nspname AS schema,
  defaclobjtype AS object_type,
  defaclacl AS acl
FROM pg_default_acl d
JOIN pg_namespace n ON n.oid = d.defaclnamespace;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- تمكين RLS على جدول orders
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- المحللون يمكنهم فقط رؤية الطلبات المكتملة
CREATE POLICY pol_analytics_completed
  ON orders FOR SELECT
  TO analytics_team
  USING (order_status = 'completed');

-- فريق التطوير يمكنه رؤية جميع الصفوف
CREATE POLICY pol_dev_all
  ON orders FOR ALL
  TO dev_team
  USING (true)
  WITH CHECK (true);

CREATE TABLE

-- تمكين RLS على جدول customers
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

-- كل مستخدم خدمة عملاء يرى فقط منطقته المعينة
CREATE POLICY pol_region_access
  ON customers FOR SELECT
  USING (region = current_setting('app.region', true));

-- فرض التزام مالك الجدول أيضاً بـ RLS
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE TABLE

-- فريق المبيعات يمكنه فقط إدراج طلبات في منطقته
CREATE POLICY pol_sales_insert
  ON orders FOR INSERT
  TO dev_team
  WITH CHECK (region = current_setting('app.region', true));

-- فريق المبيعات يمكنه فقط تحديث الطلبات في منطقته
CREATE POLICY pol_sales_update
  ON orders FOR UPDATE
  TO dev_team
  USING (region = current_setting('app.region', true))
  WITH CHECK (region = current_setting('app.region', true));

INSERT 0 1

-- إنشاء دور قوي مع NOINHERIT
CREATE ROLE admin_role NOINHERIT;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_role;

-- Diana في admin_role لكن لا يمكنها استخدام صلاحيات المسؤول تلقائياً
CREATE USER diana PASSWORD 'SecureP@ss5' IN ROLE admin_role NOINHERIT;

-- يجب على Diana التبديل صراحةً لاستخدام حقوق المسؤول
SET ROLE admin_role;
-- الآن يمكن لـ diana تنفيذ عمليات المسؤول
RESET ROLE;
-- العودة إلى الصلاحيات العادية

CREATE TABLE

-- سرد جميع الأدوار وسماتها
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin, rolinherit
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%'
ORDER BY rolname;

-- سرد عضوية الأدوار
SELECT
  r1.rolname AS member,
  r2.rolname AS role
FROM pg_auth_members m
JOIN pg_roles r1 ON r1.oid = m.member
JOIN pg_roles r2 ON r2.oid = m.roleid
ORDER BY r2.rolname, r1.rolname;

-- التحقق من صلاحيات الجدول لدور
SELECT table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'analytics_team'
ORDER BY table_name, privilege_type;

CREATE TABLE

-- الخطوة 1: إنشاء أدوار المجموعة
CREATE ROLE dev_team NOINHERIT;
CREATE ROLE analytics_team NOINHERIT;
CREATE ROLE ops_team INHERIT;
CREATE ROLE audit_team NOINHERIT;
CREATE ROLE app_service LOGIN PASSWORD 'AppSecRet!';

-- الخطوة 2: منح صلاحيات المخطط
GRANT CREATE, USAGE ON SCHEMA public TO dev_team;
GRANT USAGE ON SCHEMA public TO analytics_team;
GRANT CREATE, USAGE ON SCHEMA analytics TO analytics_team;
GRANT USAGE ON SCHEMA public TO ops_team;
GRANT USAGE ON SCHEMA audit TO audit_team;

-- الخطوة 3: منح صلاحيات الجدول
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO dev_team;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_team;
GRANT SELECT ON ALL TABLES IN SCHEMA audit TO audit_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON products, orders, order_items, customers TO app_service;

-- الخطوة 4: الصلاحيات الافتراضية للجداول المستقبلية
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO analytics_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dev_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA audit
  GRANT SELECT ON TABLES TO audit_team;

-- الخطوة 5: صلاحيات التسلسلات والدوال
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dev_team;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO dev_team;

-- الخطوة 6: RLS - المحللون يرون فقط الطلبات المكتملة
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY pol_analytics_orders
  ON orders FOR SELECT
  TO analytics_team
  USING (order_status = 'completed');

-- الخطوة 7: RLS - فريق التدقيق يرى فقط مخطط audit
ALTER TABLE price_audit_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY pol_audit_read
  ON price_audit_log FOR SELECT
  TO audit_team
  USING (true);

-- الخطوة 8: إنشاء مستخدمي تسجيل الدخول وتعيين الأدوار
CREATE USER alice_dev PASSWORD 'DevP@ss1' IN ROLE dev_team;
CREATE USER bob_dev PASSWORD 'DevP@ss2' IN ROLE dev_team;
CREATE USER charlie_analyst PASSWORD 'AnlP@ss3' IN ROLE analytics_team;
CREATE USER diana_ops PASSWORD 'OpsP@ss4' IN ROLE ops_team CREATEROLE;
CREATE USER eve_audit PASSWORD 'AudP@ss5' IN ROLE audit_team;