public inbox for [email protected]
help / color / mirror / Atom feedRe: Customize psql prompt to show current_role
8+ messages / 6 participants
[nested] [flat]
* Re: Customize psql prompt to show current_role
@ 2024-09-23 12:22 Asad Ali <[email protected]>
2024-09-23 12:51 ` Re: Customize psql prompt to show current_role Erik Wienhold <[email protected]>
2024-09-23 13:02 ` Re: Customize psql prompt to show current_role Greg Sabino Mullane <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Asad Ali @ 2024-09-23 12:22 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: pgsql-general
Hi Dominique,
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 approach requires you to execute it manually or include it in your
.psqlrc file for automatic loading with each session.
I hope this helps!
Best regards,
Asad Ali
On Mon, Sep 23, 2024 at 2:31 PM Dominique Devienne <[email protected]>
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
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Customize psql prompt to show current_role
2024-09-23 12:22 Re: Customize psql prompt to show current_role Asad Ali <[email protected]>
@ 2024-09-23 12:51 ` Erik Wienhold <[email protected]>
2024-09-23 12:59 ` Re: Customize psql prompt to show current_role Dominique Devienne <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Erik Wienhold @ 2024-09-23 12:51 UTC (permalink / raw)
To: Asad Ali <[email protected]>; +Cc: Dominique Devienne <[email protected]>; pgsql-general
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 <[email protected]>
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Customize psql prompt to show current_role
2024-09-23 12:22 Re: Customize psql prompt to show current_role Asad Ali <[email protected]>
2024-09-23 12:51 ` Re: Customize psql prompt to show current_role Erik Wienhold <[email protected]>
@ 2024-09-23 12:59 ` Dominique Devienne <[email protected]>
2024-09-23 13:05 ` Re: Customize psql prompt to show current_role Laurenz Albe <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Dominique Devienne @ 2024-09-23 12:59 UTC (permalink / raw)
To: Erik Wienhold <[email protected]>; +Cc: Asad Ali <[email protected]>; pgsql-general
On Mon, Sep 23, 2024 at 2:51 PM Erik Wienhold <[email protected]> wrote:
> You could instead use this:
>
> SELECT current_role \gset
> \set PROMPT1 '%n@%/ (%:current_role:)=%# '
>
> But that won't work with subsequent SET ROLE commands.
Bummer... That was kinda the point, that it updates automatically.
Then I'd like to respectfully request a small enhancement to psql
to support a new \r (or some other letter) for current_role in the prompt.
Thanks, --DD
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Customize psql prompt to show current_role
2024-09-23 12:22 Re: Customize psql prompt to show current_role Asad Ali <[email protected]>
2024-09-23 12:51 ` Re: Customize psql prompt to show current_role Erik Wienhold <[email protected]>
2024-09-23 12:59 ` Re: Customize psql prompt to show current_role Dominique Devienne <[email protected]>
@ 2024-09-23 13:05 ` Laurenz Albe <[email protected]>
2024-09-23 13:08 ` Re: Customize psql prompt to show current_role Dominique Devienne <[email protected]>
2024-09-23 14:54 ` Re: Customize psql prompt to show current_role Tom Lane <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Laurenz Albe @ 2024-09-23 13:05 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; Erik Wienhold <[email protected]>; +Cc: Asad Ali <[email protected]>; pgsql-general
On Mon, 2024-09-23 at 14:59 +0200, Dominique Devienne wrote:
> On Mon, Sep 23, 2024 at 2:51 PM Erik Wienhold <[email protected]> wrote:
> > You could instead use this:
> >
> > SELECT current_role \gset
> > \set PROMPT1 '%n@%/ (%:current_role:)=%# '
> >
> > But that won't work with subsequent SET ROLE commands.
>
> Bummer... That was kinda the point, that it updates automatically.
>
> Then I'd like to respectfully request a small enhancement to psql
> to support a new \r (or some other letter) for current_role in the prompt.
To get the current role, psql would have to query the database whenever
it displays the prompt. That would be rather expensive...
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Customize psql prompt to show current_role
2024-09-23 12:22 Re: Customize psql prompt to show current_role Asad Ali <[email protected]>
2024-09-23 12:51 ` Re: Customize psql prompt to show current_role Erik Wienhold <[email protected]>
2024-09-23 12:59 ` Re: Customize psql prompt to show current_role Dominique Devienne <[email protected]>
2024-09-23 13:05 ` Re: Customize psql prompt to show current_role Laurenz Albe <[email protected]>
@ 2024-09-23 13:08 ` Dominique Devienne <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Dominique Devienne @ 2024-09-23 13:08 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Erik Wienhold <[email protected]>; Asad Ali <[email protected]>; pgsql-general
On Mon, Sep 23, 2024 at 3:05 PM Laurenz Albe <[email protected]> wrote:
> On Mon, 2024-09-23 at 14:59 +0200, Dominique Devienne wrote:
> > On Mon, Sep 23, 2024 at 2:51 PM Erik Wienhold <[email protected]> wrote:
> > > You could instead use this:
> > >
> > > SELECT current_role \gset
> > > \set PROMPT1 '%n@%/ (%:current_role:)=%# '
> > >
> > > But that won't work with subsequent SET ROLE commands.
> >
> > Bummer... That was kinda the point, that it updates automatically.
> >
> > Then I'd like to respectfully request a small enhancement to psql
> > to support a new \r (or some other letter) for current_role in the prompt.
>
> To get the current role, psql would have to query the database whenever
> it displays the prompt. That would be rather expensive...
Hi Laurenz. Sure, although expensive might be a bit strong a word.
But that would be opt-in anyway, so just a small warning in the doc
about that new option would suffice, no? --DD
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Customize psql prompt to show current_role
2024-09-23 12:22 Re: Customize psql prompt to show current_role Asad Ali <[email protected]>
2024-09-23 12:51 ` Re: Customize psql prompt to show current_role Erik Wienhold <[email protected]>
2024-09-23 12:59 ` Re: Customize psql prompt to show current_role Dominique Devienne <[email protected]>
2024-09-23 13:05 ` Re: Customize psql prompt to show current_role Laurenz Albe <[email protected]>
@ 2024-09-23 14:54 ` Tom Lane <[email protected]>
2024-09-23 15:07 ` Re: Customize psql prompt to show current_role Dominique Devienne <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2024-09-23 14:54 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Dominique Devienne <[email protected]>; Erik Wienhold <[email protected]>; Asad Ali <[email protected]>; pgsql-general
Laurenz Albe <[email protected]> writes:
> To get the current role, psql would have to query the database whenever
> it displays the prompt. That would be rather expensive...
See previous discussion:
https://www.postgresql.org/message-id/flat/CAFj8pRBFU-WzzQhNrwRHn67N0Ug8a9-0-9BOo69PPtcHiBDQMA%40mai...
At the time I didn't like the idea too much, but now that we've seen
a second independent request, maybe our opinion of its value should
go up a notch.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Customize psql prompt to show current_role
2024-09-23 12:22 Re: Customize psql prompt to show current_role Asad Ali <[email protected]>
2024-09-23 12:51 ` Re: Customize psql prompt to show current_role Erik Wienhold <[email protected]>
2024-09-23 12:59 ` Re: Customize psql prompt to show current_role Dominique Devienne <[email protected]>
2024-09-23 13:05 ` Re: Customize psql prompt to show current_role Laurenz Albe <[email protected]>
2024-09-23 14:54 ` Re: Customize psql prompt to show current_role Tom Lane <[email protected]>
@ 2024-09-23 15:07 ` Dominique Devienne <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Dominique Devienne @ 2024-09-23 15:07 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Erik Wienhold <[email protected]>; Asad Ali <[email protected]>; pgsql-general
On Mon, Sep 23, 2024 at 4:55 PM Tom Lane <[email protected]> wrote:
> Laurenz Albe <[email protected]> writes:
> > To get the current role, psql would have to query the database whenever
> > it displays the prompt. That would be rather expensive...
>
> See previous discussion:
> https://www.postgresql.org/message-id/flat/CAFj8pRBFU-WzzQhNrwRHn67N0Ug8a9-0-9BOo69PPtcHiBDQMA%40mai...
>
> At the time I didn't like the idea too much, but now that we've seen
> a second independent request, maybe our opinion of its value should
> go up a notch.
Thanks for the reference Tom. I don't follow -hackers, so missed it.
Obviously I disagree with your "it's not useful enough" comment :)
I often lose track of which ROLE is active, in my many
long-lived psql sessions, on various servers (mainly v14 and v16 these days),
especially during my recent struggle to adapt our system to v16.
I often resort to \conninfo, but it's less automatic and
harder to visually parse (IMHO) compared to a custom ad-hoc prompt.
Therefore I want to respectfully re-iterate my interest in this enhancement.
Thanks, --DD
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Customize psql prompt to show current_role
2024-09-23 12:22 Re: Customize psql prompt to show current_role Asad Ali <[email protected]>
@ 2024-09-23 13:02 ` Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Greg Sabino Mullane @ 2024-09-23 13:02 UTC (permalink / raw)
To: Asad Ali <[email protected]>; +Cc: Dominique Devienne <[email protected]>; pgsql-general
On Mon, Sep 23, 2024 at 8:22 AM Asad Ali <[email protected]> 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.
>
Please do not use chatgpt or other LLMs to answer questions on this mailing
list! If you had even done the bare minimum of trying your own
lazily-generated answer, you would have found it did not work.
Greg
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-09-23 15:07 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-23 12:22 Re: Customize psql prompt to show current_role Asad Ali <[email protected]>
2024-09-23 12:51 ` Erik Wienhold <[email protected]>
2024-09-23 12:59 ` Dominique Devienne <[email protected]>
2024-09-23 13:05 ` Laurenz Albe <[email protected]>
2024-09-23 13:08 ` Dominique Devienne <[email protected]>
2024-09-23 14:54 ` Tom Lane <[email protected]>
2024-09-23 15:07 ` Dominique Devienne <[email protected]>
2024-09-23 13:02 ` Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox