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