Create Foreign Data Wrapper without superuser Privilege

Question

I am trying to create FDW without superuser privilege in PostgreSQL 11.12(Azure).
But it is throwing error "SQL Error [42501]: ERROR: permission denied for foreign-data wrapper postgres_fdw" .
Hint: Must be superuser to create a foreign-data wrapper.
Kindly help if there are any other options to create the same. Below snippet is used to create FDW.

CREATE SERVER fdw_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '@hosting ', dbname '@dbname ', port '@port');

Answer 1

Hi @RAGHU C , welcome to Microsoft Q&A forum.

It seems that you have already added the postgres_fdw extension on the specific database and then want to create foreign data wrapper on the database.

So this issue comes when we try to run the create foreign data wrapper command with users that don't have proper usage rights on postgres_fdw extension.

Please run the below command from the superuser/admin account on the database and then try executing it again.

GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO <user>;  

PG13 Trusted Extension usability issue

Please let us know if this helps or else we can discuss further on the same.

If the answer helps, please mark it 'Accept Answer' as it could help others having similar query.