Luca: Created page with "Category: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'''..."

2024-06-18T08:04:16Z

Created page with "Category: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: <syntaxhighlight lang=postgresql>CREATE EXTENSION postgres_fdw;</syntaxhighlight> Then we create a ''server'' wich is the link to read remote data. <br>Suppose our database '''B'''..."

New page

[[Category: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:
<syntaxhighlight lang=postgresql>CREATE EXTENSION postgres_fdw;</syntaxhighlight>

Then we create a ''server'' wich is the link to read remote data.
<br>Suppose our database '''B''' is on a server at address '''192.168.1.22''' on port '''5432''' and the database is called '''muninn'''.
<syntaxhighlight lang=postgresql>CREATE SERVER muninn_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.1.22', dbname 'muninn', port '5432');</syntaxhighlight>

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'''.
<br>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''':
<syntaxhighlight lang=postgresql>CREATE USER MAPPING FOR odin
SERVER muninn_server
OPTIONS (user 'saturn', password 'aPPlep13');</syntaxhighlight>

Now we need to grant to user '''odin''' usage of foreign server '''muninn_server''':
<syntaxhighlight lang=postgresql>GRANT USAGE ON FOREIGN SERVER muninn_server TO odin;</syntaxhighlight>

Now we can connect as user '''odin''' to database '''A'''.

I strongly suggest to create an appropriate schema where make remote objects visible:
<syntaxhighlight lang=postgresql>CREATE SCHEMA rem_muninn;</syntaxhighlight>

And now we can grant visibility of remote objects of a specific schema in our new created schema:
<syntaxhighlight lang=postgresql>IMPORT FOREIGN SCHEMA remote_schema
FROM SERVER muninn_server INTO rem_muninn;</syntaxhighlight>

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

Create foreign data wrapper (Postgresql) - Revision history