DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=from:reply-to:to:subject:date:user-agent:cc:references:in-reply-to
	:mime-version:content-type:content-transfer-encoding
	:content-disposition:message-id;
	b=jXWYbQIvi9k9Q4F+fJlSHpCbfEoHbAdcfzBd+P5aXZYLPc5SrsARDpHw40BOHDu0wt
	5tbsb5erv0pbADrQO07D7KlwJoRzQ3EkLa5zJv6oLHIlVkHZjujC7/3NkGU5rkrffZSj
	viToXGsd84o+KtuTDiiihcrRhjLbTFr2dxA24=
From: Adrian Klaver <adrian.klaver@gmail.com>
Reply-To: adrian.klaver@gmail.com
To: pgsql-sql@postgresql.org
Subject: Re: create role
Date: Fri, 31 Dec 2010 09:05:35 -0800
User-Agent: KMail/1.9.10
Cc: tcapobianco@prospectiv.com,
 "Gibransyah Fakhri" <gibransyah@gmail.com>
References: <1293747263.1650.7.camel@tony1.localdomain>
	<AANLkTikHcZy-H_iS2KMvvoMLnKDekE=3KBKD9EoBaNa5@mail.gmail.com>
	<6b49785d598885ab7f8c5d3955afd74c.squirrel@mail.prospectiv.com>
In-Reply-To: <6b49785d598885ab7f8c5d3955afd74c.squirrel@mail.prospectiv.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201012310905.35527.adrian.klaver@gmail.com>

On Friday 31 December 2010 8:22:23 am tcapobianco@prospectiv.com wrote:
> > On 31 December 2010 05:14, Tony Capobianco <tcapobianco@prospectiv.com>
> >
> > wrote:
> >> esave_dw=3D> \d members
> >> Did not find any relation named "members".
> >> esave_dw=3D>
> >> esave_dw=3D> \d esave.members
> >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Table "esave.members"
> >> =A0 =A0 =A0 Column =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0Type =A0 =
=A0 =A0 =A0 =A0 =A0 | Modifiers
> >> ---------------------+-----------------------------+-----------
> >> =A0memberid =A0 =A0 =A0 =A0 =A0 =A0| numeric =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 | not null
> >> =A0etc....
> >>
> >> How can I get this so I don't have to preface the \d with the schema
> >> name every time?
> >
> > Hi Tony, you should changes the default search_path for the specified
> > users.
> > http://sql-info.de/postgresql/schemas.html
> > take a look at practical schema usage section.
>
> Gibransyah,
> That did the trick!  Thanks for your help.  I modified my role name from
> developer to dev since I already have a dev schema.  Here's the steps I
> ran below to get it working:
>
> create role dev login;
> alter role dev set default_tablespace=3Ddev;
> alter role dev set search_path=3Ddev,staging, esave, support, email,publi=
c;
>
> grant usage on schema esave to dev;
> grant usage on schema dev to dev;
> grant select on members to dev;
> grant create on schema dev to dev;
> grant create on tablespace dev to dev;
>
> I am a little confused as to why I had to grant usage & create on dev to
> dev since it's both the dev role's default_tablespace and has a schema
> named after it.  Either way, this corrects my issue.  Thanks for your
> help!
>
> Tony

In my previous message I forgot to add the following.

The set commands and grants are not linked. Setting something does not=20
necessarily confer privileges for that object. The search_path for instance=
=2E It=20
really only sets up the search order for unqualified object names. What you=
 can=20
see or do with those objects is determined by the privileges on those objec=
ts.=20
Those privileges come from either the role that created the object or are=20
GRANT(ed) by a sufficiently privileged role to another role.

=2D-=20
Adrian Klaver
adrian.klaver@gmail.com