Postgres, how to set up queries between different databases

In our System, data about users is recorded in one database, and data about their trades in another; without additional settings, Postgres does not allow using data from different databases in one request.

That is, a request like:

select a.id from auth.public.user a join trade.public.tenders t on a.id = t.user_id;

Will cause the error “[0A000] ERROR: cross-database references are not implemented”.

What to do ?

You need to create a wrapper for an external table; read below on how to do this.

Setting up an external database

We connect to the DBMS under the super user postgres, connect to the database, which is external to the database from which we will make the request.

docker exec -it postgres psql -U postgres
\c trade

We create the data source we need, in our case this is a view

create or replace view tender_users AS
select    t.id_user
from    tender t        
group by    id_user
order by id_user
;

We give rights to the created source (yes, rights to absolutely everything, for now we can do that)

GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO trade;

In addition, this is an illogical command, because it is needed even if we create views under the trade user, but without it it does not work, it gives an error of not having enough rights (when we make a request through FOREIGN DATA WRAPPER).

Setting up your own database

Switch to the database from which we will make queries

\c prod

We connect the extension for queries to an external database

create extension postgres_fdw;

Checking that the extension is enabled

\dx

Must see:

                               List of installed extensions
     Name     | Version |   Schema   |                    Description
--------------+---------+------------+----------------------------------------------------
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(2 rows)

We need the second line, the first one is “stray”.

Creating an external server

CREATE SERVER tenders FOREIGN DATA WRAPPER postgres_fdw  OPTIONS (dbname 'trade', host 'postgres', port '5432');

We issue rights to use the extension and server

GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw to prod;
GRANT USAGE ON FOREIGN SERVER tenders TO prod;

Now the most important thing is to set the credentials for connecting to the external database

CREATE USER MAPPING for prod SERVER tenders OPTIONS (user 'trade', password '***');

Connecting the data source

Now you can create external tables manually, or automatically. You can read how to create a data source manually using the links from the bibliography at the end of the article. If automatically, then:

IMPORT FOREIGN SCHEMA public LIMIT TO (tender_users) FROM SERVER tenders INTO public;

Checking that the data is readable

prod=# select count(*) from tender_users;
 count
-------
     0
(1 row)

There are no reading errors, which means the connection has been successfully established. Now we can use the data from the external table in queries against our internal tables.

Success!

Postscriptum

The last time I did this was 5 years ago, and perhaps my knowledge is outdated, how is this problem solved in current versions of Postgres? write in the comments.

DBMS version

prod=# select version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 13.15 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20240309) 13.2.1 20240309, 64-bit
(1 row)

References