Message-ID: <a2382acd-e465-85b2-9d8e-f9ed1a5a66e9@postgrespro.ru>
Date: Mon, 5 Dec 2022 23:21:08 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: predefined role(s) for VACUUM and ANALYZE
Content-Language: en-US
To: Andrew Dunstan <andrew@dunslane.net>,
 Nathan Bossart <nathandbossart@gmail.com>
Cc: Corey Huinker <corey.huinker@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 Stephen Frost <sfrost@snowman.net>,
 Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
 "David G. Johnston" <david.g.johnston@gmail.com>,
 Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
 Michael Paquier <michael@paquier.xyz>, Robert Haas <robertmhaas@gmail.com>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
References: <20220930231834.GA366260@nathanxps13>
 <CADkLM=fg15_SYRGBcTFpoW-vVUTCkLfe+NTM0G32RjWY1W5Pvw@mail.gmail.com>
 <20221114234004.GA1771874@nathanxps13> <20221115050813.GA1953731@nathanxps13>
 <287b17b8-92f3-2bc2-6bcf-31dc1305b65a@dunslane.net>
 <20221117043952.GA116054@nathanxps13> <20221118170504.GA401589@nathanxps13>
 <20221119185004.GA539143@nathanxps13> <20221120165713.GA597801@nathanxps13>
 <0b00a6ff-1475-c0ba-15ec-5b5e381c6359@dunslane.net>
 <20221123235444.GA479104@nathanxps13>
 <8609e4f7-5ffd-9fef-a5e0-78edb8818f3c@dunslane.net>
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
In-Reply-To: <8609e4f7-5ffd-9fef-a5e0-78edb8818f3c@dunslane.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/a2382acd-e465-85b2-9d8e-f9ed1a5a66e9%40postgrespro.ru>
Precedence: bulk

Hello,

While looking into the new feature, I found the following situation with 
the \dp command displaying privileges on the system tables:

GRANT VACUUM, ANALYZE ON TABLE pg_type TO alice;

SELECT relacl FROM pg_class WHERE oid = 'pg_type'::regclass;
                            relacl
-------------------------------------------------------------
  {=r/postgres,postgres=arwdDxtvz/postgres,alice=vz/postgres}
(1 row)

But the \dp command does not show the granted privileges:

\dp pg_type
                             Access privileges
  Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+------+-------------------+-------------------+----------
(0 rows)

The comment in src/bin/psql/describe.c explains the situation:

     /*
      * Unless a schema pattern is specified, we suppress system and temp
      * tables, since they normally aren't very interesting from a 
permissions
      * point of view.  You can see 'em by explicit request though, eg 
with \z
      * pg_catalog.*
      */


So to see the privileges you have to explicitly specify the schema name:

\dp pg_catalog.pg_type
                                      Access privileges
    Schema   |  Name   | Type  |      Access privileges      | Column 
privileges | Policies
------------+---------+-------+-----------------------------+-------------------+----------
  pg_catalog | pg_type | table | =r/postgres +|                   |
             |         |       | 
postgres=arwdDxtvz/postgres+|                   |
             |         |       | alice=vz/postgres |                   |
(1 row)

But perhaps this behavior should be reviewed or at least documented?

-----
Pavel Luzanov