Thread
Commits
-
psql: Change new \conninfo to use SSL instead of TLS
- 6d6480066c1a 18.0 landed
-
Change \conninfo to use tabular format
- bba2fbc6238b 18.0 landed
-
Update extension lookup routines to use the syscache
- d8df7ac5c04c 18.0 cited
-
Improve COPY TO performance when server and client encodings match
- b619852086ed 17.0 cited
-
doc: Remove superfluous bracket in synopsis
- fdfb92c0307c 17.0 cited
-
Remove psql support for server versions preceding 9.2.
- cf0cab868aa4 15.0 cited
-
Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-06T17:27:01Z
Hi, 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. Note: As this is a prototype, I will adjust the rest (documentation, tests, etc.) once an agreement is reached. Use cases for both the current and improved command bellow. Connection 1 ("remote server"): [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 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) Connection 2 (socket): [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 ------------------+----------------------- Info | Connected via socket! Database | postgres User | postgres Socket Directory | /tmp Server Version | 17devel Server Port | 5432 Session PID | 27586 (7 rows) Connection 3 (localhost): [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 Host | localhost Server Version | 17devel Server Address | ::1/128 Server Port | 5432 Client Address | ::1/128 Client Port | 46824 Session PID | 27598 (9 rows) Connection 4 (127.0.0.1): [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 | 34876 Session PID | 27624 (8 rows) Regards, Maiquel O. Grassi. -
Re: Psql meta-command conninfo+
Nathan Bossart <nathandbossart@gmail.com> — 2024-02-06T18:19:19Z
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
-
Re: Psql meta-command conninfo+
Erik Wienhold <ewie@ewie.name> — 2024-02-06T19:10:54Z
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
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-06T20:52:09Z
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.
-
Re: Psql meta-command conninfo+
Nathan Bossart <nathandbossart@gmail.com> — 2024-02-06T21:06:05Z
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 -
Re: Psql meta-command conninfo+
Nathan Bossart <nathandbossart@gmail.com> — 2024-02-06T21:12:05Z
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 -
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-06T21:39:20Z
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. -
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-06T21:45:54Z
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. -
Re: Psql meta-command conninfo+
Nathan Bossart <nathandbossart@gmail.com> — 2024-02-06T21:50:22Z
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
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-07T04:13:40Z
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. -
Re: Psql meta-command conninfo+
Erik Wienhold <ewie@ewie.name> — 2024-02-07T09:54:21Z
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 -
Re: Psql meta-command conninfo+
Pavel Luzanov <p.luzanov@postgrespro.ru> — 2024-02-07T12:35:34Z
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
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-07T12:41:56Z
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. -
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-07T14:47:31Z
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. -
Re: Psql meta-command conninfo+
Pavel Luzanov <p.luzanov@postgrespro.ru> — 2024-02-07T16:40:28Z
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
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-07T20:13:15Z
> 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. -
Re: Psql meta-command conninfo+
Pavel Luzanov <p.luzanov@postgrespro.ru> — 2024-02-08T11:01:55Z
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 -
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-02-08T11:01:55Z
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
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-08T14:58:19Z
> 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. -
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-08T15:50:20Z
> 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. -
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-02-08T19:37:38Z
On 08.02.24 16:50, Maiquel Grassi wrote: > 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. v8 no longer throws a permission denied error for non-superusers - it is IMHO much nicer this way. $ /usr/local/postgres-dev/bin/psql postgres -p 5432 -h 127.0.0.1 -U jim psql (17devel) Type "help" for help. postgres=# \x Expanded display is on. postgres=# \conninfo+ Current Connection Information -[ RECORD 1 ]------+---------- Database | postgres Authenticated User | jim System User | Current User | jim Session User | jim Session PID | 1321493 Server Version | 17devel Server Address | 127.0.0.1 Server Port | 5432 Client Address | 127.0.0.1 Client Port | 49366 Socket Directory | Host | 127.0.0.1 postgres=# SET ROLE foo; SET postgres=> \conninfo+ Current Connection Information -[ RECORD 1 ]------+---------- Database | postgres Authenticated User | jim System User | Current User | foo Session User | jim Session PID | 1321493 Server Version | 17devel Server Address | 127.0.0.1 Server Port | 5432 Client Address | 127.0.0.1 Client Port | 49366 Socket Directory | Host | 127.0.0.1 The patch now applies cleanly. One thing I just noticed. The psql autocomplete feature does not suggest the new + option of \conninfo. For instance, when typing "\connin[TAB]" it automatically autocompletes to "\conninfo ". I guess it should also be included in this patch. I can do a more thorough review of the code when you add the documentation and tests to the patch. Thanks! -- Jim
-
Re: Psql meta-command conninfo+
Erik Wienhold <ewie@ewie.name> — 2024-02-08T20:37:54Z
On 2024-02-08 20:37 +0100, Jim Jones wrote: > One thing I just noticed. The psql autocomplete feature does not suggest > the new + option of \conninfo. For instance, when typing "\connin[TAB]" > it automatically autocompletes to "\conninfo ". I guess it should also > be included in this patch. Modifiers such as + or S in \dS are not covered by autocompletion. src/bin/psql/tab-complete.c only specifies backslash commands in their basic form (without modifiers). (\dS<TAB> actually autocompletes to \ds to my surprise) > I can do a more thorough review of the code when you add the > documentation and tests to the patch. I noticed that the pattern parameter in listConnectionInformation is unused. exec_command_conninfo scans the pattern but \conninfo should not accept any argument. So the pattern can be removed entirely. -- Erik
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-08T20:47:44Z
> v8 no longer throws a permission denied error for non-superusers - it is > IMHO much nicer this way. > > $ /usr/local/postgres-dev/bin/psql postgres -p 5432 -h 127.0.0.1 -U jim > psql (17devel) > Type "help" for help. > > postgres=# \x > Expanded display is on. > postgres=# \conninfo+ > Current Connection Information > -[ RECORD 1 ]------+---------- > Database | postgres > Authenticated User | jim > System User | > Current User | jim > Session User | jim > Session PID | 1321493 > Server Version | 17devel > Server Address | 127.0.0.1 > Server Port | 5432 > Client Address | 127.0.0.1 > Client Port | 49366 > Socket Directory | > Host | 127.0.0.1 > > postgres=# SET ROLE foo; > SET > postgres=> \conninfo+ > Current Connection Information > -[ RECORD 1 ]------+---------- > Database | postgres > Authenticated User | jim > System User | > Current User | foo > Session User | jim > Session PID | 1321493 > Server Version | 17devel > Server Address | 127.0.0.1 > Server Port | 5432 > Client Address | 127.0.0.1 > Client Port | 49366 > Socket Directory | > Host | 127.0.0.1 > > The patch now applies cleanly. > > One thing I just noticed. The psql autocomplete feature does not suggest > the new + option of \conninfo. For instance, when typing "\connin[TAB]" > it automatically autocompletes to "\conninfo ". I guess it should also > be included in this patch. > > I can do a more thorough review of the code when you add the > documentation and tests to the patch. > > Thanks! --//-- Hi Jim, It's not a psql standard to use tab-complete for commands ending with +. You can verify this in the /* psql's backslash commands. */ section of the file "/src/bin/psql/tab-complete.c". https://github.com/postgres/postgres/blob/fdfb92c0307c95eba10854196628d88e6708901e/src/bin/psql/tab-complete.c In v9, I started the documentation work and am open to suggestions. > "I can do a more thorough review of the code when you add the > documentation and tests to the patch." Soon I'll be developing the tests. And that will be welcome. Thanks a lot! Regards, Maiquel Grassi.
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-08T22:28:29Z
>On 2024-02-08 20:37 +0100, Jim Jones wrote: >> One thing I just noticed. The psql autocomplete feature does not suggest >> the new + option of \conninfo. For instance, when typing "\connin[TAB]" >> it automatically autocompletes to "\conninfo ". I guess it should also >> be included in this patch. > >Modifiers such as + or S in \dS are not covered by autocompletion. >src/bin/psql/tab-complete.c only specifies backslash commands in their >basic form (without modifiers). > >(\dS<TAB> actually autocompletes to \ds to my surprise) > >> I can do a more thorough review of the code when you add the >> documentation and tests to the patch. > >I noticed that the pattern parameter in listConnectionInformation is >unused. exec_command_conninfo scans the pattern but \conninfo should >not accept any argument. So the pattern can be removed entirely. --//-- Hi Erik, Exactly, in "\conninfo+" the "pattern" argument was not introduced and therefore "listConnectionInformation()" can be declared (signed) without any arguments. In the future, if necessary, and if there is a need to add any "pattern", then that can be done. However, that's not the current scenario. I removed everything related to the "pattern" from the patch. Regarding "(\dS<TAB> actually autocompletes to \ds to my surprise)", I was also surprised. I haven't studied the code yet to understand why this happens. It seems curious to me. I'll try to understand this implementation better. I'm continuing the development of "\conninfo+" and now moving on to tests. Tks a lot! Regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-02-08T23:34:54Z
Hi Erik On 08.02.24 21:37, Erik Wienhold wrote: > Modifiers such as + or S in \dS are not covered by autocompletion. > src/bin/psql/tab-complete.c only specifies backslash commands in their > basic form (without modifiers). > > (\dS<TAB> actually autocompletes to \ds to my surprise) > Aha... I never noticed it. Well, with most commands having 1 - 3 characters it is not a surprised I never used it :) That "\dS<TAB>" autocompletes to "\ds " surprises me even more. Thanks for pointing out! -- Jim
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-09T02:00:11Z
On 08.02.24 21:37, Erik Wienhold wrote: >> Modifiers such as + or S in \dS are not covered by autocompletion. >> src/bin/psql/tab-complete.c only specifies backslash commands in their >> basic form (without modifiers). >> >> (\dS<TAB> actually autocompletes to \ds to my surprise) >> >Aha... I never noticed it. Well, with most commands having 1 - 3 >characters it is not a surprised I never used it :) >That "\dS<TAB>" autocompletes to "\ds " surprises me even more. >Thanks for pointing out! --//-- Good evening, dear all! Here is the mechanism that implements this: https://github.com/postgres/postgres/blob/b619852086ed2b5df76631f5678f60d3bebd3745/src/bin/psql/tab-complete.h . . . 1673 /* Match the last N words before point, case-sensitively. */ 1674 #define TailMatchesCS(...) \ 1675 TailMatchesImpl(true, previous_words_count, previous_words, \ 1676 VA_ARGS_NARGS(__VA_ARGS__), __VA_ARGS__) . . . 4824 else if (TailMatchesCS("\\ds*")) 4825 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences); . . . There is a rather large list of meta-commands that are handled by TailMatchesCS(...). For example: \ENCODING<TAB> autocompletes to \encoding \eNcOdInG<TAB> autocompletes to \encoding \dU<TAB> or \DU<TAB> autocompletes to \du Including the command under discussion: \CONNINFO<TAB> autocompletes to \conninfo For the meta-commands[+], there is no autocomplete. Regards, Maiquel Grassi. -
Re: Psql meta-command conninfo+
Nathan Bossart <nathandbossart@gmail.com> — 2024-02-09T02:41:55Z
Sorry if this has been brought up, but I noticed that some of the information is listed below the result set: postgres=# \conninfo+ Current Connection Information -[ RECORD 1 ]------+--------- Database | postgres Authenticated User | nathan System User | Current User | nathan Session User | nathan Session PID | 659410 Server Version | 17devel Server Address | ::1 Server Port | 5432 Client Address | ::1 Client Port | 59886 Socket Directory | Host | ::1 SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Shouldn't we move this information into the result set? Separately, does the server version really belong here? I'm not sure I would consider that to be connection information. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
-
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-02-09T07:24:41Z
Hi Nathan On 09.02.24 03:41, Nathan Bossart wrote: > Separately, does > the server version really belong here? I'm not sure I would consider that > to be connection information. > I tend to agree with you. The info there wouldn't hurt, but perhaps the client version would be a better fit. -- Jim
-
Re: Psql meta-command conninfo+
Pavel Luzanov <p.luzanov@postgrespro.ru> — 2024-02-09T07:58:15Z
Hi, Maiquel! The patch v10 build ends with a warning: $ make -j --silent describe.c:911:1: warning: no previous prototype for ‘listConnectionInformation’ [-Wmissing-prototypes] 911 | listConnectionInformation() | ^~~~~~~~~~~~~~~~~~~~~~~~~ About terms. postgres@postgres(17.0)=# \x \conninfo+ Expanded display is on. Current Connection Information -[ RECORD 1 ]------+--------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres *Session PID | 951112 *Server Version | 17devel Server Address | Server Port | 5401 Client Address | Client Port | Socket Directory | /tmp Host | It looks like "Session PID" is a new term for the server process identifier. How about changing the name to "Backend PID" (from pg_backend_pid) or even PID (from pg_stat_activity)? On 08.02.2024 17:58, Maiquel Grassi wrote: > > 1. > + if (db == NULL) > + printf(_("You are currently not connected to > a database.\n")); > > This check is performed for \conninfo, but not > for \conninfo+. > 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. I found that \conninfo and \conninfo+ act differently when the connection is broken. I used pg_terminate_backend function from another session to terminate an open psql session. After that, \conninfo does not see the connection break (surprisingly!), and \conninfo+ returns an error: postgres@postgres(17.0)=# \conninfo+ FATAL: terminating connection due to administrator command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. postgres@postgres(17.0)=# \conninfo You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5401". Another surprise is that this check:if (db == NULL) did not work in both cases. -- Pavel Luzanov Postgres Professional:https://postgrespro.com -
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-09T08:30:54Z
>Sorry if this has been brought up, but I noticed that some of the >information is listed below the result set: > > postgres=# \conninfo+ > Current Connection Information > -[ RECORD 1 ]------+--------- > Database | postgres > Authenticated User | nathan > System User | > Current User | nathan > Session User | nathan > Session PID | 659410 > Server Version | 17devel > Server Address | ::1 > Server Port | 5432 > Client Address | ::1 > Client Port | 59886 > Socket Directory | > Host | ::1 > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) >Shouldn't we move this information into the result set? Separately, does >the server version really belong here? I'm not sure I would consider that >to be connection information. --//-- Hi Nathan, The "Server Version" information is closely related to the connection. However, it does seem to be an element that does not belong to this set. I removed this column and left only what is truly connection info. Regarding the functions "printSSLInfo()" and "printGSSInfo()", I agree that we should include them in the returned dataset (as two additional columns). A good argument is that this will make more sense when using \x (Expanded display). However, they are declared and defined within the "command.c" file. To make calls to "printSSLInfo()" and "printGSSInfo()" within "describe.c", we would need to move their declarations to a new header and create a new C file. I believe something like "ssl_gss_info.h" and "ssl_gss_info.c". I'm not sure, but at first glance, this is what occurs to me. Do you have any better or more concise suggestions for resolving this? Regards, Maiquel Grassi.
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-09T09:18:44Z
Hi Pavel! >The patch v10 build ends with a warning: >$ make -j --silent >describe.c:911:1: warning: no previous prototype for ‘listConnectionInformation’ [-Wmissing-prototypes] > 911 | listConnectionInformation() | ^~~~~~~~~~~~~~~~~~~~~~~~~ >About terms. I resolved this in v11. I had forgotten to put the 'void' inside the parentheses (describe.h and describe.c). >postgres@postgres(17.0)=# \x \conninfo+ >Expanded display is on. >Current Connection Information >-[ RECORD 1 ]------+--------- >Database | postgres >Authenticated User | postgres >System User | >Current User | postgres >Session User | postgres >Session PID | 951112 >Server Version | 17devel >Server Address | >Server Port | 5401 >Client Address | >Client Port | >Socket Directory | /tmp >Host | >It looks like "Session PID" is a new term for the server process identifier. >How about changing the name to "Backend PID" (from pg_backend_pid) or even PID (from pg_stat_activity)? You're right, it's better to stick with the proper terms already in use in PostgreSQL. This ensures that the user doesn't get confused. I've changed it to "Backend PID". >On 08.02.2024 17:58, Maiquel Grassi wrote: > 1. > + if (db == NULL) > + printf(_("You are currently not connected to a database.\n")); > > This check is performed for \conninfo, but not for \conninfo+. 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. >I found that \conninfo and \conninfo+ act differently when the connection is broken. >I used pg_terminate_backend function from another session to terminate an open psql session. >After that, \conninfo does not see the connection break (surprisingly!), and \conninfo+ returns an error: >postgres@postgres(17.0)=# \conninfo+ >FATAL: terminating connection due to administrator command >server closed the connection unexpectedly >This probably means the server terminated abnormally >before or while processing the request. >The connection to the server was lost. Attempting reset: Succeeded. For this case, I believe it's already resolved, because if you get a return indicating that the connection was terminated, and indeed it was, then "describe.c" is handling it correctly. At least that's what it seems like. >postgres@postgres(17.0)=# \conninfo >You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5401". Here it seems like we have an issue to be studied and subsequently resolved. >Another surprise is that this check: if (db == NULL) did not work in both cases. I will investigate further and, if necessary, remove it. Here's v12, in the next version, I'll try to address the above situation. Thanks a lot! Maiquel Grassi. -
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-09T09:38:35Z
>Hi Nathan > >On 09.02.24 03:41, Nathan Bossart wrote: > >> Separately, does >> the server version really belong here? I'm not sure I would consider that >> to be connection information. >> >I tend to agree with you. The info there wouldn't hurt, but perhaps the >client version would be a better fit. --//-- Hi! I believe that if we include the server version, it would also be necessary to include the version of psql. This would make it clearer for the user. I agree that this won't make much difference in practice, especially because we're interested in creating a setof information directly related to the connection. I prefer to keep it suppressed for now. In the future, if necessary, we can add this information without any problem. In v12, "Server Version" is already removed. Tks! Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-02-09T10:19:03Z
Hmm, I noticed that this would call printSSLInfo() and printGSSInfo() after listConnectionInformation. It would be much better to show these in tabular format as well and remove the calls to printSSL/GSSInfo. I propose additional columns to the same \conninfo+ table; when SSL, like this: Database | postgres [...] Host | 127.0.0.1 Encryption | SSL Protocol | PQsslAttribute(protocol) Cipher | PQsslAttribute(cipher) Compression | PQsslAttribute(compression) When GSS, like this Database | postgres [...] Host | 127.0.0.1 Encryption | GSS (why don't we print anything else in printGSSInfo()? That's weird.) -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-09T15:33:23Z
>Hmm, I noticed that this would call printSSLInfo() and printGSSInfo() >after listConnectionInformation. It would be much better to show these >in tabular format as well and remove the calls to printSSL/GSSInfo. > >I propose additional columns to the same \conninfo+ table; when SSL, >like this: > >Database | postgres >[...] >Host | 127.0.0.1 >Encryption | SSL >Protocol | PQsslAttribute(protocol) >Cipher | PQsslAttribute(cipher) >Compression | PQsslAttribute(compression) > >When GSS, like this > >Database | postgres >[...] >Host | 127.0.0.1 >Encryption | GSS > >(why don't we print anything else in printGSSInfo()? That's weird.) --//-- Hi Álvaro, Thank you for the suggestion. I will remove printSSLInfo() and printGSSInfo() and incorporate the suggested fields into the tabular result of "\conninfo+". If it's necessary to adjust printGSSInfo(), we can work on that as well. At first glance, I leave it open for others to analyze and give their opinion. I'd also like to ask for help with a difficulty. Currently, I'm working on resolving this situation (highlighted by Pavel Luzanov). How can we make \conninfo return the same message as \conninfo+ after closing the current session in another session with pg_terminate_backend(pid)? [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 | Backend PID | Server Address | Server Port | Client Address | Client Port | Socket Directory | Host ----------+--------------------+-------------+--------------+--------------+-------------+----------------+-------------+----------------+-------------+------------------+------ postgres | postgres | | postgres | postgres | 17281 | | 5432 | | | /tmp | (1 row) postgres=# 2024-02-09 09:15:24.152 -03 [17281] FATAL: terminating connection due to administrator command postgres=# \conninfo You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432". postgres=# \conninfo+ FATAL: terminating connection due to administrator command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. postgres=# Tks a lot! Maiquel Grassi. -
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-10T17:38:23Z
>Database | postgres >[...] >Host | 127.0.0.1 >Encryption | SSL >Protocol | PQsslAttribute(protocol) >Cipher | PQsslAttribute(cipher) >Compression | PQsslAttribute(compression) > >When GSS, like this > >Database | postgres >[...] >Host | 127.0.0.1 >Encryption | GSS --//-- Hi PgHackers, Columns were added for SSL and GSS. For SSL, I conducted some tests as follows. For GSS, I will perform them and intend to provide a sample here in the next interaction. If anyone can and wants to test GSSAPI as well, I appreciate it. [postgres@localhost bin]$ ./psql -h localhost -p 5432 -x 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 -[ RECORD 1 ]------+---------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Backend PID | 15809 Server Address | ::1 Server Port | 5432 Client Address | ::1 Client Port | 56890 Socket Directory | Host | localhost postgres=# \q [postgres@localhost bin]$ ./psql -h localhost -p 5433 -x psql (17devel, server 15.6) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off) Type "help" for help. postgres=# \conninfo You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5433". SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off) postgres=# \conninfo+ Current Connection Information -[ RECORD 1 ]------+---------------------------- Database | postgres Authenticated User | postgres Current User | postgres Session User | postgres Backend PID | 15811 Server Address | ::1 Server Port | 5433 Client Address | ::1 Client Port | 40622 Socket Directory | Host | localhost Encryption | SSL Protocol | TLSv1.2 Cipher | ECDHE-RSA-AES256-GCM-SHA384 Compression | off Regards, Maiquel Grassi.
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-12T14:16:00Z
>I found that \conninfo and \conninfo+ act differently when the connection is broken. >I used pg_terminate_backend function from another session to terminate an open psql session. >After that, \conninfo does not see the connection break (surprisingly!), and \conninfo+ returns an error: > >postgres@postgres(17.0)=# \conninfo+ >FATAL: terminating connection due to administrator command >server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. >The connection to the server was lost. Attempting reset: Succeeded. > >postgres@postgres(17.0)=# \conninfo >You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5401". > >Another surprise is that this check: if (db == NULL) did not work in both cases. --//-- Hi Pavel! (v14) The "if (db == NULL)" has been removed, as well as the message inside it. To always maintain the same error messages, I changed the validation of "\conninfo" to match the validation used for "\conninfo+". Therefore, now "\conninfo" and "\conninfo+" return the same error when "pg_terminate_backend()" terminates this session through another session. The result of the adjustment is as follows: Case 1 ("\conninfo+"): [postgres@localhost bin]$ ./psql -x 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 -[ RECORD 1 ]------+--------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Backend PID | 24381 Server Address | Server Port | 5432 Client Address | Client Port | Socket Directory | /tmp Host | postgres=# \conninfo+ FATAL: terminating connection due to administrator command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. Case 2 ("\conninfo"): [postgres@localhost bin]$ ./psql -x 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 -[ RECORD 1 ]------+--------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Backend PID | 24539 Server Address | Server Port | 5432 Client Address | Client Port | Socket Directory | /tmp Host | postgres=# \conninfo FATAL: terminating connection due to administrator command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. In both cases, the sessions were terminated by another session. Regards, Maiquel Grassi. -
Re: Psql meta-command conninfo+
Pavel Luzanov <p.luzanov@postgrespro.ru> — 2024-02-12T21:53:50Z
Hi, On 12.02.2024 17:16, Maiquel Grassi wrote: > > The "if (db == NULL)" has been removed, as well as the message inside > it. To always maintain the same error messages, I changed the > validation of "\conninfo" to match the validation used for > "\conninfo+". Therefore, now "\conninfo" and "\conninfo+" return the > same error when "pg_terminate_backend()" terminates this session > through another session. > This make sense to me. Thank you for this work. -- Pavel Luzanov Postgres Professional:https://postgrespro.com
-
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-02-14T11:36:49Z
On 12.02.24 15:16, Maiquel Grassi wrote: > > (v14) > > v14 applies cleanly and the SSL info is now shown as previously suggested. Here is a more comprehensive test: $ /usr/local/postgres-dev/bin/psql -x "\ host=server.uni-muenster.de hostaddr=172.19.42.1 user=jim dbname=postgres sslrootcert=server-certificates/server.crt sslcert=jim-certificates/jim.crt sslkey=jim-certificates/jim.key" psql (17devel) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help. postgres=# SET ROLE foo; SET postgres=> \conninfo+ Current Connection Information -[ RECORD 1 ]------+--------------------------------------------------------------------------------------------------------------------------- Database | postgres Authenticated User | jim System User | cert:emailAddress=jim@uni-muenster.de,CN=jim,OU=WWU IT,O=Universitaet Muenster,L=Muenster,ST=Nordrhein-Westfalen,C=DE Current User | foo Session User | jim Backend PID | 278294 Server Address | 172.19.42.1 Server Port | 5432 Client Address | 192.168.178.27 Client Port | 54948 Socket Directory | Host | server.uni-muenster.de Encryption | SSL Protocol | TLSv1.3 Cipher | TLS_AES_256_GCM_SHA384 Compression | off postgres=> \conninfo You are connected to database "postgres" as user "jim" on host "server.uni-muenster.de" (address "127.0.0.1") at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) A little odd is that "Server Address" of \conninfo+ differs from "address" of \conninfo... I think the documentation could add a little more info than just this: > When no + is specified, it simply prints a textual description of a few connection options. When + is given, more complete information is displayed as a table. Perhaps briefly mentioning the returned columns or simply listing them would be IMHO a nice addition. For some users the semantics of "Authenticated User", "System User", "Current User" and "Session User" can be a little confusing. And yes, I realize the current documentation of \conninfo is already a little vague :). Thanks for working on this -- Jim
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-15T19:47:05Z
>v14 applies cleanly and the SSL info is now shown as previously >suggested. Here is a more comprehensive test: > > >$ /usr/local/postgres-dev/bin/psql -x "\ > host=server.uni-muenster.de > hostaddr=172.19.42.1 > user=jim dbname=postgres > sslrootcert=server-certificates/server.crt > sslcert=jim-certificates/jim.crt > sslkey=jim-certificates/jim.key" > >psql (17devel) >SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, >compression: off) >Type "help" for help. > >postgres=# SET ROLE foo; >SET >postgres=> \conninfo+ >Current Connection Information >-[ RECORD 1 >]------+--------------------------------------------------------------------------------------------------------------------------- >Database | postgres >Authenticated User | jim >System User | cert:emailAddress=jim@uni-muenster.de,CN=jim,OU=WWU >IT,O=Universitaet Muenster,L=Muenster,ST=Nordrhein-Westfalen,C=DE >Current User | foo >Session User | jim >Backend PID | 278294 >Server Address | 172.19.42.1 >Server Port | 5432 >Client Address | 192.168.178.27 >Client Port | 54948 >Socket Directory | >Host | server.uni-muenster.de >Encryption | SSL >Protocol | TLSv1.3 >Cipher | TLS_AES_256_GCM_SHA384 >Compression | off > >postgres=> \conninfo >You are connected to database "postgres" as user "jim" on host >"server.uni-muenster.de" (address "127.0.0.1") at port "5432". >SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, >compression: off) >A little odd is that "Server Address" of \conninfo+ differs from >"address" of \conninfo... ----//---- Hi Jim! Tests performed on CentOS Linux 7. I made some further observations and concluded that there will be more cases where the "address" from \conninfo will differ from the "Server Address" from \conninfo+. Below is a more detailed example. The input of "hostaddr" or "host" in the psql call can be any pointing to "loopback local" and the connection will still be established. For example, all of these are accepted: Case (inet): psql -x --host 0 psql -x --host 0.0.0.0 psql -x hostaddr=0 psql -x hostaddr=0.0.0.0 All these examples will have "Server Address" = 127.0.0.1 Case (inet6): psql -x --host :: psql -x --host * (this entry is not accepted) psql -x --host \* psql -x --host "*" psql -x hostaddr=:: psql -x hostaddr=* All these examples will have "Server Address" = ::1 Thus, the inet_server_addr() function will return 127.0.0.1 or ::1 which in some cases will differ from the "address" from \conninfo. [postgres@localhost bin]$ ./psql -x hostaddr=0 Password for user postgres: psql (17devel) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off) Type "help" for help. postgres=# SET ROLE maiquel; SET postgres=> \conninfo You are connected to database "postgres" as user "postgres" on host "0" (address "0.0.0.0") at port "5432". SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off) postgres=> \conninfo+ Current Connection Information -[ RECORD 1 ]------+---------------------------- Database | postgres Authenticated User | postgres System User | scram-sha-256:postgres Current User | maiquel Session User | postgres Backend PID | 15205 Server Address | 127.0.0.1 Server Port | 5432 Client Address | 127.0.0.1 Client Port | 57598 Socket Directory | Host | 0 Encryption | SSL Protocol | TLSv1.2 Cipher | ECDHE-RSA-AES256-GCM-SHA384 Compression | off postgres=> \q [postgres@localhost bin]$ ping 0.0.0.0 PING 0.0.0.0 (127.0.0.1) 56(84) bytes of data. 64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.061 ms 64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.069 ms 64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.071 ms 64 bytes from 127.0.0.1: icmp_seq=4 ttl=64 time=0.107 ms ^C --- 0.0.0.0 ping statistics --- 4 packets transmitted, 4 received, 0% packet loss, time 3003ms rtt min/avg/max/mdev = 0.061/0.077/0.107/0.017 ms As demonstrated above, "address" = 0.0.0.0 and "Server Address" = 127.0.0.1 are divergent. In practice, these IPs are the "same", and the ping from the example proves it. However, we are concerned here with the psql user, and this may seem confusing to them at first glance. I would like to propose a change in "address" so that it always returns the same as "Server Address", that is, to use the inet_server_address() function in "address". Result: [postgres@localhost bin]$ ./psql -x hostaddr=0 psql (17devel) Type "help" for help. postgres=# \conninfo You are connected to database "postgres" as user "postgres" on host "0" (address "127.0.0.1") at port "5432". postgres=# \conninfo+ Current Connection Information -[ RECORD 1 ]------+---------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Backend PID | 26859 Server Address | 127.0.0.1 Server Port | 5432 Client Address | 127.0.0.1 Client Port | 58254 Socket Directory | Host | 0 ----//---- >I think the documentation could add a little more info than just this: >> When no + is specified, it simply prints a textual description of a >>few connection options. When + is given, more complete information is >>displayed as a table. >Perhaps briefly mentioning the returned columns or simply listing them >would be IMHO a nice addition. For some users the semantics of >"Authenticated User", "System User", "Current User" and "Session User" >can be a little confusing. And yes, I realize the current documentation >of \conninfo is already a little vague :). Your suggestion was well received, and I'will made the adjustment to make the command description more comprehensive. Here is version v15 where I sought to correct 'Adress' to make it the same as 'Server Address'. Could you perform the same test and validate? Thank you so much! Maiquel Grassi.
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-15T22:16:33Z
Hi! (v16) In this version, I made a small adjustment to the indentation of the \conninfo code and described the columns as returned by \conninfo+ as suggested by Jim Jones. Regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-02-16T11:18:45Z
On 15.02.24 23:16, Maiquel Grassi wrote: > > Hi! > > (v16) > > In this version, I made a small adjustment to the indentation > of the \conninfo code and described the columns as returned > by \conninfo+ as suggested by Jim Jones. > > I've performed the following tests with v16: 1) hostaddr=172.19.42.1 $ /usr/local/postgres-dev/bin/psql -x "\ host=server.uni-muenster.de hostaddr=172.19.42.1 user=jim dbname=postgres sslrootcert=server-certificates/server.crt sslcert=jim-certificates/jim.crt sslkey=jim-certificates/jim.key" -c "\conninfo+" -c "\conninfo" Current Connection Information -[ RECORD 1 ]------+--------------------------------------------------------------------------------------------------------------------------- Database | postgres Authenticated User | jim System User | cert:emailAddress=wwwadmin@uni-muenster.de,CN=jim,OU=WWU IT,O=Universitaet Muenster,L=Muenster,ST=Nordrhein-Westfalen,C=DE Current User | jim Session User | jim Backend PID | 386839 Server Address | 172.19.42.1 Server Port | 5432 Client Address | 192.168.178.27 Client Port | 35602 Socket Directory | Host | server.uni-muenster.de Encryption | SSL Protocol | TLSv1.3 Cipher | TLS_AES_256_GCM_SHA384 Compression | off You are connected to database "postgres" as user "jim" on host "server.uni-muenster.de" (address "172.19.42.1") at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) The same with non-superusers $ /usr/local/postgres-dev/bin/psql -x "\ host=server.uni-muenster.de hostaddr=172.19.42.1 user=jim dbname=postgres sslrootcert=server-certificates/server.crt sslcert=jim-certificates/jim.crt sslkey=jim-certificates/jim.key" -c "SET ROLE foo" -c "\conninfo+" -c "\conninfo" SET Current Connection Information -[ RECORD 1 ]------+--------------------------------------------------------------------------------------------------------------------------- Database | postgres Authenticated User | jim System User | cert:emailAddress=wwwadmin@uni-muenster.de,CN=jim,OU=WWU IT,O=Universitaet Muenster,L=Muenster,ST=Nordrhein-Westfalen,C=DE Current User | foo Session User | jim Backend PID | 547733 Server Address | 172.19.42.1 Server Port | 5432 Client Address | 192.168.178.27 Client Port | 58508 Socket Directory | Host | server.uni-muenster.de Encryption | SSL Protocol | TLSv1.3 Cipher | TLS_AES_256_GCM_SHA384 Compression | off You are connected to database "postgres" as user "jim" on host "server.uni-muenster.de" (address "172.19.42.1") at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) 2) -h 192.168.178.27 $ /usr/local/postgres-dev/bin/psql -x -U postgres -h 192.168.178.27 -c "\conninfo+" -c "\conninfo" Current Connection Information -[ RECORD 1 ]------+----------------------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Backend PID | 399670 Server Address | 192.168.178.27 Server Port | 5432 Client Address | 192.168.178.27 Client Port | 44174 Socket Directory | Host | 192.168.178.27 Encryption | SSL Protocol | TLSv1.3 Cipher | TLS_AES_256_GCM_SHA384 Compression | off You are connected to database "postgres" as user "postgres" on host "192.168.178.27" at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) 3) via socket $ /usr/local/postgres-dev/bin/psql -x -U postgres -c "\conninfo+" -c "\conninfo" Current Connection Information -[ RECORD 1 ]------+--------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Backend PID | 394273 Server Address | Server Port | 5432 Client Address | Client Port | Socket Directory | /tmp Host | You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432". 4) -h 127.0.0.1 $ /usr/local/postgres-dev/bin/psql -x -U postgres -h 127.0.0.1 -c "\conninfo+" -c "\conninfo" Current Connection Information -[ RECORD 1 ]------+----------------------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Backend PID | 396070 Server Address | 127.0.0.1 Server Port | 5432 Client Address | 127.0.0.1 Client Port | 52528 Socket Directory | Host | 127.0.0.1 Encryption | SSL Protocol | TLSv1.3 Cipher | TLS_AES_256_GCM_SHA384 Compression | off You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) 5) -h localhost $ /usr/local/postgres-dev/bin/psql -x -U postgres -h localhost -c "\conninfo+" -c "\conninfo" Current Connection Information -[ RECORD 1 ]------+----------------------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Backend PID | 397056 Server Address | 127.0.0.1 Server Port | 5432 Client Address | 127.0.0.1 Client Port | 53578 Socket Directory | Host | localhost Encryption | SSL Protocol | TLSv1.3 Cipher | TLS_AES_256_GCM_SHA384 Compression | off You are connected to database "postgres" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) 6) -h 0 $ /usr/local/postgres-dev/bin/psql -x -U postgres -h 0 -c "\conninfo+" -c "\conninfo" Current Connection Information -[ RECORD 1 ]------+----------------------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Backend PID | 406342 Server Address | 127.0.0.1 Server Port | 5432 Client Address | 127.0.0.1 Client Port | 38674 Socket Directory | Host | 0 Encryption | SSL Protocol | TLSv1.3 Cipher | TLS_AES_256_GCM_SHA384 Compression | off You are connected to database "postgres" as user "postgres" on host "0" (address "127.0.0.1") at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) 7) -h 0.0.0.0 - As you mentioned, this is one of the cases where host and "server address" differ. I am not sure if it is an issue. Perhaps the other reviewers might have an opinion on it $ /usr/local/postgres-dev/bin/psql -x -U postgres -h 0.0.0.0 -c "\conninfo+" -c "\conninfo" Current Connection Information -[ RECORD 1 ]------+----------------------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Backend PID | 404395 Server Address | 127.0.0.1 Server Port | 5432 Client Address | 127.0.0.1 Client Port | 54806 Socket Directory | Host | 0.0.0.0 Encryption | SSL Protocol | TLSv1.3 Cipher | TLS_AES_256_GCM_SHA384 Compression | off You are connected to database "postgres" as user "postgres" on host "0.0.0.0" at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) > I would like to propose a change in "address" so that it always returns the same as > "Server Address", that is, to use the inet_server_address() function in "address". I'm not sure of the impact of this change in the existing \conninfo - at least the cfbot and "make -j check-world" didn't complain. I'll take a closer look at it as soon we have test cases. Docs: + <term><literal>\conninfo[+]</literal></term> <listitem> <para> Outputs information about the current database connection. + When no <literal>+</literal> is specified, it simply prints + a textual description of a few connection options. + When <literal>+</literal> is given, more complete information + is displayed as a table. + </para> To keep it consistent with the other options, we might wanna use "+ is appended" instead of "+ is specified" or "+ is given" + When <literal>+</literal> is given, more complete information + is displayed as a table. + </para> + + <para> + "Database", "Authenticated User", "System User" (only for PostgreSQL 16 or higher), + "Current User", "Session User", "Backend PID", "Server Address", "Server Port", + "Client Address", "Client Port", "Socket Directory", and "Host" columns are listed + by default when <literal>\conninfo+</literal> is invoked. The columns "Encryption", + "Protocol", "Cipher", and "Compression" are added to this output when TLS (SSL) + authentication is used. The same applies to GSS authentication is used, where the + "GSSAPI" column is also added to the <literal>\conninfo+</literal> output. I think that a list with a brief description of all columns would be more interesting in this case (it is just a suggestion based on personal taste, so feel to ignore it) I had something along these lines in mind: Outputs a string containing information about the current database connection. When + is appended, it outputs a table containing the following columns: * Database: lorem ipsum * Authenticated User:lorem ipsum * System User: lorem ipsum * Current User: lorem ipsum * Session User: lorem ipsum * Backend PID: lorem ipsum * Server Address: lorem ipsum * Server Port: lorem ipsum * Client Address:lorem ipsum * Client Port: lorem ipsum * Socket Directory: lorem ipsum * Host: lorem ipsum TLS (SSL) authentication These columns are added to the table TLS (SSL) authentication is used * Encryption:lorem ipsum * Cipher:lorem ipsum * Protocol:lorem ipsum GSS authentication ... ... Thanks -- Jim
-
Re: Psql meta-command conninfo+
Nathan Bossart <nathandbossart@gmail.com> — 2024-02-16T15:54:49Z
Thanks for your work on this. I haven't been keeping up with the discussion, but I took a quick look at the latest patch. + <para> + "Database", "Authenticated User", "System User" (only for PostgreSQL 16 or higher), + "Current User", "Session User", "Backend PID", "Server Address", "Server Port", + "Client Address", "Client Port", "Socket Directory", and "Host" columns are listed + by default when <literal>\conninfo+</literal> is invoked. The columns "Encryption", + "Protocol", "Cipher", and "Compression" are added to this output when TLS (SSL) + authentication is used. The same applies to GSS authentication is used, where the + "GSSAPI" column is also added to the <literal>\conninfo+</literal> output. </para> I might be alone on this, but I think this command should output the same columns regardless of the version, whether it's using SSL, etc. and just put NULL in any that do not apply. IMHO that would simplify the code and help prevent confusion. Plus, I'm not aware of any existing meta-commands that provide certain columns conditionally. + if (PQsslInUse(pset.db)) + { + protocol = PQsslAttribute(pset.db, "protocol"); + cipher = PQsslAttribute(pset.db, "cipher"); + compression = PQsslAttribute(pset.db, "compression"); + appendPQExpBuffer(&buf, + " ,'SSL' AS \"Encryption\",\n" + " '%s' AS \"Protocol\",\n" + " '%s' AS \"Cipher\",\n" + " '%s' AS \"Compression\"\n", + protocol ? protocol : _("unknown"), + cipher ? cipher : _("unknown"), + (compression && strcmp(compression, "off") != 0) ? _("on") : _("off")); + } Could we pull some of this information from pg_stat_ssl instead of from libpq? The reason I suggest this is because I think it would be nice if the query that \conninfo+ uses could be copy/pasted as needed and not rely on hard-coded values chosen by the client. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com -
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-16T21:48:16Z
Hi! >7) -h 0.0.0.0 - As you mentioned, this is one of the cases where host >and "server address" differ. > I am not sure if it is an issue. Perhaps the other reviewers might >have an opinion on it >$ /usr/local/postgres-dev/bin/psql -x -U postgres -h 0.0.0.0 -c >"\conninfo+" -c "\conninfo" >Current Connection Information >-[ RECORD 1 ]------+----------------------- >Database | postgres >Authenticated User | postgres >System User | >Current User | postgres >Session User | postgres >Backend PID | 404395 >Server Address | 127.0.0.1 >Server Port | 5432 >Client Address | 127.0.0.1 >Client Port | 54806 >Socket Directory | >Host | 0.0.0.0 >Encryption | SSL >Protocol | TLSv1.3 >Cipher | TLS_AES_256_GCM_SHA384 >Compression | off I believe we don't actually have a problem here. To me, this makes sense. I'm not a Networking expert, but from my standpoint, any string or IP accepted by the "host" parameter of psql, pointing to "127.0.0.1" or to "::1", is correct. I see it as a convention (would need to revisit Tanenbaum's books haha), and I don't think there's a need to modify it. But as you mentioned, if someone with more Networking knowledge could weigh in, a suggestion would be welcome. >I'm not sure of the impact of this change in the existing \conninfo - at >least the cfbot and "make -j check-world" didn't complain. >I'll take a closer look at it as soon we have test cases. This type of modification, in principle, does not generate errors as we are only changing the display on screen of the strings already previously stored. >To keep it consistent with the other options, we might wanna use "+ is >appended" instead of "+ is specified" or "+ is given" It seems to me that "appended" sounds good. I opted for it. Following your suggestion, and merging this with the existing previous ideas from the documentation, I've created a provisional prototype of the column descriptions. At a later stage, I'll place the descriptions correctly by removing placeholders (blah blah blah). Along with this, I'll evaluate an iteration suggested by Nathan Bossart, who also proposed changes. Thank you for your work. Regards, Maiquel Grassi.
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-17T14:53:43Z
Hi Nathan! (v18) >I might be alone on this, but I think this command should output the same >columns regardless of the version, whether it's using SSL, etc. and just >put NULL in any that do not apply. IMHO that would simplify the code and >help prevent confusion. Plus, I'm not aware of any existing meta-commands >that provide certain columns conditionally. I implemented your suggestion. Now, whenever the \conninfo+ command is invoked, all columns will appear. Contextually inappropriate cases will return NULL. I also adjusted the names of the columns related to SSL to make this information clearer for the user. I haven't focused on documenting the columns yet. I will do that soon. >Could we pull some of this information from pg_stat_ssl instead of from >libpq? The reason I suggest this is because I think it would be nice if >the query that \conninfo+ uses could be copy/pasted as needed and not rely >on hard-coded values chosen by the client. I've been considering using the views "pg_stat_ssl" and "pg_stat_gssapi"; however, I realized that dealing with version-related cases using them is more complicated. Let me explain the reasons: The "pg_stat_ssl" view is available from >= PG 9.5, and the "pg_stat_gssapi" view is available from >= PG 12. The "compression" column was removed from the "pg_stat_ssl" from >= PG 14. All of these cases introduce greater complexity in maintaining the SQL. The central idea from the beginning has always been to show the user all the information from \conninfo and extend it in \conninfo+. The absence of the "compression" column in version 14 and above makes dealing with this even more complicated, and not showing it seems to contradict \conninfo. SSL support has been available since version 7.1 (see documentation); if there was support before that, I can't say. In this regard, it may seem strange, but there are still many legacy systems running older versions of PostgreSQL. Just yesterday, I assisted a client who is still using PG 8.2. In these cases, using the "pg_stat_ssl" and "pg_stat_gssapi" views would not be possible because they don't exist on the server. I believe that psql should cover as many cases as possible when it comes to compatibility with older versions (even those no longer supported). In this case, concerning SSL and GSS, I think libpq is better prepared to handle this. I may be mistaken in my statement and I welcome any better suggestions. For now, I've maintained the implementation using libpq as it seems to be working well and is not contradicting \conninfo. If you have any suggestions on how to work around the absence of the "compression" column, we can reconsider how to implement it without using libpq. Tests: [postgres@localhost bin]$ ./psql -x -h 127.0.0.1 -p 5432 Password for user postgres: 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 -[ RECORD 1 ]------+----------------------- Database | postgres Authenticated User | postgres System User | scram-sha-256:postgres Current User | postgres Session User | postgres Backend PID | 22431 Server Address | 127.0.0.1 Server Port | 5432 Client Address | 127.0.0.1 Client Port | 51300 Socket Directory | Host | 127.0.0.1 SSL Connection | f SSL Protocol | SSL Cipher | SSL Compression | GSSAPI | f [postgres@localhost bin]$ ./psql -x -h 127.0.0.1 -p 5433 Password for user postgres: psql (17devel, server 15.6) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off) Type "help" for help. postgres=# \conninfo You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5433". SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off) postgres=# \conninfo+ Current Connection Information -[ RECORD 1 ]------+---------------------------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Backend PID | 22438 Server Address | 127.0.0.1 Server Port | 5433 Client Address | 127.0.0.1 Client Port | 36016 Socket Directory | Host | 127.0.0.1 SSL Connection | t SSL Protocol | TLSv1.2 SSL Cipher | ECDHE-RSA-AES256-GCM-SHA384 SSL Compression | off GSSAPI | f Thank you very much for your sugestions and help! Maiquel Grassi.
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-21T23:30:17Z
Hi! (v19) Adjusted the description of each column in the documentation as promised. I used the existing documentation as a basis for each SQL function used, as well as for functions and views related to SSL and GSSAPI (documentation). If you can validate, I appreciate it. Regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Nathan Bossart <nathandbossart@gmail.com> — 2024-02-24T20:44:12Z
On Sat, Feb 17, 2024 at 02:53:43PM +0000, Maiquel Grassi wrote: > The "pg_stat_ssl" view is available from >= PG 9.5, and the "pg_stat_gssapi" view is > available from >= PG 12. The "compression" column was removed from the > "pg_stat_ssl" from >= PG 14. All of these cases introduce greater complexity in > maintaining the SQL. The central idea from the beginning has always been to show > the user all the information from \conninfo and extend it in \conninfo+. IMHO we should use the views whenever possible (for the reason stated above [0]). I think it's okay if we need to fall back to a different approach for older versions. But presumably we'll discontinue psql support for these old server versions at some point, at which point we can simply delete the dead code that doesn't use the views. > The absence > of the "compression" column in version 14 and above makes dealing with this even > more complicated, and not showing it seems to contradict \conninfo. I would be okay with using PQsslAttribute() for all versions for this one since any remaining support for this feature is on its way out. Once psql no longer supports any versions that allow SSL compression, we could probably remove it from \conninfo[+] completely or hard-code it to "off". > SSL support has been available since version 7.1 (see documentation); if there was > > support before that, I can't say. In this regard, it may seem strange, but there are still > many legacy systems running older versions of PostgreSQL. Just yesterday, I assisted > a client who is still using PG 8.2. In these cases, using the "pg_stat_ssl" and > "pg_stat_gssapi" views would not be possible because they don't exist on the server. > I believe that psql should cover as many cases as possible when it comes to compatibility > with older versions (even those no longer supported). In this case, concerning SSL and > GSS, I think libpq is better prepared to handle this. At the moment, the psql support cutoff appears to be v9.2 (see commit cf0cab8), which has been out of support for over 6 years. If \conninfo+ happens to work for older versions, then great, but I don't think we should expend too much energy in this area. [0] https://postgr.es/m/20240216155449.GA1236054%40nathanxps13 -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-02-29T22:02:21Z
On Sat, Feb 17, 2024 at 02:53:43PM +0000, Maiquel Grassi wrote: >> The "pg_stat_ssl" view is available from >= PG 9.5, and the "pg_stat_gssapi" view is >> available from >= PG 12. The "compression" column was removed from the >> "pg_stat_ssl" from >= PG 14. All of these cases introduce greater complexity in >> maintaining the SQL. The central idea from the beginning has always been to show >> the user all the information from \conninfo and extend it in \conninfo+. >IMHO we should use the views whenever possible (for the reason stated >above [0]). I think it's okay if we need to fall back to a different >approach for older versions. But presumably we'll discontinue psql support >for these old server versions at some point, at which point we can simply >delete the dead code that doesn't use the views. >> The absence >> of the "compression" column in version 14 and above makes dealing with this even >> more complicated, and not showing it seems to contradict \conninfo. >I would be okay with using PQsslAttribute() for all versions for this one >since any remaining support for this feature is on its way out. Once psql >no longer supports any versions that allow SSL compression, we could >probably remove it from \conninfo[+] completely or hard-code it to "off". >> SSL support has been available since version 7.1 (see documentation); if there was >> support before that, I can't say. In this regard, it may seem strange, but there are still >> many legacy systems running older versions of PostgreSQL. Just yesterday, I assisted >> a client who is still using PG 8.2. In these cases, using the "pg_stat_ssl" and >> "pg_stat_gssapi" views would not be possible because they don't exist on the server. >> I believe that psql should cover as many cases as possible when it comes to compatibility >> with older versions (even those no longer supported). In this case, concerning SSL and >> GSS, I think libpq is better prepared to handle this. >At the moment, the psql support cutoff appears to be v9.2 (see commit >cf0cab8), which has been out of support for over 6 years. If \conninfo+ >happens to work for older versions, then great, but I don't think we should >expend too much energy in this area. [0] https://postgr.es/m/20240216155449.GA1236054%40nathanxps13 ----//---- Hi Nathan! Sorry for the delay. I will make the adjustments as requested soon. Regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Nathan Bossart <nathandbossart@gmail.com> — 2024-02-29T22:41:31Z
On Thu, Feb 29, 2024 at 10:02:21PM +0000, Maiquel Grassi wrote: > Sorry for the delay. I will make the adjustments as requested soon. Looking forward to it. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
-
Re: Psql meta-command conninfo+
Nathan Bossart <nathandbossart@gmail.com> — 2024-03-14T16:46:11Z
Hi Maiquel, On Thu, Feb 29, 2024 at 10:02:21PM +0000, Maiquel Grassi wrote: > Sorry for the delay. I will make the adjustments as requested soon. We have only a few weeks left before feature-freeze for v17. Do you think you will be able to send an updated patch soon? -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-03-18T22:05:17Z
On Thu, Feb 29, 2024 at 10:02:21PM +0000, Maiquel Grassi wrote: > Sorry for the delay. I will make the adjustments as requested soon. Looking forward to it. ----//---- Hi Nathan! Sorry for the delay, I was busy with other professional as well as personal activities. I made all the changes you suggested. I removed the variables and started using views "pg_stat_ssl" and "pg_stat_gssapi". I handled the PostgreSQL versioning regarding the views used. Here's a brief demonstration of the result: [postgres@localhost ~]$ /home/pgsql-17devel/bin/psql -E -x -p 5433 psql (17devel) Type "help" for help. postgres=# \conninfo+ /******** QUERY *********/ SELECT pg_catalog.current_database() AS "Database", 'postgres' AS "Authenticated User", pg_catalog.system_user() AS "System User", pg_catalog.current_user() AS "Current User", pg_catalog.session_user() AS "Session User", pg_catalog.pg_backend_pid() AS "Backend PID", pg_catalog.inet_server_addr() AS "Server Address", pg_catalog.current_setting('port') AS "Server Port", pg_catalog.inet_client_addr() AS "Client Address", pg_catalog.inet_client_port() AS "Client Port", '/tmp' AS "Socket Directory", CASE WHEN pg_catalog.inet_server_addr() IS NULL AND pg_catalog.inet_client_addr() IS NULL THEN NULL ELSE '/tmp' END AS "Host", (SELECT gss_authenticated AS "GSSAPI" FROM pg_catalog.pg_stat_gssapi WHERE pid = pg_catalog.pg_backend_pid()), ssl.ssl AS "SSL Connection", ssl.version AS "SSL Protocol", ssl.cipher AS "SSL Cipher", NULL AS "SSL Compression" FROM pg_catalog.pg_stat_ssl ssl WHERE pid = pg_catalog.pg_backend_pid() ; /************************/ Current Connection Information -[ RECORD 1 ]------+--------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Backend PID | 29007 Server Address | Server Port | 5433 Client Address | Client Port | Socket Directory | /tmp Host | GSSAPI | f SSL Connection | f SSL Protocol | SSL Cipher | SSL Compression | Rergards, Maiquel Grassi. -
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-03-27T13:23:32Z
> Hi Nathan! > > Sorry for the delay, I was busy with other professional as well as personal activities. > I made all the changes you suggested. I removed the variables and started using > views "pg_stat_ssl" and "pg_stat_gssapi". I handled the PostgreSQL versioning regarding the views used. --//-- Olá Nathan! I think we are very close to possibly finishing this patch, and I would like your help to do it. Is there any correction you deem necessary still? Regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Sami Imseih <simseih@amazon.com> — 2024-03-27T23:08:24Z
Hi, Thanks for working on this. I have a few comments about the current patch. 1/ I looked through other psql-meta commands and the “+” adds details but does not change output format. In this patch, conninfo and conninfo+ have completely different output. The former is a string with all the details and the latter is a table. Should conninfo just be a table with the minimal info and additional details can be displayed with "+" appended? instead of \conninfo displaying a string You are connected to database "postgres" as user "postgres" on host "127.0.01" (address "127.0.0.1") at port "5432". It can instead display the same information in table format Current Connection Information -[ RECORD 1 ]-----------+----------------------- Database | postgres Authenticated User | postgres Socket Directory | Host | 127.0.0.1 Port | 5432 and \conninfo+ can show the rest of the details Current Connection Information -[ RECORD 1 ]----------+---------------------------- Database | postgres Authenticated User | postgres Socket Directory | Host | 127.0.0.1 Port | 5432 Backend PID | 1234 ... ..... 2/ GSSAPI details should show the full details, such as "principal", "encrypted" and "credentials_delegated". This also means that pg_stat_ssl will need to be joined with pg_stat_gssapi FROM pg_catalog.pg_stat_ssl ssl LEFT JOIN pg_catalog.pg_stat_gssapi gssapi ON ssl.pid = gssapi.pid ssl.pid = pg_catalog.pg_backend_pid() 3/ A very nice to have is "Application Name", in the case one sets the application_name within a connection or in the connection string. Regards, Sami Imseih Amazon Web Services (AWS)
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-03-28T19:07:47Z
Hi Sami! (v21) First and foremost, thank you very much for the review. > 1/ I looked through other psql-meta commands and the “+” adds details but > does not change output format. In this patch, conninfo and conninfo+ > have completely different output. The former is a string with all the details > and the latter is a table. Should conninfo just be a table with the minimal info > and additional details can be displayed with "+" appended? The initial and central idea was always to keep the metacommand "\conninfo" in its original state, that is, to preserve the string as it is. The idea of "\conninfo+" is to expand this to include more information. If I change the "\conninfo" command and transform it into a table, I would have to remove all the translation part (files) that has already been implemented in the past. I believe that keeping the string and its translations is a good idea and there is no great reason to change that. > 2/ GSSAPI details should show the full details, such as "principal", > "encrypted" and "credentials_delegated". In this new patch, I added these columns. I handled the 'server versions' for cases where the 'credentials_delegated' column is not in the view. It turned out well. Thank you for the idea. 3/ A very nice to have is "Application Name", in the case one sets the application_name within a connection or in the connection string. I did the same here. I liked your idea and added this column in the SQL. Look below to see how it turned out after it's finished. Exemple 1: [postgres@localhost ~]$ /home/pgsql-17devel/bin/psql -x -p 5432 psql (17devel, server 15.6) 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 -[ RECORD 1 ]----------------+--------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Application Name | psql Backend PID | 19439 Server Address | Server Port | 5432 Client Address | Client Port | Socket Directory | /tmp Host | SSL Connection | f SSL Protocol | SSL Cipher | SSL Compression | GSSAPI Authenticated | f GSSAPI Principal | GSSAPI Encrypted | f GSSAPI Credentials Delegated | Exemple 2: [postgres@localhost ~]$ /home/pgsql-17devel/bin/psql -x -p 5000 psql (17devel) Type "help" for help. postgres=# \conninfo You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5000". postgres=# \conninfo+ Current Connection Information -[ RECORD 1 ]----------------+--------- Database | postgres Authenticated User | postgres System User | Current User | postgres Session User | postgres Application Name | psql Backend PID | 19468 Server Address | Server Port | 5000 Client Address | Client Port | Socket Directory | /tmp Host | SSL Connection | f SSL Protocol | SSL Cipher | SSL Compression | GSSAPI Authenticated | f GSSAPI Principal | GSSAPI Encrypted | f GSSAPI Credentials Delegated | f Regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Sami Imseih <simseih@amazon.com> — 2024-03-29T06:13:56Z
Thank you for the updated patch. First and foremost, thank you very much for the review. > The initial and central idea was always to keep the metacommand > "\conninfo" in its original state, that is, to preserve the string as it is. > The idea of "\conninfo+" is to expand this to include more information. > If I change the "\conninfo" command and transform it into a table, > I would have to remove all the translation part (files) that has already > been implemented in the past. I believe that keeping the string and > its translations is a good idea and there is no great reason to change that. You are right. Not much to be gained to change this. For the current patch, I have a few more comments. 1/ The output should be reorganized to show the fields that are part of “conninfo” first. I suggest it should look like this: Current Connection Information -[ RECORD 1 ]----------------+--------- Database | postgres Authenticated User | postgres Socket Directory | /tmp Host | Server Port | 5432 Server Address | Client Address | Client Port | Backend PID | 30846 System User | Current User | postgres Session User | postgres Application Name | psql SSL Connection | f SSL Protocol | SSL Cipher | SSL Compression | GSSAPI Authenticated | f GSSAPI Principal | GSSAPI Encrypted | f GSSAPI Credentials Delegated | f With regards to the documentation. I think it's a good idea that every field has an description. However, I have some comments: 1/ For the description of the conninfo command, what about simplifying like below? "Outputs a string displaying information about the current database connection. When + is appended, more details about the connection are displayed in table format:" 2/ I don't think the descriptions need to start with "Displays". It is very repetitive. 3/ For the "Socket Directory" description, this could be NULL if the host was not specified. what about the below? "The socket directory of the connection. NULL if the host or hostaddr are specified for the connection" 4/ For most of the fields, they are just the output of a function, such as "pg_catalog.system_user()". What about the docs simply link to the documentation of the function. This way we are not copying descriptions and have to be concerned if the description of the function changes in the future. 5/ "true" and "false", do not need double quotes. This is not the convention used in other places docs. Regards, Sami
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-03-30T16:23:24Z
> For the current patch, I have a few more comments. > 1/ The output should be reorganized to show the fields that are part of “conninfo” first. > With regards to the documentation. I think it's a good idea that every field has an > description. However, I have some comments: > 1/ For the description of the conninfo command, what about simplifying like below? > "Outputs a string displaying information about the current database connection. When + is appended, more details about the connection are displayed in table format:" > 2/ I don't think the descriptions need to start with "Displays". It is very repetitive. > 3/ For the "Socket Directory" description, this could be NULL if the host was not specified. > What about the below? > "The socket directory of the connection. NULL if the host or hostaddr are specified for the connection" > 4/ For most of the fields, they are just the output of a function, such as "pg_catalog.system_user()". What about the docs simply > link to the documentation of the function. This way we are not copying descriptions and have to be concerned if the description > of the function changes in the future. > 5/ "true" and "false", do not need double quotes. This is not the convention used in other places docs. -----//----- Hi Sami! (v22) I did everything you mentioned earlier, that is, I followed all your suggestions. However, I didn't complete item 4. I'm not sure, but I believe that linking it to the documentation could confuse the user a bit. I chose to keep the descriptions as they were. However, if you have any ideas on how we could outline it, let me know and perhaps we can implement it. Thank you so much! Exemples: [postgres@localhost bin]$ ./psql -x -p 5000 -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 "5000". postgres=# \conninfo+ Current Connection Information -[ RECORD 1 ]----------------+---------- Database | postgres Authenticated User | postgres Socket Directory | Host | 127.0.0.1 Server Port | 5000 Server Address | 127.0.0.1 Client Address | 127.0.0.1 Client Port | 33100 Backend PID | 2974 System User | Current User | postgres Session User | postgres Application Name | psql SSL Connection | f SSL Protocol | SSL Cipher | SSL Compression | GSSAPI Authenticated | f GSSAPI Principal | GSSAPI Encrypted | f GSSAPI Credentials Delegated | f postgres=# \q [postgres@localhost bin]$ ./psql -x -p 5432 -h localhost Password for user postgres: psql (17devel, server 15.6) 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 -[ RECORD 1 ]----------------+---------- Database | postgres Authenticated User | postgres Socket Directory | Host | localhost Server Port | 5432 Server Address | ::1 Client Address | ::1 Client Port | 57010 Backend PID | 3000 System User | Current User | postgres Session User | postgres Application Name | psql SSL Connection | f SSL Protocol | SSL Cipher | SSL Compression | GSSAPI Authenticated | f GSSAPI Principal | GSSAPI Encrypted | f GSSAPI Credentials Delegated | Regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-03-30T17:09:19Z
Hello, Note that, in the patch as posted, the column names are not translatable. In order to be translatable, the code needs to do something like appendPQExpBuffer(&buf, " NULL AS \"%s\",\n" " NULL AS \"%s\",\n" " NULL AS \"%s\",\n" " NULL AS \"%s\",\n", _("SSL Connection"), _("SSL Protocol"), _("SSL Cipher"), _("SSL Compression")); instead of appendPQExpBuffer(&buf, " NULL AS \"SSL Connection\",\n" " NULL AS \"SSL Protocol\",\n" " NULL AS \"SSL Cipher\",\n" " NULL AS \"SSL Compression\",\n"); Please list me as reviewer for this patch, as I provided significant guidance before it was even posted. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ -
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-03-31T21:39:54Z
Note that, in the patch as posted, the column names are not translatable. In order to be translatable, the code needs to do something like appendPQExpBuffer(&buf, " NULL AS \"%s\",\n" " NULL AS \"%s\",\n" " NULL AS \"%s\",\n" " NULL AS \"%s\",\n", _("SSL Connection"), _("SSL Protocol"), _("SSL Cipher"), _("SSL Compression")); instead of appendPQExpBuffer(&buf, " NULL AS \"SSL Connection\",\n" " NULL AS \"SSL Protocol\",\n" " NULL AS \"SSL Cipher\",\n" " NULL AS \"SSL Compression\",\n"); Please list me as reviewer for this patch, as I provided significant guidance before it was even posted. -----//----- Hello! (v23) Yes Álvaro, I have already appointed you as the patch reviewer. It's true, even before publishing it on Commifest, you have already provided good ideas and guidance. I adjusted the translation syntax for the SSL and GSSAPI columns. After your validation, that is, an okay confirmation that it's fine, I'll proceed with the others. Test: [postgres@localhost bin]$ ./psql -x -p 5000 -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 "5000". postgres=# \conninfo+ Current Connection Information -[ RECORD 1 ]----------------+---------- Database | postgres Authenticated User | postgres Socket Directory | Host | 127.0.0.1 Server Port | 5000 Server Address | 127.0.0.1 Client Address | 127.0.0.1 Client Port | 52966 Backend PID | 1693 System User | Current User | postgres Session User | postgres Application Name | psql SSL Connection | f SSL Protocol | SSL Cipher | SSL Compression | GSSAPI Authenticated | f GSSAPI Principal | GSSAPI Encrypted | f GSSAPI Credentials Delegated | f Regards, Maiquel Grassi. -
Re: Psql meta-command conninfo+
Sami Imseih <simseih@amazon.com> — 2024-03-31T22:59:44Z
>. However, > I didn't complete item 4. I'm not sure, but I believe that linking it to the documentation > could confuse the user a bit. I chose to keep the descriptions as they were. However, if > you have any ideas on how we could outline it, let me know and perhaps we can > implement it. That is fair, after spending some time thinking about this, it is better to make the documentation as crystal clear as possible. I do have some rewording suggestions for the following fields. Database: The database name of the connection. Authenticated User: The authenticated database user of the connection. Socket Directory: The Unix socket directory of the connection. NULL if host or hostaddr are specified. Host: The host name or address of the connection. NULL if a Unix socket is used. Server Port: The IP address of the connection. NULL if a Unix socket is used. Server Address: The IP address of the host name. NULL if a Unix socket is used. Client Address: The IP address of the client connected to this backend. NULL if a Unix socket is used. Client Port: The port of the client connected to this backend. NULL if a Unix socket is used. Backend PID: The process id of the backend for the connection. Application name: <literal>psql<literal> is the default for a psql connection unless otherwise specified in the <xref linkend="guc-application-name"/> configuration parameter. For System User, you should use the full definition here https://github.com/postgres/postgres/blob/master/doc/src/sgml/func.sgml#L24251-L24259. The current path is missing the full description. Regards, Sami
-
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-04-01T10:51:18Z
On 2024-Mar-31, Maiquel Grassi wrote: > Yes Álvaro, I have already appointed you as the patch reviewer. > It's true, even before publishing it on Commifest, you have > already provided good ideas and guidance. Thanks. > I adjusted the translation syntax for the SSL and GSSAPI columns. > After your validation, that is, an okay confirmation that it's fine, I'll > proceed with the others. I meant those columns to be just examples, but this should be applied to all the other columns in the output as well. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "No necesitamos banderas No reconocemos fronteras" (Jorge González)
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-04-01T14:53:05Z
Hi! (v24) > I meant those columns to be just examples, but this should be applied to > all the other columns in the output as well. Applied the translation to all columns. Regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-04-01T16:22:41Z
Hello Yeah, that's what I meant about the translations, thanks. Two more comments, - You don't need a two-level conditional here if (pset.sversion >= 90500) { if (pset.sversion < 140000) appendPQExpBuffer(&buf, " ssl.ssl AS \"%s\",\n" " ssl.version AS \"%s\",\n" " ssl.cipher AS \"%s\",\n" " ssl.compression AS \"%s\",\n", _("SSL Connection"), _("SSL Protocol"), _("SSL Cipher"), _("SSL Compression")); if (pset.sversion >= 140000) appendPQExpBuffer(&buf, " ssl.ssl AS \"%s\",\n" " ssl.version AS \"%s\",\n" " ssl.cipher AS \"%s\",\n" " NULL AS \"%s\",\n", _("SSL Connection"), _("SSL Protocol"), _("SSL Cipher"), _("SSL Compression")); } else appendPQExpBuffer(&buf, " NULL AS \"%s\",\n" " NULL AS \"%s\",\n" " NULL AS \"%s\",\n" " NULL AS \"%s\",\n", _("SSL Connection"), _("SSL Protocol"), _("SSL Cipher"), _("SSL Compression")); Instead you can just do something like if (pset.version >= 140000) one thing; else if (pset.version > 90500) second thing; else third thing; This also appears where you add the GSSAPI columns; and it's also in the final part where you append the FROM clause, though it looks a bit different there. - You have three lines to output a semicolon at the end of the query based on version number. Remove the first two, and just have a final one where the semicolon is added unconditionally. - I don't think one <para> for each item in the docs is reasonable. There's too much vertical whitespace in the output. Maybe do this instead: [...] database connection. When <literal>+</literal> is appended, more details about the connection are displayed in table format: <simplelist> <member> <term>Database:</term> The name of the current database on this connection. </member> <member> <term>Authenticated User:</term> The authenticated user at the time of psql connection with the server. </member> ... </simplelist> - This text is wrong to start with "Returns the": System User: Returns the authentication method and the identity (if any) that the user presented during the authentication cycle before they were assigned a database role. It is represented as auth_method:identity or NULL if the user has not been authenticated. That minor point aside, I disagree with Sami about repeating the docs for system_user() here. I would just say "The authentication data provided for this connection; see the function system_user() for more details." with a link to the appropriate section of the docs. Making us edit this doc if we ever modify the behavior of the function is not great. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "We're here to devour each other alive" (Hobbes) -
Re: Psql meta-command conninfo+
Sami Imseih <simseih@amazon.com> — 2024-04-01T17:45:10Z
> That minor point aside, I disagree with Sami about repeating the docs > for system_user() here. I would just say "The authentication data > provided for this connection; see the function system_user() for more > details." +1. FWIW; Up the thread [1], I did mention we should link to the functions page, but did not have a very strong opinion on that. I do like your suggestion and the same should be done for "Current User" and "Session User" as well. [1] https://www.postgresql.org/message-id/640B2586-EECF-44C0-B474-CA8510F8EAFC%40amazon.com Regards, Sami
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-04-01T19:19:58Z
Database: The database name of the connection. Authenticated User: The authenticated database user of the connection. Socket Directory: The Unix socket directory of the connection. NULL if host or hostaddr are specified. Host: The host name or address of the connection. NULL if a Unix socket is used. Server Port: The IP address of the connection. NULL if a Unix socket is used. Server Address: The IP address of the host name. NULL if a Unix socket is used. Client Address: The IP address of the client connected to this backend. NULL if a Unix socket is used. Client Port: The port of the client connected to this backend. NULL if a Unix socket is used. Backend PID: The process id of the backend for the connection. Application name: <literal>psql<literal> is the default for a psql connection unless otherwise specified in the <xref linkend="guc-application-name"/> configuration parameter. -----//----- Hi Sami, I considered your suggestions and Álvaro's suggestions Regards, Maiquel Grassi.
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-04-01T19:51:57Z
Amigos, boa tarde! (v25) > if (pset.version >= 140000) > one thing; > else if (pset.version > 90500) > second thing; > else > third thing; > > This also appears where you add the GSSAPI columns; and it's also in the > final part where you append the FROM clause, though it looks a bit > different there. > > - You have three lines to output a semicolon at the end of the query > based on version number. Remove the first two, and just have a final > one where the semicolon is added unconditionally. Adjustment made. > - I don't think one <para> for each item in the docs is reasonable. > There's too much vertical whitespace in the output. Maybe do this > instead: > > [...] > database connection. When <literal>+</literal> is appended, > more details about the connection are displayed in table > format: > > <simplelist> > <member> > <term>Database:</term> The name of the current > database on this connection. > </member> > > <member> > <term>Authenticated User:</term> The authenticated > user at the time of psql connection with the server. > </member> > > ... > </simplelist> Adjustment made. But I think it needs a review glance. > - This text is wrong to start with "Returns the": > > System User: Returns the authentication method and the identity (if > any) that the user presented during the authentication cycle before > they were assigned a database role. It is represented as > auth_method:identity or NULL if the user has not been authenticated. > > That minor point aside, I disagree with Sami about repeating the docs > for system_user() here. I would just say "The authentication data > provided for this connection; see the function system_user() for more > details." with a link to the appropriate section of the docs. Making > us edit this doc if we ever modify the behavior of the function is not > great. Here I considered your suggestion (Sami and Álvaro's). However, I haven't yet added the links for the functions system_user(), current_user(), and session_user(). I'm not sure how to do it. Any suggestion on how to create/add the link? Regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Sami Imseih <simseih@amazon.com> — 2024-04-01T21:29:28Z
> Here I considered your suggestion (Sami and Álvaro's). However, I haven't yet > added the links for the functions system_user(), current_user(), and session_user(). > 'm not sure how to do it. Any suggestion on how to create/add the link? Here is an example [1] where the session information functions are referenced. The public doc for this example is in [2]. Something similar can be done here. For example: The session user's name; see the <function>session_user()</function> function in <xref linkend="functions-info-session-table"/> for more details. [1] https://github.com/postgres/postgres/blob/master/doc/src/sgml/system-views.sgml#L1663-L1664 [2] https://www.postgresql.org/docs/16/view-pg-locks.html Regards, Sami
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-04-03T10:01:08Z
Hello Sami, (v26) > Here is an example [1] where the session information functions are referenced. > The public doc for this example is in [2]. > Something similar can be done here. For example: > The session user's name; see the <function>session_user()</function> function in > <xref linkend="functions-info-session-table"/> for more details. I updated the patch. Thank you for this help. Regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Sami Imseih <simseih@amazon.com> — 2024-04-03T17:38:22Z
Building the docs fail for v26. The error is: ref/psql-ref.sgml:1042: element member: validity error : Element term is not declared in member list of possible children </member> ^ I am able to build up to v24 before the <para> was replaced with <listitem><member> I tested building with a modified structure as below; the <listitem> is a <para> that has a <simplelist> within it. Each field is a simple list member and the the name of the fields should be <literal>. <varlistentry id="app-psql-meta-command-conninfo"> <term><literal>\conninfo[+]</literal></term> <listitem> <para> Outputs a string displaying information about the current database connection. When <literal>+</literal> is appended, more details about the connection are displayed in table format: <simplelist> <member><literal>Database:</literal>The database name of the connection.</member> <member><literal>Authenticated User:</literal>The authenticated database user of the connection.</member> <member><literal>Current User:</literal>The user name of the current execution context; see the <function>current_user()</function> function in <xref linkend="functions-info-session-table"/> for more details.</member> </simplelist> </para> </listitem> </varlistentry> Regards, Sami -
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-04-04T14:34:20Z
Building the docs fail for v26. The error is: ref/psql-ref.sgml:1042: element member: validity error : Element term is not declared in member list of possible children </member> ^ I am able to build up to v24 before the <para> was replaced with <listitem><member> I tested building with a modified structure as below; the <listitem> is a <para> that has a <simplelist> within it. Each field is a simple list member and the the name of the fields should be <literal>. <varlistentry id="app-psql-meta-command-conninfo"> <term><literal>\conninfo[+]</literal></term> <listitem> <para> Outputs a string displaying information about the current database connection. When <literal>+</literal> is appended, more details about the connection are displayed in table format: <simplelist> <member><literal>Database:</literal>The database name of the connection.</member> <member><literal>Authenticated User:</literal>The authenticated database user of the connection.</member> <member><literal>Current User:</literal>The user name of the current execution context; see the <function>current_user()</function> function in <xref linkend="functions-info-session-table"/> for more details.</member> </simplelist> </para> </listitem> </varlistentry> ---//--- Hi Sami! (v27) I made the adjustment in the documentation. Thank you for the time dedicated to this feature. Regards, Maiquel Grassi. -
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-04-04T14:57:46Z
Hi folks, (v28) I made a modification to a variable in the 'host' column in the archive 'describe.c'. Test: [postgres@localhost ~]$ /home/pgsql-17devel/bin/psql -x -p 5432 psql (17devel, server 15.6) 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 -[ RECORD 1 ]----------------+--------- Database | postgres Authenticated User | postgres Socket Directory | /tmp Host | Server Port | 5432 Server Address | Client Address | Client Port | Backend PID | 26728 System User | Current User | postgres Session User | postgres Application Name | psql SSL Connection | f SSL Protocol | SSL Cipher | SSL Compression | GSSAPI Authenticated | f GSSAPI Principal | GSSAPI Encrypted | f GSSAPI Credentials Delegated | postgres=# \q [postgres@localhost ~]$ /home/pgsql-17devel/bin/psql -x -p 5000 psql (17devel) Type "help" for help. postgres=# \conninfo You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5000". postgres=# \conninfo+ Current Connection Information -[ RECORD 1 ]----------------+--------- Database | postgres Authenticated User | postgres Socket Directory | /tmp Host | Server Port | 5000 Server Address | Client Address | Client Port | Backend PID | 26730 System User | Current User | postgres Session User | postgres Application Name | psql SSL Connection | f SSL Protocol | SSL Cipher | SSL Compression | GSSAPI Authenticated | f GSSAPI Principal | GSSAPI Encrypted | f GSSAPI Credentials Delegated | f Regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Peter Eisentraut <peter@eisentraut.org> — 2024-04-04T15:19:53Z
The existing \conninfo command gets its values from libpq APIs. You are changing all of this to make a server query, which is a totally different thing. If we wanted to take a change like this, I don't think it should be reusing the \conninfo command. But I don't really see the point of this. The information you are querying is already available in various system views. This proposal is just a shorthand for a collection of various random things some people like to see. Like, by what reason is application name included as connection info? Why not any other session settings? What about long-term maintenance: By what logic should things be added to this?
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-04-04T15:42:29Z
The existing \conninfo command gets its values from libpq APIs. You are changing all of this to make a server query, which is a totally different thing. If we wanted to take a change like this, I don't think it should be reusing the \conninfo command. But I don't really see the point of this. The information you are querying is already available in various system views. This proposal is just a shorthand for a collection of various random things some people like to see. Like, by what reason is application name included as connection info? Why not any other session settings? What about long-term maintenance: By what logic should things be added to this? --//-- Hello Peter, thank you for your participation. No, "they are not random things that random people like to see", that's not true. Have you read the entire conversation from the beginning? We have already discussed it a bit and I have provided an explanation about the motivation to implement this expansion of the "\conninfo" command. The thing is, if you have really been or worked as a DBA on a daily basis, taking care of many databases and PostgreSQL clusters, something like this additional command is the shortcut that every DBA needs. The application name was a suggestion from a member. If you think it's not necessary, we can remove it. Furthermore, if you believe that the patch is not well implemented, you, being a PostgreSQL guru, tell me how I can improve the current patch and we move towards v29. I'm not in a hurry, I just want it to be implemented in the best possible shape. Best regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-04-04T15:53:37Z
On 2024-Apr-04, Peter Eisentraut wrote: > But I don't really see the point of this. The information you are querying > is already available in various system views. This proposal is just a > shorthand for a collection of various random things some people like to see. > Like, by what reason is application name included as connection info? Why > not any other session settings? What about long-term maintenance: By what > logic should things be added to this? Usability is precisely the point. You could also claim that we don't need \dconfig, since you could get the same thing by querying pg_settings for non-default settings. But \dconfig is very handy. I expect \conninfo+ to be equally useful. We don't give up command history just on the grounds that you can obtain the same effect by retyping the command. I'm not sure to what extent is it useful to make a distinction between the values that the client knows from those that the server knows. If it is, then we can redefine \conninfo+ to use the client values. The point about application_name is a valid one. I guess it's there because it's commonly given from the client side rather than being set server-side, even though it's still a GUC. Arguably we could remove it from \conninfo+, and claim that nothing that shows up in \dconfig should also appear in \conninfo+. Then users should get in the habit of using both to obtain a full picture. This sounds to me a very good compromise actually. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ Tom: There seems to be something broken here. Teodor: I'm in sackcloth and ashes... Fixed. http://postgr.es/m/482D1632.8010507@sigaev.ru -
Re: Psql meta-command conninfo+
Tom Lane <tgl@sss.pgh.pa.us> — 2024-04-04T15:58:59Z
Maiquel Grassi <grassi@hotmail.com.br> writes: >> The existing \conninfo command gets its values from libpq APIs. You are >> changing all of this to make a server query, which is a totally >> different thing. If we wanted to take a change like this, I don't think >> it should be reusing the \conninfo command. FWIW, I agree with Peter's concern here, for a couple of reasons: * If \conninfo involves a server query, then it will fail outright if the server is in an aborted transaction, or if there's something wrong with the connection --- which seems like one of the primary cases where you'd wish to use \conninfo. * What if there's a discrepancy between what libpq thinks and what the server thinks? (This is hardly unlikely for, say, host names.) In the current situation you can use \conninfo to investigate the client-side parameters and then use a manual query to see what the server thinks. If we replace \conninfo with a server query then there is no way at all to verify libpq parameters from the psql command line. So I concur that \conninfo should continue to report on libpq values and nothing else. We can certainly talk about expanding it within that charter, if there's useful stuff it doesn't show now. But a command that retrieves settings from the server should be a distinct thing. regards, tom lane
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-04-04T16:15:39Z
>> The existing \conninfo command gets its values from libpq APIs. You are >> changing all of this to make a server query, which is a totally >> different thing. If we wanted to take a change like this, I don't think >> it should be reusing the \conninfo command. FWIW, I agree with Peter's concern here, for a couple of reasons: * If \conninfo involves a server query, then it will fail outright if the server is in an aborted transaction, or if there's something wrong with the connection --- which seems like one of the primary cases where you'd wish to use \conninfo. * What if there's a discrepancy between what libpq thinks and what the server thinks? (This is hardly unlikely for, say, host names.) In the current situation you can use \conninfo to investigate the client-side parameters and then use a manual query to see what the server thinks. If we replace \conninfo with a server query then there is no way at all to verify libpq parameters from the psql command line. So I concur that \conninfo should continue to report on libpq values and nothing else. We can certainly talk about expanding it within that charter, if there's useful stuff it doesn't show now. But a command that retrieves settings from the server should be a distinct thing. ---//--- Well, I can revert \conninfo to its original state and keep \conninfo+ as it is, perhaps removing the application name, as I believe everything else is important for a DBA/SysAdmin. Do you think we can proceed with the patch this way? I am open to ideas that make \conninfo not limited to just four or five basic pieces of information and easily bring everything else to the screen. Regards, Maiquel Grassi.
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-04-04T16:59:55Z
Hi! (v29) I left the command \conninfo in its original form. I removed the 'Application Name' column from the \conninfo+ command. Thanks, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Sami Imseih <simseih@amazon.com> — 2024-04-04T19:20:41Z
> The point about application_name is a valid one. I guess it's there > because it's commonly given from the client side rather than being set >server-side, even though it's still a GUC. Arguably we could remove it > from \conninfo+, and claim that nothing that shows up in \dconfig should > also appear in \conninfo+. Then users should get in the habit of using > both to obtain a full picture. This sounds to me a very good compromise > actually. Perhaps another reason to remove "application_name" is because the value can be modified after the connection is established. If that is also another good reason, the same can be said about "Current User" and "Session User" as those could be modified with SET commands. This way conninfo could be only for attributes that will not change during the lifetime of the connection. Also, I just realized that pg_stat_ssl and pg_stat_gssapi will both return 0 rows if there is a set role/set session authorization, this means \conninfo+ will return empty. postgres=> select current_user; current_user -------------- user1 (1 row) postgres=> select * from pg_stat_ssl ; pid | ssl | version | cipher | bits | client_dn | client_serial | issuer_dn -------+-----+---------+-----------------------------+------+-----------+---------------+----------- 27223 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | | | (1 row) postgres=> set role = user2; SET postgres=> select * from pg_stat_ssl ; pid | ssl | version | cipher | bits | client_dn | client_serial | issuer_dn -----+-----+---------+--------+------+-----------+---------------+----------- (0 rows) postgres=> select current_user; current_user -------------- user2 (1 row) postgres=> reset role; RESET postgres=> select current_user; current_user -------------- user1 (1 row) postgres=> select * from pg_stat_ssl ; pid | ssl | version | cipher | bits | client_dn | client_serial | issuer_dn -------+-----+---------+-----------------------------+------+-----------+---------------+----------- 27223 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | | | (1 row) Regards, Sami
-
Re: Psql meta-command conninfo+
Peter Eisentraut <peter@eisentraut.org> — 2024-04-05T14:50:18Z
On 04.04.24 18:15, Maiquel Grassi wrote: > Well, I can revert \conninfo to its original state and keep \conninfo+ > as it is, perhaps removing the application name, as I believe everything > else is important for a DBA/SysAdmin. Do you think we can proceed > with the patch this way? I am open to ideas that make \conninfo not > limited to just four or five basic pieces of information and easily bring > everything else to the screen. The original \conninfo was designed to report values from the libpq API about what libpq connected to. And the convention in psql is that "+" provide more or less the same information but a bit more. So I think it is wrong to make "\conninfo+" something fundamentally different than "\conninfo". And even more so if it contains information that isn't really "connection information". For example, current_user() doesn't make sense here, I think. I mean, if you want to add a command \some_useful_status_information, we can talk about that, but let's leave \conninfo to what it does. But I think there are better ways to implement this kind of server-side status, for example, with a system view. One problem in this patch is that it has no tests. Any change in any of the involved functions or views will silently break this. (Note that plain \conninfo doesn't have this problem to this extent because it only relies on libpq function calls. Also, a system view would not have this problem.)
-
Re: Psql meta-command conninfo+
Sami Imseih <simseih@amazon.com> — 2024-04-05T16:04:53Z
> The original \conninfo was designed to report values from the libpq API > about what libpq connected to. And the convention in psql is that "+" > provide more or less the same information but a bit more. So I think it > is wrong to make "\conninfo+" something fundamentally different than > "\conninfo". That is a fair argument. Looking at this a bit more, it looks like we can enhance the GSSAPI output of conninfo to get the fields that GSSAPI fields that conninfo+ was aiming for Right now it just shows: printf(_("GSSAPI-encrypted connection\n")); but we can use libpq to get the other GSSAPI attributes in the output. > And even more so if it contains information that isn't really > "connection information". For example, current_user() doesn't make > sense here, I think. > I mean, if you want to add a command \some_useful_status_information, we > can talk about that, but let's leave \conninfo to what it does. > But I think there are better ways to implement this kind of server-side > status, for example, with a system view. What about a \sessioninfo command that shows all the information for the current session, PID, app_name, current_user, session_user, system_user, client_address, client_port, etc. These will be the fields that we cannot gather if the connection is broken for whatever reason. The distinction here is \sessioninfo are the details after the connection is established and could possibly be changed during the connection. Regards, Sami -
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-05-29T12:37:21Z
Hi, Is there someone willing to help me with this development and guide the patch so that it can be committed one day? Regards, Maiquel.
-
Re: Psql meta-command conninfo+
Nathan Bossart <nathandbossart@gmail.com> — 2024-06-03T18:30:21Z
On Wed, May 29, 2024 at 12:37:21PM +0000, Maiquel Grassi wrote: > Is there someone willing to help me with this development and guide the > patch so that it can be committed one day? From a quick skim of the latest messages in this thread, it sounds like there are a couple of folks who think \conninfo (and consequently \conninfo+) should only report values from the libpq API. I think they would prefer server-side information to be provided via a system view or maybe an entirely new psql meta-command. IIUC a new system view would more-or-less just gather information from other system views and functions. A view would be nice because it could be used outside psql, but I'm not sure to what extent we are comfortable adding system views built on other system views. Something like \sessioninfo (as proposed by Sami) would look more similar to what you have in your latest patch, i.e., we'd teach psql about a complicated query for gathering all this disparate information. IMHO a good way to help move this forward is to try implementing it as a system view so that we can compare the two approaches. I've had luck in the past with implementing something a couple different ways to help drive consensus. -- nathan
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-06-05T12:32:20Z
From a quick skim of the latest messages in this thread, it sounds like there are a couple of folks who think \conninfo (and consequently \conninfo+) should only report values from the libpq API. I think they would prefer server-side information to be provided via a system view or maybe an entirely new psql meta-command. IIUC a new system view would more-or-less just gather information from other system views and functions. A view would be nice because it could be used outside psql, but I'm not sure to what extent we are comfortable adding system views built on other system views. Something like \sessioninfo (as proposed by Sami) would look more similar to what you have in your latest patch, i.e., we'd teach psql about a complicated query for gathering all this disparate information. IMHO a good way to help move this forward is to try implementing it as a system view so that we can compare the two approaches. I've had luck in the past with implementing something a couple different ways to help drive consensus. --//-- Great Nathan, we can follow that path of the view. I leave it open for anyone in the thread who has been following from the beginning to start the development of the view. Even you, if you have the interest and time to do it. At this exact moment, I am involved in a "my baby born" project, so I am unable to stop to look into this. If someone wants to start, I would appreciate it. Regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-08-02T12:11:11Z
Hi, I have read the entire thread discussion. I understood the importance of this enhancement related to /conninfo+ meta command. I really appreciate the efforts of Maiquel and suggestions made by the reviewers. According to best of my understanding, libpq API should be used instead of creating server query for conninfo+ meta command. Building on the patch (v29) provided by Maiquel, I made changes to retrieve some extra information related to connection from libpq API. Extra information includes: - Protocol Version - SSL Connection - GSSAPI Authenticated - Client Encoding - Server Encoding - Session User - Backend PID Output of \conninfo+: 1. $ bin/psql --port=5430 postgres -h localhost psql (18devel) Type "help" for help. postgres=# \conninfo+ You are connected to database "postgres" as user "hunaid" on host "localhost" (address "127.0.0.1") at port "5430". Protocol Version: 3 SSL Connection: no GSSAPI Authenticated: no Client Encoding: UTF8 Server Encoding: UTF8 Session User: hunaid Backend PID: 163816 I have also edited the documentation and added it to the patch. Please let me know if any changes are required. Regards, Hunaid Sohail On Wed, Jun 5, 2024 at 5:32 PM Maiquel Grassi <grassi@hotmail.com.br> wrote: > From a quick skim of the latest messages in this thread, it sounds like > there are a couple of folks who think \conninfo (and consequently > \conninfo+) should only report values from the libpq API. I think they > would prefer server-side information to be provided via a system view or > maybe an entirely new psql meta-command. > > IIUC a new system view would more-or-less just gather information from > other system views and functions. A view would be nice because it could be > used outside psql, but I'm not sure to what extent we are comfortable > adding system views built on other system views. Something like > \sessioninfo (as proposed by Sami) would look more similar to what you have > in your latest patch, i.e., we'd teach psql about a complicated query for > gathering all this disparate information. > > IMHO a good way to help move this forward is to try implementing it as a > system view so that we can compare the two approaches. I've had luck in > the past with implementing something a couple different ways to help drive > consensus. > > --//-- > > Great Nathan, we can follow that path of the view. I leave it open for > anyone in the thread who has been following from the beginning to start the > development of the view. Even you, if you have the interest and time to do > it. At this exact moment, I am involved in a "my baby born" project, so I > am unable to stop to look into this. If someone wants to start, I would > appreciate it. > Regards, > Maiquel Grassi. >
-
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-09-09T13:22:46Z
Hi Hunaid On 02.08.24 14:11, Hunaid Sohail wrote: > > I have also edited the documentation and added it to the patch. Please > let me know if any changes are required. > I just wanted to review this patch again but v30 does not apply === Applying patches on top of PostgreSQL commit ID d8df7ac5c04cd17bf13bd3123dcfcaf8007c6280 === /etc/rc.d/jail: WARNING: Per-jail configuration via jail_* variables is obsolete. Please consider migrating to /etc/jail.conf. === applying patch ./v30-0001-psql-meta-command-conninfo-plus.patch patch unexpectedly ends in middle of line gpatch: **** Only garbage was found in the patch input. I will set the status to "Waiting on Author". -- Jim
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-09-10T04:32:56Z
Hi, I have attached a rebased patch. Regards, Hunaid Sohail On Mon, Sep 9, 2024 at 6:22 PM Jim Jones <jim.jones@uni-muenster.de> wrote: > Hi Hunaid > > On 02.08.24 14:11, Hunaid Sohail wrote: > > > > I have also edited the documentation and added it to the patch. Please > > let me know if any changes are required. > > > > I just wanted to review this patch again but v30 does not apply > > === Applying patches on top of PostgreSQL commit ID > d8df7ac5c04cd17bf13bd3123dcfcaf8007c6280 === > /etc/rc.d/jail: WARNING: Per-jail configuration via jail_* variables is > obsolete. Please consider migrating to /etc/jail.conf. > === applying patch ./v30-0001-psql-meta-command-conninfo-plus.patch > patch unexpectedly ends in middle of line > gpatch: **** Only garbage was found in the patch input. > > > I will set the status to "Waiting on Author". > > -- > Jim > >
-
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-09-10T16:00:51Z
On 10.09.24 06:32, Hunaid Sohail wrote: > > I have attached a rebased patch. Thanks. Is \conninfo+ no longer supposed to return the results in tabular form? At least it wasn't the case till v28. $ /usr/local/postgres-dev/bin/psql -d postgres -h 0 -c "\conninfo+" You are connected to database "postgres" as user "jim" on host "0" (address "0.0.0.0") at port "5432". Protocol Version: 3 SSL Connection: no GSSAPI Authenticated: no Client Encoding: UTF8 Server Encoding: UTF8 Session User: jim Backend PID: 579041 $ /usr/local/postgres-dev/bin/psql -d postgres -h 127.0.0.1 -c "\conninfo+" You are connected to database "postgres" as user "jim" on host "127.0.0.1" at port "5432". Protocol Version: 3 SSL Connection: no GSSAPI Authenticated: no Client Encoding: UTF8 Server Encoding: UTF8 Session User: jim Backend PID: 579087 Sorry if I missed that in the thread. v31 has a couple of small indentation problems: /home/jim/patches/conninfo/v31-0001-Add-psql-meta-command-conninfo-plus.patch:87: indent with spaces. show_verbose = strchr(cmd, '+') ? true : false; /home/jim/patches/conninfo/v31-0001-Add-psql-meta-command-conninfo-plus.patch:106: trailing whitespace. Checking patch doc/src/sgml/ref/psql-ref.sgml... Checking patch src/bin/psql/command.c... Checking patch src/bin/psql/help.c... Applied patch doc/src/sgml/ref/psql-ref.sgml cleanly. Applied patch src/bin/psql/command.c cleanly. Applied patch src/bin/psql/help.c cleanly. warning: 2 lines add whitespace errors. -- Jim
-
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-09-10T16:16:54Z
On 2024-Sep-10, Jim Jones wrote: > Is \conninfo+ no longer supposed to return the results in tabular form? > At least it wasn't the case till v28. I suspect the reason it's no longer a table is that it was previously a query (which is easily printed as a table by calling printQuery) and now it's just a client-side thing, and Hunaid didn't know how to handle that as a table. The good news is, it should be really easy to do printTableInit(), then a bunch of printTableAddHeader() and printTableAddCell(), end with printTable(). I think the tabular format is better for sure. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "La primera ley de las demostraciones en vivo es: no trate de usar el sistema. Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-09-11T08:16:21Z
Hi, On Tue, Sep 10, 2024 at 9:16 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > On 2024-Sep-10, Jim Jones wrote: > > > Is \conninfo+ no longer supposed to return the results in tabular form? > > At least it wasn't the case till v28. > > I suspect the reason it's no longer a table is that it was previously a > query (which is easily printed as a table by calling printQuery) and now > it's just a client-side thing, and Hunaid didn't know how to handle that > as a table. The good news is, it should be really easy to do > printTableInit(), then a bunch of printTableAddHeader() and > printTableAddCell(), end with printTable(). I think the tabular format > is better for sure. > I have made the requested changes. Now output is returned in tabular form. Indentation/whitespace issues are fixed. $bin/psql --port=5430 postgres postgres=# \conninfo+ You are connected to database "postgres" as user "hunaid" via socket in "/tmp" at port "5430". Connection Information Parameter | Value ----------------------+-------- Protocol Version | 3 SSL Connection | no GSSAPI Authenticated | no Client Encoding | UTF8 Server Encoding | UTF8 Session User | hunaid Backend PID | 121800 (7 rows) Regards, Hunaid Sohail -
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-09-11T10:03:05Z
On 11.09.24 10:16, Hunaid Sohail wrote: > I have made the requested changes. Now output is returned in tabular > form. Indentation/whitespace issues are fixed. > > $bin/psql --port=5430 postgres > postgres=# \conninfo+ > You are connected to database "postgres" as user "hunaid" via socket > in "/tmp" at port "5430". > Connection Information > Parameter | Value > ----------------------+-------- > Protocol Version | 3 > SSL Connection | no > GSSAPI Authenticated | no > Client Encoding | UTF8 > Server Encoding | UTF8 > Session User | hunaid > Backend PID | 121800 > (7 rows) Thanks for working on this. Any particular reason for the design change? In v28 it returned a table with a single row and multiple columns --- one column per attribute. But now it returns multiple rows. In this case, I was expecting 1 row with 7 columns instead of 7 rows with 2 columns. Jim
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-09-11T11:35:20Z
Hi Jim, On Wed, Sep 11, 2024 at 3:03 PM Jim Jones <jim.jones@uni-muenster.de> wrote: > Thanks for working on this. > > Any particular reason for the design change? In v28 it returned a table > with a single row and multiple columns --- one column per attribute. But > now it returns multiple rows. In this case, I was expecting 1 row with 7 > columns instead of 7 rows with 2 columns. > I am not sure which design you are referring to. I haven't applied the v28 patch but the original author in thread [1] provided sample output. The output is in tabular form with 2 columns and multiple rows. [1] https://www.postgresql.org/message-id/CP8P284MB249615AED23882E1E185C8ABEC3C2%40CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM Regards, Hunaid Sohail
-
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-09-12T11:08:01Z
On 11.09.24 13:35, Hunaid Sohail wrote: > Hi Jim, > > On Wed, Sep 11, 2024 at 3:03 PM Jim Jones <jim.jones@uni-muenster.de> > wrote: > > Thanks for working on this. > > Any particular reason for the design change? In v28 it returned a > table > with a single row and multiple columns --- one column per > attribute. But > now it returns multiple rows. In this case, I was expecting 1 row > with 7 > columns instead of 7 rows with 2 columns. > > > I am not sure which design you are referring to. > I haven't applied the v28 patch but the original author in thread [1] > provided sample output. The output is in tabular form with 2 columns > and multiple rows. > > [1] https://www.postgresql.org/message-id/CP8P284MB249615AED23882E1E185C8ABEC3C2%40CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM > It may look like this, but it is a single record --- mind the header "-[ RECORD 1 ]----------------+---------". psql was called in expanded mode: > $ /home/pgsql-17devel/bin/psql -x -p 5432 "-x" or "--expanded" Example: $ psql postgres -xc "SELECT 'foo' col1, 'bar' col2" -[ RECORD 1 ] col1 | foo col2 | bar -- Jim
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-09-13T04:49:55Z
Hi, On Thu, Sep 12, 2024 at 4:08 PM Jim Jones <jim.jones@uni-muenster.de> wrote: > It may look like this, but it is a single record --- mind the header "-[ > RECORD 1 ]----------------+---------". > psql was called in expanded mode: > > > $ /home/pgsql-17devel/bin/psql -x -p 5432 > > "-x" or "--expanded" > > Example: > > $ psql postgres -xc "SELECT 'foo' col1, 'bar' col2" > -[ RECORD 1 ] > col1 | foo > col2 | bar > I guess I missed the expanded mode. I have attached a new patch. Please check the output now. ``` $ bin/psql --port=5430 postgres psql (18devel) Type "help" for help. postgres=# \conninfo+ You are connected to database "postgres" as user "hunaid" via socket in "/tmp" at port "5430". Connection Information Protocol Version | SSL Connection | GSSAPI Authenticated | Client Encoding | Server Encoding | Session User | Backend P ID ------------------+----------------+----------------------+-----------------+-----------------+--------------+---------- --- 3 | no | no | UTF8 | UTF8 | hunaid | 55598 (1 row) postgres=# \x Expanded display is on. postgres=# \conninfo+ You are connected to database "postgres" as user "hunaid" via socket in "/tmp" at port "5430". Connection Information -[ RECORD 1 ]--------+------- Protocol Version | 3 SSL Connection | no GSSAPI Authenticated | no Client Encoding | UTF8 Server Encoding | UTF8 Session User | hunaid Backend PID | 55598 ``` Regards, Hunaid Sohail -
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-09-13T11:27:33Z
On 13.09.24 06:49, Hunaid Sohail wrote: > > $ bin/psql --port=5430 postgres > psql (18devel) > Type "help" for help. > > postgres=# \conninfo+ > You are connected to database "postgres" as user "hunaid" via socket > in "/tmp" at port "5430". > Connection Information > Protocol Version | SSL Connection | GSSAPI Authenticated | Client > Encoding | Server Encoding | Session User | Backend P > ID > ------------------+----------------+----------------------+-----------------+-----------------+--------------+---------- > --- > 3 | no | no | UTF8 > | UTF8 | hunaid | 55598 > (1 row) Nice. I just noticed that messages' order has been slightly changed. The message "You are connected to database "postgres" as user "hunaid" via socket in "/tmp" at port "5430" used to be printed after the table, and now it is printed before. $ /usr/local/postgres-dev/bin/psql -x "\ hostaddr=0 user=jim dbname=postgres port=54322" -c "\conninfo+" You are connected to database "postgres" as user "jim" on host "0" (address "0.0.0.0") at port "54322". Connection Information -[ RECORD 1 ]--------+-------- Protocol Version | 3 SSL Connection | no GSSAPI Authenticated | no Client Encoding | UTF8 Server Encoding | UTF8 Session User | jim Backend PID | 2419033 It is IMHO a little strange because the "SSL connection" info keeps being printed in the end. I would personally prefer if they're printed together --- preferably after the table. But I'm not sure if there's any convention for that. $ /usr/local/postgres-dev/bin/psql -x "\ host=server.uni-muenster.de hostaddr=127.0.0.1 user=jim dbname=postgres port=54322 sslmode=verify-full sslrootcert=server-certificates/server.crt sslcert=jim-certificates/jim.crt sslkey=jim-certificates/jim.key" -c "\conninfo+" You are connected to database "postgres" as user "jim" on host "server.uni-muenster.de" (address "127.0.0.1") at port "54322". Connection Information -[ RECORD 1 ]--------+-------- Protocol Version | 3 SSL Connection | yes GSSAPI Authenticated | no Client Encoding | UTF8 Server Encoding | UTF8 Session User | jim Backend PID | 2421556 SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql) Also, there are a few compilation warnings regarding const qualifiers: command.c:810:49: warning: assignment discards ‘const’ qualifier from pointer target type [-Wdiscarded-qualifiers] 810 | client_encoding = PQparameterStatus(pset.db, "client_encoding"); | ^ command.c:811:49: warning: assignment discards ‘const’ qualifier from pointer target type [-Wdiscarded-qualifiers] 811 | server_encoding = PQparameterStatus(pset.db, "server_encoding"); | ^ command.c:812:46: warning: assignment discards ‘const’ qualifier from pointer target type [-Wdiscarded-qualifiers] 812 | session_user = PQparameterStatus(pset.db, "session_authorization"); -- Jim
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-09-14T12:28:39Z
Hi Jim, On Fri, Sep 13, 2024 at 4:27 PM Jim Jones <jim.jones@uni-muenster.de> wrote: > I just noticed that messages' order has been slightly changed. The > message "You are connected to database "postgres" as user "hunaid" via > socket in "/tmp" at port "5430" used to be printed after the table, and > now it is printed before. > > $ /usr/local/postgres-dev/bin/psql -x "\ > hostaddr=0 > user=jim dbname=postgres > port=54322" -c "\conninfo+" > > You are connected to database "postgres" as user "jim" on host "0" > (address "0.0.0.0") at port "54322". > Connection Information > -[ RECORD 1 ]--------+-------- > Protocol Version | 3 > SSL Connection | no > GSSAPI Authenticated | no > Client Encoding | UTF8 > Server Encoding | UTF8 > Session User | jim > Backend PID | 2419033 > > It is IMHO a little strange because the "SSL connection" info keeps > being printed in the end. I would personally prefer if they're printed > together --- preferably after the table. But I'm not sure if there's any > convention for that. > I agree that both messages should be printed together. IMO the message "You are connected to database..." should be printed at the top, no? Because it shows important info that the user may be interested to see first. Then we can combine the ssl message. postgres=# \x Expanded display is on. postgres=# \conninfo+ You are connected to database "postgres" as user "hunaid" on host "localhost" (address "127.0.0.1") at port "5430". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql) Connection Information -[ RECORD 1 ]--------+------- Protocol Version | 3 SSL Connection | yes GSSAPI Authenticated | no Client Encoding | UTF8 Server Encoding | UTF8 Session User | hunaid Backend PID | 109092 > Also, there are a few compilation warnings regarding const qualifiers: > Noted. I will fix them in the next patch. Regards, Hunaid Sohail
-
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-09-14T17:37:11Z
On 2024-Sep-14, Hunaid Sohail wrote: > I agree that both messages should be printed together. IMO the message > "You are connected to database..." should be printed at the top, no? > Because it shows important info that the user may be interested to see > first. Then we can combine the ssl message. > > postgres=# \x > Expanded display is on. > postgres=# \conninfo+ > You are connected to database "postgres" as user "hunaid" on host > "localhost" (address "127.0.0.1") at port "5430". > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, > compression: off, ALPN: postgresql) > Connection Information > -[ RECORD 1 ]--------+------- > Protocol Version | 3 > SSL Connection | yes > GSSAPI Authenticated | no > Client Encoding | UTF8 > Server Encoding | UTF8 > Session User | hunaid > Backend PID | 109092 I don't understand why this is is printing half the information in free-form plain text and the other half in tabular format. All these items that you have in the free-form text lines should be part of the table, I think. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ #error "Operator lives in the wrong universe" ("Use of cookies in real-time system development", M. Gleixner, M. Mc Guire) -
Re: Psql meta-command conninfo+
Tom Lane <tgl@sss.pgh.pa.us> — 2024-09-14T17:50:00Z
Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > I don't understand why this is is printing half the information in > free-form plain text and the other half in tabular format. All these > items that you have in the free-form text lines should be part of the > table, I think. +1, that was my reaction as well. I can see the point of showing those items the same way as plain \conninfo does, but I think we're better off just making \conninfo+ produce a table and nothing else. regards, tom lane
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-09-16T06:51:04Z
Hi, On Sat, Sep 14, 2024 at 10:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > > I don't understand why this is is printing half the information in > > free-form plain text and the other half in tabular format. All these > > items that you have in the free-form text lines should be part of the > > table, I think. > > +1, that was my reaction as well. I can see the point of showing > those items the same way as plain \conninfo does, but I think > we're better off just making \conninfo+ produce a table and nothing > else. > I have attached a new patch that now prints all info in tabular format for \conninfo+. I have also made the table output dynamic, so if the connection uses SSL, the columns in the table will expand accordingly. ``` $ bin/psql "port=5430 sslmode=require dbname=postgres" -x -h localhost postgres=# \conninfo+ Connection Information -[ RECORD 1 ]--------+----------------------- Database | postgres Current User | hunaid Session User | hunaid Host | localhost Host Address | 127.0.0.1 Port | 5430 Protocol Version | 3 SSL Connection | yes SSL Protocol | TLSv1.3 Cipher | TLS_AES_256_GCM_SHA384 Compression | off ALPN | postgresql GSSAPI Authenticated | no Client Encoding | UTF8 Server Encoding | UTF8 Backend PID | 88803 $ bin/psql "port=5430 sslmode=disable dbname=postgres" -x -h localhost postgres=# \conninfo+ Connection Information -[ RECORD 1 ]--------+---------- Database | postgres Current User | hunaid Session User | hunaid Host | localhost Host Address | 127.0.0.1 Port | 5430 Protocol Version | 3 SSL Connection | no GSSAPI Authenticated | no Client Encoding | UTF8 Server Encoding | UTF8 Backend PID | 88900 $ bin/psql "port=5430 sslmode=disable dbname=postgres" -x postgres=# \conninfo+ Connection Information -[ RECORD 1 ]--------+--------- Database | postgres Current User | hunaid Session User | hunaid Socket Directory | /tmp Port | 5430 Protocol Version | 3 SSL Connection | no GSSAPI Authenticated | no Client Encoding | UTF8 Server Encoding | UTF8 Backend PID | 89035 ``` I have also updated the documentation. Regards, Hunaid Sohail
-
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-09-16T13:40:07Z
On 16.09.24 08:51, Hunaid Sohail wrote: > I have attached a new patch that now prints all info in tabular format > for \conninfo+. I have also made the table output dynamic, so if the > connection uses SSL, the columns in the table will expand accordingly. > It looks much cleaner now. > I have also updated the documentation. The CF bot is still giving some warnings: command.c:886:79: error: ‘alpn’ may be used uninitialized [-Werror=maybe-uninitialized] 886 | printTableAddCell(&cont, (alpn && alpn[0] != '\0') ? alpn : _("none"), false, false); | ~~~~^~~ command.c:803:50: note: ‘alpn’ was declared here 803 | *alpn; | ^~~~ command.c:885:82: error: ‘compression’ may be used uninitialized [-Werror=maybe-uninitialized] 885 | printTableAddCell(&cont, (compression && strcmp(compression, "off") != 0) ? _("on") : _("off"), false, false); | ^~~~~~~~~~~~~~~~~~~~~~~~~~ command.c:802:50: note: ‘compression’ was declared here 802 | *compression, | ^~~~~~~~~~~ command.c:884:41: error: ‘cipher’ may be used uninitialized [-Werror=maybe-uninitialized] 884 | printTableAddCell(&cont, cipher ? cipher : _("unknown"), false, false); | ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ command.c:801:50: note: ‘cipher’ was declared here 801 | *cipher, | ^~~~~~ command.c:883:41: error: ‘protocol’ may be used uninitialized [-Werror=maybe-uninitialized] 883 | printTableAddCell(&cont, protocol ? protocol : _("unknown"), false, false); | ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ command.c:800:42: note: ‘protocol’ was declared here 800 | char *protocol, | ^~~~~~~~ I have a few questions regarding this example: $ /usr/local/postgres-dev/bin/psql -x "\ host=server.uni-muenster.de hostaddr=192.168.178.27 user=jim dbname=db port=5432 sslmode=verify-full sslrootcert=server-certificates/server.crt sslcert=jim-certificates/jim.crt sslkey=jim-certificates/jim.key" psql (18devel) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql) Type "help" for help. db=# SET ROLE foo; SET db=> SELECT current_user, session_user; -[ RECORD 1 ]+---- current_user | foo session_user | jim db=> \conninfo+ Connection Information -[ RECORD 1 ]--------+----------------------- Database | db Current User | jim Session User | jim Host | server.uni-muenster.de Host Address | 192.168.178.27 Port | 5432 Protocol Version | 3 SSL Connection | yes SSL Protocol | TLSv1.3 Cipher | TLS_AES_256_GCM_SHA384 Compression | off ALPN | postgresql GSSAPI Authenticated | no Client Encoding | UTF8 Server Encoding | UTF8 Backend PID | 315187 * The value of "Current User" does not match the function current_user() --- as one might expcect. It is a little confusing, as there is no mention of "Current User" in the docs. In case this is the intended behaviour, could you please add it to the docs? * "SSL Connection" says "yes", but the docs say: "True if the current connection to the server uses SSL, and false otherwise.". Is it supposed to be like this? I haven't checked other similar doc entries.. -- Jim -
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-09-16T14:22:03Z
On 2024-Sep-16, Jim Jones wrote: > * The value of "Current User" does not match the function current_user() > --- as one might expcect. It is a little confusing, as there is no > mention of "Current User" in the docs. In case this is the intended > behaviour, could you please add it to the docs? It is intended. As Peter said[1], what we wanted was to display client-side info, so PQuser() is the right thing to do. Now maybe "Current User" is not the perfect column header, but at least the definition seems consistent with the desired end result. Now, I think the current docs saying to look at session_user() are wrong, they should point to the libpq docs for the function instead; something like "The name of the current user, as returned by PQuser()" and so on. Otherwise, in the cases where these things differ, it is going to be hard to explain. [1] https://postgr.es/m/f4fc729d-7903-4d58-995d-6cd146049992@eisentraut.org > * "SSL Connection" says "yes", but the docs say: "True if the current > connection to the server uses SSL, and false otherwise.". Is it supposed > to be like this? I haven't checked other similar doc entries.. Yeah, I think we should print what a boolean value would look like from SQL, so "true" rather than "yes". I think the code structure is hard to follow. It would be simpler if it was a bunch of /* Database */ printTableAddHeader(&cont, _("Database"), true, 'l'); printTableAddCell(&cont, db, false, false); ... /* Port */ printTableAddHeader(&cont, _("Por"), true, 'l'); printTableAddCell(&cont, PQport(pset.db), false, false); /* ... */ And so on. I don't think the printTable() API forces you to set all headers first followed by all cells. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ -
Re: Psql meta-command conninfo+
Tom Lane <tgl@sss.pgh.pa.us> — 2024-09-16T15:30:58Z
Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > On 2024-Sep-16, Jim Jones wrote: >> * The value of "Current User" does not match the function current_user() >> --- as one might expcect. It is a little confusing, as there is no >> mention of "Current User" in the docs. In case this is the intended >> behaviour, could you please add it to the docs? > It is intended. As Peter said[1], what we wanted was to display > client-side info, so PQuser() is the right thing to do. Now maybe > "Current User" is not the perfect column header, but at least the > definition seems consistent with the desired end result. Seems like "Session User" would be closer to being accurate, since PQuser()'s result does not change when you do SET ROLE etc. > Now, I think > the current docs saying to look at session_user() are wrong, they should > point to the libpq docs for the function instead; something like "The > name of the current user, as returned by PQuser()" and so on. Sure, but this does not excuse choosing a misleading column name when there are better choices readily available. regards, tom lane
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-09-17T11:53:02Z
Hi, On Mon, Sep 16, 2024 at 8:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > > On 2024-Sep-16, Jim Jones wrote: > >> * The value of "Current User" does not match the function current_user() > >> --- as one might expcect. It is a little confusing, as there is no > >> mention of "Current User" in the docs. In case this is the intended > >> behaviour, could you please add it to the docs? > > > It is intended. As Peter said[1], what we wanted was to display > > client-side info, so PQuser() is the right thing to do. Now maybe > > "Current User" is not the perfect column header, but at least the > > definition seems consistent with the desired end result. > > Seems like "Session User" would be closer to being accurate, since > PQuser()'s result does not change when you do SET ROLE etc. > > > Now, I think > > the current docs saying to look at session_user() are wrong, they should > > point to the libpq docs for the function instead; something like "The > > name of the current user, as returned by PQuser()" and so on. > > Sure, but this does not excuse choosing a misleading column name > when there are better choices readily available. > Maybe we can rename "Current User" to "Authenticated User" just like the previous author because it is a user returned by PQuser(). For the "Session User", I believe it is working as expected, since session_user can be changed with SET SESSION AUTHORIZATION. ``` $ bin/psql "port=5430 sslmode=disable dbname=postgres" -x -h localhost postgres=# \conninfo+ Connection Information -[ RECORD 1 ]--------+---------- Database | postgres Current User | hunaid Session User | hunaid Host | localhost Host Address | 127.0.0.1 Port | 5430 Protocol Version | 3 SSL Connection | false GSSAPI Authenticated | false Client Encoding | UTF8 Server Encoding | UTF8 Backend PID | 1337 postgres=# set SESSION AUTHORIZATION postgres; SET postgres=# \conninfo+ Connection Information -[ RECORD 1 ]--------+---------- Database | postgres Current User | hunaid Session User | postgres Host | localhost Host Address | 127.0.0.1 Port | 5430 Protocol Version | 3 SSL Connection | false GSSAPI Authenticated | false Client Encoding | UTF8 Server Encoding | UTF8 Backend PID | 1337 ``` We can update the docs as follows: Authenticated User: The name of the user returned by PQuser(). Session User: The session user's name. Opinions? Regards, Hunaid Sohail
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-09-26T07:15:26Z
Hi, This patch renames "Current User" to "Authenticated User" as suggested by me in my last email. I have also updated the documentation accordingly. On Tue, Sep 17, 2024 at 4:53 PM Hunaid Sohail <hunaidpgml@gmail.com> wrote: > We can update the docs as follows: > Authenticated User: The name of the user returned by PQuser(). > Session User: The session user's name. > Moreover, as Álvaro suggested, I made some modifications to the code structure and moved verbose code into a separate function. Please let me know if you have any questions or further suggestions. Regards, Hunaid Sohail
-
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-09-30T18:16:09Z
Hi On 26.09.24 09:15, Hunaid Sohail wrote: > This patch renames "Current User" to "Authenticated User" as suggested > by me in my last email. I have also updated the documentation accordingly. Could you perhaps in the documentation elaborate a bit more on the difference between "Current User" and "Authenticated User"? IMHO a couple of words on how exactly they differ would be very helpful, as they show the same user in many cases. > Authenticated User: The name of the user returned by PQuser() > Session User: The session user's name. Although a bit redundant, I'd argue that "SSL Compression" is better than just "Compression". Other than that, it looks much better now: $ /usr/local/postgres-dev/bin/psql -x "\ host=server.uni-muenster.de hostaddr=192.168.178.27 user=jim dbname=db port=54322 sslmode=verify-full sslrootcert=server-certificates/server.crt sslcert=jim-certificates/jim.crt sslkey=jim-certificates/jim.key" psql (18devel) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql) Type "help" for help. db=# SET client_encoding TO 'LATIN1'; SET db=# \conninfo+ Connection Information -[ RECORD 1 ]--------+----------------------- Database | db Authenticated User | jim Session User | jim Host | server.uni-muenster.de Host Address | 192.168.178.27 Port | 54322 Protocol Version | 3 SSL Connection | true SSL Protocol | TLSv1.3 SSL Cipher | TLS_AES_256_GCM_SHA384 Compression | off ALPN | postgresql GSSAPI Authenticated | false Client Encoding | LATIN1 Server Encoding | UTF8 Backend PID | 874890 Thanks!
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-10-01T04:27:29Z
Hi, On Mon, Sep 30, 2024 at 11:16 PM Jim Jones <jim.jones@uni-muenster.de> wrote: > On 26.09.24 09:15, Hunaid Sohail wrote: > > This patch renames "Current User" to "Authenticated User" as suggested > > by me in my last email. I have also updated the documentation > accordingly. > > Could you perhaps in the documentation elaborate a bit more on the > difference between "Current User" and "Authenticated User"? IMHO a > couple of words on how exactly they differ would be very helpful, as > they show the same user in many cases. > There is no "Current User" in the conninfo+; as I mentioned, I have renamed it to "Authenticated User". There are two users in the conninfo+: 'session' and 'authenticated'. Both are documented. > Although a bit redundant, I'd argue that "SSL Compression" is better > than just "Compression". > Noted. Regards, Hunaid Sohail
-
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-10-01T05:50:21Z
On 01.10.24 06:27, Hunaid Sohail wrote: > There are two users in the conninfo+: 'session' and 'authenticated'. > Both are documented. Right. I meant "Session User" > Authenticated User: The name of the user returned by PQuser() > Session User: The session user's name. Thanks -- Jim
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-10-02T04:48:08Z
Hi, On Tue, Oct 1, 2024 at 10:50 AM Jim Jones <jim.jones@uni-muenster.de> wrote: > Right. I meant "Session User" > > > Authenticated User: The name of the user returned by PQuser() > > Session User: The session user's name. > Should I revert to the v34 docs for Session User, or is it fine as is? <member><literal>Session User:</literal> The session user's name; see the <function>session_user()</function> function in <xref linkend="functions-info-session-table"/> for more details.</member> Any other changes required? Regards, Hunaid Sohail -
Re: Psql meta-command conninfo+
Jim Jones <jim.jones@uni-muenster.de> — 2024-10-02T09:47:42Z
On 02.10.24 06:48, Hunaid Sohail wrote: > Should I revert to the v34 docs for Session User, or is it fine as is? What I tried to say is that the current description is a bit vague --- specially "Authenticated User". > Authenticated User: The name of the user returned by PQuser() > Session User: The session user's name. I thought it would be nice to have a description that tells how both Session and Authenticated users differ. IMHO *only* a reference to PQuser() doesn't say much, but others might be ok with it. So let's see what the other reviewers say. -- Jim
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-10-03T08:39:01Z
>I thought it would be nice to have a description that tells how both >Session and Authenticated users differ. IMHO *only* a reference to >PQuser() doesn't say much, but others might be ok with it. So let's see >what the other reviewers say. Hi everyone, I believe the difference between Session and Authenticated users should indeed be made clearer, while still keeping PQuser() in the description. Other than that, I think the patch is as expected, meeting the initial proposal/idea of this meta-command and thread. I would like a committer who followed the development to volunteer for a thorough technical review so that the patch can move forward to a release candidate for a commit. Regards, Maiquel Grassi.
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-10-04T06:08:53Z
Hi, On Thu, Oct 3, 2024 at 1:39 PM Maiquel Grassi <grassi@hotmail.com.br> wrote: > >I thought it would be nice to have a description that tells how both > >Session and Authenticated users differ. IMHO *only* a reference to > >PQuser() doesn't say much, but others might be ok with it. So let's see > >what the other reviewers say. > > Hi everyone, > I believe the difference between Session and Authenticated > users should indeed be made clearer, while still keeping > PQuser() in the description. > Based on my readings from the documentation [1] and [2], we can update user descriptions as follows: Authenticated User: The name of the user returned by PQuser(), indicating the user who initiated or authenticated the current database connection. Session User: The session user's name, which is initially the same as the authenticated user but can be changed with SET SESSION AUTHORIZATION. See the session_user() function in <xref linkend="functions-info-session-table"/> for more details. Let me know if this is fine, then I can provide a new patch. [1] https://www.postgresql.org/docs/current/functions-info.html [2] https://www.postgresql.org/docs/current/sql-set-session-authorization.html Regards, Hunaid Sohail
-
Re: Psql meta-command conninfo+
David G. Johnston <david.g.johnston@gmail.com> — 2024-10-04T06:56:53Z
On Thursday, October 3, 2024, Hunaid Sohail <hunaidpgml@gmail.com> wrote: > > > Authenticated User: The name of the user returned by PQuser(), indicating > the user who initiated or authenticated the current database connection. > Session User: The session user's name, which is initially the same as the > authenticated user but can be changed with SET SESSION AUTHORIZATION. See > the session_user() function in <xref linkend="functions-info-session-table"/> > for more details. > > It seems to me a more useful definition for what this command should print out is basically the entire contents of: https://www.postgresql.org/docs/current/libpq-status.html That page has three sections: Connection Invariants Current Status Encryption (TLS) I would suggest that we thus produce three tables - one for each. In the case of SSL, a message saying “not used” instead of a table full of blanks probably suffices, though I’d lean to print all of what is available at all times. Within that framework having \conninfo[+[CSE][…]] be the command - printing out only the table specified would be the behavior (specifying no suffix letters prints all three) - would be an option. We could add a fourth table - Parameters (P) - for the various outputs of PQparameterStatus; thus making the Current Status section a bit more manageable There are obviously some things psql wouldn’t expose (like password) - we should probably list them explicitly in an exception list, and maybe note as much on the libpq page. Separately, I don’t see a reason to even show “Authenticated User” unless it is different than Session User - which means only in the rare case of a superuser invoking set session authorization. It also isn’t guaranteed to be authenticated, which the docs do try to make a point of, so “Client User” would be more appropriate. David J.
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-10-07T06:17:01Z
Hi David, Thank you for your feedback. On Fri, Oct 4, 2024 at 11:56 AM David G. Johnston < david.g.johnston@gmail.com> wrote: > It seems to me a more useful definition for what this command should print > out is basically the entire contents of: > > https://www.postgresql.org/docs/current/libpq-status.html > > That page has three sections: > Connection Invariants > Current Status > Encryption (TLS) > > I would suggest that we thus produce three tables - one for each. In the > case of SSL, a message saying “not used” instead of a table full of blanks > probably suffices, though I’d lean to print all of what is available at all > times. > We can try this approach. I would also like to have other's opinions on this approach. Most functions are already used, while some are not required (IMO). I have listed all the functions from the doc link you provided, along with my brief comments based on the latest patch (v35). PQdb - already used PQuser - already used PQpass - no need PQhost - already used PQhostaddr - already used PQport - already used PQtty - no need PQoptions - can be used PQstatus - no need PQtransactionStatus - can be used PQparameterStatus - already used PQprotocolVersion - already used PQserverVersion - no need PQerrorMessage - no need PQsocket - no need PQbackendPID - already used PQconnectionNeedsPassword - no need PQconnectionUsedPassword - can be used PQconnectionUsedGSSAPI - already used PQsslInUse - already used PQsslAttribute - only key_bits attribute not used PQsslAttributeNames - no need PQsslStruct - no need PQgetssl - no need For PQparameterStatus, some parameters are already used. server_version and application_name were already discussed and removed in v12 and v29 respectively. Do we need other parameters? > Within that framework having \conninfo[+[CSE][…]] be the command - > printing out only the table specified would be the behavior (specifying no > suffix letters prints all three) - would be an option. > 3 separate tables without suffix? If others are okay with this, I can work on this approach and will provide a patch before the next CF. Regards, Hunaid Sohail
-
Re: Psql meta-command conninfo+
David G. Johnston <david.g.johnston@gmail.com> — 2024-10-07T16:30:28Z
On Sun, Oct 6, 2024 at 11:17 PM Hunaid Sohail <hunaidpgml@gmail.com> wrote: > > PQpass - no need > I would include this as presence/absence. I concur on all of the rest. > > For PQparameterStatus, some parameters are already used. > server_version and application_name were already discussed and removed in > v12 and v29 respectively. Do we need other parameters? > Ok, I'll need to go read the reasoning for why they are deemed unneeded and form an opinion one way or the other. > >> Within that framework having \conninfo[+[CSE][…]] be the command - >> printing out only the table specified would be the behavior (specifying no >> suffix letters prints all three) - would be an option. >> > > 3 separate tables without suffix? > Yes, the tables need headers specific to their categories. I do like the idea of having 4 though, placing settings into their own. Premised on having all or most of the available parameters being on the table. If it only ends up being a few of them then keeping those in the status table makes sense. David J. >
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-10-30T06:07:27Z
Hi, I have attached a new patch that incorporates the approach suggested by David. The documentation has also been updated. $ bin/psql "port=5430 sslmode=disable dbname=postgres" -x -h localhost psql (18devel) Type "help" for help. postgres=# \conninfo+ Connection Information -[ RECORD 1 ]+---------- Database | postgres Client User | hunaid Host | localhost Host Address | 127.0.0.1 Port | 5430 Options | Current Status -[ RECORD 1 ]--------+------ Protocol Version | 3 Password Used | false GSSAPI Authenticated | false Backend PID | 26268 Server Parameter Settings -[ RECORD 1 ]---------+------- Superuser | true Client Encoding | UTF8 Server Encoding | UTF8 Session Authorization | hunaid Connection Encryption -[ RECORD 1 ]--+------ SSL Connection | false $ bin/psql "port=5430 sslmode=require dbname=postgres" -x -h localhost psql (18devel) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql) Type "help" for help. postgres=# \conninfo+ E Connection Encryption -[ RECORD 1 ]--+----------------------- SSL Connection | true Library | OpenSSL Protocol | TLSv1.3 Key Bits | 256 Cipher | TLS_AES_256_GCM_SHA384 Compression | off ALPN | postgresql I’m unsure if we need to expand the documentation further. I would appreciate your suggestions on this. Regards, Hunaid Sohail On Mon, Oct 7, 2024 at 9:31 PM David G. Johnston <david.g.johnston@gmail.com> wrote: > On Sun, Oct 6, 2024 at 11:17 PM Hunaid Sohail <hunaidpgml@gmail.com> > wrote: > >> >> PQpass - no need >> > > I would include this as presence/absence. > > I concur on all of the rest. > > >> >> For PQparameterStatus, some parameters are already used. >> server_version and application_name were already discussed and removed in >> v12 and v29 respectively. Do we need other parameters? >> > > Ok, I'll need to go read the reasoning for why they are deemed unneeded > and form an opinion one way or the other. > > >> >>> Within that framework having \conninfo[+[CSE][…]] be the command - >>> printing out only the table specified would be the behavior (specifying no >>> suffix letters prints all three) - would be an option. >>> >> >> 3 separate tables without suffix? >> > > Yes, the tables need headers specific to their categories. > > I do like the idea of having 4 though, placing settings into their own. > Premised on having all or most of the available parameters being on the > table. If it only ends up being a few of them then keeping those in > the status table makes sense. > > David J. > >>
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2024-12-13T04:47:51Z
Hi, I provided a patch a month ago with a new approach as suggested by David. Unfortunately, it didn't get any attention in the November CF. I would appreciate your feedback on whether we should proceed with this new approach or stick with the previous one. Regards, Hunaid Sohail
-
RE: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2024-12-26T14:23:10Z
>Hi, > >I provided a patch a month ago with a new approach as suggested by David. Unfortunately, it didn't get any attention in the November CF. > >I would appreciate your feedback on whether we should proceed with this new approach or stick with the previous one. > >Regards, >Hunaid Sohail --//-- Hi, The previous version was good to go and ready for a commit as soon as the final review is finalized. About David’s proposal, I’m still a little unsure, but it seems like it has a lot of potential. What do you all think? If it’s a strong direction to explore, maybe David could join us as a co-author and work with us to bring the feature. Regards, Maiquel.
-
Re: Psql meta-command conninfo+
Sami Imseih <samimseih@gmail.com> — 2025-01-06T22:03:24Z
I spent time reviewing v36 today and I have some comments. Overall I think it's in better shape and the value of being able to get this information from a single command meta-command is really useful. But I have some comments. Sorry if I am re-hashing things that have already been discussed. 1/ I am having a hard time making sense of the section "Current Status" None of the values in that section can be changed in the lifetime of a connection. The description "Current Status" makes it seem like they can change. 2/ Can't this be simplified to: "Connection Information": attributes that cannot be changed during the life of a connection. I think "Connection Encryption" seems unnecessary here as well and it could be added to "Connection Information". "Server Parameter Settings": This section should include all under [1]. For example, "in_hot_standby" is very useful, and so is "application_name". Why are they not included? 3/ I think that if we are showing "Session Authorization", we should also show "Role Name", since either one of those values changing can result in a different "Superuser" value. In the example below, the user may be confused in thinking that "postgres" is not a superuser. Showing the role name will make the picture clearer. What do you think? postgres=# \conninfo+ P Server Parameter Settings -[ RECORD 1 ]---------+--------- Superuser | true Client Encoding | UTF8 Server Encoding | UTF8 Session Authorization | postgres postgres=# set role nosuper; SET postgres=> \conninfo+ P Server Parameter Settings -[ RECORD 1 ]---------+--------- Superuser | false Client Encoding | UTF8 Server Encoding | UTF8 Session Authorization | postgres Regards, Sami Imseih Amazon Web Services (AWS) [1] https://www.postgresql.org/docs/current/libpq-status.html#LIBPQ-PQPARAMETERSTATUS
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2025-01-07T09:43:48Z
Hi, Thank you for your valuable feedback. 1/ I am having a hard time making sense of the section "Current Status" > None of the values in that section can be changed in the lifetime > of a connection. The description "Current Status" makes it > seem like they can change. > Any suggestions? 2/ Can't this be simplified to: > > "Connection Information": attributes that cannot be changed > during the life of a connection. I will update the docs. Since both "Current Status" and "Connection Information" contain attributes that cannot be changed in the lifetime of a connection, they can be merged. Any feedback? > I think "Connection Encryption" seems unnecessary here as > well and it could be added to "Connection Information". Yes, we can do that, but we’d be left with two tables: "Connection Information" and "Server Parameter Settings". Does that work? "Server Parameter Settings": This section should include > all under [1]. For example, "in_hot_standby" is very > useful, and so is "application_name". Why are they > not included? > There was a discussion about "application_name" earlier in the thread, and it was removed by the original author. However, since we now have a separate table, it makes sense to include all parameters. 3/ I think that if we are showing "Session Authorization", > we should also show "Role Name", since either one of those > values changing can result in a different "Superuser" value. > In the example below, the user may be confused in thinking > that "postgres" is not a superuser. Showing the role name > will make the picture clearer. What do you think? > Agreed. However, since we're using the libpq API, I don't think we can retrieve the role name. Maybe remove "Session Authorization" instead? Regards, Hunaid Sohail
-
Re: Psql meta-command conninfo+
Sami Imseih <samimseih@gmail.com> — 2025-01-08T02:14:25Z
>> I think "Connection Encryption" seems unnecessary here as >> well and it could be added to "Connection Information". > > > Yes, we can do that, but we’d be left with two tables: > "Connection Information" and "Server Parameter Settings". Does that work? After looking at this ever more today, I think "Server Parameter Settings" is confusing as well. I think "Connection Status" instead of "Current Status" as is defined in v36 will work better. This way we will have "Connection Info" and "Connection Status". Connection Status will reflect the values of specific parameters that the server reports. > >> "Server Parameter Settings": This section should include >> all under [1]. For example, "in_hot_standby" is very >> useful, and so is "application_name". Why are they >> not included? > > > There was a discussion about "application_name" earlier in the thread, and it was removed by the original author. > However, since we now have a separate table, it makes sense to include all parameters. Including all the parameters in [1] under "Server Parameter Settings" (or "Connection Status") seems like the easy choice here. Some may not be as useful as others, but I don't think we should pick and choose either. Maybe someone else has other thoughts about this? > Agreed. However, since we're using the libpq API, I don't think we can retrieve the role name. > Maybe remove "Session Authorization" instead? We can include role by marking the "role" guc with the GUC_REPORT flag in guc_tables.c. I really think without it, the is_superuser field will be incomplete. This is because either "role" or "session authorization" will change the is_superuser. A thought also, that if we do choose to report all the parameters in [1], it should be coded in a more dynamic way. Maybe loop through the conn->pstatus list? For example I see "search_path" will be added to the list in the next release. [1] https://www.postgresql.org/docs/devel/libpq-status.html Regards, Sami
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2025-01-09T05:53:41Z
Hi, After looking at this ever more today, I think "Server Parameter Settings" > is confusing as well. I think "Connection Status" instead of > "Current Status" as is defined in v36 will work better. > This way we will have "Connection Info" and "Connection Status". > Connection Status will reflect the values of specific parameters > that the server reports. > Noted. > Including all the parameters in [1] under > "Server Parameter Settings" (or "Connection Status") > seems like the easy choice here. Some may not be as useful as > others, but I don't think we should pick and choose either. > Maybe someone else has other thoughts about this? > Sure, let's wait for others' opinions. > We can include role by marking the "role" guc with > the GUC_REPORT flag in guc_tables.c. I really think > without it, the is_superuser field will be incomplete. > This is because either "role" or "session authorization" > will change the is_superuser. > Thanks! I will take a look. > A thought also, that if we do choose to report all the parameters > in [1], it should be coded in a more dynamic way. Maybe loop > through the conn->pstatus list? For example I see "search_path" > will be added to the list in the next release. > If we loop through conn->pstatus, we will be bypassing the official API. The list is of type pgParameterStatus, which is an internal struct defined in libpq-int.h. As the file's header warns, including this file can lead to issues. Or am I missing something? Regards, Hunaid Sohail
-
Re: Psql meta-command conninfo+
Sami Imseih <samimseih@gmail.com> — 2025-01-09T16:56:36Z
> If we loop through conn->pstatus, we will be bypassing the official API. > The list is of type pgParameterStatus, which is an internal struct defined in libpq-int.h. > As the file's header warns, including this file can lead to issues. Or am I missing something? you're right about the warning in the header file. I am just thinking of the maintenance of this in case new parameters are added in the future. How do we ensure it reflects in the command output? Maybe have an API in fe-connect.c that returns a list of all pstatus->name and then for each value call PQParameterStatus? But this patch still needs agreement on the high level points discussed earlier. Regards, Sami
-
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-01-09T19:10:54Z
On 2024-Dec-26, Maiquel Grassi wrote: > The previous version was good to go and ready for a commit as soon as > the final review is finalized. About David’s proposal, I’m still a > little unsure, but it seems like it has a lot of potential. What do > you all think? If it’s a strong direction to explore, maybe David > could join us as a co-author and work with us to bring the feature. I think the idea of \conninfo+ printing multiple tables is setting up the patch for failure. It is just too unusual. (Yes, I know "\d *" and such can print multiple tables. That one is a disaster, perhaps the worst psql command of all.) Also, in v36 you can specify another character as second argument to display only one of the several tables. How are users going to remember which is which? Most likely, they won't. v35 seems fine to me from a UI standpoint; I suggest we move forward with that. This is what I get with v36 on an unencrypted local connection: 55432 18devel 17266=# \conninfo+ Connection Information Base de Datos │ Client User │ Socket Directory │ Port │ Opciones ───────────────┼─────────────┼──────────────────┼───────┼────────── alvherre │ alvherre │ /tmp │ 55432 │ (1 fila) Current Status Protocol Version │ Password Used │ GSSAPI Authenticated │ Backend PID ──────────────────┼───────────────┼──────────────────────┼───────────── 3 │ false │ false │ 17266 (1 fila) Server Parameter Settings Superusuario │ Client Encoding │ Server Encoding │ Session Authorization ──────────────┼─────────────────┼─────────────────┼─────────────────────── true │ UTF8 │ UTF8 │ alvherre (1 fila) Connection Encryption SSL Connection ──────────────── false (1 fila) Meanwhile, here's v35: 55432 18devel 20356=# \conninfo+ Connection Information ─[ RECORD 1 ]────────┬───────── Base de Datos │ alvherre Authenticated User │ alvherre Session User │ alvherre Socket Directory │ /tmp Port │ 55432 Protocol Version │ 3 SSL Connection │ false GSSAPI Authenticated │ false Client Encoding │ UTF8 Server Encoding │ UTF8 Backend PID │ 20356 Or, if expanded mode is disabled, I get this Connection Information Base de Datos │ Authenticated User │ Session User │ Socket Directory │ Port │ Protocol Version │ SSL Connection │ GSSAPI Authenticated │ Client Encoding │ Server Encoding │ Backend PID ───────────────┼────────────────────┼──────────────┼──────────────────┼───────┼──────────────────┼────────────────┼──────────────────────┼─────────────────┼─────────────────┼───────────── alvherre │ alvherre │ alvherre │ /tmp │ 55432 │ 3 │ false │ false │ UTF8 │ UTF8 │ 29553 (1 fila) (If Dean get his "backslash-command-expanded" patch in, we'd get \conninfo+x for expanded mode here, and avoid the need to have "\x auto" in .psqlrc) -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "Small aircraft do not crash frequently ... usually only once!" (ponder, http://thedailywtf.com/) -
Re: Psql meta-command conninfo+
Sami Imseih <samimseih@gmail.com> — 2025-01-09T19:27:51Z
> v35 seems fine to me from a UI standpoint; I suggest we move forward > with that. I am also OK with moving forward with a single \conninfo+, but I think we should include all parameters in [1] as part of the output. These are the parameters the server reports back to the client. I think they are all useful to report in this command. Also, we should also make "role" a part of the PQparameterStatus list. This is because is_superuser can change when either "session authorization" or "role" changes. See [2] [1] https://www.postgresql.org/docs/current/libpq-status.html#LIBPQ-PQPARAMETERSTATUS [2] https://www.postgresql.org/message-id/CAA5RZ0vN3RbekDNUUhD7CTFL2t7imZ2JS--aFNMHz18mr_yzNA%40mail.gmail.com Regards, Sami
-
Re: Psql meta-command conninfo+
David G. Johnston <david.g.johnston@gmail.com> — 2025-01-09T19:35:00Z
On Thu, Jan 9, 2025 at 12:28 PM Sami Imseih <samimseih@gmail.com> wrote: > > v35 seems fine to me from a UI standpoint; I suggest we move forward > > with that. > > I am also OK with moving forward with a single \conninfo+, but > I think we should include all parameters in [1] as part of the output. > These are the parameters the server reports back to the client. I think > they are all useful to report in this command. > I dislike totally discarding the category information we have available to us. How about making a long output with just three columns in non-expanded mode. Setting, Value, Category ? This also makes the non-expanded output, the default, the readable one. David J.
-
Re: Psql meta-command conninfo+
Sami Imseih <samimseih@gmail.com> — 2025-01-09T20:45:55Z
> I dislike totally discarding the category information we have available to us. How about making a long output with just three columns in non-expanded mode. Setting, Value, Category ? We can probably make due without a category as the name of the setting is informative enough, right? If we go with the 3 column format, then we will just have a bunch of repeated values for Category, which looks cluttered, IMO. Regards, Sami
-
Re: Psql meta-command conninfo+
Sami Imseih <samimseih@gmail.com> — 2025-01-09T20:50:18Z
> If we go with the 3 column format, then we will just > have a bunch of repeated values for Category, which > looks cluttered, IMO. "cluttered" is maybe the wrong description. I meant the output will look overwhelming due to the repeated values :) Regards, Sami
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2025-01-10T07:47:35Z
Hi, On Fri, Jan 10, 2025 at 1:50 AM Sami Imseih <samimseih@gmail.com> wrote: > > If we go with the 3 column format, then we will just > > have a bunch of repeated values for Category, which > > looks cluttered, IMO. > > "cluttered" is maybe the wrong description. I meant the output > will look overwhelming due to the repeated values :) > +1. To summarize: - Alvaro is not happy with \conninfo+ printing multiple tables and suggests we continue with v35. - Sami is ok with both approaches but thinks that \conninfo+ should also print all parameters from PQparameterStatus in a dynamic way. Also, he suggests to include "role" if we are showing is_superuser. For that, we need two more patches: 1. Mark role as GUC_REPORT with necessary doc changes. 2. Add a new libpq function (perhaps PQparameterNames) to return the names of parameters, so we can also display parameters added in the future, along with doc updates. - David wants category information included. IMO, we should continue with v35 and add all parameters from PQparameterStatus, as Sami suggested. The names themselves are informative enough. Looking forward to your feedback. Regards, Hunaid Sohail
-
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-01-13T08:44:30Z
On 2025-Jan-10, Hunaid Sohail wrote: > IMO, we should continue with v35 and add all parameters from > PQparameterStatus, > as Sami suggested. The names themselves are informative enough. IMO we need more opinions. Maybe enough other people are not as unhappy about the three-table solution. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
-
Re: Psql meta-command conninfo+
Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-01-13T11:12:23Z
On Mon, 13 Jan 2025 at 08:44, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > > > IMO, we should continue with v35 and add all parameters from > > PQparameterStatus, > > as Sami suggested. The names themselves are informative enough. > > IMO we need more opinions. Maybe enough other people are not as unhappy > about the three-table solution. > I don't like the 3-table format either. I think it should be a single table. The trouble with v35 is that it produces 1 row with lots of columns, which is pretty unreadable unless expanded mode is used. So I think we should just do it that way round by default -- i.e., make it like \dconfig and have 2 columns, "Parameter" and "Value", with lots of rows. Perhaps it could also have a "Description" column, which might help with things like distinguishing between authenticated user and session user. Regards, Dean
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2025-01-14T07:57:29Z
Hi, On Mon, Jan 13, 2025 at 4:12 PM Dean Rasheed <dean.a.rasheed@gmail.com> wrote: > I don't like the 3-table format either. I think it should be a single > table. > > The trouble with v35 is that it produces 1 row with lots of columns, > which is pretty unreadable unless expanded mode is used. So I think we > should just do it that way round by default -- i.e., make it like > \dconfig and have 2 columns, "Parameter" and "Value", with lots of > rows. > > Perhaps it could also have a "Description" column, which might help > with things like distinguishing between authenticated user and session > user. > I've tried the approach and attached the output. I'm not attaching the patch as it requires some formatting. Does this look good? I have added a one liner description that is consistent with libpq docs. postgres=# \conninfo+ Connection Information Parameter | Value | Description ----------------------+------------------------+-------------------------------------------------------------------------------- Database | postgres | Displays the database name of the connection. Client User | hunaid | Displays the name of the user connected to the database as returned by PQuser. Host | localhost | Displays the server host name of the active connection. Host Address | 127.0.0.1 | Displays the server IP address of the active connection. Port | 5430 | Displays the port number used for the database connection. Options | | Displays any command-line options passed in the connection request. Protocol Version | 3 | Displays the frontend/backend protocol being used. Password Used | false | Indicates whether a password was used during authentication. GSSAPI Authenticated | false | Indicates if GSSAPI authentication was used for the connection. Backend PID | 43465 | Displays the process ID of the backend for the connection. SSL Connection | true | Indicates whether SSL encryption is in use for the connection. SSL Protocol | TLSv1.3 | Displays the SSL protocol used for the connection. SSL Cipher | TLS_AES_256_GCM_SHA384 | Displays the SSL cipher used for the connection. SSL Compression | off | Indicates whether SSL compression is enabled. ALPN | postgresql | Displays the ALPN protocol used in the SSL connection. (15 rows) I was wondering how we can show the descriptions of parameters from PQparameterStatus. We can create an array of descriptions, but if the order changes somehow, it can mess things up. Looking forward to your opinions. Can we continue with this approach? Regards, Hunaid Sohail -
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-01-14T08:51:45Z
On 2025-Jan-14, Hunaid Sohail wrote: > I'm not attaching the patch as it requires some formatting. Remember pgindent formats code in bulk. A quick useful workflow is to do that first, apply manual adjustments next, run pgindent again afterwards. > I've tried the approach and attached the output. Does this look good? Hmm, I'm not sure I like the third column particularly; it's noisy to have on all the time. I'd leave that for \conninfo++ maybe :-) _If_ we keep the descriptions, I suggest that the majority need to be very short, say three words tops, with exceptions allowed for egregious cases (if any). We should get rid of filler words such as "Displays the" or "Indicates whether". For instance, I'd turn "Indicates if GSSAPI authentication was used for the connection" into "GSSAPI authenticated?" which conveys exactly the same; the ending "?" matches things like "Key?" in \d of an index or the "Default?" column in \dc. It's a boolean, so people know how to read it. > I was wondering how we can show the descriptions of parameters > from PQparameterStatus. We can create an array of descriptions, > but if the order changes somehow, it can mess things up. Definitely avoid writing code that can easily be broken by later changes (future parameter additions and so on). -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "The problem with the facetime model is not just that it's demoralizing, but that the people pretending to work interrupt the ones actually working." -- Paul Graham, http://www.paulgraham.com/opensource.html -
Re: Psql meta-command conninfo+
Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-01-14T09:50:51Z
On Tue, 14 Jan 2025 at 08:51, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > > On 2025-Jan-14, Hunaid Sohail wrote: > > > I've tried the approach and attached the output. Does this look good? > > Hmm, I'm not sure I like the third column particularly; it's noisy to > have on all the time. I'd leave that for \conninfo++ maybe :-) > Yeah, I thought perhaps it might provide some useful extra information, however... > _If_ we keep the descriptions, I suggest that the majority need to be > very short, say three words tops, with exceptions allowed for egregious > cases (if any). We should get rid of filler words such as "Displays > the" or "Indicates whether". For instance, I'd turn "Indicates if > GSSAPI authentication was used for the connection" into "GSSAPI > authenticated?" which conveys exactly the same; the ending "?" matches > things like "Key?" in \d of an index or the "Default?" column in \dc. > It's a boolean, so people know how to read it. > By the time the filler words have been stripped out, the description isn't really adding any useful information that can't be guessed from the parameter name and value. So I tend to agree that it should be left out (for now at least). Regards, Dean
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2025-01-16T10:54:22Z
Hi, I have attached 3 new patches v37-000* which display the \conninfo+ output as 2 columns "Parameter" and "Value". The other 2 patches are: 1. A new libpq function, PQparameterNames, which returns names of parameters reported by the server. 2. Mark role as GUC_REPORT. All these patches include necessary documentation changes. Also, code is formatted with pgindent. Output: postgres=# \conninfo+ Connection Information Parameter | Value -------------------------------+------------------------ Database | postgres Client User | hunaid Host | localhost Host Address | 127.0.0.1 Port | 5430 Options | Protocol Version | 3 Password Used | false GSSAPI Authenticated | false Backend PID | 52044 SSL Connection | true SSL Library | OpenSSL SSL Protocol | TLSv1.3 SSL Key Bits | 256 SSL Cipher | TLS_AES_256_GCM_SHA384 SSL Compression | off ALPN | postgresql role | none server_encoding | UTF8 server_version | 18devel client_encoding | UTF8 session_authorization | hunaid standard_conforming_strings | on DateStyle | ISO, MDY scram_iterations | 4096 default_transaction_read_only | off application_name | psql is_superuser | on search_path | "$user", public IntervalStyle | postgres TimeZone | Asia/Karachi integer_datetimes | on in_hot_standby | off (33 rows) postgres=# set role test_role; SET postgres=> \conninfo+ Connection Information Parameter | Value -------------------------------+------------------------ Database | postgres Client User | hunaid Host | localhost Host Address | 127.0.0.1 Port | 5430 Options | Protocol Version | 3 Password Used | false GSSAPI Authenticated | false Backend PID | 52044 SSL Connection | true SSL Library | OpenSSL SSL Protocol | TLSv1.3 SSL Key Bits | 256 SSL Cipher | TLS_AES_256_GCM_SHA384 SSL Compression | off ALPN | postgresql is_superuser | off role | test_role server_encoding | UTF8 server_version | 18devel client_encoding | UTF8 session_authorization | hunaid standard_conforming_strings | on DateStyle | ISO, MDY scram_iterations | 4096 default_transaction_read_only | off application_name | psql search_path | "$user", public IntervalStyle | postgres TimeZone | Asia/Karachi integer_datetimes | on in_hot_standby | off (33 rows) Looking forward to your feedback. Regards, Hunaid Sohail -
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-01-16T13:01:44Z
On 2025-Jan-16, Hunaid Sohail wrote: > server_encoding | UTF8 > server_version | 18devel > client_encoding | UTF8 > session_authorization | hunaid > standard_conforming_strings | on > DateStyle | ISO, MDY > scram_iterations | 4096 > default_transaction_read_only | off > application_name | psql > is_superuser | on > search_path | "$user", public > IntervalStyle | postgres > TimeZone | Asia/Karachi > integer_datetimes | on > in_hot_standby | off Wait a second, why do we have these here? Aren't they already in \dconfig? -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "Having your biases confirmed independently is how scientific progress is made, and hence made our great society what it is today" (Mary Gardiner)
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2025-01-17T05:17:42Z
Hi, On Thu, Jan 16, 2025 at 6:01 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > On 2025-Jan-16, Hunaid Sohail wrote: > > > server_encoding | UTF8 > > server_version | 18devel > > client_encoding | UTF8 > > session_authorization | hunaid > > standard_conforming_strings | on > > DateStyle | ISO, MDY > > scram_iterations | 4096 > > default_transaction_read_only | off > > application_name | psql > > is_superuser | on > > search_path | "$user", public > > IntervalStyle | postgres > > TimeZone | Asia/Karachi > > integer_datetimes | on > > in_hot_standby | off > > Wait a second, why do we have these here? Aren't they already in > \dconfig? > There are indeed some overlaps because Sami [1] and David [2] suggested to show all parameters, with Sami specifically asking to include in_hot_standby and application_name, as they can be useful. Moreover, parameters like server_encoding and session_authorization were already included in previous patches. In other cases, we'd have to pick and choose which parameters to include. If a new parameter is reported that might be relevant to this meta command and the user wants it included, we would need to modify the code repeatedly. By the way, I just noticed CF bot failed with the error: 'undefined reference to palloc'. Maybe I should use malloc as it is used in the file, and free with PQmemfree(). [1] https://www.postgresql.org/message-id/CAA5RZ0sa0%3DJP3RCs4_hZ%2BYVFCeU8b_aHSqiSFdUrf_ushcBTtw%40mail.gmail.com [2] https://www.postgresql.org/message-id/CAKFQuwb_R-zGT41xsBkPChBEB9e%3DA4P3fsAGjeqOzTuWN1Z5Sg%40mail.gmail.com Regards, Hunaid Sohail
-
Re: Psql meta-command conninfo+
Sami Imseih <samimseih@gmail.com> — 2025-01-17T20:52:55Z
> Wait a second, why do we have these here? Aren't they already in > \dconfig? \dconfig is generated by querying pg_settings and this requires a halthy connection. The parameters being proposed with \conninfo+ are set in libpq by the server [1] and can be retrieved even if the connection breaks. Some of these parameters may overlap \dconfig, but I don't see that as a problem. [1] https://www.postgresql.org/docs/devel/libpq-status.html Regards, Sami
-
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-01-20T11:40:03Z
On 2025-Jan-17, Sami Imseih wrote: > > Wait a second, why do we have these here? Aren't they already in > > \dconfig? > > \dconfig is generated by querying pg_settings and this > requires a halthy connection. The parameters being proposed with > \conninfo+ are set in libpq by the server [1] and can be retrieved > even if the connection breaks. > > Some of these parameters may overlap \dconfig, but I don't see > that as a problem. I feel the need to step back and ask, what problem are we solving here? Your explanation isn't wrong, but I'm not sure why does a psql user need all these. I think you wanted to display "is_superuser" and that seems sensible in the context of \conninfo+, and perhaps even "role" and "in_hot_standby" would make sense; but the rest of the parameters that libpq stores do not seem terribly interesting or relevant here. That leads me to also wonder why don't we change \conninfo to have this tabular behavior instead of creating a separate command for it. Why do we need to keep the existing form of \conninfo? To me it seems strictly less useful, as it is harder to read. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "Nunca se desea ardientemente lo que solo se desea por razón" (F. Alexandre)
-
Re: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2025-01-20T13:34:22Z
>That leads me to also wonder why don't we change \conninfo to have this >tabular behavior instead of creating a separate command for it. Why do >we need to keep the existing form of \conninfo? To me it seems strictly >less useful, as it is harder to read. Here, you're suggesting that it would be useful to keep the \conninfo meta-command, improve it with a "new version," and display the returned content as a table instead of text. If that's the case, I think it's a good idea since it would show the "new settings" that the current version doesn't display and, yes, it would serve the same purpose as \conninfo+. Regarding which settings to display, the discussion tends to get very broad, and we can never settle on what should be shown definitively. I believe that, often, less is more, so showing only the essential settings would be enough. Regards, Maiquel.
-
Re: Psql meta-command conninfo+
Hunaid Sohail <hunaidpgml@gmail.com> — 2025-01-21T10:17:21Z
Hi, On Mon, Jan 20, 2025 at 6:34 PM Maiquel Grassi <grassi@hotmail.com.br> wrote: > >That leads me to also wonder why don't we change \conninfo to have this > >tabular behavior instead of creating a separate command for it. Why do > >we need to keep the existing form of \conninfo? To me it seems strictly > >less useful, as it is harder to read. > > Here, you're suggesting that it would be useful to keep the \conninfo > meta-command, improve it with a "new version," and display the returned > content as a table instead of text. If that's the case, I think it's a > good idea > since it would show the "new settings" that the current version doesn't > display and, yes, it would serve the same purpose as \conninfo+. > Sure, we can proceed with that. I do hope this will be the final one we try :) > Regarding which settings to display, the discussion tends to get very > broad, > and we can never settle on what should be shown definitively. I believe > that, often, less is more, so showing only the essential settings would be > enough. > In that case, we can collectively decide which parameters should be shown in this command. My suggestion: - application_name - encodings (maybe?) - role (new patch) - is_superuser - session_authorization - in_hot_standby Feel free to suggest any additions or removals. Regards, Hunaid Sohail
-
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-02-21T18:19:10Z
I suggest the attached, which gets 99% there with 10% of the complexity, and has \conninfo (no plus sign) output this: Connection Information Parámetro │ Valor ───────────────────────┼──────────────────────── Base de Datos │ alvherre Client User │ alvherre Host │ 192.168.178.37 Port │ 55432 Opciones │ Protocol Version │ 3 Password Used? │ false GSSAPI Authenticated? │ false Backend PID │ 1589499 TLS Connection? │ true TLS Library │ OpenSSL TLS Protocol │ TLSv1.3 TLS Key Bits │ 256 TLS Cipher │ TLS_AES_256_GCM_SHA384 TLS Compression │ false ALPN │ postgresql Superuser? │ on Hot standby? │ off (18 filas) I have added the parameters is_superuser and in_hot_standby only, and stayed away from the libpq part of the patch to enumerate parameters. ISTM a hardcoded list is fine. Maybe keeping track of 'role' via ParameterStatus messages is a good idea for reasons unrelated to this patch -- maybe it can be useful for applications to be aware of role changes -- but I'm not 100% sure about that, and in particular I'm not sure how heavy the protocol traffic is going to be if such messages are emitted every time you run a security invoker function or things like that. So I'm leaving that part out for now, and it's easy to do both the libpq patch and \conninfo. Also, I don't see why we have to keep the current free-format \conninfo. When you run a query, you get things like # select 99 as "Luftballons"; Luftballons ───────────── 99 You don't get "There were only 99 Luftballons" or any such nonsense, and I don't see why \conninfo gets to play different rules. So I got rid of \conninfo+. Do people hate the question marks? -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ -
Re: Psql meta-command conninfo+
Tom Lane <tgl@sss.pgh.pa.us> — 2025-02-21T18:49:42Z
Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > Do people hate the question marks? They feel not per our usual style. I think the output would be equally readable without them. Otherwise, +1 for this general approach. I agree we can just kick the current output format to the curb. regards, tom lane
-
Re: Psql meta-command conninfo+
Maiquel Grassi <grassi@hotmail.com.br> — 2025-02-21T19:08:44Z
>You don't get "There were only 99 Luftballons" or any such nonsense, and I >don't see why \conninfo gets to play different rules. So I got rid of >\conninfo+. From my perspective across this, I support this approach. Regards, Maiquel.
-
Re: Psql meta-command conninfo+
Sami Imseih <samimseih@gmail.com> — 2025-02-21T19:54:17Z
> Maybe keeping track of 'role' via ParameterStatus messages is a good > idea for reasons unrelated to this patch -- maybe it can be useful for > applications to be aware of role changes -- but I'm not 100% sure about > that, and in particular I'm not sure how heavy the protocol traffic is > going to be if such messages are emitted every time you run a security > invoker function or things like that With the latest version of the patch, 'role' is not needed as 'session authorization' is not shown either [1]. The latest version LGTM. Regards, Sami Imseih Amazon Web Services (AWS) [1] https://www.postgresql.org/message-id/CAA5RZ0tbWopM83akPZ5M42V_RtyMTV8UfNUdE9LYw0YsPdOX5g%40mail.gmail.com
-
Re: Psql meta-command conninfo+
Tom Lane <tgl@sss.pgh.pa.us> — 2025-02-21T20:33:26Z
Sami Imseih <samimseih@gmail.com> writes: >> Maybe keeping track of 'role' via ParameterStatus messages is a good >> idea for reasons unrelated to this patch -- maybe it can be useful for >> applications to be aware of role changes -- but I'm not 100% sure about >> that, and in particular I'm not sure how heavy the protocol traffic is >> going to be if such messages are emitted every time you run a security >> invoker function or things like that > With the latest version of the patch, 'role' is not needed as > 'session authorization' is not shown either [1]. FWIW, the server currently sends at most one ParameterStatus change report per query. So I don't think that there is a huge performance argument against making 'role' be GUC_REPORT. On the other hand, supporting \conninfo is a pretty lousy argument for doing so --- surely we don't need a constantly-updated value to support a seldom-used command. Moreover, if \conninfo depended on that it wouldn't work with older servers. If we want to include 'role' in this output, what I'd propose is to have \conninfo issue "SHOW role", which is accepted by every server version. If it fails (say because we're in an aborted transaction), just omit that row from the output. regards, tom lane
-
Re: Psql meta-command conninfo+
Sami Imseih <samimseih@gmail.com> — 2025-02-21T23:30:06Z
> If we want to include 'role' in this output, what I'd propose is to > have \conninfo issue "SHOW role", which is accepted by every server > version. If it fails (say because we're in an aborted transaction), > just omit that row from the output. v37- would have handled this as the list of PQ parameters was dynamically generated and only those parameters reported by the specific version of the server showed up in \conninfo+. Of course that may have led to confusion in which some versions show role while others did not, but that could be dealt with in documentation. -- Sami Imseih Amazon Web Services (AWS)
-
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-02-22T09:16:48Z
On 2025-Feb-21, Sami Imseih wrote: > > If we want to include 'role' in this output, what I'd propose is to > > have \conninfo issue "SHOW role", which is accepted by every server > > version. If it fails (say because we're in an aborted transaction), > > just omit that row from the output. > > v37- would have handled this as the list of PQ parameters was > dynamically generated and only those parameters > reported by the specific version of the server showed up in > \conninfo+. Okay, I have pushed this with some trivial tweaks -- removed the question marks and capitalized a couple of words. I also changed "Port" to "Server Port" because I wasn't sure it was obvious it was that, and maybe we want to list the client port as well (like pg_stat_activity does). We can continue to discuss adding 'role', 'server authorization' and so on, if people think they are going to be useful. We can consider such a decision an open item for 18. I tried it with 9.2, which doesn't have in_hot_standby. It shows like this 55441 18devel 356833=# \conninfo Connection Information Parameter │ Value ──────────────────────┼──────────────────────── Database │ alvherre Client User │ alvherre Host │ localhost Host Address │ ::1 Server Port │ 55441 Options │ Protocol Version │ 3 Password Used │ false GSSAPI Authenticated │ false Backend PID │ 356833 TLS Connection │ true TLS Library │ OpenSSL TLS Protocol │ TLSv1.3 TLS Key Bits │ 256 TLS Cipher │ TLS_AES_256_GCM_SHA384 TLS Compression │ false ALPN │ none Superuser │ on Hot Standby │ unknown (19 rows) I think "unknown" here is okay, though we could probably say "unsupported by server" or just set it to null. Note that the boolean for superuser says 'on' instead of 'true'. Maybe we should make all the booleans use on/off instead of true/false? Not sure. Now we need someone to implement the PQsslAttribute() equivalent for GSS! If only to prove that the effort of tweaking the TLS layer to support multiple libraries ... Also, there's a bunch of "(char *)" casts that are 100% due to printTableAddCell() taking a char * instead of const char * for the cell value. That seems a bit silly, we should change that. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ -
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-02-25T21:18:47Z
On 2025-Feb-22, Alvaro Herrera wrote: > Also, there's a bunch of "(char *)" casts that are 100% due to > printTableAddCell() taking a char * instead of const char * for the cell > value. That seems a bit silly, we should change that. Ah, but the problem is that most of the input cells there come from PQgetValue which returns a char *, so we'd need different casts elsewhere. Nevermind this ... -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "Having your biases confirmed independently is how scientific progress is made, and hence made our great society what it is today" (Mary Gardiner)
-
Re: Psql meta-command conninfo+
Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> — 2025-02-26T12:34:28Z
Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > On 2025-Feb-22, Alvaro Herrera wrote: > >> Also, there's a bunch of "(char *)" casts that are 100% due to >> printTableAddCell() taking a char * instead of const char * for the cell >> value. That seems a bit silly, we should change that. > > Ah, but the problem is that most of the input cells there come from > PQgetValue which returns a char *, so we'd need different casts > elsewhere. Nevermind this ... Also, it calls mbvalidate(cell, ...), which can modify the contents (stripping out invalid characters), so we'd better hope all the possible PQsslAttribute() values are valid UTF-8 or we've got possible UB on our hands. - ilmari
-
Re: Psql meta-command conninfo+
Peter Eisentraut <peter@eisentraut.org> — 2025-06-12T14:25:22Z
On 21.02.25 19:19, Alvaro Herrera wrote: > I suggest the attached, which gets 99% there with 10% of the > complexity, and has \conninfo (no plus sign) output this: > > Connection Information > Parámetro │ Valor > ───────────────────────┼──────────────────────── > Base de Datos │ alvherre > Client User │ alvherre > Host │ 192.168.178.37 > Port │ 55432 > Opciones │ > Protocol Version │ 3 > Password Used? │ false > GSSAPI Authenticated? │ false > Backend PID │ 1589499 > TLS Connection? │ true > TLS Library │ OpenSSL > TLS Protocol │ TLSv1.3 > TLS Key Bits │ 256 > TLS Cipher │ TLS_AES_256_GCM_SHA384 > TLS Compression │ false > ALPN │ postgresql > Superuser? │ on > Hot standby? │ off > (18 filas) This new code uses the term "TLS" where the rest of PostgreSQL, including the rest of psql, uses the term "SSL". Making this different seems uselessly confusing. I suggest the attached patch to use "SSL" here as well.
-
Re: Psql meta-command conninfo+
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-06-12T15:28:39Z
On 2025-Jun-12, Peter Eisentraut wrote: > This new code uses the term "TLS" where the rest of PostgreSQL, including > the rest of psql, uses the term "SSL". Making this different seems > uselessly confusing. I suggest the attached patch to use "SSL" here as > well. Sure, let's do that for now. I think in the long term (19?) we should replace all uses of SSL with TLS. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
-
Re: Psql meta-command conninfo+
Peter Eisentraut <peter@eisentraut.org> — 2025-06-15T09:13:32Z
On 12.06.25 17:28, Alvaro Herrera wrote: > On 2025-Jun-12, Peter Eisentraut wrote: > >> This new code uses the term "TLS" where the rest of PostgreSQL, including >> the rest of psql, uses the term "SSL". Making this different seems >> uselessly confusing. I suggest the attached patch to use "SSL" here as >> well. > > Sure, let's do that for now. done