public inbox for [email protected]
help / color / mirror / Atom feedRe: Tablespace ACLs
2+ messages / 2 participants
[nested] [flat]
* Re: Tablespace ACLs
@ 2024-10-10 13:40 Erik Wienhold <[email protected]>
2024-10-10 13:45 ` Re: Tablespace ACLs Dominique Devienne <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Erik Wienhold @ 2024-10-10 13:40 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Tablespace ACLs
2024-10-10 13:40 Re: Tablespace ACLs Erik Wienhold <[email protected]>
@ 2024-10-10 13:45 ` Dominique Devienne <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Dominique Devienne @ 2024-10-10 13:45 UTC (permalink / raw)
To: Erik Wienhold <[email protected]>; +Cc: [email protected]
On Thu, Oct 10, 2024 at 3:40 PM Erik Wienhold <[email protected]> wrote:
> 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?
>
> You must call acldefault() with spcowner instead of oid:
>
> aclexplode(coalesce(spcacl, acldefault('t', spcowner)))
Rah, silly me. Thanks Erik, much better now. --DD
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-10-10 13:45 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-10 13:40 Re: Tablespace ACLs Erik Wienhold <[email protected]>
2024-10-10 13:45 ` Dominique Devienne <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox