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