public inbox for [email protected]
help / color / mirror / Atom feedFrom: Erik Wienhold <[email protected]>
To: Dominique Devienne <[email protected]>
Cc: [email protected]
Subject: Re: Tablespace ACLs
Date: Thu, 10 Oct 2024 15:40:07 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFCRh--u2GEZ15=WJBRmMwPdKJ3r=_XSA3rw8sjWJZ6Nc2NDYw@mail.gmail.com>
References: <CAFCRh--u2GEZ15=WJBRmMwPdKJ3r=_XSA3rw8sjWJZ6Nc2NDYw@mail.gmail.com>
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
view thread (2+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Tablespace ACLs
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox