An Error Message Is Displayed When a Common User Executes the Statement for Creating or Deleting a GDS or OBS Foreign Table, Indicating that the User Does Not Have the Permission or the Permission Is Insufficient

Symptom

An administrator can execute the statement for creating a GDS or OBS foreign table, but an error "ERROR: permission denied to create foreign table in security mode" is reported when a common user executes the statement.

CREATE USER u1 PASSWORD '{password}';
SET current_schema = u;
CREATE FOREIGN TABLE customer_ft
(
    c_customer_sk             integer               ,
    c_customer_id             char(16)              ,
    c_current_cdemo_sk        integer               ,
    c_current_hdemo_sk        integer               ,
    c_current_addr_sk         integer    
)
    SERVER gsmpp_server
    OPTIONS
(
    location 'gsfs://192.168.0.90:5000/customer1*.dat',
    FORMAT 'TEXT' ,
    DELIMITER '|',
    encoding 'utf8',
    mode 'Normal')
READ ONLY;
ERROR:  permission denied to create foreign table in security mode

Possible Causes

The error message indicates that the common user does not have the permission for creating a foreign table.

Query the user permissions in the case.

SELECT rolname,roluseft FROM pg_roles WHERE rolname ='u1' ORDER BY rolname desc;
 rolname | roluseft
---------+----------
 u1      | f
(1 row)

Handling Procedure

You can use the ALTER USER or ALTER ROLE syntax to specify the USEFT parameter, granting a role or user the permission to use foreign tables.

USEFT | NOUSEFT determines whether a new role or user can perform operations on foreign tables, such as creating, deleting, modifying, and reading/witting foreign tables.

• If USEFT is specified, the role or user can perform operations on foreign tables.
• The default value is NOUSEFT, indicating that the new role or user does not have permissions to perform operations on foreign tables.

1

ALTER USER user_name USEFT;

For details about how to modify user or role permissions, see ALTER USER or ALTER ROLE.

Common users or roles can create foreign tables after being granted the permission to use foreign tables.