I wrote:
> On 2024-10-10 14:35 +0200, Dominique Devienne wrote:
> > On a related but different matter, is it normal not having access to a
> > single tablespace makes the whole output disappear?
> >
> > ddevienne=> \db+
> > ERROR: permission denied for tablespace hdd_data
>
> This lacks permission for executing pg_tablespace_size(). Granting
> pg_read_all_stats should be sufficient. But I agree, omitting the
> non-accessible tablespaces would be better IMO.
Maybe something along those lines:
SELECT spcname AS "Name",
pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
pg_catalog.pg_tablespace_location(t.oid) AS "Location",
pg_catalog.array_to_string(spcacl, E'\n') AS "Access privileges",
spcoptions AS "Options",
pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(t.oid)) AS "Size",
pg_catalog.shobj_description(t.oid, 'pg_tablespace') AS "Description"
FROM pg_catalog.pg_tablespace t
LEFT JOIN pg_catalog.pg_database d
ON datname = pg_catalog.current_database()
AND dattablespace = t.oid
WHERE pg_catalog.has_tablespace_privilege(t.oid, 'CREATE')
OR pg_has_role('pg_read_all_stats', 'USAGE')
OR dattablespace IS NOT NULL -- is default tablespace?
ORDER BY 1;
--
Erik
