Re: Schemas, Roles & Search Path

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.