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

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

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

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