Cannot create a Foreign Data Wrapper in RDS Postgres

Question

So I'm trying to create a FDW in a running v12 Postgres RDS instance.

The extension already exists, but for some reason I cannot create one. This seems crazy simple having come from an on premises set up.

What am I missing here ?

   sourceDatabase=> create extension postgres_fdw ; 
ERROR:  extension "postgres_fdw" already exists
sourceDatabase=> create foreign data wrapper testwrap ; 
ERROR:  permission denied to create foreign-data wrapper "testwrap"
HINT:  Must be superuser to create a foreign-data wrapper.
sourceDatabase=> \du
                                                               List of roles
         Role name         |                   Attributes                   |                          Member of                           
---------------------------+------------------------------------------------+--------------------------------------------------------------
 rds_superuser             | Cannot login                                   | {pg_monitor,pg_signal_backend,rds_replication,rds_password}
 rdsadmin                  | Superuser, Create role, Create DB, Replication+| {}
                           | Password valid until infinity                  | 
 rdsrepladmin              | No inheritance, Cannot login, Replication      | {}
 sourceUser                | Create role, Create DB                        +| {rds_superuser}
                           | Password valid until infinity                  |

So what user are you doing this as because the prompt would seem to say you are not doing it as a superuser. — Adrian Klaver
– Adrian Klaver, Commented Nov 15, 2020 at 22:08
The error message: ERROR: permission denied to create foreign-data wrapper "testwrap" HINT: Must be superuser to create a foreign-data wrapper. tells you can't do that without becoming a superuser. You will need to do this as rdsadmin. — Adrian Klaver
– Adrian Klaver, Commented Nov 15, 2020 at 22:26
and my understanding is I cannot connect as this user, unless this has changed ? — Gurmokh
– Gurmokh, Commented Nov 15, 2020 at 22:45
So.. the syntax is different in RDS. docs.aws.amazon.com/AmazonRDS/latest/UserGuide/… — Gurmokh
– Gurmokh, Commented Nov 15, 2020 at 23:19

Laurenz Albe · Accepted Answer · 2020-11-16 08:07:46Z

4

If you have the postgres_fdw extension installed, you already have a foreign data wrapper called postgres_fdw, so there is no need to create another one.

Use \dew in psql to verify that there is already a foreign data wrapper.

What you need to do now is create a foreign server that contains the connect string to the remote PostgreSQL database, a user mapping that contains the authentication information for that database and foreign tables which point to tables in the remote database.

answered Nov 16, 2020 at 8:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gurmokh Over a year ago

Thank you. I was tripping over myself.

TheDataGuy · Accepted Answer · 2020-11-16 01:52:43Z

What Adrian Klaver said is correct. (What is different is whether you want to create a wrapper or a server).

Foreign data wrapper:

This is Postgres's native feature to access the data across the multiple databases including databases like Postgres, Oracle, Redis and etc. The funny fact is you can read your Twitter messages via the foreign-data wrapper. Here is the list of active wrappers.

But you have to write the handlers and etc to access the data.

https://www.postgresql.org/docs/10/sql-createforeigndatawrapper.html

Postgres_FDW:

postgres_fdw is an extension developed under the postgres's contrib library and it's only specific to Postgres to make the Postgres integration much easier. All the necessary handlers and the functions are bundled with this extension.

Creating the foreign data wrappers needs the real superuser permission and it won't work rds_superuser.

The functionality provided by this module overlaps substantially with the functionality of the older dblink module. But postgres_fdw provides more transparent and standards-compliant syntax for accessing remote tables, and can give better performance in many cases.

So please refer to the postgres_fdw documentation instead of the Foreign data wrapper document.

OR better refer to the official AWS RDS doc which is already shared by VynlJunkie(in the comments section)

Collectives™ on Stack Overflow

Cannot create a Foreign Data Wrapper in RDS Postgres

2 Answers 2

1 Comment

Foreign data wrapper:

Postgres_FDW:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Foreign data wrapper:

Postgres_FDW:

Comments

Your Answer

Sign up or log in

Post as a guest

Related