public inbox for [email protected]
help / color / mirror / Atom feedFrom: Maiquel Grassi <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: Jim Jones <[email protected]>
Cc: Pavel Luzanov <[email protected]>
Cc: Erik Wienhold <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: Psql meta-command conninfo+
Date: Mon, 18 Mar 2024 22:05:17 +0000
Message-ID: <CP8P284MB24961A4A0AFD1050391EBAF9EC2D2@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <20240229224131.GA3004270@nathanxps13>
References: <[email protected]>
<CP8P284MB2496C7F14594722472C9FEBAEC482@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<[email protected]>
<CP8P284MB24969B39579E808F9F44CCF8EC4D2@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<CP8P284MB249664C44A1793823C4546F8EC4D2@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<[email protected]>
<20240216155449.GA1236054@nathanxps13>
<CP8P284MB2496AAFB879ABC5B1CD01CF4EC532@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<20240224204412.GA1866465@nathanxps13>
<CP8P284MB2496EC00918E9439500DF0E2EC5F2@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
<20240229224131.GA3004270@nathanxps13>
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.
Attachments:
[application/octet-stream] v20-0001-psql-meta-command-conninfo-plus.patch (13.6K, ../CP8P284MB24961A4A0AFD1050391EBAF9EC2D2@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM/3-v20-0001-psql-meta-command-conninfo-plus.patch)
download | inline diff:
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index cc7d797..ebb1f4a 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1029,11 +1029,106 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
</varlistentry>
<varlistentry id="app-psql-meta-command-conninfo">
- <term><literal>\conninfo</literal></term>
+ <term><literal>\conninfo[+]</literal></term>
<listitem>
<para>
Outputs information about the current database connection.
+ When no <literal>+</literal> is appended, it simply prints
+ a textual (string) description of a few connection options.
+ When <literal>+</literal> is given, more complete information
+ is displayed as a table containing the following columns:
</para>
+
+ <para>
+ <literal>Database:</literal> Displays the name of the current
+ database on this connection.
+ </para>
+
+ <para>
+ <literal>Authenticated User:</literal> Displays the authenticated
+ user at the time of psql connection with the server.
+ </para>
+
+ <para>
+ <literal>System User:</literal> 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.
+ </para>
+
+ <para>
+ <literal>Current User:</literal> Displays the user name of the
+ current execution context.
+ </para>
+
+ <para>
+ <literal>Session User:</literal> Displays the session user's name.
+ </para>
+
+ <para>
+ <literal>Backend PID:</literal> Displays the process ID of the server
+ process attached to the current session.
+ </para>
+
+ <para>
+ <literal>Server Address:</literal> Displays the IP address on which
+ the server accepted the current connection, or NULL if the current
+ connection is via a Unix-domain socket.
+ </para>
+
+ <para>
+ <literal>Server Port:</literal> Displays the IP port number on which
+ the server accepted the current connection, or NULL if the current
+ connection is via a Unix-domain socket.
+ </para>
+
+ <para>
+ <literal>Client Address:</literal> Displays the IP address of the
+ current client, or NULL if the current connection is via a Unix-domain socket.
+ </para>
+
+ <para>
+ <literal>Client Port:</literal> Displays the IP port number of the
+ current client, or NULL if the current connection is via a Unix-domain socket.
+ </para>
+
+ <para>
+ <literal>Socket Directory:</literal> Displays the directory where
+ Unix-domain socket was created.
+ </para>
+
+ <para>
+ <literal>Host:</literal> Displays the host of the connection,
+ which refers to the address or name of the machine where the server
+ is running. When a socket connection is established, it displays NULL.
+ </para>
+
+ <para>
+ <literal>GSSAPI:</literal> Displays "true" if GSSAPI is in use, or "false"
+ if GSSAPI is not in use on this connection.
+ </para>
+
+ <para>
+ <literal>SSL Connection:</literal> Displays "true" if the current
+ connection to the server uses SSL, and "false" otherwise.
+ </para>
+
+ <para>
+ <literal>SSL Protocol:</literal> Displays the name of the protocol
+ used for the SSL connection (e.g., TLSv1.0, TLSv1.1, TLSv1.2 or TLSv1.3).
+ </para>
+
+ <para>
+ <literal>SSL Cipher:</literal> Displays the name of the cipher used
+ for the SSL connection (e.g., DHE-RSA-AES256-SHA).
+ </para>
+
+ <para>
+ <literal>SLL Compression:</literal> Displays "on" if SSL compression
+ is in use, "off" if not, or NULL if SSL is not in use on this connection.
+ </para>
+
</listitem>
</varlistentry>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 9b0fa04..fb5f63d 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -68,7 +68,8 @@ static backslashResult exec_command_C(PsqlScanState scan_state, bool active_bran
static backslashResult exec_command_connect(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_cd(PsqlScanState scan_state, bool active_branch,
const char *cmd);
-static backslashResult exec_command_conninfo(PsqlScanState scan_state, bool active_branch);
+static backslashResult exec_command_conninfo(PsqlScanState scan_state, bool active_branch,
+ const char *cmd);
static backslashResult exec_command_copy(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_copyright(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_crosstabview(PsqlScanState scan_state, bool active_branch);
@@ -317,8 +318,8 @@ exec_command(const char *cmd,
status = exec_command_connect(scan_state, active_branch);
else if (strcmp(cmd, "cd") == 0)
status = exec_command_cd(scan_state, active_branch, cmd);
- else if (strcmp(cmd, "conninfo") == 0)
- status = exec_command_conninfo(scan_state, active_branch);
+ else if (strcmp(cmd, "conninfo") == 0 || strcmp(cmd, "conninfo+") == 0)
+ status = exec_command_conninfo(scan_state, active_branch, cmd);
else if (pg_strcasecmp(cmd, "copy") == 0)
status = exec_command_copy(scan_state, active_branch);
else if (strcmp(cmd, "copyright") == 0)
@@ -643,47 +644,80 @@ exec_command_cd(PsqlScanState scan_state, bool active_branch, const char *cmd)
}
/*
- * \conninfo -- display information about the current connection
+ * \conninfo, \conninfo+ -- display information about the current connection
*/
static backslashResult
-exec_command_conninfo(PsqlScanState scan_state, bool active_branch)
+exec_command_conninfo(PsqlScanState scan_state, bool active_branch, const char *cmd)
{
+ bool success = true;
+
if (active_branch)
{
char *db = PQdb(pset.db);
+ bool show_verbose;
+
+ show_verbose = strchr(cmd, '+') ? true : false;
- if (db == NULL)
- printf(_("You are currently not connected to a database.\n"));
+ /*
+ * \conninfo+
+ */
+ if (show_verbose)
+ success = listConnectionInformation();
+
+ /*
+ * \conninfo
+ */
else
{
- char *host = PQhost(pset.db);
- char *hostaddr = PQhostaddr(pset.db);
+ PGresult *res;
+ PQExpBufferData buf;
- if (is_unixsock_path(host))
- {
- /* hostaddr overrides host */
- if (hostaddr && *hostaddr)
- printf(_("You are connected to database \"%s\" as user \"%s\" on address \"%s\" at port \"%s\".\n"),
- db, PQuser(pset.db), hostaddr, PQport(pset.db));
- else
- printf(_("You are connected to database \"%s\" as user \"%s\" via socket in \"%s\" at port \"%s\".\n"),
- db, PQuser(pset.db), host, PQport(pset.db));
- }
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT\n"
+ " inet_server_addr();");
+
+ res = PSQLexec(buf.data);
+
+ termPQExpBuffer(&buf);
+
+ if (!res)
+ return PSQL_CMD_ERROR;
else
{
- if (hostaddr && *hostaddr && strcmp(host, hostaddr) != 0)
- printf(_("You are connected to database \"%s\" as user \"%s\" on host \"%s\" (address \"%s\") at port \"%s\".\n"),
- db, PQuser(pset.db), host, hostaddr, PQport(pset.db));
+ char *host = PQhost(pset.db);
+ char *hostaddr = PQhostaddr(pset.db);
+
+ if (is_unixsock_path(host))
+ {
+ /* hostaddr overrides host */
+ if (hostaddr && *hostaddr)
+ printf(_("You are connected to database \"%s\" as user \"%s\" on address \"%s\" at port \"%s\".\n"),
+ db, PQuser(pset.db), PQgetvalue(res, 0, 0), PQport(pset.db));
+ else
+ printf(_("You are connected to database \"%s\" as user \"%s\" via socket in \"%s\" at port \"%s\".\n"),
+ db, PQuser(pset.db), host, PQport(pset.db));
+ }
else
- printf(_("You are connected to database \"%s\" as user \"%s\" on host \"%s\" at port \"%s\".\n"),
- db, PQuser(pset.db), host, PQport(pset.db));
+ {
+ if (hostaddr && *hostaddr && strcmp(host, hostaddr) != 0)
+ printf(_("You are connected to database \"%s\" as user \"%s\" on host \"%s\" (address \"%s\") at port \"%s\".\n"),
+ db, PQuser(pset.db), host, PQgetvalue(res, 0, 0), PQport(pset.db));
+ else
+ printf(_("You are connected to database \"%s\" as user \"%s\" on host \"%s\" at port \"%s\".\n"),
+ db, PQuser(pset.db), host, PQport(pset.db));
+ }
+ PQclear(res);
+ printSSLInfo();
+ printGSSInfo();
}
- printSSLInfo();
- printGSSInfo();
}
}
+ else
+ ignore_slash_options(scan_state);
- return PSQL_CMD_SKIP_LINE;
+ return success ? PSQL_CMD_SKIP_LINE : PSQL_CMD_ERROR;
}
/*
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 6433497..3b0d913 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -26,6 +26,7 @@
#include "fe_utils/mbprint.h"
#include "fe_utils/print.h"
#include "fe_utils/string_utils.h"
+#include "libpq/pqcomm.h"
#include "settings.h"
#include "variables.h"
@@ -901,6 +902,111 @@ error_return:
return false;
}
+/*
+ * listConnectionInformation
+ *
+ * for \conninfo+
+ */
+bool
+listConnectionInformation(void)
+{
+ PGresult *res;
+ PQExpBufferData buf;
+ printQueryOpt myopt = pset.popt;
+
+ char *host = PQhost(pset.db);
+ char *hostaddr = PQhostaddr(pset.db);
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT\n"
+ " pg_catalog.current_database() AS \"Database\",\n"
+ " '%s' AS \"Authenticated User\",\n",
+ PQuser(pset.db));
+ if (pset.sversion >= 160000)
+ appendPQExpBuffer(&buf,
+ " pg_catalog.system_user() AS \"System User\",\n");
+ else
+ appendPQExpBuffer(&buf,
+ " NULL AS \"System User\",\n");
+ appendPQExpBuffer(&buf,
+ " pg_catalog.current_user() AS \"Current User\",\n"
+ " pg_catalog.session_user() AS \"Session User\",\n"
+ " pg_catalog.pg_backend_pid() AS \"Backend PID\",\n"
+ " pg_catalog.inet_server_addr() AS \"Server Address\",\n"
+ " pg_catalog.current_setting('port') AS \"Server Port\",\n"
+ " pg_catalog.inet_client_addr() AS \"Client Address\",\n"
+ " pg_catalog.inet_client_port() AS \"Client Port\",\n");
+ if (is_unixsock_path(host) && !(hostaddr && *hostaddr))
+ appendPQExpBuffer(&buf,
+ " '%s' AS \"Socket Directory\",\n",
+ host);
+ else
+ appendPQExpBuffer(&buf,
+ " NULL AS \"Socket Directory\",\n");
+ appendPQExpBuffer(&buf,
+ " CASE\n"
+ " WHEN\n"
+ " pg_catalog.inet_server_addr() IS NULL\n"
+ " AND pg_catalog.inet_client_addr() IS NULL\n"
+ " THEN NULL\n"
+ " ELSE '%s'\n"
+ " END AS \"Host\",\n",
+ host);
+ if (pset.sversion >= 120000)
+ appendPQExpBuffer(&buf,
+ " (SELECT gss_authenticated AS \"GSSAPI\"\n"
+ " FROM pg_catalog.pg_stat_gssapi\n"
+ " WHERE pid = pg_catalog.pg_backend_pid()),\n");
+ else
+ appendPQExpBuffer(&buf,
+ " NULL AS \"GSSAPI\",\n");
+ if (pset.sversion >= 90500)
+ {
+ if (pset.sversion < 140000)
+ appendPQExpBuffer(&buf,
+ " ssl.ssl AS \"SSL Connection\",\n"
+ " ssl.version AS \"SSL Protocol\",\n"
+ " ssl.cipher AS \"SSL Cipher\",\n"
+ " ssl.compression AS \"SSL Compression\"\n"
+ "FROM\n"
+ " pg_catalog.pg_stat_ssl ssl\n"
+ "WHERE\n"
+ " pid = pg_catalog.pg_backend_pid()\n");
+ if (pset.sversion >= 140000)
+ appendPQExpBuffer(&buf,
+ " ssl.ssl AS \"SSL Connection\",\n"
+ " ssl.version AS \"SSL Protocol\",\n"
+ " ssl.cipher AS \"SSL Cipher\",\n"
+ " NULL AS \"SSL Compression\"\n"
+ "FROM\n"
+ " pg_catalog.pg_stat_ssl ssl\n"
+ "WHERE\n"
+ " pid = pg_catalog.pg_backend_pid()\n");
+ }
+ else
+ appendPQExpBuffer(&buf,
+ " NULL AS \"SSL Connection\",\n"
+ " NULL AS \"SSL Protocol\",\n"
+ " NULL AS \"SSL Cipher\",\n"
+ " NULL AS \"SSL Compression\"\n");
+ appendPQExpBuffer(&buf,
+ ";");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.title = _("Current Connection Information");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
/*
* listAllDbs
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 273f974..8a4e83f 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -64,6 +64,9 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
/* \dFt */
extern bool listTSTemplates(const char *pattern, bool verbose);
+/* \conninfo */
+extern bool listConnectionInformation(void);
+
/* \l */
extern bool listAllDbs(const char *pattern, bool verbose);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 4e79a81..2c5426d 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -310,7 +310,7 @@ slashUsage(unsigned short int pager)
else
HELP0(" \\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}\n"
" connect to new database (currently no connection)\n");
- HELP0(" \\conninfo display information about current connection\n");
+ HELP0(" \\conninfo[+] display information about current connection\n");
HELP0(" \\encoding [ENCODING] show or set client encoding\n");
HELP0(" \\password [USERNAME] securely change the password for a user\n");
HELP0("\n");
view thread (43+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: RE: Psql meta-command conninfo+
In-Reply-To: <CP8P284MB24961A4A0AFD1050391EBAF9EC2D2@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox