Here it is - view permissions - Mailing list pgsql-hackers

First step done,

    below  is  the patch to have views to override the permission
    checks for the accessed tables. Now we can do the following:

    CREATE VIEW db_user AS SELECT
         usename,
         usesysid,
         usecreatedb,
         usetrace,
         usecatupd,
         '**********'::text as passwd,
         valuntil
        FROM pg_user;

    REVOKE ALL ON pg_user FROM public;
    REVOKE ALL ON db_user FROM public;
    GRANT SELECT ON db_user TO public;

    And after setting up a non-privileged user account  the  user
    does

    wieck=> select usename, usesysid, passwd from pg_user;
    ERROR:  pg_user: Permission denied.
    wieck=> select usename, usesysid, passwd from db_user;
    usename|usesysid|passwd
    -------+--------+----------
    pgsql  |      24|**********
    wieck  |     201|**********
    (2 rows)

    wieck=>

    For  now  and  as  long  as  only superusers are permitted to
    create rules or views due to the pg_rewrite permissions  this
    should  be  ok.   But  we should change it soon by adding the
    flag to pg_class as I said earlier and  have  only  the  view
    owner and superusers permitted to change this flag.

    This  flag is useful anyway. We change the world now that ALL
    views are overriding the acl checks. If we have this flag  we
    can  setup  views  that  behave  like before (user cannot see
    anything through the view he cannot see without) and use  the
    overriding  only  in  cases  we need it (like for the pg_user
    permissions problem). I'll work on it.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #


diff -r -c /usr/local/src/pgsql.orig/src/backend/executor/execMain.c ./src/backend/executor/execMain.c
*** /usr/local/src/pgsql.orig/src/backend/executor/execMain.c    Fri Feb 13 10:17:15 1998
--- ./src/backend/executor/execMain.c    Fri Feb 20 04:43:39 1998
***************
*** 299,304 ****
--- 299,315 ----
      {
          RangeTblEntry *rte = lfirst(lp);

+         if (rte->skipAcl)
+         {
+             /*
+              * This happens if the access to this table is due
+              * to a view query rewriting - the rewrite handler
+              * checked the permissions against the view owner,
+              * so we just skip this entry.
+              */
+             continue;
+         }
+
          relid = rte->relid;
          htp = SearchSysCacheTuple(RELOID,
                                    ObjectIdGetDatum(relid),
diff -r -c /usr/local/src/pgsql.orig/src/backend/nodes/copyfuncs.c ./src/backend/nodes/copyfuncs.c
*** /usr/local/src/pgsql.orig/src/backend/nodes/copyfuncs.c    Fri Feb 13 10:17:30 1998
--- ./src/backend/nodes/copyfuncs.c    Fri Feb 20 02:38:13 1998
***************
*** 1495,1500 ****
--- 1495,1501 ----
      newnode->relid = from->relid;
      newnode->inh = from->inh;
      newnode->inFromCl = from->inFromCl;
+     newnode->skipAcl  = from->skipAcl;


      return newnode;
diff -r -c /usr/local/src/pgsql.orig/src/backend/rewrite/rewriteHandler.c ./src/backend/rewrite/rewriteHandler.c
*** /usr/local/src/pgsql.orig/src/backend/rewrite/rewriteHandler.c    Wed Feb  4 09:33:56 1998
--- ./src/backend/rewrite/rewriteHandler.c    Fri Feb 20 04:39:37 1998
***************
*** 10,15 ****
--- 10,16 ----
   *
   *-------------------------------------------------------------------------
   */
+ #include <string.h>
  #include "postgres.h"
  #include "miscadmin.h"
  #include "utils/palloc.h"
***************
*** 29,41 ****
  #include "commands/creatinh.h"
  #include "access/heapam.h"

  static void ApplyRetrieveRule(Query *parsetree, RewriteRule *rule,
!                   int rt_index, int relation_level, int *modified);
  static List *fireRules(Query *parsetree, int rt_index, CmdType event,
            bool *instead_flag, List *locks, List **qual_products);
  static void QueryRewriteSubLink(Node *node);
  static List       *QueryRewriteOne(Query *parsetree);
  static List *deepRewriteQuery(Query *parsetree);

  /*
   * gatherRewriteMeta -
--- 30,48 ----
  #include "commands/creatinh.h"
  #include "access/heapam.h"

+ #include "utils/syscache.h"
+ #include "utils/acl.h"
+ #include "catalog/pg_user.h"
+
  static void ApplyRetrieveRule(Query *parsetree, RewriteRule *rule,
!                   int rt_index, int relation_level,
!                   Relation relation, int *modified);
  static List *fireRules(Query *parsetree, int rt_index, CmdType event,
            bool *instead_flag, List *locks, List **qual_products);
  static void QueryRewriteSubLink(Node *node);
  static List       *QueryRewriteOne(Query *parsetree);
  static List *deepRewriteQuery(Query *parsetree);
+ static void CheckViewPerms(Relation view, List *rtable);

  /*
   * gatherRewriteMeta -
***************
*** 219,225 ****
                  *instead_flag = TRUE;
                  return rule_lock->actions;
              }
!             ApplyRetrieveRule(parsetree, rule_lock, rt_index, relation_level,
                                &modified);
              if (modified)
              {
--- 226,232 ----
                  *instead_flag = TRUE;
                  return rule_lock->actions;
              }
!             ApplyRetrieveRule(parsetree, rule_lock, rt_index, relation_level, relation,
                                &modified);
              if (modified)
              {
***************
*** 247,252 ****
--- 254,260 ----
                    RewriteRule *rule,
                    int rt_index,
                    int relation_level,
+                   Relation relation,
                    int *modified)
  {
      Query       *rule_action = NULL;
***************
*** 256,271 ****
      int            nothing,
                  rt_length;
      int            badsql = FALSE;

      rule_qual = rule->qual;
      if (rule->actions)
      {
          if (length(rule->actions) > 1)    /* ??? because we don't handle
!                                          * rules with more than one
!                                          * action? -ay */
              return;
          rule_action = copyObject(lfirst(rule->actions));
          nothing = FALSE;
      }
      else
      {
--- 264,304 ----
      int            nothing,
                  rt_length;
      int            badsql = FALSE;
+     int            viewAclOverride = FALSE;

      rule_qual = rule->qual;
      if (rule->actions)
      {
          if (length(rule->actions) > 1)    /* ??? because we don't handle
!                          * rules with more than one
!                          * action? -ay */
!
!                         /* WARNING!!!
!                          * If we sometimes handle
!                          * rules with more than one
!                          * action, the view acl checks
!                          * might get broken.
!                          * viewAclOverride should only
!                          * become true (below) if this
!                          * is a relation_level, instead,
!                          * select query - Jan
!                          */
              return;
          rule_action = copyObject(lfirst(rule->actions));
          nothing = FALSE;
+
+         /*
+          * If this rule is on the relation level, the rule action
+          * is a select and the rule is instead then it must be
+          * a view. Permissions for views now follow the owner of
+          * the view, not the current user.
+          */
+         if (relation_level && rule_action->commandType == CMD_SELECT
+                     && rule->isInstead)
+         {
+             CheckViewPerms(relation, rule_action->rtable);
+             viewAclOverride = TRUE;
+         }
      }
      else
      {
***************
*** 284,290 ****
          rte->inFromCl = false;
      }
      rt_length = length(rtable);
!     rtable = nconc(rtable, copyObject(rule_action->rtable));
      parsetree->rtable = rtable;

      rule_action->rtable = rtable;
--- 317,346 ----
          rte->inFromCl = false;
      }
      rt_length = length(rtable);
!
!     if (viewAclOverride)
!     {
!         List        *rule_rtable, *rule_rt;
!         RangeTblEntry    *rte;
!
!         rule_rtable = copyObject(rule_action->rtable);
!         foreach(rule_rt, rule_rtable)
!         {
!             rte = lfirst(rule_rt);
!
!             /*
!              * tell the executor that the ACL check on this
!              * range table entry is already done
!              */
!             rte->skipAcl = true;
!         }
!
!         rtable = nconc(rtable, rule_rtable);
!     }
!     else
!     {
!         rtable = nconc(rtable, copyObject(rule_action->rtable));
!     }
      parsetree->rtable = rtable;

      rule_action->rtable = rtable;
***************
*** 750,752 ****
--- 806,850 ----

      return rewritten;
  }
