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 RESOURCE QUEUE {queue_name | NONE} 
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');

Comments