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


Index: src/backend/catalog/namespace.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/catalog/namespace.c,v
retrieving revision 1.63
diff -u -r1.63 namespace.c
--- src/backend/catalog/namespace.c     13 Feb 2004 01:08:20 -0000      1.63
+++ src/backend/catalog/namespace.c     9 Apr 2004 02:57:36 -0000
@@ -1806,8 +1806,7 @@
                /*
                 * Verify that all the names are either valid namespace names or
                 * "$user".  We do not require $user to correspond to a valid
-                * namespace.  We do not check for USAGE rights, either; should
-                * we?
+                * namespace.
                 *
                 * When source == PGC_S_TEST, we are checking the argument of an
                 * ALTER DATABASE SET or ALTER USER SET command.  It could be that
@@ -1821,12 +1820,11 @@
 
                        if (strcmp(curname, "$user") == 0)
                                continue;
-                       if (!SearchSysCacheExists(NAMESPACENAME,
-                                                                         
CStringGetDatum(curname),
-                                                                         0, 0, 0))
-                               ereport((source == PGC_S_TEST) ? NOTICE : ERROR,
-                                               (errcode(ERRCODE_UNDEFINED_SCHEMA),
-                                          errmsg("schema \"%s\" does not exist", 
curname)));
+
+                       /* Test to make sure the schema exists and
+                        * that the user has USAGE privs on the
+                        * schema. */
+                       LookupExplicitNamespace(curname);
                }
        }
 
Index: src/bin/psql/describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.96
diff -u -r1.96 describe.c
--- src/bin/psql/describe.c     6 Apr 2004 04:05:17 -0000       1.96
+++ src/bin/psql/describe.c     9 Apr 2004 02:57:36 -0000
@@ -1612,7 +1612,9 @@
                "FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
                "       ON n.nspowner=u.usesysid\n"
                "WHERE  (n.nspname NOT LIKE 'pg\\\\_temp\\\\_%%' OR\n"
-               "                n.nspname = 
(pg_catalog.current_schemas(true))[1])\n", /* temp schema is first */
+               "                n.nspname = (pg_catalog.current_schemas(true))[1]) 
AND\n"      /* temp schema is first */
+               "                pg_catalog.has_schema_privilege(n.nspname, 'USAGE')",
+
                                          _("Name"),
                                          _("Owner"));
        processNamePattern(&buf, pattern, true, false,
Index: src/bin/psql/tab-complete.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/tab-complete.c,v
retrieving revision 1.104
diff -u -r1.104 tab-complete.c
--- src/bin/psql/tab-complete.c 5 Apr 2004 03:02:09 -0000       1.104
+++ src/bin/psql/tab-complete.c 9 Apr 2004 02:57:37 -0000
@@ -341,7 +341,8 @@
 
 #define Query_for_list_of_schemas \
 "SELECT pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace "\
-" WHERE substring(pg_catalog.quote_ident(nspname),1,%d)='%s'"
+" WHERE substring(pg_catalog.quote_ident(nspname),1,%d)='%s' "\
+"   AND pg_catalog.has_schema_privilege(n.nspname, 'USAGE')"
 
 #define Query_for_list_of_system_relations \
 "SELECT pg_catalog.quote_ident(relname) "\
@@ -349,7 +350,8 @@
 " WHERE c.relkind IN ('r', 'v', 's', 'S') "\
 "   AND substring(pg_catalog.quote_ident(relname),1,%d)='%s' "\
 "   AND c.relnamespace = n.oid "\
-"   AND n.nspname = 'pg_catalog'"
+"   AND n.nspname = 'pg_catalog'"\
+"   AND pg_catalog.has_schema_privilege(n.nspname, 'USAGE')"
 
 #define Query_for_list_of_users \
 " SELECT pg_catalog.quote_ident(usename) "\
@@ -1546,7 +1548,7 @@
                        appendPQExpBuffer(&query_buffer, "\nUNION\n"
                                                          "SELECT 
pg_catalog.quote_ident(n.nspname) || '.' "
                                                          "FROM 
pg_catalog.pg_namespace n "
-                                                         "WHERE 
substring(pg_catalog.quote_ident(n.nspname) || '.',1,%d)='%s'",
+                                                         "WHERE 
pg_catalog.has_schema_privilege(n.nspname, 'USAGE') AND 
substring(pg_catalog.quote_ident(n.nspname) || '.',1,%d)='%s'",
                                                          string_length, e_text);
                        appendPQExpBuffer(&query_buffer,
                                                          " AND (SELECT 
pg_catalog.count(*)"
@@ -1562,7 +1564,7 @@
                        appendPQExpBuffer(&query_buffer, "\nUNION\n"
                                                          "SELECT 
pg_catalog.quote_ident(n.nspname) || '.' || %s "
                                                          "FROM %s, 
pg_catalog.pg_namespace n "
-                                                         "WHERE %s = n.oid AND ",
+                                                         "WHERE 
pg_catalog.has_schema_privilege(n.nspname, 'USAGE') AND %s = n.oid AND ",
                                                          qualresult,
                                                          completion_squery->catname,
                                                          
completion_squery->namespace);


--
Sean Chittenden

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to