From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: 12 to 13 migration, the privs error with pg_pltemplate
Date: Fri, 11 Dec 2020 13:20:15 -0700
Message-ID: <0D69A245-392A-4F61-AD45-C190204D1303@elevated-dev.com> (raw)
In-Reply-To: <9D3AE935-3CAA-4336-81F9-8AB8DFB61DF8@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>
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:
============================
postgres=# \dp pg_pltemplate
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+------+-------------------+-------------------+----------
(0 rows)
postgres=# select t2.relname, t1.initprivs, relacl, privtype from pg_init_privs t1 join pg_class t2 on (t1.objoid = t2.oid) where t2.relname = 'pg_pltemplate';
relname | initprivs | relacl | privtype
---------------+-----------------------------------------+-----------------------------------------+----------
pg_pltemplate | {postgres=arwdDxt/postgres,=r/postgres} | {postgres=arwdDxt/postgres,=r/postgres} | i
(1 row)
postgres=# \c risk_dev
psql (12.2, server 12.4)
You are now connected to database "risk_dev" as user "postgres".
risk_dev=# \dp pg_pltemplate
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+------+-------------------+-------------------+----------
(0 rows)
risk_dev=# select t2.relname, t1.initprivs, relacl, privtype from pg_init_privs t1 join pg_class t2 on (t1.objoid = t2.oid) where t2.relname = 'pg_pltemplate';
relname | initprivs | relacl | privtype
---------------+-----------------------------------------+----------------------------------------------------------------+----------
pg_pltemplate | {postgres=arwdDxt/postgres,=r/postgres} | {postgres=arwdDxt/postgres,=r/postgres,srv_risk_ro=r/postgres} | i
(1 row)
============================
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, tgl@sss.pgh.pa.us, pgsql-admin@lists.postgresql.org
Subject: Re: 12 to 13 migration, the privs error with pg_pltemplate
In-Reply-To: <0D69A245-392A-4F61-AD45-C190204D1303@elevated-dev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox