Re: Tablespace ACLs

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