Thread: Privileges granted on dblink extension function do not survive database dump and restore
Privileges granted on dblink extension function do not survive database dump and restore
From
Bryan Ellerbrock
Date:
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?
I've used the commands 'CREATE EXTENSION dblink WITH SCHEMA public;' and
'GRANT EXECUTE ON FUNCTION public.dblink_send_query(text, text) TO web_usr;' to set this up,
and 'SELECT proacl FROM pg_proc WHERE proname='dblink_send_query';' to verify the user privileges before and after the dump and restore.
Bug or not, are there other methods out there for keeping materialized views update to date I could explore?
And ideas are welcome, this has been driving me crazy!
--
Bryan Ellerbrock
Research Specialist, Mueller Lab
Boyce Thompson Institute for Plant Research
Office/Lab: 211 | 607-227-9868
Research Specialist, Mueller Lab
Boyce Thompson Institute for Plant Research
Office/Lab: 211 | 607-227-9868
Re: Privileges granted on dblink extension function do not survive database dump and restore
From
Joe Conway
Date:
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
Attachment
Re: Privileges granted on dblink extension function do not survive database dump and restore
From
Bryan Ellerbrock
Date:
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@joeconway.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
Research Specialist, Mueller Lab
Boyce Thompson Institute for Plant Research
Office/Lab: 211 | 607-227-9868
Re: Privileges granted on dblink extension function do not survive database dump and restore
From
"David G. Johnston"
Date:
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.
Re: Privileges granted on dblink extension function do not survive database dump and restore
From
Joe Conway
Date:
On 02/26/2016 07:12 AM, David G. Johnston wrote: > 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. Exactly. As the docs say "Such a change is usually only sensible if you concurrently make the same change in the extension's script file." So you can edit this file: $(pg_config --sharedir)/extension/dblink--1.1.sql and add your GRANT. However also be advised that the change will be lost on a major upgrade. An alternate might be to wrap the dblink function call with your own security definer function. That would get dumped and reloaded. Just be careful what you allow that function to do -- i.e. don't let it run arbitrary queries, just one very specific one. See http://www.postgresql.org/docs/9.5/interactive/sql-createfunction.html HTH, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Attachment
Re: Privileges granted on dblink extension function do not survive database dump and restore
From
Jerry Sievers
Date:
Bryan Ellerbrock <bje24@cornell.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 leastbe 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@joeconway.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.consulting@comcast.net p: 312.241.7800