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: Fri, 11 Dec 2020 15:36:34 -0500
Message-ID: <20201211203634.GB16415@tamriel.snowman.net> (raw)
In-Reply-To: <0D69A245-392A-4F61-AD45-C190204D1303@elevated-dev.com> <20201209195954.GF16415@tamriel.snowman.net>
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
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: <20201211203634.GB16415@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