Re: 12 to 13 migration, the privs error with pg_pltemplate

Поиск
Список
Период
Сортировка
От Scott Ribe
Тема Re: 12 to 13 migration, the privs error with pg_pltemplate
Дата
Msg-id 0D69A245-392A-4F61-AD45-C190204D1303@elevated-dev.com
обсуждение исходный текст
Ответ на Re: 12 to 13 migration, the privs error with pg_pltemplate  (Scott Ribe <scott_ribe@elevated-dev.com>)
Ответы Re: 12 to 13 migration, the privs error with pg_pltemplate
Список 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_privstable, 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
fixedit. 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
defaultin a user db to set privs on a catalog db??? Is this even possible in normal PG commands, or am I looking at the
debrisof an ancient erroneous attempt to directly manipulate system catalogs? 


В списке pgsql-admin по дате отправления:

Предыдущее
От: MichaelDBA
Дата:
Сообщение: Re: How to gather transaction information for reporting in postgress
Следующее
От: Stephen Frost
Дата:
Сообщение: Re: 12 to 13 migration, the privs error with pg_pltemplate