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