12 to 13 migration, the privs error with pg_pltemplate

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
25 messages Options
12
Reply | Threaded
Open this post in threaded view
|

12 to 13 migration, the privs error with pg_pltemplate

Scott Ribe-2
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
[hidden email]
https://www.linkedin.com/in/scottribe/





Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Scott Ribe <[hidden email]> writes:
> 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
> Suggestions?

There is probably an entry in pg_default_acl that is causing this.
You could likely just manually remove that.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

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

Scott Ribe-2
> On Dec 8, 2020, at 12:48 PM, Tom Lane <[hidden email]> 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 erroneous entries for namespace public, which I've deleted. But I don't have a lot of confidence about that being the problem.



Reply | Threaded
Open this post in threaded view
|

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

Scott Ribe-2
> On Dec 9, 2020, at 7:06 AM, Scott Ribe <[hidden email]> wrote:
>
>> On Dec 8, 2020, at 12:48 PM, Tom Lane <[hidden email]> 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 erroneous entries for namespace public, which I've deleted. But I don't have a lot of confidence about that being the problem.

Nope, deleting the ACLs for public didn't help. I'm going to start stripping things down--dropping schemas, or maybe the whole db--and see if it persists.

Any other suggestions? What could possibly be triggering this GRANT?





Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Scott Ribe <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Scott Ribe-2
> On Dec 9, 2020, at 10:19 AM, Tom Lane <[hidden email]> wrote:
>
> Scott Ribe <[hidden email]> 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.




Reply | Threaded
Open this post in threaded view
|

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

Stephen Frost
Greetings,

* Scott Ribe ([hidden email]) wrote:

> > On Dec 9, 2020, at 10:19 AM, Tom Lane <[hidden email]> wrote:
> > Scott Ribe <[hidden email]> 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Stephen Frost <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Stephen Frost
Greetings,

* Tom Lane ([hidden email]) wrote:
> Stephen Frost <[hidden email]> 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Scott Ribe-2
In reply to this post by Stephen Frost
> On Dec 9, 2020, at 11:49 AM, Stephen Frost <[hidden email]> 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.




Reply | Threaded
Open this post in threaded view
|

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

Stephen Frost
Greetings,

* Scott Ribe ([hidden email]) wrote:
> > On Dec 9, 2020, at 11:49 AM, Stephen Frost <[hidden email]> 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Scott Ribe-2

> On Dec 9, 2020, at 12:24 PM, Stephen Frost <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Stephen Frost
Greetings,

* Scott Ribe ([hidden email]) wrote:
> > On Dec 9, 2020, at 12:24 PM, Stephen Frost <[hidden email]> 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Scott Ribe-2
> On Dec 9, 2020, at 12:59 PM, Stephen Frost <[hidden email]> 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.



Reply | Threaded
Open this post in threaded view
|

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

Stephen Frost
Greetings,

* Scott Ribe ([hidden email]) wrote:
> > On Dec 9, 2020, at 12:59 PM, Stephen Frost <[hidden email]> 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Scott Ribe-2
Well, ALTER DEFAULT PRIVILEGES... got rid of all the references to the user in the pg_init_privs table, but the problem persists

Reply | Threaded
Open this post in threaded view
|

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

Scott Ribe-2
One more follow up:

plain dump does include the unwanted GRANT SELECT ON TABLE...


Reply | Threaded
Open this post in threaded view
|

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

Scott Ribe-2
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?

Reply | Threaded
Open this post in threaded view
|

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

Stephen Frost
Greetings,

* Scott Ribe ([hidden email]) wrote:
> 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:

Yes, I specifically asked if you were looking at the correct database
previously, because it matters:

* Stephen Frost ([hidden email]) 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 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?

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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Scott Ribe-2
> On Dec 11, 2020, at 1:36 PM, Stephen Frost <[hidden email]> 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 PRIVILEGES in 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 from the commonly-used service accounts.

12