pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed12 to 13 migration, the privs error with pg_pltemplate
25+ messages / 4 participants
[nested] [flat]
* 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-08 18:55 Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Ribe @ 2020-12-08 18:55 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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/
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-08 19:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 25+ messages in thread
From: Tom Lane @ 2020-12-08 19:48 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-09 14:06 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Ribe @ 2020-12-09 14:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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 erroneous entries for namespace public, which I've deleted. But I don't have a lot of confidence about that being the problem.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-09 16:59 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Ribe @ 2020-12-09 16:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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 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?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-09 17:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 2 replies; 25+ messages in thread
From: Tom Lane @ 2020-12-09 17:19 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-09 17:33 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 25+ messages in thread
From: Scott Ribe @ 2020-12-09 17:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-09 18:49 Stephen Frost <sfrost@snowman.net>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 2 replies; 25+ messages in thread
From: Stephen Frost @ 2020-12-09 18:49 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-09 19:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 1 reply; 25+ messages in thread
From: Tom Lane @ 2020-12-09 19:02 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-09 19:04 Stephen Frost <sfrost@snowman.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 25+ messages in thread
From: Stephen Frost @ 2020-12-09 19:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-09 19:20 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 1 reply; 25+ messages in thread
From: Scott Ribe @ 2020-12-09 19:20 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-09 19:24 Stephen Frost <sfrost@snowman.net>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 25+ messages in thread
From: Stephen Frost @ 2020-12-09 19:24 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-09 19:42 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Ribe @ 2020-12-09 19:42 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-09 19:59 Stephen Frost <sfrost@snowman.net>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 2 replies; 25+ messages in thread
From: Stephen Frost @ 2020-12-09 19:59 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-09 20:19 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 1 reply; 25+ messages in thread
From: Scott Ribe @ 2020-12-09 20:19 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-09 20:26 Stephen Frost <sfrost@snowman.net>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 25+ messages in thread
From: Stephen Frost @ 2020-12-09 20:26 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-10 04:47 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Ribe @ 2020-12-10 04:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Well, ALTER DEFAULT PRIVILEGES... got rid of all the references to the user in the pg_init_privs table, but the problem persists
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-10 15:57 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Ribe @ 2020-12-10 15:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
One more follow up:
plain dump does include the unwanted GRANT SELECT ON TABLE...
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-11 20:20 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 25+ messages in thread
From: Scott Ribe @ 2020-12-11 20:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-11 20:36 Stephen Frost <sfrost@snowman.net>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 1 reply; 25+ messages in thread
From: Stephen Frost @ 2020-12-11 20:36 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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. 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 (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 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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-11 21:04 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 25+ messages in thread
From: Scott Ribe @ 2020-12-11 21:04 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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 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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-11 21:10 Stephen Frost <sfrost@snowman.net>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 25+ messages in thread
From: Stephen Frost @ 2020-12-11 21:10 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 superuser from 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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-11 21:11 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 0 replies; 25+ messages in thread
From: Scott Ribe @ 2020-12-11 21:11 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-17 18:40 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 25+ messages in thread
From: Bruce Momjian @ 2020-12-17 18:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-17 18:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 25+ messages in thread
From: Tom Lane @ 2020-12-17 18:50 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: 12 to 13 migration, the privs error with pg_pltemplate
@ 2020-12-18 16:19 Stephen Frost <sfrost@snowman.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 25+ messages in thread
From: Stephen Frost @ 2020-12-18 16:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <bruce@momjian.us>; Scott Ribe <scott_ribe@elevated-dev.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2020-12-18 16:19 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-08 18:55 12 to 13 migration, the privs error with pg_pltemplate Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-08 19:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-12-09 14:06 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-09 16:59 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-09 17:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-12-09 17:33 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-09 18:49 ` Stephen Frost <sfrost@snowman.net>
2020-12-09 19:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-12-09 19:04 ` Stephen Frost <sfrost@snowman.net>
2020-12-09 19:20 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-09 19:24 ` Stephen Frost <sfrost@snowman.net>
2020-12-09 19:42 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-09 19:59 ` Stephen Frost <sfrost@snowman.net>
2020-12-09 20:19 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-09 20:26 ` Stephen Frost <sfrost@snowman.net>
2020-12-10 04:47 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-10 15:57 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-11 20:20 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-11 20:36 ` Stephen Frost <sfrost@snowman.net>
2020-12-11 21:04 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-11 21:10 ` Stephen Frost <sfrost@snowman.net>
2020-12-11 21:11 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-17 18:40 ` Bruce Momjian <bruce@momjian.us>
2020-12-17 18:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-12-18 16:19 ` Stephen Frost <sfrost@snowman.net>
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