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