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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
