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.