Hi hackers,

While trying REVOKE ... GRANTED BY in PostgreSQL 19, I noticed that
psql doesn't complete the clause or the grantor's name.

I tried the corresponding GRANT and found that pressing Tab after
a trailing space on this line inserts TO, making the statement invalid.

GRANT SELECT ON t TO r WITH GRANT OPTION

Before the patch, the generic GRANT rule treats the trailing GRANT
OPTION as GRANT followed by a role name. The patch matches WITH
GRANT OPTION first, so Tab inserts GRANTED BY. It also completes
the clause and grantor name in both commands.

Thanks,

-- 
Ze Chen (Neil)
HighGo Software Co., Ltd.
https://www.highgo.com/
From 5b28b450d9e60f84a2bdf9a7a035f6f3afd13ac7 Mon Sep 17 00:00:00 2001
From: Neil Chen <[email protected]>
Date: Fri, 20 Nov 2026 16:46:39 +0800
Subject: [PATCH] psql: Complete GRANTED BY in GRANT and REVOKE

PostgreSQL 19 added support for choosing an explicit grantor for
object privileges (dd1398f1378).  In psql, REVOKE does not suggest
the GRANTED BY clause after the grantee or role names after the
clause has been typed.

The corresponding GRANT form has a completion conflict.  After
WITH GRANT OPTION, a bare Tab inserts TO because the generic GRANT
rule treats the trailing OPTION as a role name.

Match WITH GRANT OPTION before the generic rule.  Keep this suffix
separate from the ADMIN, INHERIT, and SET options for role membership.
Complete GRANTED BY and its role argument in both commands using the
existing role lookup, with the new rules limited to a single grantee.
---
 src/bin/psql/t/010_tab_completion.pl | 10 ++++++++
 src/bin/psql/tab-complete.in.c       | 34 ++++++++++++++++++++++++----
 2 files changed, 40 insertions(+), 4 deletions(-)

diff --git a/src/bin/psql/t/010_tab_completion.pl b/src/bin/psql/t/010_tab_completion.pl
index 1d2e5f5..623dc31 100644
--- a/src/bin/psql/t/010_tab_completion.pl
+++ b/src/bin/psql/t/010_tab_completion.pl
@@ -206,6 +206,16 @@ check_completion(
 
 clear_query();
 
+# The generic GRANT rule used to insert TO after WITH GRANT OPTION.
+check_completion("grant select on tab1 to r with grant option \t",
+	qr/with grant option granted by /i, "complete GRANTED BY after WITH GRANT OPTION");
+clear_query();
+
+check_completion("revoke select on tab1 from r granted \t",
+	qr/granted by /i, "complete BY after GRANTED in REVOKE");
+check_completion("pg_mon\t", qr/pg_monitor /, "complete the grantor role");
+clear_query();
+
 # check interpretation of referenced names
 check_completion(
 	"alter table tab1 drop constraint t\t",
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 96de78c..2080a45 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -4607,6 +4607,12 @@ match_previous_words(int pattern_id,
 			 TailMatches("REVOKE", "GRANT", "OPTION", "FOR", MatchAny, MatchAny, "ON", "PARAMETER", MatchAny))
 		COMPLETE_WITH("FROM");
 
+	/* Match WITH GRANT OPTION before the generic rule for GRANT <word>. */
+	else if (Matches("GRANT", MatchAnyN, "TO", MatchAny, "WITH", "GRANT", "OPTION") ||
+			 Matches("GRANT", MatchAnyN, "TO", MatchAny, "WITH",
+					 "ADMIN|INHERIT|SET", "OPTION|TRUE|FALSE"))
+		COMPLETE_WITH("GRANTED BY");
+
 	/*
 	 * Complete GRANT/REVOKE <privilege> with "ON", GRANT/REVOKE <role> with
 	 * TO/FROM
@@ -4740,11 +4746,31 @@ match_previous_words(int pattern_id,
 					  "GRANT OPTION");
 	else if (Matches("GRANT", MatchAnyN, "TO", MatchAny, "WITH", "ADMIN|INHERIT|SET"))
 		COMPLETE_WITH("OPTION", "TRUE", "FALSE");
-	else if (Matches("GRANT", MatchAnyN, "TO", MatchAny, "WITH", MatchAny, "OPTION"))
+	else if (Matches("REVOKE", MatchAnyN, "FROM", MatchAnyExcept("GROUP")) &&
+			 !ends_with(prev_wd, ','))
 		COMPLETE_WITH("GRANTED BY");
-	else if (Matches("GRANT", MatchAnyN, "TO", MatchAny, "WITH", MatchAny, "OPTION", "GRANTED", "BY"))
-		COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_roles,
-								 Keywords_for_list_of_grant_roles);
+	/* GRANTED can itself be a role name, including after GROUP or a comma. */
+	else if (Matches("GRANT", MatchAnyN, "TO", MatchAnyExcept("GROUP"), "GRANTED") ||
+			 Matches("REVOKE", MatchAnyN, "FROM", MatchAnyExcept("GROUP"), "GRANTED") ||
+			 Matches("GRANT", MatchAnyN, "TO", MatchAny, "WITH",
+					 "GRANT", "OPTION", "GRANTED") ||
+			 Matches("GRANT", MatchAnyN, "TO", MatchAny, "WITH",
+					 "ADMIN|INHERIT|SET", "OPTION|TRUE|FALSE", "GRANTED"))
+	{
+		if (!ends_with(prev2_wd, ','))
+			COMPLETE_WITH("BY");
+	}
+	else if (Matches("GRANT", MatchAnyN, "TO", MatchAnyExcept("GROUP"), "GRANTED", "BY") ||
+			 Matches("REVOKE", MatchAnyN, "FROM", MatchAnyExcept("GROUP"), "GRANTED", "BY") ||
+			 Matches("GRANT", MatchAnyN, "TO", MatchAny, "WITH",
+					 "GRANT", "OPTION", "GRANTED", "BY") ||
+			 Matches("GRANT", MatchAnyN, "TO", MatchAny, "WITH",
+					 "ADMIN|INHERIT|SET", "OPTION|TRUE|FALSE", "GRANTED", "BY"))
+	{
+		if (!ends_with(prev3_wd, ','))
+			COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_roles,
+									 Keywords_for_list_of_owner_roles);
+	}
 	/* Complete "ALTER DEFAULT PRIVILEGES ... GRANT/REVOKE ... TO/FROM */
 	else if (Matches("ALTER", "DEFAULT", "PRIVILEGES", MatchAnyN, "TO|FROM"))
 		COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_roles,

base-commit: 47ca0f45e6fed487d9c3b83db0e7c6a631c1cfb7

Reply via email to