"Bruce Momjian" <pgman@candle.pha.pa.us> wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Someone asked me a question about view and function permissions.  I
> > > assumed all object access done by a view would be based on the
> > > permissions on the view, and not the permissions of the objects.
> >
> > Table references are checked according to the owner of the view, but use
> > in a view does not change the execution context for function or operator
> > calls.  This is how it's always been done.
> >
> > > Is this a bug?
> >
> > Changing it would be a major definitional change (and a pretty major
> > implementation change too).  It might be better, but please don't
> > pre-judge the issue by labeling it a bug.
>
> Well, it sure sounds like a bug.  What logic is there that table access
> use the view permissions, but not function access?  Could we just use
> SECURITY DEFINER for function calls in views?

I already had this problem, look here:

http://groups.google.it/groups?q=postgres+security+definer+gaetano+mendola&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=b711hu%241g25%241%40news.hub.org&rnum=1

and I had no reply :-(

Regards
Gaetano Mendola


Sun, 14 Sep 2003 02:07:10 +0200
"Gaetano Mendola" <mendola@bigfoot.com>
Re: Problem with function permission test in a view
I am documenting this behavior in the CREATE VIEW manual page, diff
attached.


---------------------------------------------------------------------------

Gaetano Mendola wrote:
> "Bruce Momjian" <pgman@candle.pha.pa.us> wrote:
> > Tom Lane wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > Someone asked me a question about view and function permissions.  I
> > > > assumed all object access done by a view would be based on the
> > > > permissions on the view, and not the permissions of the objects.
> > >
> > > Table references are checked according to the owner of the view, but use
> > > in a view does not change the execution context for function or operator
> > > calls.  This is how it's always been done.
> > >
> > > > Is this a bug?
> > >
> > > Changing it would be a major definitional change (and a pretty major
> > > implementation change too).  It might be better, but please don't
> > > pre-judge the issue by labeling it a bug.
> >
> > Well, it sure sounds like a bug.  What logic is there that table access
> > use the view permissions, but not function access?  Could we just use
> > SECURITY DEFINER for function calls in views?
> 
> I already had this problem, look here:
> 
> http://groups.google.it/groups?q=postgres+security+definer+gaetano+mendola&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=b711hu%241g25%241%40news.hub.org&rnum=1
> 
> and I had no reply :-(
> 
> Regards
> Gaetano Mendola
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Index: doc/src/sgml/ref/create_view.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/create_view.sgml,v
retrieving revision 1.24
diff -c -c -r1.24 create_view.sgml
*** doc/src/sgml/ref/create_view.sgml	12 Sep 2003 00:12:47 -0000	1.24
--- doc/src/sgml/ref/create_view.sgml	26 Sep 2003 23:53:34 -0000
***************
*** 118,123 ****
--- 118,129 ----
  CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
  </programlisting>
     </para>
+ 
+    <para>
+     While access to tables in the view is controlled entirely by permissions
+     on the view, functions called by the view are checked independently.
+    </para>
+ 
   </refsect1>
  
   <refsect1>
Fri, 26 Sep 2003 20:10:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem with function permission test in a view
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am documenting this behavior in the CREATE VIEW manual page, diff
> attached.

> +    <para>
> +     While access to tables in the view is controlled entirely by permissions
> +     on the view, functions called by the view are checked independently.
> +    </para>

That seems a tad vague, not to say content-free.  Perhaps instead say
"Access to tables referenced in the view is determined by permissions of
the view owner.  However, functions called in the view are treated the
same as if they had been called directly from the query using the view.
Therefore the user of a view must have permissions to call all functions
used by the view."

As I said earlier, it's quite possible that we should consider this a
mistake.  But it's way too late to consider fixing it for 7.4, even if
we had consensus that it should be changed, which I don't think we do
yet.  In the meantime we should document the behavior clearly.

			regards, tom lane

Fri, 26 Sep 2003 23:04:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problem with function permission test in a view 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I am documenting this behavior in the CREATE VIEW manual page, diff
> > attached.
> 
> > +    <para>
> > +     While access to tables in the view is controlled entirely by permissions
> > +     on the view, functions called by the view are checked independently.
> > +    </para>
> 
> That seems a tad vague, not to say content-free.  Perhaps instead say
> "Access to tables referenced in the view is determined by permissions of
> the view owner.  However, functions called in the view are treated the
> same as if they had been called directly from the query using the view.
> Therefore the user of a view must have permissions to call all functions
> used by the view."
> 
> As I said earlier, it's quite possible that we should consider this a
> mistake.  But it's way too late to consider fixing it for 7.4, even if
> we had consensus that it should be changed, which I don't think we do
> yet.  In the meantime we should document the behavior clearly.

I like your text much better --- added.  I will throw this email in the
7.5 queue and we can decide if it is a bug then.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sat, 27 Sep 2003 21:20:06 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem with function permission test in a view
Bruce Momjian wrote:

> I like your text much better --- added.  I will throw this email in the
> 7.5 queue and we can decide if it is a bug then.

If is a bug is better have a patch for the 7.4
May be is only a missing feature.
For sure for us was and is actually a nightmare imagine :

V1 -> V2 -> F
          /
V3 -> V4


This mean that if a group A can use the view V1 and
a group B can use a view V3 whe shall back trace that
the function F must be executable by group A due V1
and by group B due V3. Fortunatelly Postgres now take
trace of dependencies so we can ( not easily ) analyze
each function and see if is used inside a view or not,
and choose the right permission.

We have ~ 100 View and ~ 100 functions :-(


Regards
Gaetano Mendola

PS: I vote that is a bug :-)



Mon, 29 Sep 2003 01:11:55 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Problem with function permission test in a view