Date: Fri, 11 Dec 2020 15:36:34 -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: <20201211203634.GB16415@tamriel.snowman.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="PcWOnlnLteg7ZPSe"
Content-Disposition: inline
In-Reply-To: <0D69A245-392A-4F61-AD45-C190204D1303@elevated-dev.com>
 <20201209195954.GF16415@tamriel.snowman.net>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


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

Greetings,

* Scott Ribe (scott_ribe@elevated-dev.com) wrote:
> However, I was in the database postgres when I did all of that. I needed to execute REVOKE in the target database. That fixed it. What is also interesting, is that psql's \dp command apparently always looks at the global privs:

Yes, I specifically asked if you were looking at the correct database
previously, because it matters:

* Stephen Frost (sfrost@snowman.net) wrote:
> Are you sure you're looking at the right database?  Do you see GRANT or
> REVOKE commands for that table when you run pg_dump by hand..?

[ ... ]

> Seems confusing--like one can create an entry in a db to set privs on a table in a different db, or one can create a default in a user db to set privs on a catalog db??? Is this even possible in normal PG commands, or am I looking at the debris of an ancient erroneous attempt to directly manipulate system catalogs?

The reality is that pg_class is a per-database catalog table and that's
where ACLs are stored, so you can easily end up with privileges
associated with a shared catalog table which are different in different
databases- just depends which database you're connected to when you
issue the GRANT commands.

I'm pretty sure none of this has anything to do with DEFAULT PRIVILEGES
as those only actually apply when a new table is created (and not from a
template database), and that's just never the case with any PG catalog
tables.

What might be useful to point out is that only a superuser can change
the privileges associated with PG catalog tables and that you really
should be careful who you grant superuser privileges to.

Thanks,

Stephen

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

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

iQIcBAEBCgAGBQJf09hRAAoJEO1sijiDR2RVHEQP/1zSUHQYNmuT15rQPLQA41pF
0oebAG+3z3TntIMisSRWTHd3xHgWROvdv4ifq/NpYYcQfHlBTZ6P+2x3QE6uZvBC
RA1IlVahIhXCCh7mYvAhpqKaLQv3FQGNiinn5Zt3y4WMkPRprpFscsu40r5Xgo52
84Ow8zWHz5ElgMluJsJ5OfbKxKFKLZrpw/A2yck95bhHu1+reYvXb8iGSEp0TuFw
Nb35Nj3q6MfxHbLm5/3kVbLr7c1CFAlZKG2/3d5srEt8HC58o8aOEvjYOtG6Hwwf
t6HdXzKFSgL7FbZmq2WNuKWj1KyycF3V9JWN0RBwT0biolUMuCZyUZ6muO2Pvq9I
Q+dmOZB8I2Lx++hyQojhW2Te5I7WZZLDhYQEbUTg/xePQkfBrB7Q6aS/NrdlAkZP
e+f06r0WRn284OaV92A3OLzy8blszBfLUzEIB6yZ+IHwOIxDAGjCIWW99b5h0MAp
Xo1SO0vg0Vd2UtH6uXOLSKuZTZ2x0L/cGyZoON3VyGqL8iw0/rg/5c4Vbl7BiFIr
xHDZhW+ApEr5oaZLzNS4bNRlTRtLPMt0Mz94GLzXPQHmGziHMA2qV36kgT+mSL0F
BqHLRbrRpW0KIZTpjtCUX8D6Z41qw++k4M000PIlO579fUupYnxX/2n5l+lzrJKJ
/RFLQNXNR5KJR6YsNry1
=k7Xu
-----END PGP SIGNATURE-----

--PcWOnlnLteg7ZPSe--