Thread: problem permission on view
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
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
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
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
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
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
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
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