Thread: Checking for USAGE on SET search_path...

Checking for USAGE on SET search_path...

From
Sean Chittenden
Date:
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


Attachment

Re: Checking for USAGE on SET search_path...

From
Tom Lane
Date:
Sean Chittenden <sean@chittenden.org> writes:
> 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.

Why is that needed?  It's already a no-op AFAIR.  It also is
incompatible with the existing behavior, in which nonexistent schemas
(think "$user") are dropped silently rather than noisily.  Your patch
also breaks the previous careful tweak to allow ALTER DATABASE SET
to succeed when mentioning a schema not present in the current database.

> 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.

This requires considerable discussion.  Should \df only list functions
you are allowed to call?  \dt only tables you are allowed to read?
\h only commands you are allowed to execute?

I'm not that thrilled with patches that propose basic changes in
behavior and have not been justified by any preceding discussion
on pghackers...

            regards, tom lane