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 http://dcparris.net/ <https://www.xing.com/profile/Don_Parris> <http://www.linkedin.com/in/dcparris> GPG Key ID: F5E179BE
