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
[EMAIL PROTECTED] wrote on 16-10-2003 19:29:53:
> [EMAIL PROTECTED] 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
