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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
