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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Don Parris <parrisdc(at)gmail(dot)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(at)postgresql(dot)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.