On Thursday 02 May 2002 05:33, Tom Lane wrote: [on establishing whether a relation is in the search path] > This doesn't yield much insight about cases where the match pattern > includes a (partial?) schema-name specification, though. If I'm > allowed to write something like "\z s*.t*" to find tables beginning > with t in schemas beginning with s, should that include all schemas > beginning with s? Only those in my search path (probably wrong)? > Only those that I have USAGE privilege on? Not sure.
If namespace privileges are based around the Unix directory/file protection model (as you stated in another thread, see: http://geocrawler.com/archives/3/10/2002/4/450/8433871/ ), then a wildcard search on the schema name should logically include all visible schemas, not just the ones where the user has USAGE privilege. Or put it another way, is there any reason to exclude information from say \z which the user can find out by querying pg_class? At the moment (at least in CVS from 30.4.02) a user can see permissions on tables in schemas on which he/she has no USAGE privileges: template1=# create database schema_test; CREATE DATABASE template1=# \c schema_test You are now connected to database schema_test. schema_test=# create schema foo; CREATE schema_test=# create table foo.bar (pk int, txt text); CREATE schema_test=# create schema foo2; CREATE schema_test=# create table foo2.bar (pk int, txt text); CREATE schema_test=# create user joe; CREATE USER schema_test=# grant usage on schema foo to joe; GRANT schema_test=# \c - joe You are now connected as new user joe. schema_test=> SELECT nspname AS schema, schema_test-> relname AS object, schema_test-> relkind AS type, schema_test-> relacl AS access schema_test-> FROM pg_class c schema_test-> INNER JOIN pg_namespace n schema_test-> ON c.relnamespace=n.oid schema_test-> WHERE relkind in ('r', 'v', 'S') AND schema_test-> relname NOT LIKE 'pg$_%%' ESCAPE '$' AND schema_test-> nspname || '.' || relname LIKE 'f%.b%'; schema | object | type | access --------+--------+------+-------- foo | bar | r | foo2 | bar | r | (2 rows) i.e. user "joe" can see which objects exist in schema "foo2", even though he has no USAGE privilege. (Is this behaviour intended?) Yours Ian Barwick ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
