Thread

[PATCH v6 5/5] Improve tab completion for ALTER SYSTEM (SET|RESET) for non-superusers

Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> — 2025-08-08T23:29:39Z

Non-superusers can only use ALTER SYSTEM on variables they've
explicitly been GRANTed access to, and can't read the data_directory
setting or pg_settings.sourceline column, so the existing tab
completion for ALTER SYSTEM isn't very useful for them.

Instead, on servers > v15, query pg_parameter_acl to show the variables
they do have permission to set via ALTER SYSTEM.
---
 src/bin/psql/tab-complete.in.c | 46 +++++++++++++++++++++++++++++++---
 1 file changed, 43 insertions(+), 3 deletions(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 156fef50085..08a1df85492 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -304,6 +304,23 @@ do { \
 	COMPLETE_WITH_VERSIONED_QUERY_LIST(query, list); \
 } while (0)
 
+#define COMPLETE_WITH_VERSIONED_QUERY_VERBATIM(query) \
+	COMPLETE_WITH_VERSIONED_QUERY_VERBATIM_LIST(query, NULL)
+
+#define COMPLETE_WITH_VERSIONED_QUERY_VERBATIM_LIST(query, list) \
+do { \
+	completion_vquery = query; \
+	completion_charpp = list; \
+	completion_verbatim = true; \
+	matches = rl_completion_matches(text, complete_from_versioned_query); \
+} while (0)
+
+#define COMPLETE_WITH_VERSIONED_QUERY_VERBATIM_PLUS(query, ...) \
+do { \
+	static const char *const list[] = { __VA_ARGS__, NULL }; \
+	COMPLETE_WITH_VERSIONED_QUERY_VERBATIM_LIST(query, list); \
+} while (0)
+
 #define COMPLETE_WITH_SCHEMA_QUERY(query) \
 	COMPLETE_WITH_SCHEMA_QUERY_LIST(query, NULL)
 
@@ -1067,6 +1084,19 @@ static const SchemaQuery Query_for_trigger_of_table = {
 " WHERE sourcefile ~ '[\\\\/]postgresql\\.auto\\.conf$' " \
 "   AND pg_catalog.lower(name) LIKE pg_catalog.lower('%s')"
 
+static const VersionedQuery Query_for_list_of_alter_system_granted_vars[] = {
+	{150000,
+		"SELECT pg_catalog.lower(parname) FROM pg_catalog.pg_parameter_acl "
+		" WHERE EXISTS (SELECT FROM pg_catalog.aclexplode(paracl) "
+		"                WHERE pg_has_role(current_role, grantee, 'usage') "
+		"                  AND privilege_type = 'ALTER SYSTEM') "
+		"   AND pg_catalog.lower(parname) LIKE pg_catalog.lower('%s')",
+	},
+	/* this is only used for non-superusers, who can't ALTER SYSTEM before 15,
+	 * so no point in any fallback*/
+	{0, NULL},
+};
+
 #define Query_for_list_of_set_vars \
 "SELECT pg_catalog.lower(name) FROM pg_catalog.pg_settings "\
 " WHERE context IN ('user', 'superuser') "\
@@ -2653,10 +2683,20 @@ match_previous_words(int pattern_id,
 	else if (Matches("ALTER", "SYSTEM"))
 		COMPLETE_WITH("SET", "RESET");
 	else if (Matches("ALTER", "SYSTEM", "SET"))
-		COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_alter_system_set_vars);
+	{
+		if (is_superuser())
+			COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_alter_system_set_vars);
+		else
+			COMPLETE_WITH_VERSIONED_QUERY_VERBATIM(Query_for_list_of_alter_system_granted_vars);
+	}
 	else if (Matches("ALTER", "SYSTEM", "RESET"))
-		COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_alter_system_reset_vars,
-										  "ALL");
+	{
+		if (is_superuser())
+			COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_alter_system_reset_vars,
+											  "ALL");
+		else
+			COMPLETE_WITH_VERSIONED_QUERY_VERBATIM(Query_for_list_of_alter_system_granted_vars);
+	}
 	else if (Matches("ALTER", "SYSTEM", "SET", MatchAny))
 		COMPLETE_WITH("TO");
 	/* ALTER VIEW <name> */
-- 
2.51.2


--=-=-=--