Postgres Pro
Downloads
Home   >   mailing lists

Thread: Tablespace ACLs

Tablespace ACLs

From
Dominique Devienne
Date:
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)

Re: Tablespace ACLs

From
Erik Wienhold
Date:
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

Re: Tablespace ACLs

From
Dominique Devienne
Date:
On Thu, Oct 10, 2024 at 3:40 PM Erik Wienhold <ewie@ewie.name> 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

Re: Tablespace ACLs

From
Erik Wienhold
Date:
I wrote:
> On 2024-10-10 14:35 +0200, Dominique Devienne wrote:
> > 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.

Maybe something along those lines:

    SELECT spcname AS "Name",
      pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
      pg_catalog.pg_tablespace_location(t.oid) AS "Location",
      pg_catalog.array_to_string(spcacl, E'\n') AS "Access privileges",
      spcoptions AS "Options",
      pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(t.oid)) AS "Size",
      pg_catalog.shobj_description(t.oid, 'pg_tablespace') AS "Description"
    FROM pg_catalog.pg_tablespace t
    LEFT JOIN pg_catalog.pg_database d
      ON datname = pg_catalog.current_database()
      AND dattablespace = t.oid
    WHERE pg_catalog.has_tablespace_privilege(t.oid, 'CREATE')
      OR pg_has_role('pg_read_all_stats', 'USAGE')
      OR dattablespace IS NOT NULL -- is default tablespace?
    ORDER BY 1;

-- 
Erik

Re: Tablespace ACLs

From
Erik Wienhold
Date:
I wrote:
> > On 2024-10-10 14:35 +0200, Dominique Devienne wrote:
> > > 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.

On second thought, just guarding the pg_tablespace_size() call and
reporting an unknown size when permissions are insufficient is better,
so that \db+ still lists all tablespaces like \db does.

    SELECT spcname AS "Name",
      pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
      pg_catalog.pg_tablespace_location(t.oid) AS "Location",
      pg_catalog.array_to_string(spcacl, E'\n') AS "Access privileges",
      spcoptions AS "Options",
      CASE WHEN pg_catalog.has_tablespace_privilege(t.oid, 'CREATE')
             OR pg_catalog.pg_has_role('pg_read_all_stats', 'USAGE')
             OR dattablespace IS NOT NULL -- is default tablespace
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(t.oid))
        ELSE null -- unknown size
      END AS "Size",
      pg_catalog.shobj_description(t.oid, 'pg_tablespace') AS "Description"
    FROM pg_catalog.pg_tablespace t
      LEFT JOIN pg_catalog.pg_database
        ON datname = pg_catalog.current_database()
        AND dattablespace = t.oid
    ORDER BY 1;

-- 
Erik

Re: Tablespace ACLs

From
Dominique Devienne
Date:
On Thu, Oct 10, 2024 at 4:19 PM Erik Wienhold <ewie@ewie.name> wrote:
> > > On 2024-10-10 14:35 +0200, Dominique Devienne wrote:
> > > > On a related but different matter, is it normal not having access to a
> > > > single tablespace makes the whole output disappear?
> On second thought, just guarding the pg_tablespace_size() call and
> reporting an unknown size when permissions are insufficient is better,
> so that \db+ still lists all tablespaces like \db does.

FWIW, I agree. --DD