Bryan Ellerbrock <bje24@xxxxxxxxxxx> 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@xxxxxxxxxxxxx> 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.consulting@xxxxxxxxxxx p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
