Hi all,
I'm having some problem with permissions on views, I spoke with Josh on IRC
about it and I'm reposting it:


I found a not simmetrical behavior about permission on views and functions.
Let me explain:

If I use the view/table T  inside the view V, is enough give the select
permission on view V remove the select permission on the view/table used
and all is working as expected.

If I use the view/table T inside the funcion F is enough declare F with
the "Secuity definer" attribute and of course give the execution permission,
the select permission on the view/table used and all is working as expected

In these two cases above all is working fine, the following case have some
problems:

If the view V use a function F.

In this last case is not enough have the select permisson on V but I have
to give also the Execution permission on F!!!

This fact are driving us to put
1) Select permission on V
2) Exceute permission + Security Definer attr on F.

this last point give to the user the possibility to execute F with any
aribitrary value, instead of only the values present on the view ( already
filtered ).


Regards
Gaetano Mendola









Fri, 17 Sep 2004 00:16:25 +0200
Gaetano Mendola <mendola@bigfoot.com>
problem permission on view
Folks,

> If the view V use a function F.
>
> In this last case is not enough have the select permisson on V but I have
> to give also the Execution permission on F!!!

As said, I discussed this with Gaetano on IRC, and am not sure why things are 
set up the way they are.    If a user has permission on a view, shouldn't 
that include permission on any functions executed by the view?    If not, why 
not?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Thu, 16 Sep 2004 15:25:08 -0700
Josh Berkus <josh@agliodbs.com>
Re: problem permission on view
Josh Berkus <josh@agliodbs.com> writes:
> As said, I discussed this with Gaetano on IRC, and am not sure why things are
> set up the way they are.    If a user has permission on a view, shouldn't 
> that include permission on any functions executed by the view?    If not, why
> not?

See prior discussions.  The two stumbling blocks I can recall are:

1. Not breaking the ability of functions called by views to tell who the
real user is --- see the filtering in the pg_statistic view for a handy
real-world example.  This constrains the possible design solutions.

2. Given the flexibility of the rule rewrite system, it's not unlikely
that a user could find a way to execute any function invoked by a rule
on data of his choosing.  This is a bad idea if the function should not
ordinarily be his to call.

(Enlarging on point 2: there are no views.  There are only rules, and
any proposed solution has to be cast in terms of what happens with
arbitrary rules.)

I'm not saying we'll never do this, but I am saying that the topic has
been visited before and no one's come up with an acceptable design.

			regards, tom lane

Thu, 16 Sep 2004 18:57:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: problem permission on view 

Josh Berkus <josh@agliodbs.com> writes:

> As said, I discussed this with Gaetano on IRC, and am not sure why things are 
> set up the way they are.    If a user has permission on a view, shouldn't 
> that include permission on any functions executed by the view?    If not, why 
> not?

Then all someone would have to do to bypass security on a function would be to
define a function of their own calling it?

If I execute shell script that calls a setuid root-only binary that doesn't
give me permission to execute the root-only binary...

-- 
greg


16 Sep 2004 18:57:39 -0400
Greg Stark <gsstark@mit.edu>
Re: problem permission on view
Gaetano Mendola wrote:
> Hi all,
> I'm having some problem with permissions on views, I spoke with Josh on IRC
> about it and I'm reposting it:
> 
> 
> I found a not simmetrical behavior about permission on views and functions.
> Let me explain:
> 
> If I use the view/table T  inside the view V, is enough give the select
> permission on view V remove the select permission on the view/table used
> and all is working as expected.
> 
> If I use the view/table T inside the funcion F is enough declare F with
> the "Secuity definer" attribute and of course give the execution 
> permission,
> the select permission on the view/table used and all is working as expected
> 
> In these two cases above all is working fine, the following case have some
> problems:
> 
> If the view V use a function F.
> 
> In this last case is not enough have the select permisson on V but I have
> to give also the Execution permission on F!!!
> 
> This fact are driving us to put
> 1) Select permission on V
> 2) Exceute permission + Security Definer attr on F.
> 
> this last point give to the user the possibility to execute F with any
> aribitrary value, instead of only the values present on the view ( already
> filtered ).

