X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CD9E053D6E for ; Thu, 10 Mar 2005 00:44:28 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04146-08 for ; Thu, 10 Mar 2005 00:44:25 +0000 (GMT) Received: from mxca2.corp.netopia.com (host222.netopia.com [65.206.239.222]) by svr1.postgresql.org (Postfix) with ESMTP id D1C4154C71 for ; Thu, 10 Mar 2005 00:44:22 +0000 (GMT) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5250A.4E6E638D" Subject: Schemas to Search_path Date: Wed, 9 Mar 2005 16:44:23 -0800 Message-ID: <6B26A56846293C4488B54CF3E4403354339DA6@mxca2.corp.netopia.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Schemas to Search_path Thread-Index: AcUlCk33KJEKIlI2SMO02vL+GGtihQ== From: "Subbiah, Stalin" To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.057 tagged_above=0 required=5 tests=HTML_30_40, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200503/139 X-Sequence-Number: 16637 This is a multi-part message in MIME format. ------_=_NextPart_001_01C5250A.4E6E638D Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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=20 -created db "foodb" with encoding =3D 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 ------_=_NextPart_001_01C5250A.4E6E638D Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 =3D 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

------_=_NextPart_001_01C5250A.4E6E638D--