Thread: [HACKERS] pg_upgrade loses security lables and COMMENTs on blobs
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
All, * Stephen Frost (sfrost@snowman.net) wrote: > Just wanted to get a note out to -hackers about the issue, I'll see > about getting a fix written up for it soon. Attached is a patch which addresses this issue. I'm not terribly pleased with it, but I also haven't got any great ideas of what else to do. Suggestions welcome, of course. Otherwise, I'll plan to start working on the back-branch changes for this soon. Thanks! Stephen
Attachment
On Thu, Feb 23, 2017 at 10:36:37AM -0500, Stephen Frost wrote: > All, > > * Stephen Frost (sfrost@snowman.net) wrote: > > Just wanted to get a note out to -hackers about the issue, I'll see > > about getting a fix written up for it soon. > > Attached is a patch which addresses this issue. I'm not terribly > pleased with it, but I also haven't got any great ideas of what else to > do. Suggestions welcome, of course. > > Otherwise, I'll plan to start working on the back-branch changes for > this soon. Yeah, this is probably the best you can do. Your analysis of how we used to treat large objects is correct, and was never adjusted for the changes you outlined. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Bruce, * Bruce Momjian (bruce@momjian.us) wrote: > On Thu, Feb 23, 2017 at 10:36:37AM -0500, Stephen Frost wrote: > > * Stephen Frost (sfrost@snowman.net) wrote: > > > Just wanted to get a note out to -hackers about the issue, I'll see > > > about getting a fix written up for it soon. > > > > Attached is a patch which addresses this issue. I'm not terribly > > pleased with it, but I also haven't got any great ideas of what else to > > do. Suggestions welcome, of course. > > > > Otherwise, I'll plan to start working on the back-branch changes for > > this soon. > > Yeah, this is probably the best you can do. Your analysis of how we > used to treat large objects is correct, and was never adjusted for the > changes you outlined. Great, thanks, I'll be pushing this to all the branches soon, still testing. Thanks again! Stephen