ERROR: permission denied: no privilege to create a readable gpfdist(s) external table

posted Apr 28, 2017, 4:22 PM by Sachchida Ojha
Altering role to create external table.

alter role sachi CREATEEXTTABLE;
CREATE ROLE name [[WITH]option [...]] 
Its option, specify the user authentication system permissions and resource constraints and so on. 

Specific options are as follows 
SUPERUSER |NOSUPERUSER Specify whether the user is a super administrator, default is NOSUPERUSER. If the super administrator access restrictions. General application users do not give this permission. 

CREATEDB |NOCREATEDB Specify whether the user can create a database. Default is NOCREATEDB. 

CREATEROLE |NOCREATEROLE Specify whether the user can create a new user, the default is NOCREATEROLE 

CREATEEXTTABLE |NOCREATEEXTTABLE [(attribute = 'value'[, ...])] Specify whether the user can create a specific type, protocol-specific external table. You can specify the properties include type = 'readable'|'writable' protocol = 'gpfdist'|'http'|'gphdfs' If you use the file protocol, external tables or execute the agreement, must be a super administrator. The default is NOCREATEEXTTABLE 
INHERIT |NOINHERIT No independent role concept in GP, ​​the user both roles. So when a LOGIN |NOLOGIN Set whether the user login system privileges. The default is NOLOGIN. The role assignments generally used to manage permissions NOLOGIN permission. 
CONNECTION LIMIT connlimit Limit the number of concurrent connections, the default value is -1, which means there is no limit. Other more appropriate way to limit the number of concurrent connections, it is recommended not to modify the settings in the 
GP. [ENCRYPTED |UNENCRYPTED]PASSWORD 'password' Set the user's password, and specify whether the password to encrypt stored in the dictionary. 
VALID UNTIL 'timestamp' Specifies the password lifecycle. Default is never expires. 
IN ROLE rolename [, ...] Add new members to the role role, so that new users became a group concept. Grant command can be used to achieve the same effect ADMIN rolename [, ...] To add a new role to the role with admin option option. Allow the role to other role permissions granted to other roles. RESOURCE QUEUE queue_name Consumption of resources, control user connections for the user to create a resource queue. 

A user can only be assigned to a resource queue. = # CREATE ROLE admin CREATEROLE CREATEDB; GP no independent user group or role concept. A user can give another user in order to achieve the concept of role, to simplify the management of permissions. 
Such as = 
# GRANT admin TO john, sally; =
# REVOKE admin FROM bob; 

Then the admin group role, john, sally member roles, member roles from the group role inherited system privileges Similarly, the owner of the object object permissions, object permissions given set of roles, the role of role-group members to obtain the access rights of the related objects. Such as = # GRANT ALL ON TABLE mytable TO admin; = # GRANT ALL ON SCHEMA myschema TO admin; = # GRANT ALL ON DATABASE mydb TO admin; Admin members of roles will also get the corresponding object access permissions. The GP support the object privileges including Object Type Privileges Tables, Views, Sequences SELECT INSERT UPDATE DELETE RULE ALL External Tables SELECT RULE ALL Databases CONNECT CREATE TEMPORARY |TEMP ALL Functions EXECUTE Procedural Languages ​​USAGE Schemas CREATE USAGE ALL