Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.4\))
Subject: Re: 12 to 13 migration, the privs error with pg_pltemplate
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: <9D3AE935-3CAA-4336-81F9-8AB8DFB61DF8@elevated-dev.com>
Date: Fri, 11 Dec 2020 13:20:15 -0700
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <0D69A245-392A-4F61-AD45-C190204D1303@elevated-dev.com>
References: <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>
 <20201209184925.GA16415@tamriel.snowman.net>
 <B292176F-B507-498F-975B-3548D73A8352@elevated-dev.com>
 <20201209192406.GD16415@tamriel.snowman.net>
 <F2A313AD-7C0F-43E8-9D60-6A6515409E3D@elevated-dev.com>
 <20201209195954.GF16415@tamriel.snowman.net>
 <6EA7B05C-A186-4288-9381-35E4219D39D7@elevated-dev.com>
 <20201209202614.GG16415@tamriel.snowman.net>
 <71ED8280-0FF7-4673-9C19-B4202E6EBDB3@elevated-dev.com>
 <9D3AE935-3CAA-4336-81F9-8AB8DFB61DF8@elevated-dev.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Precedence: bulk

OK, I found the (remaining) cause:

As noted previously, revoking privs on the pg_pltemplate table did not =
help. Per Tom Lane's suggestion, I looked at the pg_init_privs table, =
and did see grants that should not have been there. I ran the =
appropriate ALTER DEFAULT PRIVILEGES command, observed that the bogus =
values in pg_init_privs were cleaned up, and the problem was still not =
fixed.

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:

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D

postgres=3D# \dp pg_pltemplate
                           Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
=
--------+------+------+-------------------+-------------------+----------
(0 rows)

postgres=3D# select t2.relname, t1.initprivs, relacl, privtype from =
pg_init_privs t1 join pg_class t2 on (t1.objoid =3D t2.oid) where =
t2.relname =3D 'pg_pltemplate';
   relname    |                initprivs                |                =
 relacl                  | privtype
=
---------------+-----------------------------------------+----------------=
-------------------------+----------
pg_pltemplate | {postgres=3DarwdDxt/postgres,=3Dr/postgres} | =
{postgres=3DarwdDxt/postgres,=3Dr/postgres} | i
(1 row)

postgres=3D# \c risk_dev
psql (12.2, server 12.4)
You are now connected to database "risk_dev" as user "postgres".
risk_dev=3D# \dp pg_pltemplate
                           Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
=
--------+------+------+-------------------+-------------------+----------
(0 rows)

risk_dev=3D# select t2.relname, t1.initprivs, relacl, privtype from =
pg_init_privs t1 join pg_class t2 on (t1.objoid =3D t2.oid) where =
t2.relname =3D 'pg_pltemplate';
   relname    |                initprivs                |                =
             relacl                             | privtype
=
---------------+-----------------------------------------+----------------=
------------------------------------------------+----------
pg_pltemplate | {postgres=3DarwdDxt/postgres,=3Dr/postgres} | =
{postgres=3DarwdDxt/postgres,=3Dr/postgres,srv_risk_ro=3Dr/postgres} | i
(1 row)

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D

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?=