Re: View permissions in 7.1 - Mailing list pgsql-general

Lieven Van Acker <lieven@elisa.be> writes:
> I guess I was a bit to optimistic about the patch. It seems like the
> select permissions are solved, but update (inc. insert / delete)
> operations still fail with permission denied on the nested views.

Okay, the example you sent me off-list turns out to exhibit one bug
and one not-yet-implemented feature.  There is a bug in permissions
checking for insert/update/delete rules (any references therein to
NEW or OLD should be checked against the rule owner, not the calling
user).  A patch for that is attached.  However, you were also expecting
that an SQL function call would provide "setuid" behavior, and it
doesn't.  (I believe changing that is on the TODO list.)  In the
meantime, you'd need to replace the current_adm() function call in your
adm_base view rules with explicit subselects, so that the accesses to
the "users" table are checked against the rule owner rather than the
calling user.

BTW, this patch changes the stored form of a rule, so you'd need to
drop and recreate the rule to make it take effect.

            regards, tom lane


*** src/backend/rewrite/rewriteDefine.c.orig    Fri Mar 23 13:45:24 2001
--- src/backend/rewrite/rewriteDefine.c    Thu May  3 17:16:48 2001
***************
*** 8,14 ****
   *
   *
   * IDENTIFICATION
!  *      $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/rewrite/rewriteDefine.c,v 1.61 2001/03/23 04:49:54
momjianExp $ 
   *
   *-------------------------------------------------------------------------
   */
--- 8,14 ----
   *
   *
   * IDENTIFICATION
!  *      $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/rewrite/rewriteDefine.c,v 1.62 2001/05/03 21:16:48
tglExp $ 
   *
   *-------------------------------------------------------------------------
   */
***************
*** 377,383 ****
       * We want the rule's table references to be checked as though by the
       * rule owner, not the user referencing the rule.  Therefore, scan
       * through the rule's rtables and set the checkAsUser field on all
!      * rtable entries (except *OLD* and *NEW*).
       */
      foreach(l, action)
      {
--- 377,383 ----
       * We want the rule's table references to be checked as though by the
       * rule owner, not the user referencing the rule.  Therefore, scan
       * through the rule's rtables and set the checkAsUser field on all
!      * rtable entries.
       */
      foreach(l, action)
      {
***************
*** 426,454 ****
  /*
   * setRuleCheckAsUser
   *        Recursively scan a query and set the checkAsUser field to the
!  *        given userid in all rtable entries except *OLD* and *NEW*.
   */
  static void
  setRuleCheckAsUser(Query *qry, Oid userid)
  {
      List       *l;

!     /* Set all the RTEs in this query node, except OLD and NEW */
      foreach(l, qry->rtable)
      {
          RangeTblEntry *rte = (RangeTblEntry *) lfirst(l);

-         if (strcmp(rte->eref->relname, "*NEW*") == 0)
-             continue;
-         if (strcmp(rte->eref->relname, "*OLD*") == 0)
-             continue;
-
          if (rte->subquery)
          {
!
!             /*
!              * Recurse into subquery in FROM
!              */
              setRuleCheckAsUser(rte->subquery, userid);
          }
          else
--- 426,453 ----
  /*
   * setRuleCheckAsUser
   *        Recursively scan a query and set the checkAsUser field to the
!  *        given userid in all rtable entries.
!  *
!  * Note: for a view (ON SELECT rule), the checkAsUser field of the *OLD*
!  * RTE entry will be overridden when the view rule is expanded, and the
!  * checkAsUser field of the *NEW* entry is irrelevant because that entry's
!  * checkFor bits will never be set.  However, for other types of rules it's
!  * important to set these fields to match the rule owner.  So we just set
!  * them always.
   */
  static void
  setRuleCheckAsUser(Query *qry, Oid userid)
  {
      List       *l;

!     /* Set all the RTEs in this query node */
      foreach(l, qry->rtable)
      {
          RangeTblEntry *rte = (RangeTblEntry *) lfirst(l);

          if (rte->subquery)
          {
!             /* Recurse into subquery in FROM */
              setRuleCheckAsUser(rte->subquery, userid);
          }
          else