Date: Mon, 23 Sep 2024 14:51:08 +0200
From: Erik Wienhold <ewie@ewie.name>
To: Asad Ali <asadalinagri@gmail.com>
Cc: Dominique Devienne <ddevienne@gmail.com>, pgsql-general@postgresql.org
Subject: Re: Customize psql prompt to show current_role
Message-ID: <ac0884a4-daee-465e-bc82-8e3585998053@ewie.name>
References: <CAFCRh-_VwScttZi4R8grSd-b=7qguAnSzXp_+jbdpUkp=TfxPQ@mail.gmail.com>
 <CAJ9xe=ttSfESR=z2YZycow8DubyfQeOgOzMDUCE95f2vN-TzFg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CAJ9xe=ttSfESR=z2YZycow8DubyfQeOgOzMDUCE95f2vN-TzFg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/ac0884a4-daee-465e-bc82-8e3585998053%40ewie.name>
Precedence: bulk

On 2024-09-23 14:22 +0200, Asad Ali wrote:
> There is no direct prompt escape sequence like %n for displaying the
> current_role in the psql prompt. However, you can work around this by using
> a \set command to define a custom prompt that includes the result of
> current_role.
> You can use the following command to set your psql PROMPT1 to include both
> the session_user and current_role:
> 
> Here’s how you can achieve this:
> 
> You can define a function in your psqlrc file that captures the current
> role.
> 
> Use \set to set a custom prompt that includes both the session user (%n)
> and the current_role.
> Here's an example of how you can do it:
> 
> Edit your .psqlrc file to include a custom query and set a prompt:
> 
> -- Query to set the current role into a psql variable
> \set current_role 'SELECT current_role;'
> 
> -- Define a custom prompt with both the session user and the current role
> \set PROMPT1 '%n@%/ (%`current_role`)=%# '
> 
> Load the .psqlrc or start a new psql session, and your prompt will now show
> the session user and current role.
> 
> The key here is that \set allows you to run SQL commands within the prompt,
> which can be used to extract the current_role.

This is wrong.  First of all, \set does not run any SQL commands.
Secondly, %`current_role` will be sent to the shell and execute command
current_role.

You could instead use this:

    SELECT current_role \gset
    \set PROMPT1 '%n@%/ (%:current_role:)=%# '

But that won't work with subsequent SET ROLE commands.

> This approach requires you to execute it manually or include it in your
> .psqlrc file for automatic loading with each session.
> 
> On Mon, Sep 23, 2024 at 2:31 PM Dominique Devienne <ddevienne@gmail.com>
> wrote:
> 
> > Hi. I've successfully customized my psql PROMPT1,
> > using %n for session_user, but I'd like to see
> > current_role as well. And I can't seem to find a way.
> >
> > I didn't find a direct \x for it.
> > I didn't find a %'X' variable for it.
> > I didn't find a command to %`X` either.
> > (and X = `select current_role` does not work).
> >
> > Surely there's a way, no? Thanks, --DD

-- 
Erik