Hi, I'm first time mailing-list user with a problem. I'm working on a UTF8 encoded database using psql (9.5.1, server 9.4.6)
I've implemented a very large materialized view to speed up certain search queries. I need to give users the ability to start a concurrent refresh on demand, without waiting around an hour for it to complete, so I've been looking at using the dblink extension. Specifically, giving a web usr the privilege to execute dblink functions like the asynchronous 'dblink_send_query' function to refresh the materialized view.
The problem I have, is that while I can grant execute on dblink functions to my web_usr in an existing database, if I dump and restore that database the execute privileges disappear. Is this expected behavior? Or a bug?
I've used the commands 'CREATE EXTENSION dblink WITH SCHEMA public;' and
'GRANT EXECUTE ON FUNCTION public.dblink_send_query(text, text) TO web_usr;' to set this up,
and 'SELECT proacl FROM pg_proc WHERE proname='dblink_send_query';' to verify the user privileges before and after the dump and restore.
Bug or not, are there other methods out there for keeping materialized views update to date I could explore?
And ideas are welcome, this has been driving me crazy!
--
Bryan Ellerbrock
Research Specialist, Mueller Lab
Boyce Thompson Institute for Plant Research
Office/Lab: 211 | 607-227-9868
Research Specialist, Mueller Lab
Boyce Thompson Institute for Plant Research
Office/Lab: 211 | 607-227-9868
