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

11 files changed, 350 insertions, 3 deletions

diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index 96addded814..c629cbe3830 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -25,6 +25,7 @@ SUBDIRS = \
 		  test_misc \
 		  test_oat_hooks \
 		  test_parser \
+		  test_pg_db_role_setting \
 		  test_pg_dump \
 		  test_predtest \
 		  test_rbtree \
diff --git a/src/test/modules/meson.build b/src/test/modules/meson.build
index 1d265448549..911a768a294 100644
--- a/src/test/modules/meson.build
+++ b/src/test/modules/meson.build
@@ -19,6 +19,7 @@ subdir('test_lfind')
 subdir('test_misc')
 subdir('test_oat_hooks')
 subdir('test_parser')
+subdir('test_pg_db_role_setting')
 subdir('test_pg_dump')
 subdir('test_predtest')
 subdir('test_rbtree')
diff --git a/src/test/modules/test_pg_db_role_setting/.gitignore b/src/test/modules/test_pg_db_role_setting/.gitignore
new file mode 100644
index 00000000000..5dcb3ff9723
--- /dev/null
+++ b/src/test/modules/test_pg_db_role_setting/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/test_pg_db_role_setting/Makefile b/src/test/modules/test_pg_db_role_setting/Makefile
new file mode 100644
index 00000000000..aacd78f74c5
--- /dev/null
+++ b/src/test/modules/test_pg_db_role_setting/Makefile
@@ -0,0 +1,29 @@
+# src/test/modules/test_pg_db_role_setting/Makefile
+
+MODULE_big = test_pg_db_role_setting
+OBJS = \
+	$(WIN32RES) \
+	test_pg_db_role_setting.o
+EXTENSION = test_pg_db_role_setting
+DATA = test_pg_db_role_setting--1.0.sql
+
+PGFILEDESC = "test_pg_db_role_setting - tests for default GUC values stored in pg_db_role_settings"
+
+REGRESS = test_pg_db_role_setting
+
+# disable installcheck for now
+NO_INSTALLCHECK = 1
+# and also for now force NO_LOCALE and UTF8
+ENCODING = UTF8
+NO_LOCALE = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/test_pg_db_role_setting
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/test_pg_db_role_setting/expected/test_pg_db_role_setting.out b/src/test/modules/test_pg_db_role_setting/expected/test_pg_db_role_setting.out
new file mode 100644
index 00000000000..4da17dca28c
--- /dev/null
+++ b/src/test/modules/test_pg_db_role_setting/expected/test_pg_db_role_setting.out
@@ -0,0 +1,143 @@
+CREATE EXTENSION test_pg_db_role_setting;
+CREATE USER super_user SUPERUSER;
+CREATE USER regular_user;
+\c - regular_user
+-- successfully set a placeholder value
+SET test_pg_db_role_setting.superuser_param = 'aaa';
+-- module is loaded, the placeholder value is thrown away
+SELECT load_test_pg_db_role_setting();
+WARNING:  permission denied to set parameter "test_pg_db_role_setting.superuser_param"
+ load_test_pg_db_role_setting 
+------------------------------
+ 
+(1 row)
+
+SHOW test_pg_db_role_setting.superuser_param;
+ test_pg_db_role_setting.superuser_param 
+-----------------------------------------
+ superuser_param_value
+(1 row)
+
+SHOW test_pg_db_role_setting.user_param;
+ test_pg_db_role_setting.user_param 
+------------------------------------
+ user_param_value
+(1 row)
+
+\c - regular_user
+-- fail, not privileges
+ALTER ROLE regular_user SET test_pg_db_role_setting.superuser_param = 'aaa';
+ERROR:  permission denied to set parameter "test_pg_db_role_setting.superuser_param"
+ALTER ROLE regular_user SET test_pg_db_role_setting.user_param = 'bbb';
+ERROR:  permission denied to set parameter "test_pg_db_role_setting.user_param"
+-- success for USER SET parameters
+ALTER ROLE regular_user SET test_pg_db_role_setting.superuser_param = 'aaa' USER SET;
+ALTER ROLE regular_user SET test_pg_db_role_setting.user_param = 'bbb' USER SET;
+\drds regular_user
+                                 List of settings
+     Role     | Database |                  Settings                   | User set 
+--------------+----------+---------------------------------------------+----------
+ regular_user |          | test_pg_db_role_setting.superuser_param=aaa+| t       +
+              |          | test_pg_db_role_setting.user_param=bbb      | t
+(1 row)
+
+\c - regular_user
+-- successfully set placeholders
+SHOW test_pg_db_role_setting.superuser_param;
+ test_pg_db_role_setting.superuser_param 
+-----------------------------------------
+ aaa
+(1 row)
+
+SHOW test_pg_db_role_setting.user_param;
+ test_pg_db_role_setting.user_param 
+------------------------------------
+ bbb
+(1 row)
+
+-- module is loaded, the placeholder value of superuser param is thrown away
+SELECT load_test_pg_db_role_setting();
+WARNING:  permission denied to set parameter "test_pg_db_role_setting.superuser_param"
+ load_test_pg_db_role_setting 
+------------------------------
+ 
+(1 row)
+
+SHOW test_pg_db_role_setting.superuser_param;
+ test_pg_db_role_setting.superuser_param 
+-----------------------------------------
+ superuser_param_value
+(1 row)
+
+SHOW test_pg_db_role_setting.user_param;
+ test_pg_db_role_setting.user_param 
+------------------------------------
+ bbb
+(1 row)
+
+\c - super_user
+ALTER ROLE regular_user SET test_pg_db_role_setting.superuser_param = 'aaa';
+\drds regular_user
+                                 List of settings
+     Role     | Database |                  Settings                   | User set 
+--------------+----------+---------------------------------------------+----------
+ regular_user |          | test_pg_db_role_setting.superuser_param=aaa+| f       +
+              |          | test_pg_db_role_setting.user_param=bbb      | t
+(1 row)
+
+\c - regular_user
+-- don't have a priviledge to change superuser value to user set one
+ALTER ROLE regular_user SET test_pg_db_role_setting.superuser_param = 'ccc' USER SET;
+ERROR:  permission denied to set parameter "test_pg_db_role_setting.superuser_param"
+\c - super_user
+SELECT load_test_pg_db_role_setting();
+ load_test_pg_db_role_setting 
+------------------------------
+ 
+(1 row)
+
+-- give the privilege to set SUSET param to the regular user
+GRANT SET ON PARAMETER test_pg_db_role_setting.superuser_param TO regular_user;
+\c - regular_user
+ALTER ROLE regular_user SET test_pg_db_role_setting.superuser_param = 'ccc';
+\drds regular_user
+                                 List of settings
+     Role     | Database |                  Settings                   | User set 
+--------------+----------+---------------------------------------------+----------
+ regular_user |          | test_pg_db_role_setting.superuser_param=ccc+| f       +
+              |          | test_pg_db_role_setting.user_param=bbb      | t
+(1 row)
+
+\c - regular_user
+-- successfully set placeholders
+SHOW test_pg_db_role_setting.superuser_param;
+ test_pg_db_role_setting.superuser_param 
+-----------------------------------------
+ ccc
+(1 row)
+
+SHOW test_pg_db_role_setting.user_param;
+ test_pg_db_role_setting.user_param 
+------------------------------------
+ bbb
+(1 row)
+
+-- module is loaded, and placeholder values are succesfully set
+SELECT load_test_pg_db_role_setting();
+ load_test_pg_db_role_setting 
+------------------------------
+ 
+(1 row)
+
+SHOW test_pg_db_role_setting.superuser_param;
+ test_pg_db_role_setting.superuser_param 
+-----------------------------------------
+ ccc
+(1 row)
+
+SHOW test_pg_db_role_setting.user_param;
+ test_pg_db_role_setting.user_param 
+------------------------------------
+ bbb
+(1 row)
+
diff --git a/src/test/modules/test_pg_db_role_setting/meson.build b/src/test/modules/test_pg_db_role_setting/meson.build
new file mode 100644
index 00000000000..3a6410cca21
--- /dev/null
+++ b/src/test/modules/test_pg_db_role_setting/meson.build
@@ -0,0 +1,35 @@
+# FIXME: prevent install during main install, but not during test :/
+
+test_pg_db_role_setting_sources = files(
+  'test_pg_db_role_setting.c',
+)
+
+if host_system == 'windows'
+  test_pg_db_role_setting_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+    '--NAME', 'test_pg_db_role_setting',
+    '--FILEDESC', 'test_pg_db_role_setting - tests for default GUC values stored in pg_db_role_settings',])
+endif
+
+test_pg_db_role_setting = shared_module('test_pg_db_role_setting',
+  test_pg_db_role_setting_sources,
+  kwargs: pg_mod_args,
+)
+testprep_targets += test_pg_db_role_setting
+
+install_data(
+  'test_pg_db_role_setting.control',
+  'test_pg_db_role_setting--1.0.sql',
+  kwargs: contrib_data_args,
+)
+
+tests += {
+  'name': 'test_pg_db_role_setting',
+  'sd': meson.current_source_dir(),
+  'bd': meson.current_build_dir(),
+  'regress': {
+    'sql': [
+      'test_pg_db_role_setting',
+    ],
+    'regress_args': ['--no-locale', '--encoding=UTF8'],
+  },
+}
diff --git a/src/test/modules/test_pg_db_role_setting/sql/test_pg_db_role_setting.sql b/src/test/modules/test_pg_db_role_setting/sql/test_pg_db_role_setting.sql
new file mode 100644
index 00000000000..cb6eb0448e3
--- /dev/null
+++ b/src/test/modules/test_pg_db_role_setting/sql/test_pg_db_role_setting.sql
@@ -0,0 +1,63 @@
+CREATE EXTENSION test_pg_db_role_setting;
+CREATE USER super_user SUPERUSER;
+CREATE USER regular_user;
+
+\c - regular_user
+-- successfully set a placeholder value
+SET test_pg_db_role_setting.superuser_param = 'aaa';
+
+-- module is loaded, the placeholder value is thrown away
+SELECT load_test_pg_db_role_setting();
+
+SHOW test_pg_db_role_setting.superuser_param;
+SHOW test_pg_db_role_setting.user_param;
+
+\c - regular_user
+-- fail, not privileges
+ALTER ROLE regular_user SET test_pg_db_role_setting.superuser_param = 'aaa';
+ALTER ROLE regular_user SET test_pg_db_role_setting.user_param = 'bbb';
+-- success for USER SET parameters
+ALTER ROLE regular_user SET test_pg_db_role_setting.superuser_param = 'aaa' USER SET;
+ALTER ROLE regular_user SET test_pg_db_role_setting.user_param = 'bbb' USER SET;
+
+\drds regular_user
+
+\c - regular_user
+-- successfully set placeholders
+SHOW test_pg_db_role_setting.superuser_param;
+SHOW test_pg_db_role_setting.user_param;
+
+-- module is loaded, the placeholder value of superuser param is thrown away
+SELECT load_test_pg_db_role_setting();
+
+SHOW test_pg_db_role_setting.superuser_param;
+SHOW test_pg_db_role_setting.user_param;
+
+\c - super_user
+ALTER ROLE regular_user SET test_pg_db_role_setting.superuser_param = 'aaa';
+\drds regular_user
+
+\c - regular_user
+-- don't have a priviledge to change superuser value to user set one
+ALTER ROLE regular_user SET test_pg_db_role_setting.superuser_param = 'ccc' USER SET;
+
+\c - super_user
+SELECT load_test_pg_db_role_setting();
+-- give the privilege to set SUSET param to the regular user
+GRANT SET ON PARAMETER test_pg_db_role_setting.superuser_param TO regular_user;
+
+\c - regular_user
+ALTER ROLE regular_user SET test_pg_db_role_setting.superuser_param = 'ccc';
+
+\drds regular_user
+
+\c - regular_user
+-- successfully set placeholders
+SHOW test_pg_db_role_setting.superuser_param;
+SHOW test_pg_db_role_setting.user_param;
+
+-- module is loaded, and placeholder values are succesfully set
+SELECT load_test_pg_db_role_setting();
+
+SHOW test_pg_db_role_setting.superuser_param;
+SHOW test_pg_db_role_setting.user_param;
diff --git a/src/test/modules/test_pg_db_role_setting/test_pg_db_role_setting--1.0.sql b/src/test/modules/test_pg_db_role_setting/test_pg_db_role_setting--1.0.sql
new file mode 100644
index 00000000000..1ed3d285c7e
--- /dev/null
+++ b/src/test/modules/test_pg_db_role_setting/test_pg_db_role_setting--1.0.sql
@@ -0,0 +1,7 @@
+/* src/test/modules/test_pg_db_role_setting/test_pg_db_role_setting--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_pg_db_role_setting" to load this file. \quit
+
+CREATE FUNCTION load_test_pg_db_role_setting() RETURNS void
+  AS 'MODULE_PATHNAME' LANGUAGE C;
diff --git a/src/test/modules/test_pg_db_role_setting/test_pg_db_role_setting.c b/src/test/modules/test_pg_db_role_setting/test_pg_db_role_setting.c
new file mode 100644
index 00000000000..3982ae5629f
--- /dev/null
+++ b/src/test/modules/test_pg_db_role_setting/test_pg_db_role_setting.c
@@ -0,0 +1,57 @@
+/*--------------------------------------------------------------------------
+ *
+ * test_pg_db_role_setting.c
+ *		Code for testing mandatory access control (MAC) using object access hooks.
+ *
+ * Copyright (c) 2022, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ *		src/test/modules/test_pg_db_role_setting/test_pg_db_role_setting.c
+ *
+ * -------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "utils/guc.h"
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(load_test_pg_db_role_setting);
+
+static char *superuser_param;
+static char *user_param;
+
+/*
+ * Module load callback
+ */
+void
+_PG_init(void)
+{
+	DefineCustomStringVariable("test_pg_db_role_setting.superuser_param",
+							   "Sample superuser parameter.",
+							   NULL,
+							   &superuser_param,
+							   "superuser_param_value",
+							   PGC_SUSET,
+							   0,
+							   NULL, NULL, NULL);
+
+	DefineCustomStringVariable("test_pg_db_role_setting.user_param",
+							   "Sample user parameter.",
+							   NULL,
+							   &user_param,
+							   "user_param_value",
+							   PGC_USERSET,
+							   0,
+							   NULL, NULL, NULL);
+}
+
+/*
+ * Empty function, which is used just to trigger load of this module.
+ */
+Datum
+load_test_pg_db_role_setting(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_VOID();
+}
diff --git a/src/test/modules/test_pg_db_role_setting/test_pg_db_role_setting.control b/src/test/modules/test_pg_db_role_setting/test_pg_db_role_setting.control
new file mode 100644
index 00000000000..9678cff376d
--- /dev/null
+++ b/src/test/modules/test_pg_db_role_setting/test_pg_db_role_setting.control
@@ -0,0 +1,7 @@
+# test_pg_db_role_setting extension
+comment = 'test_pg_db_role_setting - tests for default GUC values stored in pg_db_role_setting'
+default_version = '1.0'
+module_pathname = '$libdir/test_pg_db_role_setting'
+relocatable = true
+superuser = false
+trusted = true
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index b4cb6ffb5b4..8fc62cebd2d 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6188,9 +6188,9 @@ List of schemas
 (0 rows)
 
 \drds "no.such.setting"
-      List of settings
- Role | Database | Settings 
-------+----------+----------
+           List of settings
+ Role | Database | Settings | User set 
+------+----------+----------+----------
 (0 rows)
 
 \dRp "no.such.publication"