Pivotal Knowledge Base

Follow

New TRUNCATE behaviour introduced from GPDB 4.3.3.0

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.3 and above

Symptom

The GRANT and REVOKE commands support the TRUNCATE privilege on a table. This is one of the new features of Pivotal Greenplum 4.3.3.0, per release notes.
You can use the GRANT and REVOKE commands to allow or prohibit a GPDB role (or a user) from removing all the rows in a table with the TRUNCATE command.

If you GRANT privileges directly to a role on a certain table and then upgrade from a previous release to 4.3.3.0 or higher, that role will not be able to TRUNCATE that table. Even if it's the owner of that table, unless you explicitly GRANT TRUNCATE on that table after the upgrade, the table would not be TRUNCATE'ed.

If you do not GRANT privileges directly to a role, after the upgrade that role will still be able to TRUNCATE a table if it is the owner of that table.

Cause

Once the privilege bitmap (technically "ACL array") has been initialized for a specific table/role, TRUNCATE will be denied unless its is set to True.

If the bitmap has not been initialized, by default a table owner will still be able to TRUNCATE its tables.

Resolution

For minor upgrades (e.g. from 4.3.x to 4.3.3.0 or higher), a script will be provided to GRANT TRUNCATE automatically to all users that are table-owners, only on the tables they own. Customers will be responsible for running this script. See attached script UpdateTruncateForOwner.sh

For major upgrades (e.g. from 4.2.x to 4.3.3.0 or higher), this script will run automatically during the upgrade, via gpmigrator.

Comments

Powered by Zendesk