From: Pavel Luzanov <p.luzanov@postgrespro.ru>
To: Andrew Dunstan <andrew@dunslane.net>
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Corey Huinker <corey.huinker@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: predefined role(s) for VACUUM and ANALYZE
Date: Mon, 5 Dec 2022 23:21:08 +0300
Message-ID: <a2382acd-e465-85b2-9d8e-f9ed1a5a66e9@postgrespro.ru> (raw)
In-Reply-To: <8609e4f7-5ffd-9fef-a5e0-78edb8818f3c@dunslane.net>
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>
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
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: pgsql-hackers@postgresql.org
Cc: p.luzanov@postgrespro.ru, andrew@dunslane.net, nathandbossart@gmail.com, corey.huinker@gmail.com, tgl@sss.pgh.pa.us, sfrost@snowman.net, bharath.rupireddyforpostgres@gmail.com, david.g.johnston@gmail.com, horikyota.ntt@gmail.com, michael@paquier.xyz, robertmhaas@gmail.com
Subject: Re: predefined role(s) for VACUUM and ANALYZE
In-Reply-To: <a2382acd-e465-85b2-9d8e-f9ed1a5a66e9@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox