Psql meta-command conninfo+

On Tue, Feb 06, 2024 at 05:27:01PM +0000, Maiquel Grassi wrote:

I'm seeking to improve the \conninfo meta-command in psql. Currently, it
provides limited information about the current connection. I believe that
expanding it using the concept of "plus" [+] could ease the work of DBAs,
SysAdmins, DevOps, etc., who manage a large volume of databases and/or
multiple PostgreSQL servers. The objective of this enhancement is to
obtain quick information about the current connection (session). I
believe that for a PostgreSQL administrator, it is not feasible to write
a plpgsql function and apply it to all databases (for example, imagine
managing over 200 databases). I have an example on GitHub
https://github.com/maiquelgrassi/DBA-toolkit/blob/main/cluster/dba_whoami_function.sql
of a plpgsql function demonstrating exactly what I believe is impractical
for the daily routine of a PostgreSQL professional. I see psql's
meta-commands as significant allies in daily work in productive
environments.

This seems like a reasonable idea to me.

postgres=# \conninfo+
Current Connection Information
Attribute | Value
----------------+----------------
Database | postgres
User | postgres
Server Version | 16.1
Server Address | 192.168.0.5/32
Server Port | 5433
Client Address | 192.168.0.5/32
Client Port | 52716
Session PID | 21624
(8 rows)

My first reaction is that this should instead return a single row with the
same set of columns for any connection type (the not-applicable ones would
just be set to NULL). That would match the other meta-commands like \l and
\du, and you could still get an expanded display with \x if needed. Also,
I think it would simplify the code a bit.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On 2024-02-06 19:19 +0100, Nathan Bossart wrote:

On Tue, Feb 06, 2024 at 05:27:01PM +0000, Maiquel Grassi wrote:

postgres=# \conninfo+
Current Connection Information
Attribute | Value
----------------+----------------
Database | postgres
User | postgres
Server Version | 16.1
Server Address | 192.168.0.5/32
Server Port | 5433
Client Address | 192.168.0.5/32
Client Port | 52716
Session PID | 21624
(8 rows)

My first reaction is that this should instead return a single row with the
same set of columns for any connection type (the not-applicable ones would
just be set to NULL). That would match the other meta-commands like \l and
\du, and you could still get an expanded display with \x if needed. Also,
I think it would simplify the code a bit.

+1 for a single-row result and triggering expanded display with \x for
consistency with other commands.

--
Erik

On 2024-02-06 19:19 +0100, Nathan Bossart wrote:

On Tue, Feb 06, 2024 at 05:27:01PM +0000, Maiquel Grassi wrote:

postgres=# \conninfo+
Current Connection Information
Attribute | Value
----------------+----------------
Database | postgres
User | postgres
Server Version | 16.1
Server Address | 192.168.0.5/32
Server Port | 5433
Client Address | 192.168.0.5/32
Client Port | 52716
Session PID | 21624
(8 rows)

My first reaction is that this should instead return a single row with the
same set of columns for any connection type (the not-applicable ones would
just be set to NULL). That would match the other meta-commands like \l and
\du, and you could still get an expanded display with \x if needed. Also,
I think it would simplify the code a bit.

+1 for a single-row result and triggering expanded display with \x for
consistency with other commands.

--//--

I made the adjustment in the code and updated the patch. I believe this is the format suggested by you all. Would this be it?

[postgres@localhost bin]$ ./psql -h 192.168.0.220 -p 5433 -U postgres -d postgres
psql (17devel, server 16.1)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.0.220" at port "5433".
postgres=# \conninfo+
Current Connection Information
Attribute | Value
----------------+------------------
Database | postgres
User | postgres
Server Version | 16.1
Server Address | 192.168.0.220/32
Server Port | 5433
Client Address | 192.168.0.220/32
Client Port | 56606
Session PID | 2424
(8 rows)

[postgres@localhost bin]$ ./psql
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=# \conninfo+
Current Connection Information
Attribute | Value
----------------+----------
Database | postgres
User | postgres
Server Version | 17devel
Server Address |
Server Port | 5432
Client Address |
Client Port |
Session PID | 30216
(8 rows)

