Granting create external table priv to a role.

ALTER ROLE name RENAME TO newname 

ALTER ROLE name SET config_parameter {TO | =} {value | DEFAULT} 

ALTER ROLE name RESET config_parameter 


ALTER ROLE name [ [WITH] option [ ... ] ]

where option can be:

     SUPERUSER | NOSUPERUSER     | CREATEDB | NOCREATEDB     | CREATEROLE | NOCREATEROLE     | CREATEEXTTABLE | NOCREATEEXTTABLE        [ ( attribute='value'[, ...] ) ]            where attributes and value are:            type='readable'|'writable'            protocol='gpfdist'|'http'     | INHERIT | NOINHERIT     | LOGIN | NOLOGIN     | CONNECTION LIMIT connlimit     | [ENCRYPTED | UNENCRYPTED] PASSWORD 'password'     | VALID UNTIL 'timestamp'     | [ DENY deny_point ]     | [ DENY BETWEEN deny_point AND deny_point]     | [ DROP DENY FOR deny_point ]

When a non super user role is created in Greenplum, it does not have privilege to create external (readable or writable ) table. Grant options is not good to allow a role to create external table. It is done through alter role command. For example

create role etlroleext;

alter role etlroleext with CREATEEXTTABLE (type='readable',protocol='gpfdist');

alter role etlroleext with CREATEEXTTABLE (type='writable',protocol='gpfdist');

Please note that etlroleext is a group role. If you want to grant external priv directly to individual etl users you can do so by,

create role etluser1 login;

alter role etluser1 with CREATEEXTTABLE (type='readable',protocol='gpfdist');

alter role etluser1 with CREATEEXTTABLE (type='writable',protocol='gpfdist');