Re: Schemas, Roles & Search Path

On Sat, Jan 9, 2016 at 11:16 AM, Melvin Davidson <melvin6925(at)gmail(dot)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(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.
>

--
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