Hello all, Based on the discussion, I have updated the patch to handle the RESET form correctly without modifying psql’s tab-completion APIs.
Both the current database connection and the parsed input tokens are already available via pset.db and the word tokens.The new patch extracts: - the role name from the parsed tokens (prev5_wd), and - the database name from the parsed tokens (prev2_wd), and uses these to query pg_db_role_setting for variables that are actually set for the specific (role, database) pair. Literal quoting is now done with PQescapeLiteral(pset.db, …), per libpq conventions, so no new helper functions were needed. SET After ALTER ROLE <role> IN DATABASE <dbname> SET <TAB> psql completes from Query_for_list_of_set_vars (same behavior as plain ALTER ROLE … SET). RESET After ALTER ROLE <role> IN DATABASE <dbname> RESET <TAB> psql now completes with the GUC names recorded in pg_db_role_setting for that specific (role,database), plus ALL. When no settings exist, only ALL is suggested. This mirrors the existing behavior of ALTER DATABASE … RESET. I have attached the patch. Regards, Vasuki On Thu, Nov 27, 2025 at 2:27 PM Ian Lawrence Barwick <[email protected]> wrote: > Hi > > I found myself needing to work with ALTER ROLE ... IN DATABASE ... recently > and was annoyed by the lack of tab completion for this, so patch attached. > > > Regards > > Ian Barwick > >
From 3897e315544445e866baeb7806df77f08cd211c9 Mon Sep 17 00:00:00 2001 From: BharatDBPG <[email protected]> Date: Thu, 27 Nov 2025 14:38:48 +0530 Subject: [PATCH] psql: Add tab-completion support for ALTER ROLE ... IN DATABASE ... SET/RESET forms MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch extends psql's tab-completion logic to recognize the "ALTER ROLE <role> IN DATABASE <dbname>" command structure, and provides appropriate completions for the SET and RESET subcommands. Specifically: • After "ALTER ROLE <role> IN DATABASE <dbname> SET", psql now completes with the list of configuration variables that may be set (Query_for_list_of_set_vars), matching the behavior of the plain ALTER ROLE ... SET form. • After "ALTER ROLE <role> IN DATABASE <dbname> RESET", psql now suggests configuration variables that are *actually set* for that specific (role,database) pair, as recorded in pg_db_role_setting, plus the keyword ALL. This mirrors the behavior of ALTER DATABASE ... RESET, where we complete only the variables currently set for the object being modified. The role name and database name are extracted from the already-parsed input tokens, and SQL literal quoting is performed via PQescapeLiteral() using the implicit PGconn (pset.db) available to the tab-completion code. This avoids any need to alter tab-completion APIs and keeps the patch self-contained. Due to the structure of tab-completion, this patch intentionally does not attempt to complete arbitrary GUC names for RESET, but rather only those that exist in pg_db_role_setting for the given role and database. When none are present, psql falls back to suggesting ALL, matching existing RESET behavior elsewhere. Vasuki M --- src/bin/psql/tab-complete.in.c | 66 ++++++++++++++++++++++++++++++++-- 1 file changed, 64 insertions(+), 2 deletions(-) diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 51806597037..ca0e64b7bcf 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -2536,12 +2536,74 @@ match_previous_words(int pattern_id, else if (Matches("ALTER", "USER|ROLE", MatchAny) && !TailMatches("USER", "MAPPING")) COMPLETE_WITH("BYPASSRLS", "CONNECTION LIMIT", "CREATEDB", "CREATEROLE", - "ENCRYPTED PASSWORD", "INHERIT", "LOGIN", "NOBYPASSRLS", - "NOCREATEDB", "NOCREATEROLE", "NOINHERIT", + "ENCRYPTED PASSWORD", "IN DATABASE", "INHERIT", "LOGIN", + "NOBYPASSRLS", "NOCREATEDB", "NOCREATEROLE", "NOINHERIT", "NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "PASSWORD", "RENAME TO", "REPLICATION", "RESET", "SET", "SUPERUSER", "VALID UNTIL", "WITH"); + /* ALTER USER,ROLE <name> IN */ + else if (Matches("ALTER", "USER|ROLE", MatchAny, "IN")) + COMPLETE_WITH("DATABASE"); + + /* ALTER USER/ROLE <name> IN DATABASE */ + else if (Matches("ALTER", "USER|ROLE", MatchAny, "IN", "DATABASE", MatchAny)) + { + /* ALTER ROLE bob IN DATABASE <TAB> → list databases */ + if (TailMatches("DATABASE")) + COMPLETE_WITH_QUERY(Query_for_list_of_databases); + + /* ALTER ROLE bob IN DATABASE mydb <TAB> → SET, RESET */ + else if (TailMatches("DATABASE", MatchAny)) + COMPLETE_WITH("SET", "RESET"); + + /* ALTER ROLE bob IN DATABASE mydb SET <TAB> */ + else if (TailMatches("DATABASE", MatchAny, "SET")) + COMPLETE_WITH_QUERY(Query_for_list_of_set_vars); + + /* ALTER ROLE bob IN DATABASE mydb RESET <TAB> */ + else if (TailMatches("DATABASE", MatchAny, "RESET")) + { + /* + * Extract tokens: + * prev5 = role name + * prev2 = database name + */ + char *role = prev5_wd; + char *dbname = prev2_wd; + char *q_role; + char *q_dbname; + char *query; + + /* Safe SQL literal quoting using libpq */ + q_role = PQescapeLiteral(pset.db, role, strlen(role)); + q_dbname = PQescapeLiteral(pset.db, dbname, strlen(dbname)); + + if (!q_role || !q_dbname) + { + /* If quoting fails, just fall back to ALL */ + COMPLETE_WITH("ALL"); + } + else + { + + query = psprintf( + "SELECT unnest(setconfig) " + " FROM pg_db_role_setting " + " WHERE setdatabase = " + " (SELECT oid FROM pg_database WHERE datname = %s) " + " AND setrole = %s::regrole", + q_dbname, q_role); + + COMPLETE_WITH_QUERY_PLUS(query, "ALL"); + + pfree(q_role); + pfree(q_dbname); + pfree(query); + } + } + } + /* ALTER USER,ROLE <name> RESET */ else if (Matches("ALTER", "USER|ROLE", MatchAny, "RESET")) { -- 2.43.0
