Pivotal Knowledge Base

Follow

How to grant users privileges to use PXF protocol in HAWQ ?

Resolution

This article documents the steps required to allow users to create pxf based external tables with sample create statement's using pxf.

With HAWQ 1.1.x, privileges to pxf protocol must be given for every database individually, else in the database for which you have been given access to pxf, you will be able to create external tables, but in other's you would see the error "ERROR:  permission denied for external protocol pxf".

Step 1: Create a user with login priviliges

gpadmin=# create role vtest with login;
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE

Step 2: Change the role to the desired user and verify permission for pxf protocol.
Note: This step is just to showcase the error observed if user does not have privilege to create pxf based tables.

gpadmin=#set role vtest;

gpadmin=> CREATE EXTERNAL TABLE aggregate_expenditure (employee_id int, expenditure_aggregate int) LOCATION ('pxf://phd11-nn.saturn.local:50070/tmp/expenditure/?Fragmenter=HdfsDataFragmenter&Accessor=LineReaderAccessor&Resolver=StringPassResolver') FORMAT 'TEXT' (DELIMITER E'\t');
ERROR: permission denied for external protocol pxf

Step 3: Grant all on pxf protocol. User would be able to create external table only in gpadmin database.

gpadmin=# grant all on protocol pxf to "vtest";
GRANT

Step 4: Change the role to the user and test creating an external table using pxf.

gpadmin=# set role vtest;
SET
gpadmin=> CREATE EXTERNAL TABLE aggregate_expenditure (employee_id int, expenditure_aggregate int) LOCATION ('pxf://phd11-nn.saturn.local:50070/tmp/expenditure/?Fragmenter=HdfsDataFragmenter&Accessor=LineReaderAccessor&Resolver=StringPassResolver') FORMAT 'TEXT' (DELIMITER E'\t');
CREATE EXTERNAL TABLE

Step5: Change to database template1, you will see the below error, so again grant privileges on protocol pxf to vtest.

gpadmin=> \c temlate1 gpadmin=# set role vtest; template1=> CREATE EXTERNAL TABLE aaadfadddafasadfaggregate_expenditure (employee_id int, expenditure_aggregate int) LOCATION ('pxf://phd11-nn.saturn.local:50070/tmp/expenditure/?Fragmenter=HdfsDataFragmenter&Accessor=LineReaderAccessor&Resolver=StringPassResolver') FORMAT 'TEXT' (DELIMITER E'\t');
ERROR: permission denied for external protocol pxf
SET

Comments

Powered by Zendesk