From: Stephen Frost <sfrost@snowman.net>
To: Scott Ribe <scott_ribe@elevated-dev.com>
Cc: 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: Wed, 9 Dec 2020 13:49:25 -0500
Message-ID: <20201209184925.GA16415@tamriel.snowman.net> (raw)
In-Reply-To: <68634B7E-3AD0-42A3-9A2D-EC3988E755E4@elevated-dev.com>
References: <074FE83A-E924-4787-9E68-27EB9CE142B3@elevated-dev.com>
<3264512.1607456932@sss.pgh.pa.us>
<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>
Greetings,
* Scott Ribe (scott_ribe@elevated-dev.com) wrote:
> > On Dec 9, 2020, at 10:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Scott Ribe <scott_ribe@elevated-dev.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
Attachments:
signature.asc (application/pgp-signature, 818B) download
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: sfrost@snowman.net, 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: <20201209184925.GA16415@tamriel.snowman.net>
* 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