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.

While table access done in a view follows this outline, function call
access does not.  In my tests below, I create a function ftest(), and
allow only 'postgres' to execute it.  I then create a view and put the
function call in the view, and grant permissions on the view to others. 
However, in the test, you will see that the function call in the view
fails.  

Is this a bug?

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

	CREATE USER dummyuser;
	CREATE USER
	SET SESSION AUTHORIZATION postgres;
	SET
	CREATE OR REPLACE FUNCTION ftest() RETURNS INTEGER
	EXTERNAL SECURITY DEFINER
	AS 'SELECT 1' LANGUAGE 'sql';
	CREATE FUNCTION
	REVOKE EXECUTE ON FUNCTION ftest() FROM PUBLIC;
	REVOKE
	SELECT ftest();
	 ftest
	-------
	     1
	(1 row)
	
	SET SESSION AUTHORIZATION dummyuser;
	SET
	SELECT ftest();
	ERROR:  permission denied for function ftest
	SET SESSION AUTHORIZATION postgres;
	SET
	CREATE VIEW vv AS SELECT ftest();
	ERROR:  relation "vv" already exists
	SELECT * FROM vv;
	 ftest
	-------
	     1
	(1 row)
	
	GRANT ALL ON vv TO PUBLIC;
	GRANT
	SET SESSION AUTHORIZATION dummyuser;
	SET
	SELECT * FROM vv;
	ERROR:  permission denied for function ftest

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

Thu, 11 Sep 2003 11:49:59 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Problem with function permission test in a view
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.

			regards, tom lane

Thu, 11 Sep 2003 13:02:47 -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:
> > 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?

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

Thu, 11 Sep 2003 13:11:10 -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:
> 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?

You're confusing two distinct questions, I think.  One is how we decide
whether it's allowed to call the function (ie, whose USAGE right do we
check).  The other is whose ID does the function run under.  If the
function is SECURITY DEFINER then the second question is determined by
the function itself, but otherwise it's not; and in any case SECURITY
DEFINER doesn't speak to the first question.

A case can be made that the answer to both questions should be "the view
owner", rather than "the view user" as it is now, but I am not sure that
this is an open-and-shut issue.  Both the user and the owner can affect
whether such a function gets called and what arguments it gets called
with (maybe not so much in a view rule, but definitely in ON INSERT and
other action rules).  It seems possible that we'd just be switching from
one set of security issues to another.

			regards, tom lane

Thu, 11 Sep 2003 15:57:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problem with function permission test in a view 
"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



Fri, 12 Sep 2003 00:28:21 +0200
"Gaetano Mendola" <gmendola@mbigroup.it>
Re: Problem with function permission test in a view