From: | Stephen Frost <sfrost(at)snowman(dot)net> |
---|---|
To: | Noah Misch <noah(at)leadboat(dot)com> |
Cc: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: dumping database privileges broken in 9.6 |
Date: | 2016-07-12 20:18:19 |
Message-ID: | 20160712201819.GA4028@tamriel.snowman.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
All,
* Noah Misch (noah(at)leadboat(dot)com) wrote:
> On Wed, Jun 29, 2016 at 11:50:17AM -0400, Stephen Frost wrote:
> > * Peter Eisentraut (peter(dot)eisentraut(at)2ndquadrant(dot)com) wrote:
> > > Do this:
> > >
> > > CREATE DATABASE test1;
> > > REVOKE CONNECT ON DATABASE test1 FROM PUBLIC;
> > >
> > > Run pg_dumpall.
> > >
> > > In 9.5, this produces
> > >
> > > CREATE DATABASE test1 WITH TEMPLATE = template0 OWNER = peter;
> > > REVOKE ALL ON DATABASE test1 FROM PUBLIC;
> > > REVOKE ALL ON DATABASE test1 FROM peter;
> > > GRANT ALL ON DATABASE test1 TO peter;
> > > GRANT TEMPORARY ON DATABASE test1 TO PUBLIC;
> > >
> > > In 9.6, this produces only
> > >
> > > CREATE DATABASE test1 WITH TEMPLATE = template0 OWNER = peter;
> > > GRANT TEMPORARY ON DATABASE test1 TO PUBLIC;
> > > GRANT ALL ON DATABASE test1 TO peter;
> > >
> > > Note that the REVOKE statements are missing. This does not
> > > correctly recreate the original state.
> >
> > I see what happened here, the query in dumpCreateDB() needs to be
> > adjusted to pull the default information to then pass to
> > buildACLComments(), similar to how the objects handled by pg_dump work.
> > The oversight was in thinking that databases didn't have any default
> > rights granted, which clearly isn't correct.
> >
> > I'll take care of that in the next day or so and add an appropriate
> > regression test.
>
> This PostgreSQL 9.6 open item is past due for your status update. Kindly send
> a status update within 24 hours, and include a date for your subsequent status
> update. Refer to the policy on open item ownership:
> http://www.postgresql.org/message-id/20160527025039.GA447393@tornado.leadboat.com
Attached is a patch to address this.
After much consideration and deliberation, I went with the simpler
solution to simply dump out the database privileges based on what a new
creation of those privileges would yield, resulting in output similar to
pre-9.6. We document that template1 is allowed to be dropped/recreated,
which greatly complicates using pg_init_privs to record and produce a
delta against the initdb-time values, as we lose the connection between
pg_init_privs and the "template1" database as soon as it is dropped
(something which can't be done with objects in that catalog).
Comments welcome.
Thanks!
Stephen
Attachment | Content-Type | Size |
---|---|---|
pg_dumpall_privs_v1.patch | text/x-diff | 30.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-07-12 20:20:22 | Re: GiST index build versus NaN coordinates |
Previous Message | Tom Lane | 2016-07-12 19:42:25 | Re: BUG #14245: Segfault on weird to_tsquery |