Skip to content

Commit 096dd80

Browse files
akorotkovakorotkov
committed
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
1 parent 5defdef commit 096dd80

34 files changed

+680
-51
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3194,6 +3194,16 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
31943194
Defaults for run-time configuration variables
31953195
</para></entry>
31963196
</row>
3197+
3198+
<row>
3199+
<entry role="catalog_table_entry"><para role="column_definition">
3200+
<structfield>setuser</structfield> <type>bool[]</type>
3201+
</para>
3202+
<para>
3203+
Values of <link linkend="sql-alterrole-user-set"><literal>USER SET</literal></link>
3204+
flag for every setting in <structfield>setconfig</structfield>
3205+
</para></entry>
3206+
</row>
31973207
</tbody>
31983208
</tgroup>
31993209
</table>

‎doc/src/sgml/ref/alter_database.sgml

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@ ALTER DATABASE <replaceable class="parameter">name</replaceable> SET TABLESPACE
3737

3838
ALTER DATABASE <replaceable class="parameter">name</replaceable> REFRESH COLLATION VERSION
3939

40-
ALTER DATABASE <replaceable class="parameter">name</replaceable> SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
40+
ALTER DATABASE <replaceable class="parameter">name</replaceable> SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | <replaceable>value</replaceable> USER SET | DEFAULT }
4141
ALTER DATABASE <replaceable class="parameter">name</replaceable> SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
4242
ALTER DATABASE <replaceable class="parameter">name</replaceable> RESET <replaceable>configuration_parameter</replaceable>
4343
ALTER DATABASE <replaceable class="parameter">name</replaceable> RESET ALL
@@ -206,6 +206,19 @@ ALTER DATABASE <replaceable class="parameter">name</replaceable> RESET ALL
206206
</para>
207207
</listitem>
208208
</varlistentry>
209+
210+
<varlistentry>
211+
<term><literal>USER SET</literal></term>
212+
<listitem>
213+
<para>
214+
Specifies that variable should be set on behalf of ordinary role.
215+
That lets non-superuser and non-replication role to set placeholder
216+
variables, with permission requirements is not known yet;
217+
see <xref linkend="runtime-config-custom"/>. The variable won't
218+
be set if it appears to require superuser privileges.
219+
</para>
220+
</listitem>
221+
</varlistentry>
209222
</variablelist>
210223
</refsect1>
211224

‎doc/src/sgml/ref/alter_role.sgml

Lines changed: 21 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -38,7 +38,7 @@ ALTER ROLE <replaceable class="parameter">role_specification</replaceable> [ WIT
3838

3939
ALTER ROLE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
4040

41-
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 }
41+
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 }
4242
ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
4343
ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] RESET <replaceable>configuration_parameter</replaceable>
4444
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
234234
</para>
235235
</listitem>
236236
</varlistentry>
237+
238+
<varlistentry id="sql-alterrole-user-set">
239+
<term><literal>USER SET</literal></term>
240+
<listitem>
241+
<para>
242+
Specifies that variable should be set on behalf of ordinary role.
243+
That lets non-superuser and non-replication role to set placeholder
244+
variables, with permission requirements is not known yet;
245+
see <xref linkend="runtime-config-custom"/>. The variable won't
246+
be set if it appears to require superuser privileges.
247+
</para>
248+
</listitem>
249+
</varlistentry>
237250
</variablelist>
238251
</refsect1>
239252

@@ -329,6 +342,13 @@ ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
329342

330343
<programlisting>
331344
ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
345+
</programlisting></para>
346+
347+
<para>
348+
Give a role a non-default placeholder setting on behalf of ordinary user:
349+
350+
<programlisting>
351+
ALTER ROLE fred SET my.param = 'value' USER SET;
332352
</programlisting></para>
333353
</refsect1>
334354

