شرح كيف يمكنك إنشاء مستخدم للقراءة فقط في PostgreSQL؟




دورة postgresql (8)

أرغب في إنشاء مستخدم في PostgreSQL يمكنه فقط إجراء SELECTs من قاعدة بيانات معينة. في MySQL سيكون الأمر:

GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';

ما هو الأمر أو سلسلة الأوامر المكافئة في PostgreSQL؟

حاولت...

postgres=# CREATE ROLE xxx LOGIN PASSWORD 'yyy';
postgres=# GRANT SELECT ON DATABASE mydb TO xxx;

ولكن يبدو أن الأشياء الوحيدة التي يمكنك منحها على قاعدة بيانات هي CREATE و CONNECT و TEMPORARY و TEMP.


لقد قمت بإنشاء برنامج نصي مناسب لذلك ؛ pg_grant_read_to_db.sh . يمنح هذا البرنامج النصي امتيازات القراءة فقط إلى دور محدد على كل الجداول ، وجهات النظر والتسلسلات في مخطط قاعدة البيانات ويعينها كإعداد افتراضي.


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

لإنشاء مستخدم حقيقي للقراءة فقط مع PostgreSQL 9.0+ ، قم بتشغيل الخطوات التالية:

# This will prevent default users from creating tables
REVOKE CREATE ON SCHEMA public FROM public;

# If you want to grant a write user permission to create tables
# note that superusers will always be able to create tables anyway
GRANT CREATE ON SCHEMA public to writeuser;

# Now create the read-only user
CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'strongpassword';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;

إذا لم يكن لدى مستخدم القراءة فقط إذن بإدراج الجداول (بمعنى \d لا يعرض أية نتائج) ، فمن المحتمل أن ذلك بسبب عدم حصولك على إذن USAGE للمخطط. USAGE هو إذن يسمح للمستخدمين باستخدام الأذونات التي تم تعيينها بالفعل. ماذا يكون النقطة من هذا؟ لست متأكد. لإصلاح:

# You can either grant USAGE to everyone
GRANT USAGE ON SCHEMA public TO public;

# Or grant it just to your read only user
GRANT USAGE ON SCHEMA public TO readonlyuser;

والطريقة غير المباشرة للقيام بذلك هي منح الاختيار على كل جدول من قواعد البيانات:

postgres=# grant select on db_name.table_name to read_only_user;

يمكنك أتمتة ذلك من خلال توليد بيانات المنحة الخاصة بك من البيانات الوصفية لقاعدة البيانات.


استخدام المنحة / التحديد لجدول واحد

إذا قمت فقط بمنح CONNECT لقاعدة بيانات ، يمكن للمستخدم الاتصال ولكن ليس لديه امتيازات أخرى. يجب عليك منح USAGE على مساحات الأسماء (المخططات) وتحديد SELECT على الطاولات والآراء بشكل فردي مثل:

GRANT CONNECT ON DATABASE mydb TO xxx;
-- This assumes you're actually connected to mydb..
GRANT USAGE ON SCHEMA public TO xxx;
GRANT SELECT ON mytable TO xxx;

جداول / مشاهدات متعددة (PostgreSQL 9.0+)

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

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

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

ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON TABLES TO xxx;

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

جداول / مشاهدات متعددة (إصدارات PostgreSQL قبل الإصدار 9.0)

لتجنب الأخطاء في التغييرات متعددة الجداول الطويلة ، يوصى باستخدام العملية "التلقائية" التالية لتوليد GRANT SELECT المطلوب لكل جدول / عرض:

SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;'
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');

يجب أن يخرج هذا الأمر أوامر GRANT ذات الصلة إلى GRANT SELECT على كل الجداول ، وجهات النظر ، والتسلسلات العامة ، من أجل الحب مع النسخ واللصق. بطبيعة الحال ، لن يتم تطبيق ذلك إلا على الجداول التي تم إنشاؤها بالفعل.



إذا كانت قاعدة البيانات الخاصة بك في المخطط العام ، فمن السهل (هذا يفترض أنك قمت بالفعل بإنشاء readonlyuser )

db=> GRANT SELECT ON ALL TABLES IN SCHEMA public to readonlyuser;
GRANT
db=> GRANT CONNECT ON DATABASE mydatabase to readonlyuser;
GRANT
db=> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public to readonlyuser;
GRANT

إذا كانت قاعدة البيانات تستخدم customschema ، customschema تنفيذ الأمر أعلاه مع إضافة أمر واحد:

db=> ALTER USER readonlyuser SET search_path=customschema, public;
ALTER ROLE

مأخوذة من الرابط المنشور ردا على رابط despesz' .

يبدو 9.x postgres القدرة على القيام بما هو مطلوب. راجع الفقرة الخاصة بـ Grant On Database Objects من:

http://www.postgresql.org/docs/current/interactive/sql-grant.html

حيث تقول: "يوجد أيضًا خيار لمنح الامتيازات على كل الكائنات من نفس النوع داخل مخطط واحد أو أكثر. هذه الوظيفة مدعومة حاليًا للجداول والتسلسلات والوظائف فقط (لكن لاحظ أن كل الجداول تعتبر ذات مشاهد والجداول الأجنبية). "

تناقش هذه الصفحة أيضًا استخدام الأدوار ROLEs والامتياز الذي يسمى "جميع الامتيازات".

يوجد أيضًا معلومات حول كيفية مقارنة وظائف GRANT بمعايير SQL.


مرجع مأخوذ من هذا بلوق:

البرنامج النصي لإنشاء مستخدم للقراءة فقط:

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234' 
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

تعيين إذن لهذا الأمر بقراءة المستخدم فقط:

GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;




postgresql