Обсуждение: user name doesn't seem to resolve to $user in search_path
I am a newbie in postgresql.
I have a problem with the following.
I start psql as user "testusr" in database "testdb"
In the database I have created a schema with the name of a user
CREATE SCHEMA testusr ;
The search_path is the default $user,public
Then I create a table
CREATE TABLE test (name char(8)) ;
Now I get the error message "No namespace have been selected to create in".
If I change the search_path as follows
SET search_path To testusr;
the table create works.
I also have a problem with resetting the searchpath with
SET search_path TO $user.
The $ sign gives an error.
What am I doing wrong ?
Thanks for any help.
Vriendelijke groeten, Kind regards,
Frank Messie
Osix/Systems B.V.
Office: Bikbergerweg 18, 1272 PM Huizen, The Netherlands
Mail: Postbox 5006, 1410 AA Naarden, The Netherlands
Phone: +31(0)356946010 Fax: +31(0)356951802
Email: frank.messie@osix.nl Homepage: www.osix.nl
This is an e-mail message from The Osix Group. The information contained in this
communication is intended solely for use by the individual or entity to
whom it is addressed. Use of this communication by others is prohibited. If
the e-mail message was sent to you by mistake, please destroy it without
reading, using, copying or disclosing its contents to any other person.
Sender accepts no liability for damage related to data and/or documents which
are communicated by electronic mail.
frank.messie@osix.nl writes:
> I have a problem with the following.
> I start psql as user "testusr" in database "testdb"
> In the database I have created a schema with the name of a user
> CREATE SCHEMA testusr ;
> The search_path is the default $user,public
> Then I create a table
> CREATE TABLE test (name char(8)) ;
> Now I get the error message "No namespace have been selected to create
> in".
This sounds like the search path didn't get recomputed when you created
the new schema, but I cannot reproduce any such problem:
regression=# create user tstuser;
CREATE USER
regression=# grant create on database regression to tstuser;
GRANT
regression=# \c - tstuser
You are now connected as new user "tstuser".
regression=> select current_schemas(true);
current_schemas
---------------------
{pg_catalog,public}
(1 row)
regression=> create schema tstuser;
CREATE SCHEMA
regression=> CREATE TABLE test (name char(8)) ;
CREATE TABLE
regression=> \dt test
List of relations
Schema | Name | Type | Owner
---------+------+-------+---------
tstuser | test | table | tstuser
(1 row)
regression=> select current_schemas(true);
current_schemas
-----------------------------
{pg_catalog,tstuser,public}
(1 row)
Are you sure you spelled the schema name the same as the user name?
regards, tom lane
Tom,
Thanks for the tip.
I discovered that the problem was solved by telling Postgres
CREATE SCHEMA tstuser AUTHORIZATION tstusr
(maybe it was not clear that this was done by user postgres and that the table creation was done by user tstuser)
So, the $user is being resolved correctly, provided the user has the proper rights.
However,
I still have one problem.
If I have set the search_path as follows
SET search_path TO myschema
the $user is removed from the search_path.
How do I get it back?
SET search_path TO $user
gives an parsing error caused by the $ sign.
Vriendelijke groeten, Kind regards,
Frank Messie
pgsql-admin-owner@postgresql.org wrote on 16-10-2003 19:29:53:
> frank.messie@osix.nl writes:
> > I have a problem with the following.
> > I start psql as user "testusr" in database "testdb"
> > In the database I have created a schema with the name of a user
> > CREATE SCHEMA testusr ;
> > The search_path is the default $user,public
> > Then I create a table
> > CREATE TABLE test (name char(8)) ;
> > Now I get the error message "No namespace have been selected to create
> > in".
>
> This sounds like the search path didn't get recomputed when you created
> the new schema, but I cannot reproduce any such problem:
>
> regression=# create user tstuser;
> CREATE USER
> regression=# grant create on database regression to tstuser;
> GRANT
> regression=# \c - tstuser
> You are now connected as new user "tstuser".
> regression=> select current_schemas(true);
> current_schemas
> ---------------------
> {pg_catalog,public}
> (1 row)
>
> regression=> create schema tstuser;
> CREATE SCHEMA
> regression=> CREATE TABLE test (name char(8)) ;
> CREATE TABLE
> regression=> \dt test
> List of relations
> Schema | Name | Type | Owner
> ---------+------+-------+---------
> tstuser | test | table | tstuser
> (1 row)
>
> regression=> select current_schemas(true);
> current_schemas
> -----------------------------
> {pg_catalog,tstuser,public}
> (1 row)
>
> Are you sure you spelled the schema name the same as the user name?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
frank.messie@osix.nl writes:
> SET search_path TO $user
> gives an parsing error caused by the $ sign.
You need quotes around '$user'.
regards, tom lane