This patch does two things:
1) Changes the semantics of assign_search_path()/'SET search_path' so
that you can't set your search path to a schema you don't have USAGE
privs for.
2) Changes psql's \dn query and its schema tab completion query to
incorporate ACL checking so that \dn only lists schemas that a user has
USAGE privs on.
Rationale:
1) "SET search_path = 'noaccess';" shouldn't look like it succeeds when
the user doesn't have USAGE privs in the schema 'noaccess'. Currently,
the SET command succeeds and a list of objects in the schema also works
leading the user to believe that they're working in an empty schema.
'SET search_path' should have its behavior modified so that it fails
the same way that on the file system when an unprivileged user types
'cd noaccess'.
2) If a users doesn't have access to a schema, they shouldn't see it in
the listings provided by psql(1). If a user wants to know, they can
query the catalogs by hand (until there's some kind of row level
security on the system catalogs?), but what they don't have access to,
probably doesn't interest them or they shouldn't have flaunted in their
face. This also changes the behavior of tab completion for schemas.
Problems:
% psql test usr
test=> SET search_path = public,foo;
ERROR: permission denied for schema foo
test=> \c test dba
You are now connected to database "test" as user "dba".
test=# ALTER USER usr SET search_path = 'foo';
ALTER USER
test=# \c test usr
You are now connected to database "test" as user "usr".
test=> \dn
List of schemas
Name | Owner
--------------------+-------
information_schema | sean
pg_catalog | sean
public | sean
(3 rows)
test=> show search_path ;
search_path
-------------
foo
(1 row)
And that's the only problem I've found with this patch, but I wasn't
about ready to put in the extra foo in the ALTER USER command to have
it prevent an invalid search_path from being set. -sc
--
Sean Chittenden