Selecting a value in a hstore results in "permission denied for function fetchval"

Question

I am using hstores in my database and having trouble with permissions. When logging in as a superuser, all works fine, but when using a "normal" login role, I don't seem to be able to select a value in a hstore:

mydb=> select properties->'foo' from t1;
ERROR:  permission denied for function fetchval

Selecting the hstore itself does work, so it doesn't seem to be a table-access permission problem:

mydb=> select properties from t1;
         properties         
----------------------------
 "baz"=>"foz", "foo"=>"bar"
(1 row)

I tried searching online for this error, but got zero results. Also, I cannot find the fetchval function in my function list.

Answer 1

fetchval is the function corresponding to the -> operator on hstore.

Probably you have revoked the right to execute hstore functions from non-priviledged users.

For instance, if the hstore extension lives in the public schema, and you run something like that:

revoke execute on all functions in schema public from public;

then the users will have the right to select hstore contents but get this error message: ERROR: permission denied for function fetchval when trying to apply the -> operator.

To solve the problem, you probably need to revisit your security policy and handle differently the permissions on functions.