[postgres@localhost bin]$ ./psql -h localhost
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
postgres=# \conninfo+
Current Connection Information
Attribute | Value
----------------+----------
Database | postgres
User | postgres
Server Version | 17devel
Server Address | ::1/128
Server Port | 5432
Client Address | ::1/128
Client Port | 46872
Session PID | 30220
(8 rows)

[postgres@localhost bin]$ ./psql -h 127.0.0.1
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5432".
postgres=# \conninfo+
Current Connection Information
Attribute | Value
----------------+--------------
Database | postgres
User | postgres
Server Version | 17devel
Server Address | 127.0.0.1/32
Server Port | 5432
Client Address | 127.0.0.1/32
Client Port | 34924
Session PID | 30223
(8 rows)

Regards,
Maiquel O. Grassi.

On Tue, Feb 06, 2024 at 08:52:09PM +0000, Maiquel Grassi wrote:

I made the adjustment in the code and updated the patch. I believe this
is the format suggested by you all. Would this be it?

I was thinking something more like

SELECT pg_catalog.current_database() AS "Database",
current_user AS "User",
pg_catalog.current_setting('server_version') AS "Server Version",
pg_catalog.inet_server_addr() AS "Server Address",
pg_catalog.current_setting('port') AS "Port",
pg_catalog.inet_client_addr() AS "Client Address",
pg_catalog.inet_client_port() AS "Client Port",
pg_catalog.pg_backend_pid() AS "Session PID";

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Tue, Feb 06, 2024 at 03:06:05PM -0600, Nathan Bossart wrote:

On Tue, Feb 06, 2024 at 08:52:09PM +0000, Maiquel Grassi wrote:

I made the adjustment in the code and updated the patch. I believe this
is the format suggested by you all. Would this be it?

I was thinking something more like

SELECT pg_catalog.current_database() AS "Database",
current_user AS "User",
pg_catalog.current_setting('server_version') AS "Server Version",
pg_catalog.inet_server_addr() AS "Server Address",
pg_catalog.current_setting('port') AS "Port",
pg_catalog.inet_client_addr() AS "Client Address",
pg_catalog.inet_client_port() AS "Client Port",
pg_catalog.pg_backend_pid() AS "Session PID";

... although that seems to be missing items like the socket directory and
the host.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Tue, Feb 06, 2024 at 08:52:09PM +0000, Maiquel Grassi wrote:

I made the adjustment in the code and updated the patch. I believe this
is the format suggested by you all. Would this be it?

I was thinking something more like

SELECT pg_catalog.current_database() AS "Database",
current_user AS "User",
pg_catalog.current_setting('server_version') AS "Server Version",
pg_catalog.inet_server_addr() AS "Server Address",
pg_catalog.current_setting('port') AS "Port",
pg_catalog.inet_client_addr() AS "Client Address",
pg_catalog.inet_client_port() AS "Client Port",
pg_catalog.pg_backend_pid() AS "Session PID";

--//--

Good, I had misunderstood. I liked this adjustment. Now it truly aligns with the central idea of the other extended meta-commands.

[postgres@localhost bin]$ ./psql -h 192.168.0.220 -p 5433 -U postgres -d postgres
psql (17devel, server 16.1)
Type "help" for help.

postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Port | Client Address | Client Port | Session PID
----------+----------+----------------+----------------+------+----------------+-------------+-------------
postgres | postgres | 16.1 | 192.168.0.220 | 5433 | 192.168.0.220 | 57112 | 22120
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql
psql (17devel)
Type "help" for help.

postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Port | Client Address | Client Port | Session PID
----------+----------+----------------+----------------+------+----------------+-------------+-------------
postgres | postgres | 17devel | | 5432 | | | 31430
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h localhost
psql (17devel)
Type "help" for help.

postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Port | Client Address | Client Port | Session PID
----------+----------+----------------+----------------+------+----------------+-------------+-------------
postgres | postgres | 17devel | ::1 | 5432 | ::1 | 46918 | 31433
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h 127.0.0.1
psql (17devel)
Type "help" for help.

postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Port | Client Address | Client Port | Session PID
----------+----------+----------------+----------------+------+----------------+-------------+-------------
postgres | postgres | 17devel | 127.0.0.1 | 5432 | 127.0.0.1 | 34970 | 31435
(1 row)

Regards,
Maiquel O. Grassi.

On Tue, Feb 06, 2024 at 03:06:05PM -0600, Nathan Bossart wrote:

On Tue, Feb 06, 2024 at 08:52:09PM +0000, Maiquel Grassi wrote:

I made the adjustment in the code and updated the patch. I believe this
is the format suggested by you all. Would this be it?

I was thinking something more like

SELECT pg_catalog.current_database() AS "Database",
current_user AS "User",
pg_catalog.current_setting('server_version') AS "Server Version",
pg_catalog.inet_server_addr() AS "Server Address",
pg_catalog.current_setting('port') AS "Port",
pg_catalog.inet_client_addr() AS "Client Address",
pg_catalog.inet_client_port() AS "Client Port",
pg_catalog.pg_backend_pid() AS "Session PID";

... although that seems to be missing items like the socket directory and
the host.

--//--

My initial idea has always been that they should continue to appear because \conninfo+ should show all the things that \conninfo shows and add more information. I think that's the purpose of the 'plus.' Now we're on a better path than the initial one. We can still add the socket directory and the host.

Regards,
Maiquel O. Grassi.

On Tue, Feb 06, 2024 at 09:45:54PM +0000, Maiquel Grassi wrote:

My initial idea has always been that they should continue to appear
because \conninfo+ should show all the things that \conninfo shows and
add more information. I think that's the purpose of the 'plus.' Now we're
on a better path than the initial one. We can still add the socket
directory and the host.

Agreed.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Tue, Feb 06, 2024 at 09:45:54PM +0000, Maiquel Grassi wrote:

My initial idea has always been that they should continue to appear
because \conninfo+ should show all the things that \conninfo shows and
add more information. I think that's the purpose of the 'plus.' Now we're
on a better path than the initial one. We can still add the socket
directory and the host.

Agreed.

--//--

I believe it's resolved reasonably well this way:

SELECT
pg_catalog.current_database() AS "Database",
current_user AS "User",
pg_catalog.current_setting('server_version') AS "Server Version",
CASE
WHEN pg_catalog.inet_server_addr() IS NULL
THEN 'NULL'
ELSE pg_catalog.inet_server_addr()::text
END AS "Server Address",
pg_catalog.current_setting('port') AS "Port",
CASE
WHEN pg_catalog.inet_client_addr() IS NULL
THEN 'NULL'
ELSE pg_catalog.inet_client_addr()::text
END AS "Client Address",
CASE
WHEN pg_catalog.inet_client_port() IS NULL
THEN 'NULL'
ELSE pg_catalog.inet_client_port()::text
END AS "Client Port",
pg_catalog.pg_backend_pid() AS "Session PID",
CASE
WHEN pg_catalog.current_setting('unix_socket_directories') = ''
THEN 'NULL'
ELSE pg_catalog.current_setting('unix_socket_directories')
END AS "Socket Directory",
CASE
WHEN
pg_catalog.inet_server_addr() IS NULL
AND pg_catalog.inet_client_addr() IS NULL
THEN 'NULL'
WHEN
pg_catalog.inet_server_addr() = pg_catalog.inet_client_addr()
THEN 'localhost'
ELSE pg_catalog.inet_server_addr()::text
END AS "Host";

See below the tests:

[postgres@localhost bin]$ ./psql
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+----------+----------------+----------------+------+----------------+-------------+-------------+------------------+------
postgres | postgres | 17devel | NULL | 5432 | NULL | NULL | 14348 | /tmp | NULL
(1 row)

[postgres@localhost bin]$ ./psql -h localhost
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+----------+----------------+----------------+------+----------------+-------------+-------------+------------------+-----------
postgres | postgres | 17devel | ::1/128 | 5432 | ::1/128 | 46988 | 14353 | /tmp | localhost
(1 row)

[postgres@localhost bin]$ ./psql -h ::1
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "::1" at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+----------+----------------+----------------+------+----------------+-------------+-------------+------------------+-----------
postgres | postgres | 17devel | ::1/128 | 5432 | ::1/128 | 46990 | 14356 | /tmp | localhost
(1 row)

