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
