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=GxsRKrGjCMJBxYTTOx0l7Ve05RbCwajf4K20p4lIb1+nL09mde/XSclhjXOA3OjOOM
	pz+zHfmInbe0tblQJ31LGRM8TlFrjUKgMBxPIDudcZgQ3hEDO+tVakmgh+H1mqhjFjof
	P+yx+jMBvwT0oqiJtnFje7swoPBR5FVfZ7xd4=
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 08:34:07 -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: <201012310834.08060.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

=46irst tablespace !=3D schema. From here=20
http://www.postgresql.org/docs/9.0/interactive/manage-ag-tablespaces.html:

"Tablespaces in PostgreSQL allow database administrators to define location=
s in=20
the file system where the files representing database objects can be stored=
=2E=20
Once created, a tablespace can be referred to by name when creating databas=
e=20
objects.

By using tablespaces, an administrator can control the disk layout of a=20
PostgreSQL installation. This is useful in at least two ways. First, if the=
=20
partition or volume on which the cluster was initialized runs out of space =
and=20
cannot be extended, a tablespace can be created on a different partition an=
d=20
used until the system can be reconfigured.

Second, tablespaces allow an administrator to use knowledge of the usage pa=
ttern=20
of database objects to optimize performance. For example, an index which is=
=20
very heavily used can be placed on a very fast, highly available disk, such=
 as=20
an expensive solid state device. At the same time a table storing archived =
data=20
which is rarely used or not performance critical could be stored on a less=
=20
expensive, slower disk system. "


Second, from the schema docs if you want 'user' schemas than

"The value for search_path must be a comma-separated list of schema names. =
If=20
one of the list items is the special value $user, then the schema having th=
e=20
name returned by SESSION_USER is substituted, if there is such a schema. (I=
f=20
not, $user is ignored.) "

"The default value for this parameter is '"$user", public' (where the secon=
d=20
part will be ignored if there is no schema named public). This supports sha=
red=20
use of a database (where no users have private schemas, and all share use o=
f=20
public), private per-user schemas, and combinations of these. Other effects=
 can=20
be obtained by altering the default search path setting, either globally or=
=20
per-user. "


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