pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSchemas to Search_path
4+ messages / 3 participants
[nested] [flat]
* Schemas to Search_path
@ 2005-03-10 00:44 Subbiah, Stalin <SSubbiah@netopia.com>
2005-03-10 00:57 ` Re: Schemas to Search_path Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Subbiah, Stalin @ 2005-03-10 00:44 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Schemas to Search_path
2005-03-10 00:44 Schemas to Search_path Subbiah, Stalin <SSubbiah@netopia.com>
@ 2005-03-10 00:57 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-03-10 00:57 UTC (permalink / raw)
To: Subbiah, Stalin <SSubbiah@netopia.com>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Schemas to Search_path
@ 2005-03-10 01:21 Subbiah, Stalin <SSubbiah@netopia.com>
2005-03-10 15:41 ` Re: Schemas to Search_path Edmund Bacon <ebacon-xlii@onesystem.com>
0 siblings, 1 reply; 4+ messages in thread
From: Subbiah, Stalin @ 2005-03-10 01:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Schemas to Search_path
2005-03-10 01:21 Re: Schemas to Search_path Subbiah, Stalin <SSubbiah@netopia.com>
@ 2005-03-10 15:41 ` Edmund Bacon <ebacon-xlii@onesystem.com>
0 siblings, 0 replies; 4+ messages in thread
From: Edmund Bacon @ 2005-03-10 15:41 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-03-10 15:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-10 00:44 Schemas to Search_path Subbiah, Stalin <SSubbiah@netopia.com>
2005-03-10 00:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-10 01:21 Re: Schemas to Search_path Subbiah, Stalin <SSubbiah@netopia.com>
2005-03-10 15:41 ` Edmund Bacon <ebacon-xlii@onesystem.com>
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