Re: Psql meta-command conninfo+ - Mailing list pgsql-hackers
From | Jim Jones |
---|---|
Subject | Re: Psql meta-command conninfo+ |
Date | |
Msg-id | 8a93f6c0-795f-4260-a5fd-07ccbddbc88f@uni-muenster.de Whole thread Raw |
In response to | RE: Psql meta-command conninfo+ (Maiquel Grassi <grassi@hotmail.com.br>) |
Responses |
Re: Psql meta-command conninfo+
RE: Psql meta-command conninfo+ |
List | pgsql-hackers |
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
pgsql-hackers by date: