PostgreSQL 15 preview - 支持赋予SET或ALTER SYSTEM GUC参数权限给角色

原创 digoal 2022-01-20

427

作者

digoal

日期

2022-04-08

Allow granting SET and ALTER SYSTEM privileges on GUC parameters.  
author  Tom Lane <tgl@sss.pgh.pa.us>      
Wed, 6 Apr 2022 17:24:33 +0000 (13:24 -0400)  
committer   Tom Lane <tgl@sss.pgh.pa.us>      
Wed, 6 Apr 2022 17:24:33 +0000 (13:24 -0400)  
commit  a0ffa885e478f5eeacc4e250e35ce25a4740c487  
tree    7ce236305d5eb50f34bfccaf9a662cf3f0b77adf    tree  
parent  2ef6f11b0c77ec323c688ddfd98ffabddb72c11d    commit | diff  
Allow granting SET and ALTER SYSTEM privileges on GUC parameters.  
This patch allows "PGC_SUSET" parameters to be set by non-superusers  
if they have been explicitly granted the privilege to do so.  
The privilege to perform ALTER SYSTEM SET/RESET on a specific parameter  
can also be granted.  
Such privileges are cluster-wide, not per database.  They are tracked  
in a new shared catalog, pg_parameter_acl.  
Granting and revoking these new privileges works as one would expect.  
One caveat is that PGC_USERSET GUCs are unaffected by the SET privilege  
--- one could wish that those were handled by a revocable grant to  
PUBLIC, but they are not, because we couldn't make it robust enough  
for GUCs defined by extensions.  
Mark Dilger, reviewed at various times by Andrew Dunstan, Robert Haas,  
Joshua Brindle, and myself  
Discussion: https://postgr.es/m/3D691E20-C1D5-4B80-8BA5-6BEB63AF3029@enterprisedb.com

 122 GRANT SET ON PARAMETER zero_damaged_pages TO regress_host_resource_admin;  
 123 SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'SET');  
 124  has_parameter_privilege   
 125 -------------------------  
 126  t  
 127 (1 row)  
 128   
 129 SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'ALTER SYSTEM');  
 130  has_parameter_privilege   
 131 -------------------------  
 132  f  
 133 (1 row)  
 335 -- Grant privileges on parameters to the new non-superuser role  
 336 GRANT SET, ALTER SYSTEM ON PARAMETER  
 337     autovacuum_work_mem, hash_mem_multiplier, max_stack_depth,  
 338     shared_buffers, temp_file_limit, work_mem  
 339 TO regress_host_resource_admin;  
 340 -- Check the new role now has privilges on parameters  
 341 SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET, ALTER SYSTEM');  
 342  has_parameter_privilege   
 343 -------------------------  
 344  t  
 345 (1 row)  
 346   
 347 SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET');  
 348  has_parameter_privilege   
 349 -------------------------  
 350  t  
 351 (1 row)  
 352   
 353 SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'ALTER SYSTEM');  
 354  has_parameter_privilege   
 355 -------------------------  
 356  t  
 357 (1 row)  
 358   
 359 SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET WITH GRANT OPTION, ALTER SYSTEM WITH GRANT OPTION');  
 360  has_parameter_privilege   
 361 -------------------------  
 362  f  
 363 (1 row)