Schemas to Search_path

From: "Subbiah, Stalin" <SSubbiah(at)netopia(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Schemas to Search_path
Date: 2005-03-10 00:44:23
Message-ID: 6B26A56846293C4488B54CF3E4403354339DA6@mxca2.corp.netopia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2005-03-10 00:57:23 Re: Schemas to Search_path
Previous Message Tom Lane 2005-03-09 22:34:01 Re: Functions and transactions