Horton

Re: predefined role(s) for VACUUM and ANALYZE

Kyotaro Horiguchi <horikyota.ntt@gmail.com>

View thread View on postgr.es
From: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
To: nathandbossart@gmail.com
Cc: bharath.rupireddyforpostgres@gmail.com, pgsql-hackers@postgresql.org
Date: 2022-07-26T01:58:14Z
Lists: pgsql-hackers 
At Tue, 26 Jul 2022 10:47:12 +0900 (JST), Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote in 
> WARNING:  skipping "pg_statistic" --- only superusers, roles with privileges of pg_vacuum_analyze, or the database owner can vacuum it
> WARNING:  skipping "pg_type" --- only superusers, roles with privileges of pg_vacuum_analyze, or the database owner can vacuum it
> <snip many lines>

> WARNING:  skipping "user_mappings" --- only table or database owner can vacuum it

By the way, the last error above dissapears by granting
pg_vacuum_analyze to the role. Is there a reason the message is left
alone?  And If I specified the view directly, I would get the
following message.

postgres=> vacuum information_schema.user_mappings;
WARNING:  skipping "user_mappings" --- cannot vacuum non-tables or special system tables

So, "VACUUM;" does something wrong? Or is it the designed behavior?

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Commits

  1. Provide non-superuser predefined roles for vacuum and analyze

  2. Provide per-table permissions for vacuum and analyze.

  3. Expand AclMode to 64 bits

  4. Simplify WARNING messages from skipped vacuum/analyze on a table

  5. Allow granting SET and ALTER SYSTEM privileges on GUC parameters.

  6. Add String object access hooks