From: Erik Wienhold <ewie@ewie.name>
Subject: [PATCH v1] psql: call pg_tablespace_size if user has access privilege
Date: Thu, 10 Oct 2024 18:02:00 +0200
---
doc/src/sgml/ref/psql-ref.sgml | 4 ++--
src/bin/psql/describe.c | 25 +++++++++++++++++++++----
2 files changed, 23 insertions(+), 6 deletions(-)
inline patch 1: doc/src/sgml/ref/psql-ref.sgml, src/bin/psql/describe.c (+23/-6 lines) download inline-1.patchdiff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgmlindex b825ca96a2..3c8e614a82 100644--- a/doc/src/sgml/ref/psql-ref.sgml+++ b/doc/src/sgml/ref/psql-ref.sgml@@ -1456,8 +1456,8 @@ SELECT $1 \parse stmt1
class="parameter">pattern</replaceable>
is specified, only tablespaces whose names match the pattern are shown.
If <literal>+</literal> is appended to the command name, each tablespace
- is listed with its associated options, on-disk size, permissions and- description.+ is listed with its associated options, on-disk size (NULL if user lacks+ access privileges), permissions and description.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.cindex 6a36c91083..6b6c51ff0f 100644--- a/src/bin/psql/describe.c+++ b/src/bin/psql/describe.c@@ -223,7 +223,7 @@ describeTablespaces(const char *pattern, bool verbose)
printfPQExpBuffer(&buf,
"SELECT spcname AS \"%s\",\n"
" pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
- " pg_catalog.pg_tablespace_location(oid) AS \"%s\"",+ " pg_catalog.pg_tablespace_location(t.oid) AS \"%s\"",
gettext_noop("Name"),
gettext_noop("Owner"),
gettext_noop("Location"));
@@ -232,17 +232,34 @@ describeTablespaces(const char *pattern, bool verbose)
{
appendPQExpBufferStr(&buf, ",\n ");
printACLColumn(&buf, "spcacl");
+ /*+ * Calling pg_tablespace_size(oid) requires certain privileges or the+ * it must be the database's default tablespace. Guard that call and+ * report an unknown size by returning NULL if the current role lacks+ * the necessary privileges.+ */
appendPQExpBuffer(&buf,
",\n spcoptions AS \"%s\""
- ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""- ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",+ ",\n CASE WHEN pg_catalog.has_tablespace_privilege(t.oid, 'CREATE')\n"+ " OR pg_catalog.pg_has_role('pg_read_all_stats', 'USAGE')\n"+ " OR dattablespace IS NOT NULL\n"+ " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(t.oid))\n"+ " ELSE null\n"+ " END AS \"%s\""+ ",\n pg_catalog.shobj_description(t.oid, 'pg_tablespace') AS \"%s\"",
gettext_noop("Options"),
gettext_noop("Size"),
gettext_noop("Description"));
}
appendPQExpBufferStr(&buf,
- "\nFROM pg_catalog.pg_tablespace\n");+ "\nFROM pg_catalog.pg_tablespace t\n");++ if (verbose)+ appendPQExpBufferStr(&buf,+ " LEFT JOIN pg_catalog.pg_database"+ " ON datname = pg_catalog.current_database()"+ " AND dattablespace = t.oid\n");
if (!validateSQLNamePattern(&buf, pattern, false, false,
NULL, "spcname", NULL,
--
2.47.0
--wm2mka4nmlq6akvv--
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: pgsql-general@postgresql.org
Cc: ewie@ewie.name
Subject: Re: [PATCH v1] psql: call pg_tablespace_size if user has access privilege
In-Reply-To: <no-message-id-49425@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox