Pivotal Knowledge Base


How to obtain permission to run vacuum


Pivotal Greenplum: All versions

HAWQ: All versions


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


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 ;

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


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.


Powered by Zendesk