On 2019/11/27 13:22, Michael Paquier wrote:
On Wed, Nov 27, 2019 at 11:35:14AM +0900, Artur Zakirov wrote:
Other approach is similar to Anastasia's patch, which is scanning pg_proc,
pg_class, pg_attribute and others to get modified ACL's and compare it with
initial ACL from pg_init_privs. Next step is to find objects which names or
signatures were changed using pg_describe_object() and scanning pg_depend of
new cluster
Yeah, the actual take is if we want to make the frontend code more
complicated with a large set of SQL queries to check that each object
ACL is modified, which adds an additional maintenance cost in
pg_upgrade. Or if we want to keep the frontend simple and have more
backend facility to ease cross-catalog lookups for ACLs. Perhaps we
could also live with just checking after the ACLs of functions in the
short term and perhaps it covers most of the cases users would care
about.. That's tricky to conclude about and I am not sure which path
is better in the long-term, but at least it's worth discussing all
possible candidates IMO so as we make sure to not miss anything.
I checked what objects changed their signatures between master and 9.6.
I just ran pg_describe_object() for grantable object types, saved the
output into a file and diffed the outputs. It seems that only functions
and table columns changed their signatures. A list of functions is big
and here the list of columns:
table pg_attrdef column adsrc
table pg_class column relhasoids
table pg_class column relhaspkey
table pg_constraint column consrc
table pg_proc column proisagg
table pg_proc column proiswindow
table pg_proc column protransform
As a result I think in pg_upgrade we could just check functions and
columns signatures. It might simplify the patch. And if something
changes in a future we could fix pg_upgrade too.
(there is a problem here though: there are no entries for
relations columns).
When it comes to column ACLs, pg_shdepend stores a dependency between
the column's relation and the role.
Thank you for the hint. pg_shdepend could be used in a patch.
--
Artur
