MIME-Version: 1.0
From: Dominique Devienne <ddevienne@gmail.com>
Date: Thu, 10 Oct 2024 14:35:17 +0200
Message-ID: <CAFCRh--u2GEZ15=WJBRmMwPdKJ3r=_XSA3rw8sjWJZ6Nc2NDYw@mail.gmail.com>
Subject: Tablespace ACLs
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh--u2GEZ15%3DWJBRmMwPdKJ3r%3D_XSA3rw8sjWJZ6Nc2NDYw%40mail.gmail.com>
Precedence: bulk

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