[postgres@localhost bin]$ ./psql -h 127.0.0.1
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+----------+----------------+----------------+------+----------------+-------------+-------------+------------------+-----------
postgres | postgres | 17devel | 127.0.0.1/32 | 5432 | 127.0.0.1/32 | 35042 | 14359 | /tmp | localhost
(1 row)

[postgres@localhost bin]$ ./psql -h 192.168.0.5 -p 5433 -U postgres -d postgres
psql (17devel, server 16.1)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.0.5" at port "5433".
postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+----------+----------------+----------------+------+----------------+-------------+-------------+------------------+-----------
postgres | postgres | 16.1 | 192.168.0.5/32 | 5433 | 192.168.0.5/32 | 52783 | 18212 | NULL | localhost
(1 row)

Regards,
Maiquel O. Grassi.

On 2024-02-07 05:13 +0100, Maiquel Grassi wrote:

On Tue, Feb 06, 2024 at 09:45:54PM +0000, Maiquel Grassi wrote:

My initial idea has always been that they should continue to appear
because \conninfo+ should show all the things that \conninfo shows and
add more information. I think that's the purpose of the 'plus.' Now we're
on a better path than the initial one. We can still add the socket
directory and the host.

Agreed.

--//--

I believe it's resolved reasonably well this way:

SELECT
pg_catalog.current_database() AS "Database",
current_user AS "User",
pg_catalog.current_setting('server_version') AS "Server Version",
CASE
WHEN pg_catalog.inet_server_addr() IS NULL
THEN 'NULL'
ELSE pg_catalog.inet_server_addr()::text
END AS "Server Address",

Should be NULL instead of string 'NULL'. So the entire CASE expression
is redundant and you can just return pg_catalog.inet_server_addr().

pg_catalog.current_setting('port') AS "Port",
CASE
WHEN pg_catalog.inet_client_addr() IS NULL
THEN 'NULL'
ELSE pg_catalog.inet_client_addr()::text
END AS "Client Address",
CASE
WHEN pg_catalog.inet_client_port() IS NULL
THEN 'NULL'
ELSE pg_catalog.inet_client_port()::text
END AS "Client Port",

Same here.

pg_catalog.pg_backend_pid() AS "Session PID",
CASE
WHEN pg_catalog.current_setting('unix_socket_directories') = ''
THEN 'NULL'
ELSE pg_catalog.current_setting('unix_socket_directories')
END AS "Socket Directory",

The CASE expression can be simplified to:

nullif(pg_catalog.current_setting('unix_socket_directories'), '')

CASE
WHEN
pg_catalog.inet_server_addr() IS NULL
AND pg_catalog.inet_client_addr() IS NULL
THEN 'NULL'
WHEN
pg_catalog.inet_server_addr() = pg_catalog.inet_client_addr()
THEN 'localhost'

Is it safe to assume localhost here? \conninfo prints localhost only
when I connect with psql -hlocalhost:

$ psql -hlocalhost postgres
psql (16.1)
postgres=# \conninfo
You are connected to database "postgres" as user "ewie" on host "localhost" (address "::1") at port "5432".
postgres=# \q

$ psql -h127.0.0.1 postgres
psql (16.1)
postgres=# \conninfo
You are connected to database "postgres" as user "ewie" on host "127.0.0.1" at port "5432".

ELSE pg_catalog.inet_server_addr()::text
END AS "Host";

--
Erik

This is a good idea about extended connection info.

On 07.02.2024 07:13, Maiquel Grassi wrote:

SELECT
...
current_user AS "User",

This will be inconsistent with \conninfo.
\conninfo returns authenticated user (PQuser), not the current_user.
It might be worth showing current_user, session_user, and authenticated user,
but I can't find the appropriate sql function for PQuser.

What about to include system_user function? It shows useful authentication details.

Also, it seems that the verbose parameter in the listConnectionInformation
is unnecessary.

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

On 2024-02-07 05:13 +0100, Maiquel Grassi wrote:

On Tue, Feb 06, 2024 at 09:45:54PM +0000, Maiquel Grassi wrote:

