Date: Thu, 10 Oct 2024 15:40:07 +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: <711b77fc-f9e5-4637-8a2c-9cc1a3b3f4da@ewie.name>
References: <CAFCRh--u2GEZ15=WJBRmMwPdKJ3r=_XSA3rw8sjWJZ6Nc2NDYw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CAFCRh--u2GEZ15=WJBRmMwPdKJ3r=_XSA3rw8sjWJZ6Nc2NDYw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/711b77fc-f9e5-4637-8a2c-9cc1a3b3f4da%40ewie.name>
Precedence: bulk

On 2024-10-10 14:35 +0200, Dominique Devienne wrote:
> Hi. Why isn't the ::regrole::text cast working as usual?
> Aren't the OIDs for grantor and grantee returned by acldefault() valid ROLEs?
> 
> C:\Users\ddevienne>psql service=...
> psql (17.0)
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
> compression: off, ALPN: postgresql)
> Type "help" for help.
> 
> ddevienne=> show server_version;
>  server_version
> ----------------
>  17.0
> (1 row)
> 
> ddevienne=> select grantor::regrole::text,
> ddevienne->        case when grantee = 0 then 'PUBLIC' else
> grantee::regrole::text end,
> ddevienne->        privilege_type, is_grantable
> ddevienne->   from pg_tablespace
> ddevienne->   left join lateral aclexplode(coalesce(spcacl,
> acldefault('t', oid))) on true

You must call acldefault() with spcowner instead of oid:

    aclexplode(coalesce(spcacl, acldefault('t', spcowner)))

> ddevienne->  where spcname = 'pg_default';
>  grantor | grantee | privilege_type | is_grantable
> ---------+---------+----------------+--------------
>  1663    | 1663    | CREATE         | f
> (1 row)
> 
> 
> ddevienne=> select grantor::regrole::text,
> ddevienne->        case when grantee = 0 then 'PUBLIC' else
> grantee::regrole::text end,
> ddevienne->        privilege_type, is_grantable
> ddevienne->   from pg_tablespace
> ddevienne->   left join lateral aclexplode(coalesce(spcacl,
> acldefault('t', oid))) on true
> ddevienne->  where spcname = 'hdd_data';
>  grantor | grantee | privilege_type | is_grantable
> ---------+---------+----------------+--------------
>  2128194 | 2128194 | CREATE         | f
> (1 row)
> 
> 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.

> ddevienne=> \c - postgres
> ddevienne=# \db+
>                                      List of tablespaces
>     Name    |  Owner   |    Location    | Access privileges | Options
> |  Size   | Description
> ------------+----------+----------------+-------------------+---------+---------+-------------
>  hdd_data   | postgres | ... |                   |         | 0 bytes |
>  pg_default | postgres |                |                   |
> | 1077 MB |
>  pg_global  | postgres |                |                   |
> | 6301 kB |
> (3 rows)

-- 
Erik