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
Show quoted text
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=>
\conninfo will show you desired details
Regards,
Ganesh Korde.
On Wed, 18 May 2022, 3:38 pm Dominique Devienne, <ddevienne@gmail.com>
wrote:
Show quoted text
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 Wed, May 18, 2022 at 3:08 AM Dominique Devienne <ddevienne@gmail.com>
wrote:
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.
The server has no clue how the values sent to it came into existence - nor
should it.
Whether and how any particular client might expose this kind of debugging
information (or upgrade it to proper state info) is up to the client. I do
not know what options psql offers.
David J.
On Wed, May 18, 2022 at 5:43 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Wed, May 18, 2022 at 3:08 AM Dominique Devienne <ddevienne@gmail.com>
wrote: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.The server has no clue how the values sent to it came into existence - nor
should it.Whether and how any particular client might expose this kind of debugging
information (or upgrade it to proper state info) is up to the client. I do
not know what options psql offers.
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.
Show quoted text
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