How to connect to localhost with postgres_fdw?

Question

The idea is that I have local database named northwind, and with postgres_fdw I want to connect with another database named test on localhost (remote connection simulation, for situations like when table in my database is updated, do something in other database like save to history etc..). So I opened psql console and type:

CREATE SERVER app_db 
FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (dbname 'test', host 'localhost:5432');

As i found in A Look at Foreign Data Wrappers link. Next I also follow the tutorial:

CREATE USER MAPPING for postgres
SERVER app_db 
OPTIONS (user 'postgres', password 'postgres');

(I assume that user and password should be the same as I used e.g. in Hibernate to access database, and in place current_user from tutorial is postgres in my case cause this is the only role which exists in my PgAdmin III). Then I created foreign table:

CREATE FOREIGN TABLE groups
(
  id serial NOT NULL,
  name character varying(255) NOT NULL,
  version integer DEFAULT 0
)
 SERVER app_db OPTIONS (table_name 'groups')

And that's ok I can find it in schema/foreign tables in PgAdmin III. But when I try to SELECT * FROM groups I got:

********** ERROR**********

ERROR: could not connect to server "app_db"
SQL State: 08001

Is it possible because when I CREATE SERVER app_db.. I don't use localhost server name? I can't because it has name with space (PostgreSQL 9.3) and it causes some weird problems while creating. Thank you in advance. Update: It doesn't work even if I create another server named`app_db on localhost.

Answer 1

After many attempts probably I found a proper way to connect:

CREATE SERVER app_db 
FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (dbname 'test', port '5432', host 'localhost');

Then:

CREATE USER MAPPING for postgres
SERVER app_db 
OPTIONS (user 'postgres', password 'postgres');

And then:

CREATE FOREIGN TABLE groups
(
  id serial NOT NULL,
  name character varying(255) NOT NULL,
  version integer DEFAULT 0
)
 SERVER app_db OPTIONS (schema_name 'public', table_name 'groups')

But is there a solution to check if it's really "remote" connection? Because servers are on the same localhost and I don't know if I can be sure.

Answer 2

You may connect using Unix Domain Socket instead of TCP connection to gain simpler configuration and better performance (Linux/Unix only, not supported in Windows).

CREATE SERVER app_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'test');

Also, you may omit the password if peer authentication is enabled (default).

CREATE USER MAPPING for postgres
SERVER app_db 
OPTIONS (user 'postgres');

Note: peer authentication can only be used for postgres user as the FDW connection is created by the server backend which runs as the system user postgres.

Also, for security reasons, postgresq_fdw allows peer authentication only for clients with SUPERUSER privilege. To allow restricted users to use the FDW, you have to use password authentication as described in this answer