Grant select on views which use functions

I'm using postgresql 8.4 and am having a bit of a problem granting select privileges on a view from a database onwed by a different user when the view uses any of the databases functions.

As the new user, when I try to run, for example select * from users_pwd;开发者_如何学Go where users_pwd is defined as:

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

and get_pwd as:

CREATE OR REPLACE FUNCTION get_pwd(p_id integer)
RETURNS text AS
$BODY$
declare u record;
BEGIN
select into u * from users where id = p_id;
return u.password;
END;
$BODY$
LANGUAGE plpgsql;

I get the following error:

ERROR:  permission denied for relation users
CONTEXT:  SQL statement "select * from users where id =  $1 "
PL/pgSQL function "get_pwd" line 3 at SQL statement

The only way to have the user query the view is to explicitly grant select on the table users which I don't want to do.

If a view doesn't use any function, but rather just other tables which the new user doesn't have explicit access to it works perfectly fine.

You could create the function with owner who can select from the table users. Such a function should be created with SECURITY DEFINER clause, so it will be executed with the owner rights.

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

You can also GRANT EXECUTE privileges on functions. See GRANT in the docs.