Thread: View definition and schema search path bug or expected behaviour?
I am not sure if this is expected behaviour or a bug. Using PG 9.2 beta 2 and PGAdmin3 1.16 beta 2. Connect as bob (superuser) In public schema: create table people (cols...) create view people_view as select * from people Create schema bob create table bob.people (cols...) create view bob.people_view as select * from people (NB: view references people, not bob.people) Insert a record into bob.people Select * from bob.people_view -> Nil result set (expected to return the record from bob.people) Check definition of bob.people_view -> "create view bob.people_view as select * from public.people" (NB: "from public.people" - compare "create view bob.people_view as select * from people" above) I had hoped/expected that a view would use the search path to find the table it references. Why does bob.people_view reference public.people? Is this a bug or expected behaviour? Do view definitions require explicit reference to schema.table? My use case is that I effectively want to define a default schema to be replicated for new tenants in a multi-tenant system.
On 07/25/2012 07:25 PM, Chris Bartlett wrote: > I am not sure if this is expected behaviour or a bug. > > Using PG 9.2 beta 2 and PGAdmin3 1.16 beta 2. > > Connect as bob (superuser) > > In public schema: > create table people (cols...) > create view people_view as select * from people > > Create schema bob > create table bob.people (cols...) > create view bob.people_view as select * from people > (NB: view references people, not bob.people) > > Insert a record into bob.people > > Select * from bob.people_view > -> Nil result set > (expected to return the record from bob.people) > > Check definition of bob.people_view > -> "create view bob.people_view as select * from public.people" > (NB: "from public.people" - compare "create view bob.people_view as > select * from people" above) > > I had hoped/expected that a view would use the search path to find the > table it references. Why does bob.people_view reference public.people? > Is this a bug or expected behaviour? Do view definitions require > explicit reference to schema.table? My use case is that I effectively > want to define a default schema to be replicated for new tenants in a > multi-tenant system. http://www.postgresql.org/docs/9.2/static/runtime-config-client.html " When objects are created without specifying a particular target schema, they will be placed in the first valid schema named in search_path. An error is reported if the search path is empty. " I am guessing if you do show search_path; from psql you will see that the public schema is before the bob schema. The SELECT for the unqualified people table in CREATE VIEW bob.people_view will find public.people first in that case. -- Adrian Klaver adrian.klaver@gmail.com
At 7:37 PM -0700 25/7/12, Adrian Klaver wrote: >I am guessing if you do show search_path; from psql you will see >that the public schema is before the bob schema. The SELECT for the >unqualified people table in CREATE VIEW bob.people_view will find >public.people first in that case. I don't think that's it: show search_path -> "$user",public select SESSION_USER -> bob From the docs: "The value for search_path must be a comma-separated list of schema names. If one of the list items is the special value $user, then the schema having the name returned by SESSION_USER is substituted, if there is such a schema. (If not, $user is ignored.)" Also: select * from people -> returns records from bob.people
On 07/25/2012 07:47 PM, Chris Bartlett wrote: > At 7:37 PM -0700 25/7/12, Adrian Klaver wrote: >> I am guessing if you do show search_path; from psql you will see that >> the public schema is before the bob schema. The SELECT for the >> unqualified people table in CREATE VIEW bob.people_view will find >> public.people first in that case. > > I don't think that's it: > > show search_path > -> "$user",public > > select SESSION_USER > -> bob > > From the docs: > "The value for search_path must be a comma-separated list of schema > names. If one of the list items is the special value $user, then the > schema having the name returned by SESSION_USER is substituted, if there > is such a schema. (If not, $user is ignored.)" I see your point, but see below. http://www.postgresql.org/docs/9.2/static/runtime-config-client.html " If one of the list items is the special name $user, then the schema having the name returned by SESSION_USER is substituted, if there is such a schema and the user has USAGE permission for it. (If not, $user is ignored.)" In this version there is the qualifier that the user must have USAGE privileges on the schema. Is that the case? \dn+ should confirm. > > Also: > select * from people > -> returns records from bob.people > > -- Adrian Klaver adrian.klaver@gmail.com
At 7:51 PM -0700 25/7/12, Adrian Klaver wrote: >On 07/25/2012 07:47 PM, Chris Bartlett wrote: >>At 7:37 PM -0700 25/7/12, Adrian Klaver wrote: >>>I am guessing if you do show search_path; from psql you will see that >>>the public schema is before the bob schema. The SELECT for the >>>unqualified people table in CREATE VIEW bob.people_view will find >>>public.people first in that case. >> >>I don't think that's it: >> >>show search_path >>-> "$user",public >> >>select SESSION_USER >>-> bob >> >> From the docs: >>"The value for search_path must be a comma-separated list of schema >>names. If one of the list items is the special value $user, then the >>schema having the name returned by SESSION_USER is substituted, if there >>is such a schema. (If not, $user is ignored.)" > >I see your point, but see below. > >http://www.postgresql.org/docs/9.2/static/runtime-config-client.html >" >If one of the list items is the special name $user, then the schema >having the name returned by SESSION_USER is substituted, if there is >such a schema and the user has USAGE permission for it. (If not, >$user is ignored.)" > >In this version there is the qualifier that the user must have USAGE >privileges on the schema. Is that the case? > >\dn+ should confirm. Ah! The bob schema has no access privileges set. I had used pgAdmin3 - the schema definition pgAdmin3 reports is: CREATE SCHEMA bob AUTHORIZATION bob; (i.e., no grants) So: GRANT ALL ON SCHEMA bob TO bob; DROP VIEW bob.people; CREATE OR REPLACE VIEW bob.people_view AS SELECT people.name FROM people; SELECT * FROM bob.people_view -> returns records from bob.people as expected I had created the bob schema using pgAdmin3 (connected as bob), but when the schema owner is set to bob, there is no option in pgAdmin's New Schema... setup to grant privileges on the schema to bob. I guess I assumed that if bob owned the schema he would have all privileges on that schema. Thanks for pointing me in the right direction.
On 07/25/2012 08:19 PM, Chris Bartlett wrote: > > Ah! The bob schema has no access privileges set. I had used pgAdmin3 - > the schema definition pgAdmin3 reports is: > CREATE SCHEMA bob AUTHORIZATION bob; > (i.e., no grants) > > So: > GRANT ALL ON SCHEMA bob TO bob; > DROP VIEW bob.people; > CREATE OR REPLACE VIEW bob.people_view AS SELECT people.name FROM people; > > SELECT * FROM bob.people_view > -> returns records from bob.people as expected > > I had created the bob schema using pgAdmin3 (connected as bob), but when > the schema owner is set to bob, there is no option in pgAdmin's New > Schema... setup to grant privileges on the schema to bob. I guess I > assumed that if bob owned the schema he would have all privileges on > that schema. > > Thanks for pointing me in the right direction. There are a lot of dials to play with when creating objects and granting privileges. For my own sanity I make it a rule to confirm the privileges on an object when it is created or altered. Helps the blood pressure:) > > -- Adrian Klaver adrian.klaver@gmail.com