+
+
+ static void
+ CheckViewPerms(Relation view, List *rtable)
+ {
+     HeapTuple    utup;
+     NameData    uname;
+     List        *rt;
+     RangeTblEntry    *rte;
+     int32        aclcheck_res;
+
+     /*
+      * get the usename of the view's owner
+      */
+     utup = SearchSysCacheTuple(USESYSID, view->rd_rel->relowner, 0, 0, 0);
+     if (!HeapTupleIsValid(utup))
+     {
+         elog(ERROR, "cache lookup for userid %d failed",
+                         view->rd_rel->relowner);
+     }
+     StrNCpy(uname.data,
+             ((Form_pg_user) GETSTRUCT(utup))->usename.data,
+             NAMEDATALEN);
+
+     /*
+      * check that we have read access to all the
+      * classes in the range table of the view
+      */
+     foreach(rt, rtable)
+     {
+         rte = (RangeTblEntry *)lfirst(rt);
+
+         aclcheck_res = pg_aclcheck(rte->relname, uname.data, ACL_RD);
+         if (aclcheck_res != ACLCHECK_OK)
+         {
+             elog(ERROR, "%s: %s", rte->relname, aclcheck_error_strings[aclcheck_res]);
+         }
+     }
+ }
+
+
+
diff -r -c /usr/local/src/pgsql.orig/src/include/nodes/parsenodes.h ./src/include/nodes/parsenodes.h
*** /usr/local/src/pgsql.orig/src/include/nodes/parsenodes.h    Wed Feb 11 15:25:44 1998
--- ./src/include/nodes/parsenodes.h    Fri Feb 20 02:21:00 1998
***************
*** 864,869 ****
--- 864,870 ----
      Oid            relid;
      bool        inh;            /* inheritance? */
      bool        inFromCl;        /* comes from From Clause */
+     bool        skipAcl;        /* skip ACL check in executor */
  } RangeTblEntry;

  /*