can i simplify the use of postgres_fdw if the foreign db is in the same server

Question

my server has several databases. I want to be able to read db1 from queries in db2. I know I can use postgres_fdw to do it but that has a couple of drawbacks. The main one is that I have to pass credentials around, worry over when I change passwords etc.

Answer 1

so you set up server, user mapping and create tables:

t=# create server l foreign data wrapper postgres_fdw options (host 'localhost', dbname 't');
CREATE SERVER
t=# create user mapping FOR postgres SERVER l;
CREATE USER MAPPING
t=# create table lt(i serial);
CREATE TABLE
t=# insert into lt default values;
INSERT 0 1
t=# create foreign table ft (i int) server l options(table_name 'lt') ;
CREATE FOREIGN TABLE
t=# select * from ft;
 i
---
 1
(1 row)

now If I add md5 to hba before trust local default connection, I get:

t=# select * from ft;
ERROR:  could not connect to server "l"
DETAIL:  fe_sendauth: no password supplied

and revert:

t=# \! sed -i '43s/host/#host/' /pg/d10/pg_hba.conf
t=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

t=# select * from ft;
 i
---
 1
(1 row)

while the line is:

t=# \! sed '43,43!d' /pg/d10/pg_hba.conf
#host   all     postgres    127.0.0.1/32        md5

So my point is: If you have local databases, you by default don't need to manipulate passwords, as you have peer or trust for localhost...

update so in order to work on localhost without a password for some user you need line, like:

host    fdw_db      postgres    127.0.0.1/32        trust

to go before the line like:

host    all     all 127.0.0.1/32        md5

or any other line that restricts or rejects connections

https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

Since the pg_hba.conf records are examined sequentially for each connection attempt, the order of the records is significant. Typically, earlier records will have tight connection match parameters and weaker authentication methods, while later records will have looser match parameters and stronger authentication methods. For example, one might wish to use trust authentication for local TCP/IP connections but require a password for remote TCP/IP connections. In this case a record specifying trust authentication for connections from 127.0.0.1 would appear before a record specifying password authentication for a wider range of allowed client IP addresses.