Privileges granted on dblink extension function do not survive database dump and restore

Поиск
Список
Период
Сортировка
От Bryan Ellerbrock
Тема Privileges granted on dblink extension function do not survive database dump and restore
Дата
Msg-id CADmxfmmz-ATwptaidTSAF0XE=cPeikMyc00sj6t9xF6KCV5jCQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Privileges granted on dblink extension function do not survive database dump and restore  (Joe Conway <mail@joeconway.com>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: CS DBA
Дата:
Сообщение: Oracle conversion questions - TYPE's and ARRAY's
Следующее
От: Joe Conway
Дата:
Сообщение: Re: Privileges granted on dblink extension function do not survive database dump and restore