pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Subbiah, Stalin <SSubbiah@netopia.com>
To: pgsql-admin@postgresql.org
Subject: Schemas to Search_path
Date: Wed, 9 Mar 2005 16:44:23 -0800
Message-ID: <6B26A56846293C4488B54CF3E4403354339DA6@mxca2.corp.netopia.com> (raw)

I was playing around with schemas today to understand how it would fit
in our application design. I couldn't understand why I got to set
search_path to schema name everytime I login to psql.

This is what I did.

-ceated a user "foo" with createdb privilege as psql -d template1 
-created db "foodb" with encoding = unicode as psql -d template1 -U foo
-- this makes foo as the owner of the db.
-create schema "foo_schema" with authorization to foo user as psql -d
foodb -U foo
-dropped public schema as I don't want others or foo user to create
objects in public schema. I did this via psql -d foodb -U postgres --
since postgres being the owner of public schema I had to login as
superuser postgres.

Now comes the fun part

I logged into foodb as foo user and tried to create a table. Bang!
ERROR:  no schema has been selected to create in. search_path had $user,
public the default ones, shouldn't the table get created in user's
authorized schema. Perhaps, I'm trying to compare with oracle
users/schemas.

However, when set search path to foo_schema then table gets created
fine. Also, whenever I login, how should make \dt to just show the
objects under the user's authorized schema without setting search path
everytime.

Thanks
Stalin

view thread (4+ messages)  latest in thread

Message-ID: <6B26A56846293C4488B54CF3E4403354339DA6@mxca2.corp.netopia.com>
Permalink:  ../6B26A56846293C4488B54CF3E4403354339DA6@mxca2.corp.netopia.com/
Also on:    postgresql.org/message-id/6B26A56846293C4488B54CF3E4403354339DA6@mxca2.corp.netopia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: SSubbiah@netopia.com
  Subject: Re: Schemas to Search_path
  In-Reply-To: <6B26A56846293C4488B54CF3E4403354339DA6@mxca2.corp.netopia.com>
  References: <6B26A56846293C4488B54CF3E4403354339DA6@mxca2.corp.netopia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox