Create foreign data wrapper (Postgresql)

In this guide I'm going to show how to read a remote Postgresql database, from Postgresql.

We will work on database A that will access data of database B.

On database A we need to connect with a user with admin privileges, and install the extension:

CREATE EXTENSION postgres_fdw;

Then we create a server wich is the link to read remote data.
Suppose our database B is on a server at address 192.168.1.22 on port 5432 and the database is called muninn.

CREATE SERVER muninn_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.1.22', dbname 'muninn', port '5432');

We have also defined the name muninn_server wich is the link to our remote server B.

Now the security. We need to give permission on user on database A to read data from database B. But we need to specify credentials to read from database B.
Suppose in database A we whant user odin to read data on database B. On database B we need to specify credentials. Suppose our user on database B is saturn and the password is aPPlep13:

CREATE USER MAPPING FOR odin
SERVER muninn_server
OPTIONS (user 'saturn', password 'aPPlep13');

Now we need to grant to user odin usage of foreign server muninn_server:

GRANT USAGE ON FOREIGN SERVER muninn_server TO odin;

Now we can connect as user odin to database A.

I strongly suggest to create an appropriate schema where make remote objects visible:

CREATE SCHEMA rem_muninn;

And now we can grant visibility of remote objects of a specific schema in our new created schema:

IMPORT FOREIGN SCHEMA remote_schema
FROM SERVER muninn_server INTO rem_muninn;

Now in schema rem_muninn we can query every object in remote schema.