My initial idea has always been that they should continue to appear
because \conninfo+ should show all the things that \conninfo shows and
add more information. I think that's the purpose of the 'plus.' Now we're
on a better path than the initial one. We can still add the socket
directory and the host.

Agreed.

--//--

I believe it's resolved reasonably well this way:

SELECT
pg_catalog.current_database() AS "Database",
current_user AS "User",
pg_catalog.current_setting('server_version') AS "Server Version",
CASE
WHEN pg_catalog.inet_server_addr() IS NULL
THEN 'NULL'
ELSE pg_catalog.inet_server_addr()::text
END AS "Server Address",

Should be NULL instead of string 'NULL'. So the entire CASE expression
is redundant and you can just return pg_catalog.inet_server_addr().

pg_catalog.current_setting('port') AS "Port",
CASE
WHEN pg_catalog.inet_client_addr() IS NULL
THEN 'NULL'
ELSE pg_catalog.inet_client_addr()::text
END AS "Client Address",
CASE
WHEN pg_catalog.inet_client_port() IS NULL
THEN 'NULL'
ELSE pg_catalog.inet_client_port()::text
END AS "Client Port",

Same here.

pg_catalog.pg_backend_pid() AS "Session PID",
CASE
WHEN pg_catalog.current_setting('unix_socket_directories') = ''
THEN 'NULL'
ELSE pg_catalog.current_setting('unix_socket_directories')
END AS "Socket Directory",

The CASE expression can be simplified to:

nullif(pg_catalog.current_setting('unix_socket_directories'), '')

CASE
WHEN
pg_catalog.inet_server_addr() IS NULL
AND pg_catalog.inet_client_addr() IS NULL
THEN 'NULL'
WHEN
pg_catalog.inet_server_addr() = pg_catalog.inet_client_addr()
THEN 'localhost'

Is it safe to assume localhost here? \conninfo prints localhost only
when I connect with psql -hlocalhost:

$ psql -hlocalhost postgres
psql (16.1)
postgres=# \conninfo
You are connected to database "postgres" as user "ewie" on host "localhost" (address "::1") at port "5432".
postgres=# \q

$ psql -h127.0.0.1 postgres
psql (16.1)
postgres=# \conninfo
You are connected to database "postgres" as user "ewie" on host "127.0.0.1" at port "5432".

ELSE pg_catalog.inet_server_addr()::text
END AS "Host";

--//--

There really was no need for the CASES. However, they helped visualize the psql output since for the null value, no word is printed on the screen. I made the adjustment by removing this redundancy.

Regarding the "Host" column, the most reliable way to solve this, I believe, is by using the "host" variable. So it's necessary to declare char *host = PQhost(pset.db); in listConnectionInformation() and use it in the SQL (see patch v5). This way, we have the same return from \conninfo reliably.

Once again, I ran a series of tests.

[postgres@localhost bin]$ ./psql
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+----------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+------
postgres | postgres | 17devel | | 5432 | | | 15898 | /tmp |
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h localhost
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+----------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+-----------
postgres | postgres | 17devel | ::1 | 5432 | ::1 | 47012 | 15900 | /tmp | localhost
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h ::1
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "::1" at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+----------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+------
postgres | postgres | 17devel | ::1 | 5432 | ::1 | 47014 | 15905 | /tmp | ::1
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h 127.0.0.1
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+----------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+-----------
postgres | postgres | 17devel | 127.0.0.1 | 5432 | 127.0.0.1 | 35066 | 15908 | /tmp | 127.0.0.1
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h 192.168.0.5 -p 5432 -d postgres -U postgres
psql (17devel, server 14.3)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.0.5" at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+----------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+-------------
postgres | postgres | 14.3 | 192.168.0.5 | 5432 | 192.168.0.5 | 60904 | 29264 | | 192.168.0.5

Regards,
Maiquel O. Grassi.

This is a good idea about extended connection info.

On 07.02.2024 07:13, Maiquel Grassi wrote:
SELECT
...
current_user AS "User",

This will be inconsistent with \conninfo.
\conninfo returns authenticated user (PQuser), not the current_user.
It might be worth showing current_user, session_user, and authenticated user,
but I can't find the appropriate sql function for PQuser.

