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

Bryan Ellerbrock <bje24(at)cornell(dot)edu> writes:

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

WRong.

See pg_proc.proacl.

The same goes for relations, types, schemas... etc. The ACL is an
attribute of the entity and thus stored in the appropriate catalog row.

To wit: pg_class.relacl, pg_namespace.nspacl and so on.

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

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800