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

5 files changed, 53 insertions, 3 deletions

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$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
        Defaults for run-time configuration variables
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>setuser</structfield> <type>bool[]</type>
+      </para>
+      <para>
+       Values of <link linkend="sql-alterrole-user-set"><literal>USER SET</literal></link>
+       flag for every setting in <structfield>setconfig</structfield>
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
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 <replaceable class="parameter">name</replaceable> SET TABLESPACE
 
 ALTER DATABASE <replaceable class="parameter">name</replaceable> REFRESH COLLATION VERSION
 
-ALTER DATABASE <replaceable class="parameter">name</replaceable> SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
+ALTER DATABASE <replaceable class="parameter">name</replaceable> SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | <replaceable>value</replaceable> USER SET | DEFAULT }
 ALTER DATABASE <replaceable class="parameter">name</replaceable> SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
 ALTER DATABASE <replaceable class="parameter">name</replaceable> RESET <replaceable>configuration_parameter</replaceable>
 ALTER DATABASE <replaceable class="parameter">name</replaceable> RESET ALL
@@ -206,6 +206,19 @@ ALTER DATABASE <replaceable class="parameter">name</replaceable> RESET ALL
        </para>
       </listitem>
      </varlistentry>
+
+     <varlistentry>
+      <term><literal>USER SET</literal></term>
+      <listitem>
+       <para>
+        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 <xref linkend="runtime-config-custom"/>. The variable won't
+        be set if it appears to require superuser privileges.
+       </para>
+      </listitem>
+     </varlistentry>
   </variablelist>
  </refsect1>
 
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 <replaceable class="parameter">role_specification</replaceable> [ WIT
 
 ALTER ROLE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
 
-ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
+ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | <replaceable>value</replaceable> USER SET | DEFAULT }
 ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
 ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] RESET <replaceable>configuration_parameter</replaceable>
 ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] RESET ALL
@@ -234,6 +234,19 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
        </para>
       </listitem>
      </varlistentry>
+
+     <varlistentry id="sql-alterrole-user-set">
+      <term><literal>USER SET</literal></term>
+      <listitem>
+       <para>
+        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 <xref linkend="runtime-config-custom"/>. The variable won't
+        be set if it appears to require superuser privileges.
+       </para>
+      </listitem>
+     </varlistentry>
     </variablelist>
  </refsect1>
 
@@ -330,6 +343,13 @@ ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
 <programlisting>
 ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
 </programlisting></para>
+
+  <para>
+   Give a role a non-default placeholder setting on behalf of ordinary user:
+
+<programlisting>
+ALTER ROLE fred SET my.param = 'value' USER SET;
+</programlisting></para>
  </refsect1>
 
  <refsect1>
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 <replaceable class="parameter">role_specification</replaceable> [ WIT
 
 ALTER USER <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
 
-ALTER USER { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
+ALTER USER { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | <replaceable>value</replaceable> USER SET | DEFAULT }
 ALTER USER { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
 ALTER USER { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] RESET <replaceable>configuration_parameter</replaceable>
 ALTER USER { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] 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.
         </para>
+
+        <para>
+        Since <productname>PostgreSQL</productname> 16 the output includes
+        column with the values of
+        <link linkend="sql-alterrole-user-set"><literal>USER SET</literal></link>
+        flag for each setting.
+        </para>
         </listitem>
       </varlistentry>