Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux