postgresql - Grant select on views which use functions -

i'm using postgresql 8.4 , having bit of problem granting select privileges on view database onwed different user when view uses of databases functions.

as new user, when try run, example select * users_pwd; users_pwd defined as:

create view users_pwd select *, get_pwd(id) users; 

and get_pwd as:

create or replace function get_pwd(p_id integer) returns text $body$ declare u record; begin select u * users id = p_id; return u.password; end; $body$ language plpgsql; 

i following error:

error:  permission denied relation users context:  sql statement "select * users id =  $1 " pl/pgsql function "get_pwd" line 3 @ sql statement 

the way have user query view explicitly grant select on table users don't want do.

if view doesn't use function, rather other tables new user doesn't have explicit access works fine.

you create function owner can select table users. such function should created security definer clause, executed owner rights.

more information can find here: http://www.postgresql.org/docs/9.0/interactive/sql-createfunction.html

you can grant execute privileges on functions. see grant in docs.