What about to include system_user function? It shows useful authentication details.

Also, it seems that the verbose parameter in the listConnectionInformation
is unnecessary.

--//--

Hi,

Tks Pavel.

Analyzing the functions' code more thoroughly, it seems to make more sense.
I liked your suggestions and implemented them for validation.
Regarding "system_user," I believe it is valid and also added it to the row.

"Also, it seems that the verbose parameter in the listConnectionInformation is unnecessary."
Could you point out exactly the line or code snippet you are referring to?

To print the string from the "Authenticated User" column, I chose to use PQuser(pset.db) directly. I did the same for the "Host" column, opting for PQhost(pset.db). This does not contradict the result of \conninfo.

Here are the tests as usual, and v6 patch.

[postgres@localhost bin]$ ./psql
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | Authenticated User | Current User | Session User | System User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+--------------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+------
postgres | postgres | postgres | postgres | | 17devel | | 5432 | | | 17240 | /tmp |
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h ::1
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "::1" at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | Authenticated User | Current User | Session User | System User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+--------------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+------
postgres | postgres | postgres | postgres | | 17devel | ::1 | 5432 | ::1 | 47024 | 17242 | /tmp | ::1
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h 127.0.0.1
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | Authenticated User | Current User | Session User | System User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+--------------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+-----------
postgres | postgres | postgres | postgres | | 17devel | 127.0.0.1 | 5432 | 127.0.0.1 | 35076 | 17245 | /tmp | 127.0.0.1
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h localhost
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | Authenticated User | Current User | Session User | System User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+--------------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+-----------
postgres | postgres | postgres | postgres | | 17devel | ::1 | 5432 | ::1 | 47028 | 17248 | /tmp | localhost
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h 192.168.0.5 -p 5433 -d postgres -U postgres
psql (17devel, server 16.1)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.0.5" at port "5433".
postgres=# \conninfo+
Current Connection Information
Database | Authenticated User | Current User | Session User | System User | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+--------------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+-------------
postgres | postgres | postgres | postgres | | 16.1 | 192.168.0.5 | 5433 | 192.168.0.5 | 60115 | 28896 | | 192.168.0.5
(1 row)

Regards,
Maiquel O. Grassi.

Hi,Maiquel!

On 07.02.2024 17:47, Maiquel Grassi wrote:

"Also, it seems that the verbose parameter in the
listConnectionInformation is unnecessary." Could you point out exactly
the line or code snippet you are referring to?

+bool
+listConnectionInformation(const char *pattern,*bool verbose*)

I mean that parameter verbose is not used in the function body and listConnectionInformation called only in verbose mode.

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Hi, Maiquel!

On 07.02.2024 17:47, Maiquel Grassi wrote:

"Also, it seems that the verbose parameter in the listConnectionInformation is unnecessary."
Could you point out exactly the line or code snippet you are referring to?

+bool
+listConnectionInformation(const char *pattern, bool verbose)

I mean that parameter verbose is not used in the function body and listConnectionInformation called only in verbose mode.

--//--

There really was no need for the bool verbose. Therefore, it was removed.
Regarding the "system_user" function, as it is relatively new, I added
the necessary handling to avoid conflicts with versions lower than version 16.

I believe in v7 patch we have a quite substantial meta-command feature.

I validated the "System User" column for three versions. This way, we have a sample:

[postgres@localhost bin]$ ./psql -h 192.168.0.5 -p 5432 -U postgres -d postgres

psql (17devel, server 14.3)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.0.5" at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | Authenticated User | Current User | Session User | Session PID | Server Version | Server Address | Server Port | Client Address | Client Port | Socket Directory | Host
----------+--------------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+------------------+-------------
postgres | postgres | postgres | postgres | 9008 | 14.3 | 192.168.0.5 | 5432 | 192.168.0.5 | 63631 | | 192.168.0.5
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h 192.168.0.5 -p 5433 -U postgres -d postgres
psql (17devel, server 16.1)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.0.5" at port "5433".
postgres=# \conninfo+
Current Connection Information
Database | Authenticated User | System User | Current User | Session User | Session PID | Server Version | Server Address | Server Port | Client Address | Client Port | Socket Directory | Host
----------+--------------------+-------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+------------------+-------------
postgres | postgres | | postgres | postgres | 3348 | 16.1 | 192.168.0.5 | 5433 | 192.168.0.5 | 63633 | | 192.168.0.5
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h localhost
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | Authenticated User | System User | Current User | Session User | Session PID | Server Version | Server Address | Server Port | Client Address | Client Port | Socket Directory | Host
----------+--------------------+-------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+------------------+-----------
postgres | postgres | | postgres | postgres | 26147 | 17devel | ::1 | 5432 | ::1 | 47466 | /tmp | localhost
(1 row)

