Allow pg_read_all_stats to see database size in \l+

On Wed, Jul 22, 2026 at 8:26 PM Christoph Berg <myon@debian.org> wrote:

psql \l+ didn't consider pg_read_all_stats when determining whether
the user was allowed to see the database size.

+1 to this fix.

Split off from the "Available disk space per tablespace" patch since
it's really an independent fix. Thanks Zsolt for pointing that out.

This might be considered for backpatching.

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.

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.)

Regards,

--
Fujii Masao

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

On 2026-Jul-22, Christoph Berg wrote:

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 think it's simple enough:

@@ -1069,12 +1069,16 @@ listAllDbs(const char *pattern, bool verbose)
 	appendPQExpBufferStr(&buf, "       ");
 	printACLColumn(&buf, "d.datacl");
 	if (verbose && pset.sversion >= 80200)
+	{
 		appendPQExpBuffer(&buf,
 						  ",\n       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
+						  "          %s"
 						  "            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
 						  "            ELSE 'No Access'\n"
 						  "       END as \"%s\"",
+						  pset.sversion >= 100000 ? "OR pg_catalog.pg_has_role('pg_read_all_stats', 'USAGE')\n" : "",
 						  gettext_noop("Size"));
+	}
 	if (verbose && pset.sversion >= 80000)
 		appendPQExpBuffer(&buf,
 						  ",\n       t.spcname as \"%s\"",

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/

Re: Álvaro Herrera

+ pset.sversion >= 100000 ? "OR pg_catalog.pg_has_role('pg_read_all_stats', 'USAGE')\n" : "",

Should I create patch files for 14..19?

Christoph

On Wed, Jul 22, 2026 at 11:52 PM Christoph Berg <myon@debian.org> wrote:

Re: Álvaro Herrera

+ pset.sversion >= 100000 ? "OR pg_catalog.pg_has_role('pg_read_all_stats', 'USAGE')\n" : "",

Should I create patch files for 14..19?

I've prepared patches for the older stable branches and attached them.

While doing so, I also updated the documentation changes.

Regards,

--
Fujii Masao

Re: Fujii Masao

I've prepared patches for the older stable branches and attached them.

While doing so, I also updated the documentation changes.

Looks good to me, thanks!

I tested the PG15 patch and it works, including against a 9.6 server.

Christoph

On Thu, Jul 23, 2026 at 7:44 PM Christoph Berg <myon@debian.org> wrote:

Looks good to me, thanks!

I tested the PG15 patch and it works, including against a 9.6 server.

Thanks for the review and test! I've pushed the patch.

Regards,

--
Fujii Masao