Re: dumping database privileges broken in 9.6

On Wed, Jul 06, 2016 at 07:03:33PM -0400, Stephen Frost wrote:
> * 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
>
> I've not forgotten about this and have an initial patch, but I'm
> considering if I like the way template0/template1 are handled.
> Specifically, we don't currently record their initdb-set privileges into
> pg_init_privs (unlike all other objects with initial privileges). This
> is complicated by the idea that template1 could be dropped/recreated
> (ending up with a different OID in the process).
>
> More to come tomorrow.

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