pg_upgrade loses security lables and COMMENTs on blobs
Stephen Frost <sfrost@snowman.net>
From: Stephen Frost <sfrost@snowman.net>
To: pgsql-hackers@postgresql.org
Date: 2017-02-21T16:26:55Z
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
Commits
-
pg_upgrade: Fix large object COMMENTS, SECURITY LABELS
- e864cd25b4f5 9.2.21 landed
- 7f831f09bb85 9.3.17 landed
- 93598898c8d2 9.4.12 landed
- 6be8647f7862 9.5.7 landed
- 65a3f233b2cf 9.6.3 landed
- ff992c074e30 10.0 landed