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

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

Responses

Browse pgsql-general by date

  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