Re: Granting SET and ALTER SYSTE privileges for GUCs

> On Nov 16, 2021, at 8:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> My concern is not about performance, it's about the difficulty of
> maintaining a catalog that expects to be a more-or-less exhaustive
> list of GUCs.  I think you need to get rid of that expectation.

I'm preparing a new version of the patch that has the catalog empty to begin with, and only adds values in response to
GRANTcommands.  That also handles the issues of extension upgrades, which I think the old patch handled better than the
discussionon this thread suggested, but no matter.  The new behavior will allow granting privileges on non-existent
gucs,just as ALTER ROLE..SET allows registering settings on non-existent gucs. 

The reason I had resisted allowing grants of privileges on non-existent gucs is that you can get the following sort of
behavior(note the last two lines): 

  DROP USER regress_priv_user7;
  ERROR:  role "regress_priv_user7" cannot be dropped because some objects depend on it
  DETAIL:  privileges for table persons2
  privileges for configuration parameter sort_mem
  privileges for configuration parameter no_such_param

Rejecting "no_such_param" in the original GRANT statement seemed cleaner to me, but this discussion suggests pretty
stronglythat I can't do it that way.  Changing the historical "sort_mem" to the canonical "work_mem" name also seems
betterto me, as otherwise you could have different grants on the same GUC under different names.  I'm inclined to keep
thecanonicalization of names where known, but maybe that runs afoul the rule that these grants should not be tied very
hardto the GUC? 

> In the analogy to ALTER DATABASE/USER SET, we don't expect that
> pg_db_role_setting catalog entries will exist for all, or even
> very many, GUCs.  Also, the fact that pg_db_role_setting entries
> aren't tied very hard to the actual existence of a GUC is a good
> thing from the maintenance and upgrade standpoint.

Doing it that way....

> BTW, if we did create such a catalog, there would need to be
> pg_dump and pg_upgrade support for its entries, and you'd have to
> think about (e.g.) whether pg_upgrade would attempt to preserve
> the same OIDs.  I don't see any indication that the patch has
> addressed that infrastructure ... which is probably just as well,
> since it's work that I'd be wanting to reject.

Yeah, that's why I didn't write it.  I wanted feedback on the basic implementation before doing that work.

>  (Hm, but actually,
> doesn't pg_dump need work anyway to dump this new type of GRANT?)

Yes, if the idea of this kind of grant isn't being outright rejected, then I'll need to write that.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company