Pivotal Knowledge Base


How to Obtain Permission to Run VIntoacuum


  • Pivotal Greenplum Database (GPDB) 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 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.


Powered by Zendesk