From: | Bryan Ellerbrock <bje24(at)cornell(dot)edu> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Privileges granted on dblink extension function do not survive database dump and restore |
Date: | 2016-02-25 23:42:30 |
Message-ID: | CADmxfmmz-ATwptaidTSAF0XE=cPeikMyc00sj6t9xF6KCV5jCQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2016-02-26 00:13:12 | Re: Privileges granted on dblink extension function do not survive database dump and restore |
Previous Message | CS DBA | 2016-02-25 19:45:56 | Oracle conversion questions - TYPE's and ARRAY's |