‎doc/src/sgml/ref/alter_user.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -38,7 +38,7 @@ ALTER USER <replaceable class="parameter">role_specification</replaceable> [ WIT
3838

3939
ALTER USER <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
4040

41-
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 }
41+
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 }
4242
ALTER USER { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
4343
ALTER USER { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] RESET <replaceable>configuration_parameter</replaceable>
4444
ALTER USER { <replaceable class="parameter">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="parameter">database_name</replaceable> ] RESET ALL

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1897,6 +1897,13 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
18971897
commands are used to define per-role and per-database configuration
18981898
settings.
18991899
</para>
1900+
1901+
<para>
1902+
Since <productname>PostgreSQL</productname> 16 the output includes
1903+
column with the values of
1904+
<link linkend="sql-alterrole-user-set"><literal>USER SET</literal></link>
1905+
flag for each setting.
1906+
</para>
19001907
</listitem>
19011908
</varlistentry>
19021909

‎src/backend/catalog/pg_db_role_setting.c

Lines changed: 39 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -63,14 +63,23 @@ AlterSetting(Oid databaseid, Oid roleid, VariableSetStmt *setstmt)
6363
if (HeapTupleIsValid(tuple))
6464
{
6565
ArrayType *new = NULL;
66+
ArrayType *usersetArray;
6667
Datum datum;
68+
Datum usersetDatum;
6769
bool isnull;
70+
bool usersetIsnull;
6871

6972
datum = heap_getattr(tuple, Anum_pg_db_role_setting_setconfig,
7073
RelationGetDescr(rel), &isnull);
74+
usersetDatum = heap_getattr(tuple, Anum_pg_db_role_setting_setuser,
75+
RelationGetDescr(rel), &usersetIsnull);
7176

7277
if (!isnull)
73-
new = GUCArrayReset(DatumGetArrayTypeP(datum));
78+
{
79+
Assert(!usersetIsnull);
80+
usersetArray = DatumGetArrayTypeP(usersetDatum);
81+
new = GUCArrayReset(DatumGetArrayTypeP(datum), &usersetArray);
82+
}
7483

7584
if (new)
7685
{
@@ -86,6 +95,11 @@ AlterSetting(Oid databaseid, Oid roleid, VariableSetStmt *setstmt)
8695
repl_repl[Anum_pg_db_role_setting_setconfig - 1] = true;
8796
repl_null[Anum_pg_db_role_setting_setconfig - 1] = false;
8897

98+
repl_val[Anum_pg_db_role_setting_setuser - 1] =
99+
PointerGetDatum(usersetArray);
100+
repl_repl[Anum_pg_db_role_setting_setuser - 1] = true;
101+
repl_null[Anum_pg_db_role_setting_setuser - 1] = false;
102+
89103
newtuple = heap_modify_tuple(tuple, RelationGetDescr(rel),
90104
repl_val, repl_null, repl_repl);
91105
CatalogTupleUpdate(rel, &tuple->t_self, newtuple);
@@ -101,28 +115,39 @@ AlterSetting(Oid databaseid, Oid roleid, VariableSetStmt *setstmt)
101115
bool repl_repl[Natts_pg_db_role_setting];
102116
HeapTuple newtuple;
103117
Datum datum;
118+
Datum usersetDatum;
104119
bool isnull;
120+
bool usersetIsnull;
105121
ArrayType *a;
122+
ArrayType *usersetArray;
106123

107124
memset(repl_repl, false, sizeof(repl_repl));
108125
repl_repl[Anum_pg_db_role_setting_setconfig - 1] = true;
109126
repl_null[Anum_pg_db_role_setting_setconfig - 1] = false;
127+
repl_repl[Anum_pg_db_role_setting_setuser - 1] = true;
128+
repl_null[Anum_pg_db_role_setting_setuser - 1] = false;
110129

111-
/* Extract old value of setconfig */
130+
/* Extract old values of setconfig and setuser */
112131
datum = heap_getattr(tuple, Anum_pg_db_role_setting_setconfig,
113132
RelationGetDescr(rel), &isnull);
114133
a = isnull ? NULL : DatumGetArrayTypeP(datum);
115134

135+
usersetDatum = heap_getattr(tuple, Anum_pg_db_role_setting_setuser,
136+
RelationGetDescr(rel), &usersetIsnull);
137+
usersetArray = usersetIsnull ? NULL : DatumGetArrayTypeP(usersetDatum);
138+
116139
/* Update (valuestr is NULL in RESET cases) */
117140
if (valuestr)
118-
a = GUCArrayAdd(a, setstmt->name, valuestr);
141+
a = GUCArrayAdd(a, &usersetArray, setstmt->name, valuestr, setstmt->user_set);
119142
else
120-
a = GUCArrayDelete(a, setstmt->name);
143+
a = GUCArrayDelete(a, &usersetArray, setstmt->name);
121144

122145
if (a)
123146
{
124147
repl_val[Anum_pg_db_role_setting_setconfig - 1] =
125148
PointerGetDatum(a);
149+
repl_val[Anum_pg_db_role_setting_setuser - 1] =
150+
PointerGetDatum(usersetArray);
126151

127152
newtuple = heap_modify_tuple(tuple, RelationGetDescr(rel),
128153
repl_val, repl_null, repl_repl);
@@ -137,16 +162,18 @@ AlterSetting(Oid databaseid, Oid roleid, VariableSetStmt *setstmt)
137162
HeapTuple newtuple;
138163
Datum values[Natts_pg_db_role_setting];
139164
bool nulls[Natts_pg_db_role_setting];
140-
ArrayType *a;
165+
ArrayType *a,
166+
*usersetArray;
141167

142168
memset(nulls, false, sizeof(nulls));
143169

144-
a = GUCArrayAdd(NULL, setstmt->name, valuestr);
170+
a = GUCArrayAdd(NULL, &usersetArray, setstmt->name, valuestr, setstmt->user_set);
145171

146172
values[Anum_pg_db_role_setting_setdatabase - 1] =
147173
ObjectIdGetDatum(databaseid);
148174
values[Anum_pg_db_role_setting_setrole - 1] = ObjectIdGetDatum(roleid);
149175
values[Anum_pg_db_role_setting_setconfig - 1] = PointerGetDatum(a);
176+
values[Anum_pg_db_role_setting_setuser - 1] = PointerGetDatum(usersetArray);
150177
newtuple = heap_form_tuple(RelationGetDescr(rel), values, nulls);
151178

152179
CatalogTupleInsert(rel, newtuple);
@@ -240,20 +267,25 @@ ApplySetting(Snapshot snapshot, Oid databaseid, Oid roleid,
240267
while (HeapTupleIsValid(tup = systable_getnext(scan)))
241268
{
242269
bool isnull;
270+
bool usersetIsnull;
243271
Datum datum;
272+
Datum usersetDatum;
244273

245274
datum = heap_getattr(tup, Anum_pg_db_role_setting_setconfig,
246275
RelationGetDescr(relsetting), &isnull);
276+
usersetDatum = heap_getattr(tup, Anum_pg_db_role_setting_setuser,
277+
RelationGetDescr(relsetting), &usersetIsnull);
247278
if (!isnull)
248279
{
249280
ArrayType *a = DatumGetArrayTypeP(datum);
281+
ArrayType *usersetArray = DatumGetArrayTypeP(usersetDatum);
250282

251283
/*
252284
* We process all the options at SUSET level. We assume that the
253285
* right to insert an option into pg_db_role_setting was checked
254286
* when it was inserted.
255287
*/
256-
ProcessGUCArray(a, PGC_SUSET, source, GUC_ACTION_SET);
288+
ProcessGUCArray(a, usersetArray, PGC_SUSET, source, GUC_ACTION_SET);
257289
}
258290
}
259291

‎src/backend/catalog/pg_proc.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -698,6 +698,7 @@ ProcedureCreate(const char *procedureName,
698698
{
699699
save_nestlevel = NewGUCNestLevel();
700700
ProcessGUCArray(set_items,
701+
NULL,
701702
(superuser() ? PGC_SUSET : PGC_USERSET),
702703
PGC_S_SESSION,
703704
GUC_ACTION_SAVE);

‎src/backend/commands/functioncmds.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -662,9 +662,9 @@ update_proconfig_value(ArrayType *a, List *set_items)
662662
char *valuestr = ExtractSetVariableArgs(sstmt);
663663

664664
if (valuestr)
665-
a = GUCArrayAdd(a, sstmt->name, valuestr);
665+
a = GUCArrayAdd(a, NULL, sstmt->name, valuestr, sstmt->user_set);
666666
else /* RESET */
667-
a = GUCArrayDelete(a, sstmt->name);
667+
a = GUCArrayDelete(a, NULL, sstmt->name);
668668
}
669669
}
670670

‎src/backend/parser/gram.y

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1621,6 +1621,26 @@ generic_set:
16211621
n->args = $3;
16221622
$$ = n;
16231623
}
1624+
| var_name TO var_list USER SET
1625+
{
1626+
VariableSetStmt *n = makeNode(VariableSetStmt);
1627+
1628+
n->kind = VAR_SET_VALUE;
1629+
n->name = $1;
1630+
n->args = $3;
1631+
n->user_set = true;
1632+
$$ = n;
1633+
}
1634+
| var_name '=' var_list USER SET
1635+
{
1636+
VariableSetStmt *n = makeNode(VariableSetStmt);
1637+
1638+
n->kind = VAR_SET_VALUE;
1639+
n->name = $1;
1640+
n->args = $3;
1641+
n->user_set = true;
1642+
$$ = n;
1643+
}
16241644
| var_name TO DEFAULT
16251645
{
16261646
VariableSetStmt *n = makeNode(VariableSetStmt);

‎src/backend/utils/adt/arrayfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3344,6 +3344,7 @@ construct_array_builtin(Datum *elems, int nelems, Oid elmtype)
33443344
switch (elmtype)
33453345
{
33463346
case CHAROID:
3347+
case BOOLOID:
33473348
elmlen = 1;
33483349
elmbyval = true;
33493350
elmalign = TYPALIGN_CHAR;

‎src/backend/utils/fmgr/fmgr.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -706,6 +706,7 @@ fmgr_security_definer(PG_FUNCTION_ARGS)
706706
if (fcache->proconfig)
707707
{
708708
ProcessGUCArray(fcache->proconfig,
709+
NULL,
709710
(superuser() ? PGC_SUSET : PGC_USERSET),
710711
PGC_S_SESSION,
711712
GUC_ACTION_SAVE);

0 commit comments

Comments
 (0)