Re: Schemas: status report, call for developers

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