Thread: 12 to 13 migration, the privs error with pg_pltemplate
So I'm getting the error when migrating about not being able to GRANT SELECT on pg_catalog.pg_pltemplate, as referenced here: https://www.postgresql-archive.org/pg-upgrade-issue-upgrading-10-gt-13-td6156401.html BUT: - we have never intentionally modified privs on system tables - I tried REVOKE ALL FROM pg_catalog.pg_pltemplate ... in the source database, still got the same error - \dp pg_catalog.pg_pltemplate only shows: postgres=ardDxt/postgres+ =r/postgres Suggestions? -- Scott Ribe scott_ribe@elevated-dev.com https://www.linkedin.com/in/scottribe/
Scott Ribe <scott_ribe@elevated-dev.com> writes: > So I'm getting the error when migrating about not being able to GRANT SELECT on pg_catalog.pg_pltemplate, as referencedhere: > https://www.postgresql-archive.org/pg-upgrade-issue-upgrading-10-gt-13-td6156401.html > Suggestions? There is probably an entry in pg_default_acl that is causing this. You could likely just manually remove that. regards, tom lane
> On Dec 8, 2020, at 12:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > There is probably an entry in pg_default_acl that is causing this. > You could likely just manually remove that. I'll give it a go. But there's nothing in pg_default_acl with defaclnamespace matching oid of pg_catalog in pg_namespace. There were some erroneousentries for namespace public, which I've deleted. But I don't have a lot of confidence about that being the problem.
> On Dec 9, 2020, at 7:06 AM, Scott Ribe <scott_ribe@elevated-dev.com> wrote: > >> On Dec 8, 2020, at 12:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> There is probably an entry in pg_default_acl that is causing this. >> You could likely just manually remove that. > > I'll give it a go. > > But there's nothing in pg_default_acl with defaclnamespace matching oid of pg_catalog in pg_namespace. There were someerroneous entries for namespace public, which I've deleted. But I don't have a lot of confidence about that being theproblem. Nope, deleting the ACLs for public didn't help. I'm going to start stripping things down--dropping schemas, or maybe thewhole db--and see if it persists. Any other suggestions? What could possibly be triggering this GRANT?
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.
regards, tom lane
> 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'sa lot of tables in pg_catalog that have privs listed for the user in question.
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'sa 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
Attachment
Stephen Frost <sfrost@snowman.net> writes:
> (I'm generally not a fan of hacking around in the catalog tables
> directly...)
I'd avoid that too, except he's about to migrate off the instance
entirely, so fine points like whether dependencies are up-to-date
shouldn't cause too much problem.
regards, tom lane
Greetings, * Tom Lane (tgl@sss.pgh.pa.us) wrote: > Stephen Frost <sfrost@snowman.net> writes: > > (I'm generally not a fan of hacking around in the catalog tables > > directly...) > > I'd avoid that too, except he's about to migrate off the instance > entirely, so fine points like whether dependencies are up-to-date > shouldn't cause too much problem. A REVOKE of the privilege shouldn't cause any issues either and avoids hacking on the catalog. It's also simpler to un-do should they need to revert to the old system for whatever reason, and avoids making people feel comfortable with modifying the catalog tables directly. Thanks, Stephen
Attachment
> On Dec 9, 2020, at 11:49 AM, Stephen Frost <sfrost@snowman.net> wrote: > > REVOKE'ing the privileges on the > catalog tables/columns that are causing an issue should resolve it > though. I tried REVOKE ALL, no joy. Given where Tom pointed me, perhaps I need to give ALTER DEFAULT PRIVILEGES a try.
Greetings,
* Scott Ribe (scott_ribe@elevated-dev.com) wrote:
> > On Dec 9, 2020, at 11:49 AM, Stephen Frost <sfrost@snowman.net> wrote:
> > REVOKE'ing the privileges on the
> > catalog tables/columns that are causing an issue should resolve it
> > though.
>
> I tried REVOKE ALL, no joy. Given where Tom pointed me, perhaps I need to give ALTER DEFAULT PRIVILEGES a try.
Are you sure you have privileges to perform the REVOKE and that it
actually did something..? Check the results in psql using:
=> \dp pg_catalog.pg_pltemplate
(or whatever catalog table it is the GRANT's are being created for
in the pg_dump)
What you'd want is something like:
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
------------+---------------+-------+---------------------------+-------------------+----------
pg_catalog | pg_pltemplate | table | postgres=arwdDxt/postgres+| |
| | | =r/postgres | |
(1 row)
Thanks,
Stephen
Attachment
> On Dec 9, 2020, at 12:24 PM, Stephen Frost <sfrost@snowman.net> wrote: > > Are you sure you have privileges to perform the REVOKE and that it > actually did something..? Check the results in psql using: no errors, and your example exactly matches what I see there
Greetings, * Scott Ribe (scott_ribe@elevated-dev.com) wrote: > > On Dec 9, 2020, at 12:24 PM, Stephen Frost <sfrost@snowman.net> wrote: > > > > Are you sure you have privileges to perform the REVOKE and that it > > actually did something..? Check the results in psql using: > > no errors, and your example exactly matches what I see there Well, pg_dump here doesn't emit any GRANT commands for that table when run and the table has those privileges, so something doesn't add up. Are you sure you're looking at the right database? Do you see GRANT or REVOKE commands for that table when you run pg_dump by hand..? Thanks, Stephen
Attachment
> On Dec 9, 2020, at 12:59 PM, Stephen Frost <sfrost@snowman.net> wrote: > > Well, pg_dump here doesn't emit any GRANT commands for that table when > run and the table has those privileges, so something doesn't add up. > > Are you sure you're looking at the right database? Do you see GRANT or > REVOKE commands for that table when you run pg_dump by hand..? I'm pretty sure Tom Lane got it, because I see all sorts of privs in the pg_default_acl table that shouldn't be there, whereasprivs on the table are normal.
Greetings, * Scott Ribe (scott_ribe@elevated-dev.com) wrote: > > On Dec 9, 2020, at 12:59 PM, Stephen Frost <sfrost@snowman.net> wrote: > > > > Well, pg_dump here doesn't emit any GRANT commands for that table when > > run and the table has those privileges, so something doesn't add up. > > > > Are you sure you're looking at the right database? Do you see GRANT or > > REVOKE commands for that table when you run pg_dump by hand..? > > I'm pretty sure Tom Lane got it, because I see all sorts of privs in the pg_default_acl table that shouldn't be there,whereas privs on the table are normal. Well, we're all just shooting in the dark here since you didn't really provide enough specifics to actually be able to determine what's going on. Privileges in pg_default_acl aren't going to cause pg_dump to issue GRANT commands, which is what was in the initial problem description. Thanks, Stephen
Attachment
Well, ALTER DEFAULT PRIVILEGES... got rid of all the references to the user in the pg_init_privs table, but the problem persists
One more follow up: plain dump does include the unwanted GRANT SELECT ON TABLE...
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?
Greetings, * Scott Ribe (scott_ribe@elevated-dev.com) wrote: > However, I was in the database postgres when I did all of that. I needed to execute REVOKE in the target database. Thatfixed it. What is also interesting, is that psql's \dp command apparently always looks at the global privs: Yes, I specifically asked if you were looking at the correct database previously, because it matters: * Stephen Frost (sfrost@snowman.net) wrote: > Are you sure you're looking at the right database? Do you see GRANT or > REVOKE commands for that table when you run pg_dump by hand..? [ ... ] > 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? The reality is that pg_class is a per-database catalog table and that's where ACLs are stored, so you can easily end up with privileges associated with a shared catalog table which are different in different databases- just depends which database you're connected to when you issue the GRANT commands. I'm pretty sure none of this has anything to do with DEFAULT PRIVILEGES as those only actually apply when a new table is created (and not from a template database), and that's just never the case with any PG catalog tables. What might be useful to point out is that only a superuser can change the privileges associated with PG catalog tables and that you really should be careful who you grant superuser privileges to. Thanks, Stephen
Attachment
> On Dec 11, 2020, at 1:36 PM, Stephen Frost <sfrost@snowman.net> wrote: > > Yes, I specifically asked if you were looking at the correct database > previously, because it matters: At that time I thought I had run the original REVOKE command in the target database, and then tried ALTER DEFAULT PRIVILEGESin postgres. I was probably mistaken. > I'm pretty sure none of this has anything to do with DEFAULT PRIVILEGES > as those only actually apply when a new table is created (and not from a > template database), and that's just never the case with any PG catalog > tables. So the fact that default privs were set on the system catalogs was inappropriate, but harmless in this case? > What might be useful to point out is that only a superuser can change > the privileges associated with PG catalog tables and that you really > should be careful who you grant superuser privileges to. Yes, that's one thing I took care of earlier this year: change our processes such that we were able to remove superuser fromthe commonly-used service accounts.
Greetings, * Scott Ribe (scott_ribe@elevated-dev.com) wrote: > > On Dec 11, 2020, at 1:36 PM, Stephen Frost <sfrost@snowman.net> wrote: > > I'm pretty sure none of this has anything to do with DEFAULT PRIVILEGES > > as those only actually apply when a new table is created (and not from a > > template database), and that's just never the case with any PG catalog > > tables. > > So the fact that default privs were set on the system catalogs was inappropriate, but harmless in this case? Almost certainly. > > What might be useful to point out is that only a superuser can change > > the privileges associated with PG catalog tables and that you really > > should be careful who you grant superuser privileges to. > > Yes, that's one thing I took care of earlier this year: change our processes such that we were able to remove superuserfrom the commonly-used service accounts. ... and hopefully from most every other account. There's really very little need to have actual superuser rights (something we continue to work to limit the need of with each release). Thanks, Stephen
Attachment
> On Dec 11, 2020, at 2:10 PM, Stephen Frost <sfrost@snowman.net> wrote: > > ... and hopefully from most every other account. There's really very > little need to have actual superuser rights (something we continue to > work to limit the need of with each release). Yes, there's only 2 left: one for actual use, and of course postgres in case we screw up and disable that one.
On Wed, Dec 9, 2020 at 12:19:18PM -0500, Tom Lane 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. Should pg_dump or pg_upgrade be detecting and reporting these things, rather than requiring this analysis by the user? -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Bruce Momjian <bruce@momjian.us> writes:
> On Wed, Dec 9, 2020 at 12:19:18PM -0500, Tom Lane wrote:
>> 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.
> Should pg_dump or pg_upgrade be detecting and reporting these things,
> rather than requiring this analysis by the user?
It's a little premature to be considering that when we still haven't
identified exactly what the problem is.
regards, tom lane
Greetings, * Tom Lane (tgl@sss.pgh.pa.us) wrote: > Bruce Momjian <bruce@momjian.us> writes: > > On Wed, Dec 9, 2020 at 12:19:18PM -0500, Tom Lane wrote: > >> 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. > > > Should pg_dump or pg_upgrade be detecting and reporting these things, > > rather than requiring this analysis by the user? > > It's a little premature to be considering that when we still haven't > identified exactly what the problem is. Pretty sure we did get to the bottom of it, the OP was just in the wrong database. Anastasia, as I recall, had a patch that worked to avoid dumping out privileges and such for things which disappeared but I don't think anyone ended up finding time to review and commit it. I do think that, in general, that was a good effort though and it'd be nice to get it (or something like it) committed to avoid having this issue for users who have GRANT'd rights to pg_catalog tables that are later changed between versions. Thanks, Stephen