Pivotal Knowledge Base

Follow

How to obtain permission to run vacuum

Environment

Pivotal Greenplum: All versions

HAWQ: All versions

Overview

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

Symptom

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

Explanation

The error message above is pretty straight forward 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.

Comments

Powered by Zendesk