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

Greetings,

* Scott Ribe (scott_ribe(at)elevated-dev(dot)com) wrote:
> > On Dec 9, 2020, at 10:19 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Scott Ribe <scott_ribe(at)elevated-dev(dot)com> writes:
> >> Any other suggestions? What could possibly be triggering this GRANT?
> >
> > Ah, I'm sorry, I pointed you at the wrong catalog entirely. It's
> > not pg_default_acl that controls this, it's pg_init_privs. I believe
> > what pg_dump is doing is emitting GRANT commands that replicate
> > the difference between pg_pltemplate's current actual privileges and
> > what is shown for it in pg_init_privs. So you need to make those
> > two things match, in whichever way is easiest.
>
> OK, now *THAT* turned up a lot of suspicious entries. It will be a bit before I can try straightening that out. But there's a lot of tables in pg_catalog that have privs listed for the user in question.

Yes, if you GRANT'd privileges to system catalogs to a given role,
pg_dump is going to attempt to preserve those privleges for you.

There was work going on to try and address that the catalog tables may
change between versions to avoid emitting those, but I don't think it
ever ended up getting committed. REVOKE'ing the privileges on the
catalog tables/columns that are causing an issue should resolve it
though.

(I'm generally not a fan of hacking around in the catalog tables
directly...)

Thanks,

Stephen