agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Christoph Berg <myon@debian.org>
To: Fujii Masao <masao.fujii@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Zsolt Parragi <zsolt.parragi@percona.com>
Subject: Re: Allow pg_read_all_stats to see database size in \l+
Date: Wed, 22 Jul 2026 15:12:26 +0200
Message-ID: <amDBusAY5ysRg-LQ@msg.df7cb.de> (raw)
In-Reply-To: <CAHGQGwEm55gLocu=ev7BM_ZbMWdykUvMPW2q72Lp84PyKkaNyg@mail.gmail.com>
References: <amCo6qRmnfPVk4-V@msg.df7cb.de>
<CAHGQGwEm55gLocu=ev7BM_ZbMWdykUvMPW2q72Lp84PyKkaNyg@mail.gmail.com>
Thanks for the review!
Re: Fujii Masao
> The pg_read_all_stats check should be fine in master, since psql there
> no longer supports pre-v10 servers, which don't have that role. But,
> if this is backpatched, psql still needs to work with pre-v10 servers,
> so we'll probably need a server version check (e.g., pset.sversion >= 100000)
> before checking for pg_read_all_stats, at least in the older stable branches.
The SQL-generating code there is already quite complex, and since no
one complained, perhaps just skip the backpatching if it's complicated.
I tried the patch back to version 9.3 and the query still works even
when the role doesn't exist there. (9.2 and earlier failed due to the
protocol version grease.)
> Also, should the following description of the \l+ meta-command in
> the psql docs be updated?
>
> (Size information is only available for databases that the current
> user can connect to.)
Done in v2.
Christoph
Attachments:
[text/x-diff] v2-0001-Allow-pg_read_all_stats-to-see-database-size-in-l.patch (1.9K, ../amDBusAY5ysRg-LQ@msg.df7cb.de/2-v2-0001-Allow-pg_read_all_stats-to-see-database-size-in-l.patch)
download | inline diff:
From 5fe2f0e6bb07fa6fe2172d732c1be50b4698210d Mon Sep 17 00:00:00 2001
From: Christoph Berg <myon@debian.org>
Date: Wed, 22 Jul 2026 13:16:16 +0200
Subject: [PATCH v2] Allow pg_read_all_stats to see database size in \l+
The server already allows members of pg_read_all_stats to see the size
of all databases, but psql's \l+ was too restrictive.
---
doc/src/sgml/ref/psql-ref.sgml | 5 +++--
src/bin/psql/describe.c | 3 ++-
2 files changed, 5 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 56c2692e618..4d5ee11ddd9 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2817,8 +2817,9 @@ SELECT
are displayed in expanded mode.
If <literal>+</literal> is appended to the command name, database
sizes, default tablespaces, and descriptions are also displayed.
- (Size information is only available for databases that the current
- user can connect to.)
+ Except for superusers or roles with privileges of the
+ <literal>pg_read_all_stats</literal> role, size information is only
+ available for databases that the current user can connect to.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index a2f09c26369..ad9c8affb4f 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -986,7 +986,8 @@ listAllDbs(const char *pattern, bool verbose)
printACLColumn(&buf, "d.datacl");
if (verbose)
appendPQExpBuffer(&buf,
- ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
+ ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') OR\n"
+ " pg_catalog.pg_has_role('pg_read_all_stats', 'USAGE')\n"
" THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
" ELSE 'No Access'\n"
" END as \"%s\""
--
2.53.0
view thread (12+ messages) latest in thread
Message-ID: <amDBusAY5ysRg-LQ@msg.df7cb.de>
Permalink: ../amDBusAY5ysRg-LQ@msg.df7cb.de/
Also on: postgresql.org/message-id/amDBusAY5ysRg-LQ@msg.df7cb.de
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: pgsql-hackers@postgresql.org
Cc: myon@debian.org, masao.fujii@gmail.com, pgsql-hackers@lists.postgresql.org, zsolt.parragi@percona.com
Subject: Re: Allow pg_read_all_stats to see database size in \l+
In-Reply-To: <amDBusAY5ysRg-LQ@msg.df7cb.de>
* 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