Re: security labels on databases are bad for dump & restore

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Sun, Jul 26, 2015 at 11:43 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> > On 20 July 2015 at 01:18, Noah Misch <noah(at)leadboat(dot)com> wrote:
> >> On Wed, Jul 15, 2015 at 11:08:53AM +0200, Andres Freund wrote:
> >>> On 2015-07-15 12:04:40 +0300, Alvaro Herrera wrote:
> >>> > Andres Freund wrote:
> >>> > > One thing worth mentioning is that arguably the problem is caused by the
> >>> > > fact that we're here emitting database level information in pg_dump,
> >>> > > normally only done for dumpall.
> >>
> >> Consistency with existing practice would indeed have pg_dump ignore
> >> pg_shseclabel and have pg_dumpall reproduce its entries.
> >
> > Existing practice is pretty broken though, and not necessarily a good guide.
> >
> > COMMENT ON DATABASE and SECURITY LABEL FOR DATABASE are dumped by
> > pg_dump, but always refer to the database's name at the time it was
> > dumped, so restoring it can break.
> >
> > GRANTs on databases are ignored and not dumped by pg_dump or by
> > pg_dumpall --globals-only. The only way to dump them seems to be to
> > use pg_dumpall, which nobody uses in the real world.
> >
> > I'd be strongly in favour of teaching GRANT, SECURITY LABEL, COMMENT
> > ON DATABASE, etc to recognise CURRENT_DATABASE as a keyword. Then
> > dumping them in pg_dump --create, and in pg_dump -Fc .
> >
> > In practice I see zero real use of pg_dumpall without --globals-only,
> > and almost everyone does pg_dump -Fc . I'd like to see that method
> > case actually preserve the whole state of the system and do the right
> > thing sensibly.
> >
> > A pg_restore option to skip database-level settings could be useful,
> > but I think by default they should be restored.
>
> Yes, I think we should make restoring the database's properties the
> job of pg_dump and remove it completely from pg_dumpall, unless we can
> find a case where that's really going to break things.

I believe that means, as discussed, that we'll need to support
"CURRENT_DATABASE" or similar for all database properties, but that
seems like a wholly good thing to do anyway, provided we can do so
without causing problems.

In other words, I agree.

Thanks!

Stephen