Thread: Schemas to Search_path

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

"Subbiah, Stalin" <SSubbiah@netopia.com> writes:
> 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.

Which one?

Now, if you make the schema name the same as the user name, it will do
what you want --- that's what the $user is for.  If you want some other
schema that happens to belong to that user to be the default, you have
to adjust the normal value of search_path.  (See ALTER USER SET if you'd
like that adjustment to be done for you automatically at each login.)

            regards, tom lane

I created schema name "foo_schema" different from user name "foo".
However, when I created the schema, I had specified authorization set to
foo user.

Does that mean, if the owner of the schema and schema name are
different, then only way, is to set through alter user set or search
path correct ?

Thanks Much!

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, March 09, 2005 4:57 PM
To: Subbiah, Stalin
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Schemas to Search_path

"Subbiah, Stalin" <SSubbiah@netopia.com> writes:
> 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.

Which one?

Now, if you make the schema name the same as the user name, it will do
what you want --- that's what the $user is for.  If you want some other
schema that happens to belong to that user to be the default, you have
to adjust the normal value of search_path.  (See ALTER USER SET if you'd
like that adjustment to be done for you automatically at each login.)

            regards, tom lane

SSubbiah@netopia.com ("Subbiah, Stalin") writes:

> I created schema name "foo_schema" different from user name "foo".
> However, when I created the schema, I had specified authorization set to
> foo user.
>
> Does that mean, if the owner of the schema and schema name are
> different, then only way, is to set through alter user set or search
> path correct ?
>
There is also ALTER DATABASE foo SET search_path TO schema [, schema]
...

> Thanks Much!

--
Remove -42 for email