CODEWITHSUNDEEP
databasepostgresqlpostgresql-9.3pgadminforeign-data-wrapper
Radek Anuszewski
Radek Anuszewski

Reputation: 1910

How to connect to localhost with postgres_fdw?

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.

Upvotes: 8

Views: 12140

Answers (3)

Henrique Graça
Henrique Graça

Reputation: 1

I had the exact same problem as user Victor who commented up there (can't comment in line as I currently lack the minimum rep points)

My case was using a Docker for Desktop on a Windows machine but with containers running a Linux kernel.

This https://stackoverflow.com/a/39259965/1346844 worked like a charm :) much appreciated

Upvotes: 0

Mohammad Alhashash
Mohammad Alhashash

Reputation: 1589

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

Upvotes: 13

Radek Anuszewski
Radek Anuszewski

Reputation: 1910

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.

Upvotes: 9

Related Questions