Обсуждение: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

Ian Lawrence Barwick <barwick@gmail.com> writes:

> 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.

A noble goal, but unfortunately th RESET form can't work properly due to
limitations of the tab completion system.

> +    /* ALTER USER,ROLE <name> IN DATABASE */
> +    else if (HeadMatches("ALTER", "USER|ROLE", MatchAny, "IN"))
> +    {
[...]
> +        else if (TailMatches("DATABASE", MatchAny, "RESET"))
> +        {
> +            set_completion_reference(prev5_wd);
> +            COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_user_vars, "ALL");

This queries pg_roles.rolconfig, which only contains variables set for
the user in all databases, not in the specified database.  Instead,
you'd need to query pg_db_role_setting WHERE setdatabase = (SELECT oid
FROM pg_database WHERE datname = '%s') AND setrole = '%s'::regrole, but
unfortunately the tab completion system doesn't let you more than one
previous word in the query.  I guess you could query WHERE setdatabase
<> 0, to get variables set for the user across all databases, not just
the specified one.

Also, alter ALTER ROLE ALL RESET needs separate handling, filtering
where setrole = 0, which is actually possible in the current system.

- ilmari

Hi Ian,

+1 for the patch,LGTM

But after applying the patch i can be able to apply all the results
except the reset options user variables

postgres=#
postgres=# alter role bob
BYPASSRLS           CREATEROLE          INHERIT             NOCREATEDB
         NOLOGIN             PASSWORD            RESET
VALID UNTIL
CONNECTION LIMIT    ENCRYPTED PASSWORD  LOGIN
NOCREATEROLE        NOREPLICATION       RENAME TO           SET
         WITH
CREATEDB            IN DATABASE         NOBYPASSRLS         NOINHERIT
         NOSUPERUSER         REPLICATION         SUPERUSER
postgres=# alter role bob in DATABASE
postgres   template0  template1
postgres=# alter role bob in DATABASE postgres
RESET  SET
postgres=# alter role bob in DATABASE postgres reset ALL

postgres=# alter role bob in DATABASE postgres reset ALL

also i cross verified that my system doesn't have variables so it
returns 0 rows?but:

postgres=# SELECT name FROM pg_settings LIMIT 5;
            name
----------------------------
 allow_alter_system
 allow_in_place_tablespaces
 allow_system_table_mods
 application_name
 archive_cleanup_command
(5 rows)

Can you check this ?

-regards

Vasuki M


On Fri, Nov 21, 2025 at 8:44 AM Ian Lawrence Barwick <barwick@gmail.com> 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
>