Postgres Pro
Downloads
Home   >   mailing lists

Thread: Schemas, Roles & Search Path

Schemas, Roles & Search Path

From
Don Parris
Date:
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

Re: Schemas, Roles & Search Path

From
Tom Lane
Date:
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

Re: Schemas, Roles & Search Path

From
Melvin Davidson
Date:
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.

Re: Schemas, Roles & Search Path

From
Don Parris
Date:
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
GPG Key ID: F5E179BE

Re: Schemas, Roles & Search Path

From
Don Parris
Date:
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
GPG Key ID: F5E179BE