Schemas, Roles & Search Path
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
Don Parris <parrisdc@gmail.com> writes:
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;
The latter. A session only absorbs ALTER ROLE SET settings for the
exact role you're logging in as. (Otherwise there would be a need
for a conflict resolution rule, and it's pretty hard to see how that
would work in general for arbitrary settings.) Role "inheritance"
applies to granted privileges only.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Just a side note. the search_path only sets the priority for resolving
table locations
EG: First look in COMMON, then FACILITIES until the table name is found.
However, if you prefix the table name with the actual schema EG:
COMMON.table, then the table is located directly and search_path is not
needed.
Finally, it is NOT a good idea to use UPPERCASE or CamelCase for object
names in PostgreSQL, as PostgreSQL will naturally assume lowercase for all
objects
unless they are quoted. So probably you want schema names to be common,
facilities, etc.
On Sat, Jan 9, 2016 at 10:22 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Don Parris <parrisdc@gmail.com> writes:
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;The latter. A session only absorbs ALTER ROLE SET settings for the
exact role you're logging in as. (Otherwise there would be a need
for a conflict resolution rule, and it's pretty hard to see how that
would work in general for arbitrary settings.) Role "inheritance"
applies to granted privileges only.regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Sat, Jan 9, 2016 at 11:16 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:
Just a side note. the search_path only sets the priority for resolving
table locations
EG: First look in COMMON, then FACILITIES until the table name is found.However, if you prefix the table name with the actual schema EG:
COMMON.table, then the table is located directly and search_path is not
needed.
Melvin, thanks for that - confirms what I understand about schemas.
Finally, it is NOT a good idea to use UPPERCASE or CamelCase for object
names in PostgreSQL, as PostgreSQL will naturally assume lowercase for all
objects
unless they are quoted. So probably you want schema names to be common,
facilities, etc.
Thanks. I was just capitalizing for the e-mail. I never actually use
upper or camel case in my code.
On Sat, Jan 9, 2016 at 10:22 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Don Parris <parrisdc@gmail.com> writes:
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;The latter. A session only absorbs ALTER ROLE SET settings for the
exact role you're logging in as. (Otherwise there would be a need
for a conflict resolution rule, and it's pretty hard to see how that
would work in general for arbitrary settings.) Role "inheritance"
applies to granted privileges only.regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
--
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
On Sat, Jan 9, 2016 at 10:22 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Don Parris <parrisdc@gmail.com> writes:
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;The latter. A session only absorbs ALTER ROLE SET settings for the
exact role you're logging in as. (Otherwise there would be a need
for a conflict resolution rule, and it's pretty hard to see how that
would work in general for arbitrary settings.) Role "inheritance"
applies to granted privileges only.regards, tom lane
Thanks Tom, that helps!
Don
--
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