From: | Don Parris <parrisdc(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Schemas, Roles & Search Path |
Date: | 2016-01-09 12:54:35 |
Message-ID: | CAJ-7yokvB=p5G-8OuWpvKMOsuveCDgxOo6kSpv9uOY02ZLqq+g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
http://dcparris.net/ <https://www.xing.com/profile/Don_Parris>
<http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Smith | 2016-01-09 14:31:45 | Re: No postgresql-9.5-prefix ? |
Previous Message | Deven Phillips | 2016-01-09 05:28:01 | Re: PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question |