Date: Tue, 21 Feb 2017 11:26:55 -0500
From: Stephen Frost <sfrost@snowman.net>
To: pgsql-hackers@postgresql.org
Subject: pg_upgrade loses security lables and COMMENTs on blobs
Message-ID: <20170221162655.GE9812@tamriel.snowman.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="N8uhgasHSXhV9xCN"
Content-Disposition: inline
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--N8uhgasHSXhV9xCN
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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

--N8uhgasHSXhV9xCN
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJYrGpPAAoJEO1sijiDR2RVKYEP/2wnZai6wwxOm2F8jkJAxj3b
KRpFudV1lmr/tcdDtBzIyJEmlFXpogB0df/L7mT05ssFoKccuov7SUpaYlJ+iS+a
wEkJLZ9/3XINwsPKS9Exs63e5g5OGrquVHC4nzcPiFhWfmREzlFJUy02rn014EXy
WPOtOt9wDeGr/VFgxCra2/3UUt6o6PB++7jPK0WPHbl/6Bqb63tM/hYB6pR3n6hk
YKeUeeL729sJmf1o0R6mdYAiryDCnX3QpslEB1GtJaHPXPCwPYXW0/miUJMnCFMH
PUIxAmh4WyVwy8Wylx1lg1CCN/L6dRakaxKHnftpq7wl1XXWaWM/kL8rEROADplR
w4edwn9LPZw0KgTCxHVx7N7rP1nT1a6lFJac5iqwW0oZbTujwwT53ehw6N9Cdknu
ouoMNWWMOiYffNqCqspalcBFcE772huIHwFY2OI6u5MqsbCCp4fTu4pGqnPv82oH
/yk5lRgvz4e3C4JQUq2cUwy0Dtbghs7QkLhajOThK1M+2hNUx72dvecn2DrS/1vC
T1J/w4aRvhXxQv1rvE1cYZBrL9JNGZC8USs49XQLIgywqCOeDpYm+I5W4hPLzaIN
ZEdz8/p4difV2vg7RTVYSkiIPj0ayXqYAXXeGb7RrXxCHzoO0j3pljzSTVg1LKpz
R9yc76dXjDkDd8e0ywU6
=KKAd
-----END PGP SIGNATURE-----

--N8uhgasHSXhV9xCN--