How to Obtain Permission to Run Vacuum

Products

• VMware Tanzu Greenplum

Issue/Introduction

Unlike the other permissions which can be controlled by the command GRANT, vacuum's permission is specially designed for maintenance tasks.

Resolution

Even if we grant all the permissions of a table to another user, he still would not be able to run the vacuum, and the error would be:

gpadmin=> \dt table_for_tom;
                List of relations
 Schema |     Name      | Type  | Owner | Storage
--------+---------------+-------+-------+---------
 public | table_for_tom | table | tom   | heap
(1 row)
gpadmin=# GRANT ALL ON TABLE table_for_tom to jerry ;
GRANT

psql -h localhost -U jerry gpadmin
gpadmin=> VACUUM table_for_tom;
WARNING:  skipping "table_for_tom" --- only table or database owner can vacuum it
VACUUM

The error message above is pretty straightforward already, and if we look into the Postgres's source code's, we see

* We allow the user to vacuum a table if he is superuser, the table
	 * owner, or the database owner (but in the latter case, only if it's not
	 * a shared relation).	pg_class_ownercheck includes the superuser case.

Thus, if the user would like to control the vacuum permission of the table, he can only set the user to these 3 roles.

Additional Information

+ Environment:

• Pivotal Greenplum Database (GPDB) all versions
• HAWQ all versions