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

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.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services