Schemas, Roles & Search Path

Поиск
Список
Период
Сортировка
От Don Parris
Тема Schemas, Roles & Search Path
Дата
Msg-id CAJ-7yokvB=p5G-8OuWpvKMOsuveCDgxOo6kSpv9uOY02ZLqq+g@mail.gmail.com
обсуждение исходный текст
Ответы Re: Schemas, Roles & Search Path
Список pgsql-general
Дерево обсуждения
Hi all,

I just want to check my understanding of schemas, search paths and roles in implementing my database design.

Scenario:
A database with various "modules" (groups of tables & views, etc.), some of which are shared in common across a given organization, but others are specific to a given department within the organization.  I am thinking to use schemas for the department-specific modules.  So, for example:
Schema COMMON module: contains relations for categories, contacts and a few other items.
FACILITIES module: contains relations for facility management department, which includes the physical security team.
HR module: HR-specific relations
ACCOUNTING module: Accounting relations - but other departments need access to certain accounting functions. 
The relations within each schema still need appropriate privilege settings, of course.

I would use group and user roles to manage privileges throughout the database, so:
Group roles for HR, Accounting, Facilities, Sales, etc.
User roles assigned to appropriate group roles

I *think* I want to set the search path on the group roles so that the Facilities team can see the COMMON and FACILITIES schemas:

ALTER ROLE fm_users search_path=common, facilities, accounting;


Or do I need to set the search path for each user individually?

ALTER ROLE joe SET search_path=common, facilities, accounting;


I don't see any references to setting the search_path for group roles, so I assume I need to set this at the user role level.  Is that correct?

My thinking on the design could be off base, but I think this is the general approach I want.


Thanks!
--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
GPG Key ID: F5E179BE

В списке pgsql-general по дате отправления: