BUG #14456: pg_dump doesn't restore permissions on tables belonging to an extension
The following bug has been logged on the website:
Bug reference: 14456
Logged by: Daniele Varrazzo
Email address: daniele.varrazzo@gmail.com
PostgreSQL version: 9.5.4
Operating system: Linux
Description:
As per title. To test:
1) create an extension containing a table, e.g.:
$ cat /usr/share/postgresql/9.5/extension/testext.control
default_version = '1.0'
comment = 'test of a pg bug'
superuser = false
$ cat /usr/share/postgresql/9.5/extension/testext--1.0.sql
create table testtbl (id serial primary key, data text);
select pg_catalog.pg_extension_config_dump('testtbl', '');
2) Load the extension in the database
=# create database test;
CREATE DATABASE
=# \c test;
test=# create schema extschema;
CREATE SCHEMA
test=# create extension testext with schema extschema;
CREATE EXTENSION
3) Customize extension table permissions
test=# create user u2;
CREATE ROLE
test=# grant select on extschema.testtbl to u2;
GRANT
test=# \dpp extschema.testtbl
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies
-----------+---------+-------+-------------------+-------------------+----------
extschema | testtbl | table | piro=arwdDxt/piro+| |
| | | u2=r/piro | |
(1 row)
test=# insert into extschema.testtbl (data) values ('asdf');
INSERT 0 1
4) Create a new database and dump/restore data there
test=# create database test2;
CREATE DATABASE
$ pg_dump test | psql -1 test2
5) permissions are not restored
test2=# \dpp extschema.testtbl
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies
-----------+---------+-------+-------------------+-------------------+----------
extschema | testtbl | table | | |
(1 row)
Note that using ALTER DEFAULT PRIVILEGES doesn't work either: the default
privs on the schemas are restored after the extension is created so the
tables created by the extensions don't benefit of it.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Greetings Daniele,
* daniele.varrazzo@gmail.com (daniele.varrazzo@gmail.com) wrote:
PostgreSQL version: 9.5.4
[...]
As per title. To test:
Prior to 9.6, dump/reload of an extension with custom ACLs wasn't
really supported. This should work correctly in 9.6. Unfortunately,
the changes required are too much to be able to back-patch to prior
versions of PG.
Thanks!
Stephen
I'm experiencing issues with this new "feature".
My extension dynamically creates extension-owned tables and puts ACLs on
them.
When the database is dumped, it includes grants/revokes for those tables,
which will not exist when the extension is re-installed.
As a result, when the database is restored, I keep getting warnings because
it's trying to apply ACLs to tables that don't exist.
Is there a way around this issue?
On Thu, Dec 8, 2016 at 2:31 PM Stephen Frost <sfrost@snowman.net> wrote:
Greetings Daniele,
* daniele.varrazzo@gmail.com (daniele.varrazzo@gmail.com) wrote:
PostgreSQL version: 9.5.4
[...]
As per title. To test:
Prior to 9.6, dump/reload of an extension with custom ACLs wasn't
really supported. This should work correctly in 9.6. Unfortunately,
the changes required are too much to be able to back-patch to prior
versions of PG.Thanks!
Stephen
--
Moshe Jacobson
Principal Architect, Nead Werx Inc.
Greetings,
* Moshe Jacobson (moshe@neadwerx.com) wrote:
My extension dynamically creates extension-owned tables and puts ACLs on
them.
Ok, in 9.6, we should realize that your extension changed the ACLs for
those tables.
When the database is dumped, it includes grants/revokes for those tables,
which will not exist when the extension is re-installed.
When the database is dumped, it should include a CREATE EXTENSION
command. It also shouldn't include GRANTs/REVOKEs unless the user
changed the permissions on the extension's tables from what they were
set to when the extension was installed.
As a result, when the database is restored, I keep getting warnings because
it's trying to apply ACLs to tables that don't exist.Is there a way around this issue?
A self-contained test case against 9.6 which shows the issue you're
having would really be the best way to help us.
If the issue is that you're working on a pre-9.6 version of PG, then I'm
afraid you'll need to upgrade or live with the warnings.
Thanks!
Stephen
Stephen, thank you for responding, but your response indicated a
misunderstanding. In lieu of a standalone example, let me explain the issue
again.
Scenario:
1. Extension is installed into its own schema. Installation is now
complete.
2. Extension creates a new table in its schema
3. Extension changes ACLs on the table.
4. After changing ACLs, the table is added to the extension (ALTER
EXTENSION)
5. A pg_dump of this database will now include ACL commands for the
table.
6. A pg_restore of this file will give warnings because the ACLs refer
to a table that is not created as part of the installation process.
Because I am setting the ACLs before adding the table to the extension, is
should not include those ACLs in the pg_dump output.
Thanks.
On Thu, Jan 12, 2017 at 1:35 PM Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
* Moshe Jacobson (moshe@neadwerx.com) wrote:
My extension dynamically creates extension-owned tables and puts ACLs on
them.Ok, in 9.6, we should realize that your extension changed the ACLs for
those tables.When the database is dumped, it includes grants/revokes for those tables,
which will not exist when the extension is re-installed.When the database is dumped, it should include a CREATE EXTENSION
command. It also shouldn't include GRANTs/REVOKEs unless the user
changed the permissions on the extension's tables from what they were
set to when the extension was installed.As a result, when the database is restored, I keep getting warnings
because
it's trying to apply ACLs to tables that don't exist.
Is there a way around this issue?
A self-contained test case against 9.6 which shows the issue you're
having would really be the best way to help us.If the issue is that you're working on a pre-9.6 version of PG, then I'm
afraid you'll need to upgrade or live with the warnings.Thanks!
Stephen
--
Moshe Jacobson
Principal Architect, Nead Werx Inc.
Moshe Jacobson <moshe@neadwerx.com> writes:
Scenario:
1. Extension is installed into its own schema. Installation is now
complete.
2. Extension creates a new table in its schema
3. Extension changes ACLs on the table.
Extensions are not actors, so claiming that "the extension" did something
is at best pretty fuzzy thinking.
4. After changing ACLs, the table is added to the extension (ALTER
EXTENSION)
5. A pg_dump of this database will now include ACL commands for the
table.
Hmm. There's an argument to be made that ALTER EXTENSION ADD should
absorb whatever the object's current ACLs are into the pg_init_privs
entries for the extension. (I don't think it does that now, though
I might be wrong.) However ...
6. A pg_restore of this file will give warnings because the ACLs refer
to a table that is not created as part of the installation process.
I think this scenario is simply pilot error, or at least gross abuse of
the extension system. If you dump and reload a DB containing an extension,
the extension definition that's fetched by CREATE EXTENSION is expected
to define (at least) all the objects that belonged to the extension in the
old DB. You can't just randomly ALTER EXTENSION and not update the
extension definition script to match.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Greetings,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Moshe Jacobson <moshe@neadwerx.com> writes:
Scenario:
1. Extension is installed into its own schema. Installation is now
complete.
2. Extension creates a new table in its schema
3. Extension changes ACLs on the table.Extensions are not actors, so claiming that "the extension" did something
is at best pretty fuzzy thinking.
Agreed.
4. After changing ACLs, the table is added to the extension (ALTER
EXTENSION)
5. A pg_dump of this database will now include ACL commands for the
table.Hmm. There's an argument to be made that ALTER EXTENSION ADD should
absorb whatever the object's current ACLs are into the pg_init_privs
entries for the extension. (I don't think it does that now, though
I might be wrong.) However ...
I've not gone and looked yet, but I doubt that it does. I think I can
agree with the argument that it really should add those ACLs to
pg_init_privs. Of course, any furhter manipulation of the ACLs from
that point will cause those ACLs to be included in the pg_dump.
I'll take a look at ALTER EXTENSION ADD and pg_init_privs.
6. A pg_restore of this file will give warnings because the ACLs refer
to a table that is not created as part of the installation process.I think this scenario is simply pilot error, or at least gross abuse of
the extension system. If you dump and reload a DB containing an extension,
the extension definition that's fetched by CREATE EXTENSION is expected
to define (at least) all the objects that belonged to the extension in the
old DB. You can't just randomly ALTER EXTENSION and not update the
extension definition script to match.
Agreed.
Thanks!
Stephen
Hi Tom,
Thanks for the response.
On Thu, Jan 12, 2017 at 2:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
3. Extension changes ACLs on the table.
Extensions are not actors, so claiming that "the extension" did something
is at best pretty fuzzy thinking.
Fair enough. The extension's includes code that stores logs into a table
that is partitioned by timestamp. A function belonging to the extension is
periodically called to rotate out the currently-active partition and create
a new one.
4. After changing ACLs, the table is added to the extension (ALTER
EXTENSION)
5. A pg_dump of this database will now include ACL commands for the
table.Hmm. There's an argument to be made that ALTER EXTENSION ADD should
absorb whatever the object's current ACLs are into the pg_init_privs
entries for the extension. (I don't think it does that now, though
I might be wrong.) However ...
Yes, that's what I'm arguing for. As I write this I see another message in
which it looks like Stephen has agreed to look at this, so thank you
Stephen!
6. A pg_restore of this file will give warnings because the ACLs refer
to a table that is not created as part of the installation process.
I think this scenario is simply pilot error, or at least gross abuse of
the extension system. If you dump and reload a DB containing an extension,
the extension definition that's fetched by CREATE EXTENSION is expected
to define (at least) all the objects that belonged to the extension in the
old DB. You can't just randomly ALTER EXTENSION and not update the
extension definition script to match.
The reason I add the dynamically-created tables to the extension is so that
they are never included in the pg_dump output. If this is a gross abuse of
the extension system, is there another way you can suggest to mark these
tables as not-to-be-dumped?
Thank you.
--
Moshe Jacobson
Principal Architect, Nead Werx Inc.
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Hmm. There's an argument to be made that ALTER EXTENSION ADD should
absorb whatever the object's current ACLs are into the pg_init_privs
entries for the extension. (I don't think it does that now, though
I might be wrong.) However ...
I've not gone and looked yet, but I doubt that it does. I think I can
agree with the argument that it really should add those ACLs to
pg_init_privs. Of course, any furhter manipulation of the ACLs from
that point will cause those ACLs to be included in the pg_dump.
I'll take a look at ALTER EXTENSION ADD and pg_init_privs.
By the same token, does ALTER EXTENSION DROP remove those entries?
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Hmm. There's an argument to be made that ALTER EXTENSION ADD should
absorb whatever the object's current ACLs are into the pg_init_privs
entries for the extension. (I don't think it does that now, though
I might be wrong.) However ...I've not gone and looked yet, but I doubt that it does. I think I can
agree with the argument that it really should add those ACLs to
pg_init_privs. Of course, any furhter manipulation of the ACLs from
that point will cause those ACLs to be included in the pg_dump.I'll take a look at ALTER EXTENSION ADD and pg_init_privs.
By the same token, does ALTER EXTENSION DROP remove those entries?
I'll make sure it does. My guess at the moment is that it doesn't.
Thanks!
Stephen
Thank you both, Tom and Stephen!
On Thu, Jan 12, 2017 at 2:25 PM Stephen Frost <sfrost@snowman.net> wrote:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Hmm. There's an argument to be made that ALTER EXTENSION ADD should
absorb whatever the object's current ACLs are into the pg_init_privs
entries for the extension. (I don't think it does that now, though
I might be wrong.) However ...I've not gone and looked yet, but I doubt that it does. I think I can
agree with the argument that it really should add those ACLs to
pg_init_privs. Of course, any furhter manipulation of the ACLs from
that point will cause those ACLs to be included in the pg_dump.I'll take a look at ALTER EXTENSION ADD and pg_init_privs.
By the same token, does ALTER EXTENSION DROP remove those entries?
I'll make sure it does. My guess at the moment is that it doesn't.
Thanks!
Stephen
--
Moshe Jacobson
Principal Architect, Nead Werx Inc.
Moshe Jacobson <moshe@neadwerx.com> writes:
On Thu, Jan 12, 2017 at 2:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think this scenario is simply pilot error, or at least gross abuse of
the extension system. If you dump and reload a DB containing an extension,
the extension definition that's fetched by CREATE EXTENSION is expected
to define (at least) all the objects that belonged to the extension in the
old DB. You can't just randomly ALTER EXTENSION and not update the
extension definition script to match.
The reason I add the dynamically-created tables to the extension is so that
they are never included in the pg_dump output. If this is a gross abuse of
the extension system, is there another way you can suggest to mark these
tables as not-to-be-dumped?
The extension mechanism definitely isn't meant to do that ;-). Maybe
you could put these not-to-dump tables in their own schema and exclude
that schema from pg_dump with -N?
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Tom Lane wrote:
Moshe Jacobson <moshe@neadwerx.com> writes:
On Thu, Jan 12, 2017 at 2:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think this scenario is simply pilot error, or at least gross abuse of
the extension system. If you dump and reload a DB containing an extension,
the extension definition that's fetched by CREATE EXTENSION is expected
to define (at least) all the objects that belonged to the extension in the
old DB. You can't just randomly ALTER EXTENSION and not update the
extension definition script to match.The reason I add the dynamically-created tables to the extension is so that
they are never included in the pg_dump output. If this is a gross abuse of
the extension system, is there another way you can suggest to mark these
tables as not-to-be-dumped?The extension mechanism definitely isn't meant to do that ;-). Maybe
you could put these not-to-dump tables in their own schema and exclude
that schema from pg_dump with -N?
Is this related to this commit?
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f2fcad27d59c8e5c48f8fa0a96c8355e40f24273
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
Tom Lane wrote:
Moshe Jacobson <moshe@neadwerx.com> writes:
The reason I add the dynamically-created tables to the extension is so that
they are never included in the pg_dump output.
The extension mechanism definitely isn't meant to do that ;-). Maybe
you could put these not-to-dump tables in their own schema and exclude
that schema from pg_dump with -N?
Is this related to this commit?
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f2fcad27d59c8e5c48f8fa0a96c8355e40f24273
Seems like it doesn't quite match Moshe's use case --- he *doesn't* want
those tables dumped. I'm not sure if he cares whether they have
dependencies on the extension.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Tom, Moshe,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Hmm. There's an argument to be made that ALTER EXTENSION ADD should
absorb whatever the object's current ACLs are into the pg_init_privs
entries for the extension. (I don't think it does that now, though
I might be wrong.) However ...I've not gone and looked yet, but I doubt that it does. I think I can
agree with the argument that it really should add those ACLs to
pg_init_privs. Of course, any furhter manipulation of the ACLs from
that point will cause those ACLs to be included in the pg_dump.I'll take a look at ALTER EXTENSION ADD and pg_init_privs.
By the same token, does ALTER EXTENSION DROP remove those entries?
Please find attached a WIP patch to have ALTER EXTENSION ADD/DROP update
pg_init_privs accordingly.
It's a bit big as it needs to have independent code for every different
kind of object that ALTER EXTENSION ADD/DROP supports. Most of that
code is pretty boiler-plate, of course.
I'm planning to review it further, add more regression tests, and then
back-patch it to 9.6.
Moshe, if you're feeling adventurous and want to give it a spin and make
sure it behaves as you're expecting, that'd be great.
Tom, your thoughts and comments are always welcome, if you'd like to
peruse the patch, of course.
Otherwise, I expect to have time to wrap this all up over the weekend.
Thanks!
Stephen
Attachments:
alter_ext_adddrop_init_privs_v1_master.patchtext/x-diff; charset=us-asciiDownload+548-419
On Fri, Jan 20, 2017 at 5:10 PM Stephen Frost <sfrost@snowman.net> wrote:
Please find attached a WIP patch to have ALTER EXTENSION ADD/DROP update
pg_init_privs accordingly.
Thanks so much Stephen.
We're not used to compiling from source around here, so it may be difficult
for me to find the time to test, but if I am able, I will let you know.
--
Moshe Jacobson
Principal Architect, Nead Werx Inc.
* Moshe Jacobson (moshe@neadwerx.com) wrote:
On Fri, Jan 20, 2017 at 5:10 PM Stephen Frost <sfrost@snowman.net> wrote:
Please find attached a WIP patch to have ALTER EXTENSION ADD/DROP update
pg_init_privs accordingly.Thanks so much Stephen.
We're not used to compiling from source around here, so it may be difficult
for me to find the time to test, but if I am able, I will let you know.
No problem. I'm planning to push it later today and it'll be in 9.6.2.
Thanks!
Stephen
All,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Hmm. There's an argument to be made that ALTER EXTENSION ADD should
absorb whatever the object's current ACLs are into the pg_init_privs
entries for the extension. (I don't think it does that now, though
I might be wrong.) However ...I've not gone and looked yet, but I doubt that it does. I think I can
agree with the argument that it really should add those ACLs to
pg_init_privs. Of course, any furhter manipulation of the ACLs from
that point will cause those ACLs to be included in the pg_dump.I'll take a look at ALTER EXTENSION ADD and pg_init_privs.
By the same token, does ALTER EXTENSION DROP remove those entries?
I've pushed a fix for this and back-patched it to 9.6.
Thanks!
Stephen