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?
In short, because no one has taken the take to define and implement such behavior. I doubt it is impossible but likewise it is not trivial either - and there haven't been many complaints regarding the current limitations.
pg_dump emits a "CREATE EXTENSION" statement to the dump file and then pg_restore executes the "CREATE EXTENSION". None of the actual schema objects are dumped and thus any changes to those objects in the current database, including their permissions, are lost.
David J.