Regards,
Maiquel O. Grassi.

Hi,

On 07.02.2024 23:13, Maiquel Grassi wrote:

Regarding the "system_user" function, as it is relatively new, I added
the necessary handling to avoid conflicts with versions lower than
version 16.

Yes, that's rights.

A couple of doubts about the implementation details.
But keep in mind that I'm not very good at programming in the C language.
I hope for the help of more experienced developers.

1.
+			if (db == NULL)
+				printf(_("You are currently not connected to a database.\n"));

This check is performed for \conninfo, but not for \conninfo+.

2.
Some values (address, socket) are evaluated separately for \conninfo
(via C functions) and for \conninfo+ (via sql functions).
It may be worth evaluating them in one place. But I'm not sure about that.

The final version of the patch will require changes to the documentation and tests.

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Hi Maiquel

On 07.02.24 21:13, Maiquel Grassi wrote:

I believe in v7 patch we have a quite substantial meta-command feature.

Thanks for implementing this. I find it very handy.

I was just wondering if a "permission denied" error for non-superusers
is the best choice for "\conninfo+":

postgres=> \conninfo+
ERROR:  permission denied to examine "unix_socket_directories"
DETAIL:  Only roles with privileges of the "pg_read_all_settings" role
may examine this parameter.

.. since it is not the case with "\conninfo":

postgres=> \conninfo
You are connected to database "postgres" as user "jim" via socket in
"/tmp" at port "5432".

Perhaps excluding the column from the result set or returning NULL in
the affected columns would be less confusing?

There are also some indentation issues in your patch:

/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:142:
indent with spaces.
    PGresult   *res;
/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:143:
indent with spaces.
    PQExpBufferData buf;
/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:144:
indent with spaces.
    printQueryOpt myopt = pset.popt;
/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:146:
indent with spaces.
    initPQExpBuffer(&buf);
