Date: Wed, 9 Dec 2020 13:49:25 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Scott Ribe <scott_ribe@elevated-dev.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: 12 to 13 migration, the privs error with pg_pltemplate
Message-ID: <20201209184925.GA16415@tamriel.snowman.net>
References: <074FE83A-E924-4787-9E68-27EB9CE142B3@elevated-dev.com>
 <3264512.1607456932@sss.pgh.pa.us>
 <154608A6-0F38-4BE8-B3F4-3E3308691F91@elevated-dev.com>
 <2BD26D52-19B8-42CD-860D-B9AE21AF5C61@elevated-dev.com>
 <3632844.1607534358@sss.pgh.pa.us>
 <68634B7E-3AD0-42A3-9A2D-EC3988E755E4@elevated-dev.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="Zivyk9DQtKhfvMLX"
Content-Disposition: inline
In-Reply-To: <68634B7E-3AD0-42A3-9A2D-EC3988E755E4@elevated-dev.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--Zivyk9DQtKhfvMLX
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Scott Ribe (scott_ribe@elevated-dev.com) wrote:
> > On Dec 9, 2020, at 10:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Scott Ribe <scott_ribe@elevated-dev.com> writes:
> >> Any other suggestions? What could possibly be triggering this GRANT?
> >=20
> > Ah, I'm sorry, I pointed you at the wrong catalog entirely.  It's
> > not pg_default_acl that controls this, it's pg_init_privs.  I believe
> > what pg_dump is doing is emitting GRANT commands that replicate
> > the difference between pg_pltemplate's current actual privileges and
> > what is shown for it in pg_init_privs.  So you need to make those
> > two things match, in whichever way is easiest.
>=20
> OK, now *THAT* turned up a lot of suspicious entries. It will be a bit be=
fore I can try straightening that out. But there's a lot of tables in pg_ca=
talog that have privs listed for the user in question.

Yes, if you GRANT'd privileges to system catalogs to a given role,
pg_dump is going to attempt to preserve those privleges for you.

There was work going on to try and address that the catalog tables may
change between versions to avoid emitting those, but I don't think it
ever ended up getting committed.  REVOKE'ing the privileges on the
catalog tables/columns that are causing an issue should resolve it
though.

(I'm generally not a fan of hacking around in the catalog tables
directly...)

Thanks,

Stephen

--Zivyk9DQtKhfvMLX
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJf0Rw1AAoJEO1sijiDR2RV19IQAJvmDOWiQOCNI2up+chSgm1b
u+8rgWcC7cq37UZLpAEn3HPl6Il7i6dWdSv5HzAI6WHpS3aZo5Bu4VZu1tKSco1u
C0aKxCWtRTzhLPC8BjpE+ehq8W9/q2WGdkL7NrNCTGKayf3BiDN44Z8V078nrsk7
BU9xB7+MyfgD2TNvPVCvRgStGNgCW4FxMHqMTPzLG9JpjqlTEhub+1ahJFZZRZ0u
iLw8t/NDccgMpsjK014jZZ91oG8lCsS1ksbOl8E6e2NESTv3HHZ6tbp0kqzrwzqm
QiRQiF2s0jyxHpFBJ3r1zFj3B1UFgO9awknZ7SdyIFaEIFDmMZ7lWwJb/DPJYPw9
Z07jwC0PwnqZu4cS5ynP9ylFwHQUyvoSvG3vQg6jrFe3rYKO71XWaa3BSP7IsfS+
6bhluNZvCqK0rjWAn/DRq00bp3XVaEtcZgRDaAosfG0d9CTvSqdF4TQDdd5sltME
rDddn81olODHweRz6Qms078VKLNYM/QraTD/pKh/EElDF0pyaC+jyoZYA2lAnHKI
ri1xUAWbNbJMQs8Iw7zFBWcoJ1464bafomF9HvAJbQrbB1CFw0Sy/Jq4cXMHOfpt
SoqzA2itn7eUvLctD6Wg3XYJj0j0NxHoBuw/MWKL49vy0rYB91LQ8YkPAPq4qpgn
aOYQpxesVVMuYVH4BiqQ
=39Je
-----END PGP SIGNATURE-----

--Zivyk9DQtKhfvMLX--