From 096dd80f3ccc103c8e078fca05e6ccfb2071aa91 Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Fri, 9 Dec 2022 13:12:20 +0300 Subject: Add USER SET parameter values for pg_db_role_setting The USER SET flag specifies that the variable should be set on behalf of an ordinary role. That lets ordinary roles set placeholder variables, which permission requirements are not known yet. Such a value wouldn't be used if the variable finally appear to require superuser privileges. The new flags are stored in the pg_db_role_setting.setuser array. Catversion is bumped. This commit is inspired by the previous work by Steve Chavez. Discussion: https://postgr.es/m/CAPpHfdsLd6E--epnGqXENqLP6dLwuNZrPMcNYb3wJ87WR7UBOQ%40mail.gmail.com Author: Alexander Korotkov, Steve Chavez Reviewed-by: Pavel Borisov, Steve Chavez --- doc/src/sgml/catalogs.sgml | 10 ++++++++++ doc/src/sgml/ref/alter_database.sgml | 15 ++++++++++++++- doc/src/sgml/ref/alter_role.sgml | 22 +++++++++++++++++++++- doc/src/sgml/ref/alter_user.sgml | 2 +- doc/src/sgml/ref/psql-ref.sgml | 7 +++++++ 5 files changed, 53 insertions(+), 3 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 9ed2b020b7d..9316b811ac3 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -3194,6 +3194,16 @@ SCRAM-SHA-256$<iteration count>:&l Defaults for run-time configuration variables + + + + setuser bool[] + + + Values of USER SET + flag for every setting in setconfig + + diff --git a/doc/src/sgml/ref/alter_database.sgml b/doc/src/sgml/ref/alter_database.sgml index 89ed261b4c2..181e9d36205 100644 --- a/doc/src/sgml/ref/alter_database.sgml +++ b/doc/src/sgml/ref/alter_database.sgml @@ -37,7 +37,7 @@ ALTER DATABASE name SET TABLESPACE ALTER DATABASE name REFRESH COLLATION VERSION -ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT } +ALTER DATABASE name SET configuration_parameter { TO | = } { value | value USER SET | DEFAULT } ALTER DATABASE name SET configuration_parameter FROM CURRENT ALTER DATABASE name RESET configuration_parameter ALTER DATABASE name RESET ALL @@ -206,6 +206,19 @@ ALTER DATABASE name RESET ALL + + + USER SET + + + Specifies that variable should be set on behalf of ordinary role. + That lets non-superuser and non-replication role to set placeholder + variables, with permission requirements is not known yet; + see . The variable won't + be set if it appears to require superuser privileges. + + + diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml index 5aa5648ae7b..33ac7327070 100644 --- a/doc/src/sgml/ref/alter_role.sgml +++ b/doc/src/sgml/ref/alter_role.sgml @@ -38,7 +38,7 @@ ALTER ROLE role_specification [ WIT ALTER ROLE name RENAME TO new_name -ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT } +ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | value USER SET | DEFAULT } ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL @@ -234,6 +234,19 @@ ALTER ROLE { role_specification | A + + + USER SET + + + Specifies that variable should be set on behalf of ordinary role. + That lets non-superuser and non-replication role to set placeholder + variables, with permission requirements is not known yet; + see . The variable won't + be set if it appears to require superuser privileges. + + + @@ -329,6 +342,13 @@ ALTER ROLE worker_bee SET maintenance_work_mem = 100000; ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG; + + + + Give a role a non-default placeholder setting on behalf of ordinary user: + + +ALTER ROLE fred SET my.param = 'value' USER SET; diff --git a/doc/src/sgml/ref/alter_user.sgml b/doc/src/sgml/ref/alter_user.sgml index 0ee89f54c5c..24f737d5870 100644 --- a/doc/src/sgml/ref/alter_user.sgml +++ b/doc/src/sgml/ref/alter_user.sgml @@ -38,7 +38,7 @@ ALTER USER role_specification [ WIT ALTER USER name RENAME TO new_name -ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT } +ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | value USER SET | DEFAULT } ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index d3dd638b148..8a5285da9aa 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1897,6 +1897,13 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g commands are used to define per-role and per-database configuration settings. + + + Since PostgreSQL 16 the output includes + column with the values of + USER SET + flag for each setting. + -- cgit v1.2.3