Grant user access to PXF
The PXF framework implements the PXF protocol, which defines the communication format between a Greengage DB extension and the PXF Java service. This enables creating external tables that reference data in external data stores.
Register PXF in a database
You must enable the PXF extension in each database where you plan to use it. You must also explicitly grant permission to the PXF protocol to the roles that require access as described in Manage access to PXF. You must have Greengage DB administrator privileges to register an extension.
-
Connect to a database as
gpadmin:$ psql -d <dbname> -U gpadmin -
Create the PXF extension:
CREATE EXTENSION pxf;
Creating the extension registers the PXF protocol and the call handlers that are required for PXF to access external data.
Unregister PXF from a database
When the PXF extension in a specific database is no longer needed, you must explicitly drop it. You must have Greengage DB administrator privileges to drop an extension.
-
Connect to the database as
gpadmin:$ psql -d <dbname> -U gpadmin -
Drop the PXF extension:
DROP EXTENSION pxf;
The DROP command fails if any currently defined external tables are using the PXF protocol.
Use the CASCADE option to remove these external tables as well:
DROP EXTENSION pxf CASCADE;Manage access to PXF
To read external data with PXF, you create a readable external table with the CREATE EXTERNAL TABLE command that specifies the PXF protocol.
To write data to an external data store with PXF, you create a writable external table with the CREATE WRITABLE EXTERNAL TABLE command that specifies the PXF protocol.
You must specifically grant the SELECT and INSERT permissions to the PXF protocol to all non-superuser Greengage DB roles that require the read or write access, respectively.
To learn more about roles and privileges in Greengage DB and about using the GRANT command, see Roles and privileges.
For example, to grant the role named bill access to data referenced by an external table created with the PXF protocol:
-
Read access:
GRANT SELECT ON PROTOCOL pxf TO bill; -
Write access:
GRANT INSERT ON PROTOCOL pxf TO bill;