From: | Scott Ribe <scott_ribe(at)elevated-dev(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: 12 to 13 migration, the privs error with pg_pltemplate |
Date: | 2020-12-11 20:20:15 |
Message-ID: | 0D69A245-392A-4F61-AD45-C190204D1303@elevated-dev.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2020-12-11 20:36:34 | Re: 12 to 13 migration, the privs error with pg_pltemplate |
Previous Message | MichaelDBA | 2020-12-11 16:23:59 | Re: How to gather transaction information for reporting in postgress |