Date: Thu, 10 Oct 2024 16:19:14 +0200
From: Erik Wienhold <ewie@ewie.name>
To: Dominique Devienne <ddevienne@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Tablespace ACLs
Message-ID: <04e246e5-6808-4050-871d-07efc1cea715@ewie.name>
References: <CAFCRh--u2GEZ15=WJBRmMwPdKJ3r=_XSA3rw8sjWJZ6Nc2NDYw@mail.gmail.com>
 <711b77fc-f9e5-4637-8a2c-9cc1a3b3f4da@ewie.name>
 <833a8d75-09d7-4691-85bc-1ae0d16097e2@ewie.name>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <833a8d75-09d7-4691-85bc-1ae0d16097e2@ewie.name>
Archived-At: <https://www.postgresql.org/message-id/04e246e5-6808-4050-871d-07efc1cea715%40ewie.name>
Precedence: bulk

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.

On second thought, just guarding the pg_tablespace_size() call and
reporting an unknown size when permissions are insufficient is better,
so that \db+ still lists all tablespaces like \db does.

    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",
      CASE WHEN pg_catalog.has_tablespace_privilege(t.oid, 'CREATE')
             OR pg_catalog.pg_has_role('pg_read_all_stats', 'USAGE')
             OR dattablespace IS NOT NULL -- is default tablespace
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(t.oid))
        ELSE null -- unknown size
      END AS "Size",
      pg_catalog.shobj_description(t.oid, 'pg_tablespace') AS "Description"
    FROM pg_catalog.pg_tablespace t
      LEFT JOIN pg_catalog.pg_database
        ON datname = pg_catalog.current_database()
        AND dattablespace = t.oid
    ORDER BY 1;

-- 
Erik