Create foreign table on file_fdw as non-superuser-CodePudding

I have created a file_fdw extension and a corresponding server as superuser.

CREATE EXTENSION file_fdw;
CREATE SERVER myserver FOREIGN DATA WRAPPER file_fdw;

ALTER SERVER myserver OWNER TO nonsuperuser;

I want a non-superuser nonsuperuser to use this server to create a foreign table

CREATE FOREIGN TABLE test (
   a text NULL,
   b text NULL
)
SERVER myserver
    OPTIONS (filename '/home/me/mycsvfile.csv', format 'csv', header 'true', delimiter ';');

Executing this, leads to `only superuser can change options of a file_fdw foreign table

What can I do to enable nonsuperuser to create foreign tables? If possible I would not mind declaring the options as super user.

CodePudding user response：

Only highly privileged users are allowed to access files on the database server, that's why you need high permissions to create a file_fdw foreign table.

From the error message it becomes clear that you are using an old version of PostgreSQL; on more recent versions, the error message would look like:

only superuser or a member of the pg_read_server_files role may specify the filename option of a file_fdw foreign table

So, as an alternative to dealing out superuser privileges, you may add the user to the pg_read_server_files role.

Upgrade PostgreSQL!