Maybe this could be solved by a Security Definer flag for tables/views?

Regards,
Andreas

Fri, 17 Sep 2004 10:45:27 +0000
Andreas Pflug <pgadmin@pse-consulting.de>
Re: problem permission on view
Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> 
>>As said, I discussed this with Gaetano on IRC, and am not sure why things are
>>set up the way they are.    If a user has permission on a view, shouldn't 
>>that include permission on any functions executed by the view?    If not, why
>>not?
> 
> 
> See prior discussions.  The two stumbling blocks I can recall are:
> 
> 1. Not breaking the ability of functions called by views to tell who the
> real user is --- see the filtering in the pg_statistic view for a handy
> real-world example.  This constrains the possible design solutions.
> 
> 2. Given the flexibility of the rule rewrite system, it's not unlikely
> that a user could find a way to execute any function invoked by a rule
> on data of his choosing.  This is a bad idea if the function should not
> ordinarily be his to call.
> 
> (Enlarging on point 2: there are no views.  There are only rules, and
> any proposed solution has to be cast in terms of what happens with
> arbitrary rules.)
> 
> I'm not saying we'll never do this, but I am saying that the topic has
> been visited before and no one's come up with an acceptable design.

Trust me, manage 167 views and 341 functions is a night mare in this way,
considering that I public some views and functions with a sort of XML-RPC.

As I already wrote on IRC, giving the executable permission on a table
( with security definer ) I allow users to call the function with any value
instead of values only presents in the view. I think this is a big limitation.
I'd like to fix this by myself but for lack of time and lack of postgres
code knowledge I'm stuck.

Regards
Gaetano Mendola












Fri, 17 Sep 2004 20:00:06 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: problem permission on view
Gaetano Mendola wrote:

> I'd like to fix this by myself but for lack of time and lack of postgres
> code knowledge I'm stuck.

What you want is
CREATE VIEW foo AS
   SELECT p1, p2, bar('theValidParameter') as p3
   FROM othertab;
GRANT ALL ON TABLE foo TO public;

and don't want to grant execute on bar() to public.

What you could do is creating an intermediate function like this:

CREATE FUNCTION interfoo() RETURNS SETOF record AS
$q$
   SELECT p1, p2, bar('theValidParameter') as p3
   FROM othertab;
$q$ LANGUAGE SQL SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION interfoo() TO public;

CREATE VIEW foo AS
   SELECT f.p1, f.p2, f.p3 FROM interfoo() f(a text, b text, c text);
GRANT ALL ON TABLE foo TO public;

Regards,
Andreas

Fri, 17 Sep 2004 22:06:11 +0000
Andreas Pflug <pgadmin@pse-consulting.de>
Re: problem permission on view
Andreas Pflug wrote:
> Gaetano Mendola wrote:
> 
>> I'd like to fix this by myself but for lack of time and lack of postgres
>> code knowledge I'm stuck.
> 
> 
> What you want is
> CREATE VIEW foo AS
>   SELECT p1, p2, bar('theValidParameter') as p3
>   FROM othertab;
> GRANT ALL ON TABLE foo TO public;
> 
> and don't want to grant execute on bar() to public.
> 
> What you could do is creating an intermediate function like this:
> 
> CREATE FUNCTION interfoo() RETURNS SETOF record AS
> $q$
>   SELECT p1, p2, bar('theValidParameter') as p3
>   FROM othertab;
> $q$ LANGUAGE SQL SECURITY DEFINER;
> GRANT EXECUTE ON FUNCTION interfoo() TO public;
> 
> CREATE VIEW foo AS
>   SELECT f.p1, f.p2, f.p3 FROM interfoo() f(a text, b text, c text);
> GRANT ALL ON TABLE foo TO public;

I was thinking about it but I realized soon that this can work if the view
involved are light, what kind of optimization can do postgres in view like this:

SELECT *
FROM bar b,
      foo f
WHERE b.p1 = f.p1;

I guess the only way postgres can manage it is to execute the full scan
for materialize foo :-(


Regards
Gaetano Mendola


Sat, 18 Sep 2004 00:25:13 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: problem permission on view