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
