Pivotal Knowledge Base

Follow

Upgrade from 4.2.x to 4.3.x results in loosing truncate permission

Environment

Pivotal Greenplum (GPDB) < 4.3.4

Problem

Upgrading from database 4.2.x or 4.3.x to a version less than 4.3.4.0 result in table owner in loosing truncate permission on their own table.

And to avoid the situation you will have to explicitly re-grant the truncate using

grant truncate on <schemaname>.<table_name> to <username>;

Cause

This is GPDB bug (worked internally using MPP-25004) which has the table owners in loosing the truncate permission after the upgrade.

Solution

The upgrade script of 4.3.4 version has the issue fixed, see the readme notes for more information.

If you are planning to upgrade to a lower 4.3.x version, then once the upgrade finishes successfully, run the below command to regrant the truncate permission to its table owners.

psql -d template1 -Atc "select datname from pg_database where datname!='template0'"| while read line
 do
 echo "[............................. Database: " $line ".............................]" 
 psql -d $line -Atc "select 'grant truncate on '||schemaname||'.'||tablename ||' to ' ||tableowner|| ';' from pg_tables" > /tmp/grant_$line.out
 psql -d $line -ef /tmp/grant_$line.out
done

Comments

Powered by Zendesk