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

From: Joe Conway <mail(at)joeconway(dot)com>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Bryan Ellerbrock <bje24(at)cornell(dot)edu>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Privileges granted on dblink extension function do not survive database dump and restore
Date: 2016-02-26 15:46:37
Message-ID: 56D0735D.6050602@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Leonardo M. Ramé 2016-02-26 18:29:52 Update foreign table with trigger
Previous Message David G. Johnston 2016-02-26 15:12:13 Re: Privileges granted on dblink extension function do not survive database dump and restore