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

Huh. Thanks for the quick reply Joe. I'm still not sure I fully understand
the behavior here, but it's nice to at least be able to discuss it.

The documentation you shared makes it clear that, unless extra steps are
taken, changes to an extension's object definitions won't be preserved in a
dump. But I still don't see from it's wording why privileges granted on
those objects would also be lost. Isn't a privilege, for example EXECUTE on
a function, stored as part of the schema rather than as a change to the
definition of the function itself?

On 25 February 2016 at 19:13, Joe Conway <mail(at)joeconway(dot)com> wrote:

> 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
>
>

--
Bryan Ellerbrock
Research Specialist, Mueller Lab
Boyce Thompson Institute for Plant Research
Office/Lab: 211 | 607-227-9868