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.
