Thread: Who am I? Where am I connected?

LibPQ has various defaults for the host, user, and DB name.
There's also the password file, the service file and service name.
In the example below, I can connect with a "naked" psql invocation.

Once connected, can I find out all aspects of the connection string?
Or where they came from, like a pgpass.conf or service file?

How to get the host, port, db name once connected?
SHOW and pg_settings does not appear to be it, at first glance.

Thanks, --DD

c:\Users\ddevienne>psql
psql (12.1, server 14.2)
WARNING: psql major version 12, server major version 14.
         Some psql features might not work.
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

ddevienne=>

Hi Dominique,

you can use \conninfo in psql to show the database, user, host (or socket in my example), and port:

ewie@desktop ~ $ psql test
Null display is "".
psql (14.3)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "ewie" via socket in "/run/postgresql" at port "5432".


    - Erik


> On 18/05/2022 12:07 Dominique Devienne <ddevienne@gmail.com> wrote:
> 
>  
> LibPQ has various defaults for the host, user, and DB name.
> There's also the password file, the service file and service name.
> In the example below, I can connect with a "naked" psql invocation.
> 
> Once connected, can I find out all aspects of the connection string?
> Or where they came from, like a pgpass.conf or service file?
> 
> How to get the host, port, db name once connected?
> SHOW and pg_settings does not appear to be it, at first glance.
> 
> Thanks, --DD
> 
> c:\Users\ddevienne>psql
> psql (12.1, server 14.2)
> WARNING: psql major version 12, server major version 14.
>          Some psql features might not work.
> WARNING: Console code page (437) differs from Windows code page (1252)
>          8-bit characters might not work correctly. See psql reference
>          page "Notes for Windows users" for details.
> Type "help" for help.
> 
> ddevienne=>

## Dominique Devienne (ddevienne@gmail.com):

> Once connected, can I find out all aspects of the connection string?

\conninfo in psql (pro tip: \? actually helps), "Connection Status
Functions" https://www.postgresql.org/docs/current/libpq-status.html
in libpq; and in a pinch you could find your connection in
pg_stat_activity (pid = pg_backend_pid()) plus some assorted queries
for other details. You cannot get actual secrets like your private
ssl key or your password (the server most likely doesn't have that,
and the client assumes that you have it).

Regards,
Christoph

-- 
Spare Space

On Wed, May 18, 2022 at 12:07 PM Dominique Devienne <ddevienne@gmail.com> wrote:
> LibPQ has various defaults for the host, user, and DB name.
> There's also the password file, the service file and service name.
> In the example below, I can connect with a "naked" psql invocation.
>
> Once connected, can I find out all aspects of the connection string?

Thank you all for \conninfo.

I was more thinking at the time about the SQL-way to get that info,
not the psql way.
But thanks to https://www.postgresql.org/docs/current/functions-info.html
I managed
to emulate it, modulo resolving the server's IP into a hostname.

ddevienne=> \conninfo
You are connected to database "ddevienne" as user "ddevienne" on host
"localhost" (address "::1") at port "5432".

ddevienne=> select current_database() || ' ' || session_user || ' ' ||
inet_server_addr() || ':' || inet_server_port();
             ?column?
----------------------------------
 ddevienne ddevienne ::1/128:5432
(1 row)

> Or where they came from, like a pgpass.conf or service file?

OTOH, no one replied to that part of the question.

How to know if the user or database name was defaulted?
Or came from a service-file, using a given service name?
Is there no way, except by reverse-engineering the logic of the
env-vars and built-in defaults?

On 5/18/22 14:59, Dominique Devienne wrote:
> 

> 
> AFAIK, it’s not psql that does this though, it’s libpq the official 
> client api.
> And the fact libpq has no way to surface that info seems like an 
> important oversight.
> 

Not all clients use libpq e.g. the Postgres JDBC driver. It just uses 
the Postgres  Frontend/Backend protocol:

https://www.postgresql.org/docs/current/protocol.html


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Dominique Devienne <ddevienne@gmail.com> writes:
> AFAIK, it’s not psql that does this though, it’s libpq the official client
> api.
> And the fact libpq has no way to surface that info seems like an important
> oversight.

PQconninfo() will show you all the option values in use by a connection
object.  It's true that it's not very easy to tell where any given value
came from, but I doubt it'd be worth an ABI break to add such info.
(You could at least recognize values that match the hard-wired default
or environment variable value.)

            regards, tom lane