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

On 02/25/2016 03:42 PM, Bryan Ellerbrock wrote:
> 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?

It is expected -- see:
http://www.postgresql.org/docs/9.4/interactive/extend-extensions.html

To witt:
"Also, while you can change the definition of an extension member
object (for example, via CREATE OR REPLACE FUNCTION for a function),
bear in mind that the modified definition will not be dumped by
pg_dump. Such a change is usually only sensible if you concurrently
make the same change in the extension's script file."

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development