Skip site navigation (1) Skip section navigation (2)

pg_upgrade loses security lables and COMMENTs on blobs

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: pg_upgrade loses security lables and COMMENTs on blobs
Date: 2017-02-21 16:26:55
Message-ID: 20170221162655.GE9812@tamriel.snowman.net (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-hackers
Greetings,

When pg_upgrade calls pg_dump, it passes in "--schema-only", which is
generally correct, except that this causes everything having to do with
large objects to be excluded.  That's still usually correct, because
pg_upgrade will simply copy the pg_largeobject and
pg_largeobject_metadata tables through to the new cluster as-is
(essentially treating them as if they were user tables).

Unfortunately, those tables aren't, actually, the only places that we
store information about large objects; the general-purpose tables like
pg_seclabel and pg_description can hold information about large objects
too.

What this means is that performing a pg_upgrade will result in any
security labels or comments on large objects being dropped.  This
seems to go back at least as far as 9.2, though I found it through the
pg_dump regression testing that I've been working on.

I haven't looked at trying to fix this yet, but I'm thinking the
approach to use will probably be to modify pg_dump to still call
getBlobs() when in binary-upgrade mode (regardless of the schema-only
flag) but then have dumpBlobs(), when in binary-upgrade mode, only
output the security labels and comments.  I hope that doesn't end up
causing some kind of chicken-and-egg problem..  Presumably the large
object tables are in place and correct before the dump is restored, so I
think this will work.

Just wanted to get a note out to -hackers about the issue, I'll see
about getting a fix written up for it soon.

Thanks!

Stephen

Responses

pgsql-hackers by date

Next:From: Fujii MasaoDate: 2017-02-21 16:52:20
Subject: Re: DROP SUBSCRIPTION and ROLLBACK
Previous:From: Tom LaneDate: 2017-02-21 15:18:25
Subject: Re: drop support for Python 2.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group