Re: psql color hostname prompt

On Wed, Apr 27, 2016 at 12:09 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
> On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte <folarte@peoplecall.com>
> wrote:
>>
>> Hi Cal:
>>
>> On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand <cal@fbsdata.com> wrote:
>> ...
>> > 2)  %M vs shell call
>> > %M on when connected to the local machine displays the string "[local]"
>> > which I didn't like.  I wanted a real hostname to show no matter which
>> > client/server pair I was using.  Zero chance for mistaken commands on
>> > the
>> > wrong host.  Many times we ssh to a remote server, then run psql
>> > locally.
>>
>>
>> That can be done with a named pipe ;->  ( or with an alias / function
>> using getopt to parse the options before forwarding them to psql ).
>> But, which just \sets $hostname in a var and uses it. )  Anyway, the
>> problem with this is that if you do \connect to another. You could do
>> something similar to this using only psql/psqlrc tricks:
>>
>> cdrs=> \set fecha `date`
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:23:22 CEST 2016
>>
>> Here you would use your script instead of fecha, and interpolate it
>> using %:fecha: in the prompt.
>>
>> And now the second step of the trick:
>> cdrs=> \set recalc '\\set fecha `date`'
>> cdrs=> \echo :recalc
>> \set fecha `date`
>> cdrs=> :recalc
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:24:07 CEST 2016
>> cdrs=> :recalc
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:24:16 CEST 2016
>>
>> Now you can use :recalc if you do connect to have the prompt updated.
>>
>> Anyway, TIMTOWTDI.
>>
>> > But again, I think the more elegant approach is to alter the %M logic.
>> > Any thoughts?
>>
>> At risk of being redundant, not altering %M, another %x better.
>>
>
> The various hacks appear to not deal with the fact that there may be
> multiple instances of postgresql running on different TCP ports or Unix
> connections nor with the fact that the local connection may, in fact, be a
> pooler and not a direct connection to the database.
>
> As long as we're into hack-land, I'll offer one.
>
> First, you abuse the custom variables feature in postgresql.conf and add
> something like:
> serverinfo.name = 'steve_test'
>
> Now you can read that info from any client:
> select current_setting('serverinfo.name');
> current_setting
> -----------------
> steve_test
>
> Next you update .psqlrc with something along the lines of:
>
> select current_setting('serverid.name') as server_name;
> \gset
> \set PROMPT1 'You are connected to ' :server_name '/%/%R%# '
>
> Then when you start psql you will see:
>
>  server_name
> -------------
> steve_test
> (1 row)
>
> psql (9.4.7)
> Type "help" for help.
>
> You are connected to steve_test/steve=>
>
> On the plus side, the custom GUC setting is available to any client, not
> just psql. It also handles multiple PostgreSQL instances and connections
> that are actually routed through a pooler.
>
> On the down side, it is a hack. The method is not in any way guaranteed to
> be future-proof. It leaves an ugly bit of server output at psql startup. It
> requires ensuring that all servers have the variable set appropriately. You
> need to avoid colliding with a custom GUC used by an extension. But perhaps
> it is useful.
>
> -Steve
>
>
>
>

When you connect through ssh, aren't  a set of system environment variables set?
I think you could use them if available as a first cheap option.
(those variables may depend on the ssh server/OS  or would not be
available if you change into another user environment after remote
login though).

I just checked, and from the info page for openssh
SSH_CONNECTION        Identifies the client and server ends of the con‐
                           nection.  The variable contains four space-sepa‐
                           rated values: client IP address, client port num‐
                           ber, server IP address, and server port number.

Just my 2 cents.