/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:148:
indent with spaces.
    printfPQExpBuffer(&buf,
warning: squelched 34 whitespace errors
warning: 39 lines add whitespace errors.

Looking forward to see the documentation and tests!

--
Jim

Hi,

On 07.02.2024 23:13, Maiquel Grassi wrote:

Regarding the "system_user" function, as it is relatively new, I added
the necessary handling to avoid conflicts with versions lower than version 16.

Yes, that's rights.

A couple of doubts about the implementation details.
But keep in mind that I'm not very good at programming in the C language.
I hope for the help of more experienced developers.

1.
+                     if (db == NULL)
+                             printf(_("You are currently not connected to a database.\n"));

This check is performed for \conninfo, but not for \conninfo+.

2.
Some values (address, socket) are evaluated separately for \conninfo
(via C functions) and for \conninfo+ (via sql functions).
It may be worth evaluating them in one place. But I'm not sure about that.

The final version of the patch will require changes to the documentation and tests.

--//--

Hi Pavel,

First of all, thank you very much for your observations.

1. The connection check for the case of \conninfo+ is handled by "describe.c" itself since it deals with queries. I might be mistaken, but I believe that by using "printQuery()" via "describe.c", this is already ensured, and there is no need to evaluate the connection status.

2. I believe that by implementing the evaluations separately as they are, it becomes easier to perform future maintenance by minimizing the mixing of C code with SQL code as much as possible. However, certainly, the possibility of a better suggestion than mine remains open.

Regards,
Maiquel O. Grassi.

On 07.02.24 21:13, Maiquel Grassi wrote:

I believe in v7 patch we have a quite substantial meta-command feature.

Thanks for implementing this. I find it very handy.

I was just wondering if a "permission denied" error for non-superusers
is the best choice for "\conninfo+":

postgres=> \conninfo+
ERROR: permission denied to examine "unix_socket_directories"
DETAIL: Only roles with privileges of the "pg_read_all_settings" role
may examine this parameter.

.. since it is not the case with "\conninfo":

postgres=> \conninfo
You are connected to database "postgres" as user "jim" via socket in
"/tmp" at port "5432".

Perhaps excluding the column from the result set or returning NULL in
the affected columns would be less confusing?

There are also some indentation issues in your patch:

/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:142:
indent with spaces.
PGresult *res;
/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:143:
indent with spaces.
PQExpBufferData buf;
/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:144:
indent with spaces.
printQueryOpt myopt = pset.popt;
/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:146:
indent with spaces.
initPQExpBuffer(&buf);
/home/jim/Downloads/v7-0001-psql-meta-command-conninfo-plus.patch:148:
indent with spaces.
printfPQExpBuffer(&buf,
warning: squelched 34 whitespace errors
warning: 39 lines add whitespace errors.

Looking forward to see the documentation and tests!

--//--

Hi Jim,
Thank you for your support on this patch!
As I believe in its usability, I have been dedicating efforts to make it really interesting.
I hadn't thought about the permissioning issue for "unix_socket_directories". I appreciate that.
I thought about solving this situation using the same approach as \conninfo. I added the validation if (is_unixsock_path(host) && !(hostaddr && *hostaddr)) in the SQL part along with an "append". In case of a negative result, another "append" adds NULL.
Regarding the whitespace issue, before generating v8 patch file, I used pgindent to adjust each modified file. I believe it should be ok now. If you could verify, I'd be grateful.
Below are the tests after adjusting for the permissioning issues:

[postgres@localhost bin]$ ./psql
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | Authenticated User | System User | Current User | Session User | Session PID | Server Version | Server Address | Server Port | Client Address | Client Port | Socket Directory | Host
----------+--------------------+-------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+------------------+------
postgres | postgres | | postgres | postgres | 31479 | 17devel | | 5432 | | | /tmp |
(1 row)

postgres=# CREATE USER maiquel;
CREATE ROLE
postgres=# \q
[postgres@localhost bin]$ ./psql -U maiquel -d postgres
psql (17devel)
Type "help" for help.

postgres=> \conninfo
You are connected to database "postgres" as user "maiquel" via socket in "/tmp" at port "5432".
postgres=> \conninfo+
Current Connection Information
Database | Authenticated User | System User | Current User | Session User | Session PID | Server Version | Server Address | Server Port | Client Address | Client Port | Socket Directory | Host
----------+--------------------+-------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+------------------+------
postgres | maiquel | | maiquel | maiquel | 31482 | 17devel | | 5432 | | | /tmp |
(1 row)

postgres=> \q
[postgres@localhost bin]$ ./psql -h localhost -U maiquel -d postgres
psql (17devel)
Type "help" for help.

postgres=> \conninfo
You are connected to database "postgres" as user "maiquel" on host "localhost" (address "::1") at port "5432".
postgres=> \conninfo+
Current Connection Information
Database | Authenticated User | System User | Current User | Session User | Session PID | Server Version | Server Address | Server Port | Client Address | Client Port | Socket Directory | Host
----------+--------------------+-------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+------------------+-----------
postgres | maiquel | | maiquel | maiquel | 31485 | 17devel | ::1 | 5432 | ::1 | 47482 | | localhost
(1 row)

postgres=> \q
[postgres@localhost bin]$ ./psql -h localhost
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
postgres=# \conninfo+
Current Connection Information
Database | Authenticated User | System User | Current User | Session User | Session PID | Server Version | Server Address | Server Port | Client Address | Client Port | Socket Directory | Host
----------+--------------------+-------------+--------------+--------------+-------------+----------------+----------------+-------------+----------------+-------------+------------------+-----------
postgres | postgres | | postgres | postgres | 31488 | 17devel | ::1 | 5432 | ::1 | 47484 | | localhost
(1 row)

Regards,
Maiquel.