Rely on .pgpass in CREATE USER MAPPING
It may not fully answer your question, but:

If you specify a user mapping for a superuser for postgres_fdw you can specify it without a password. It will then rely on the .pgpass from the server.

By using a mapping for a superuser you will however need to think about what security concerns this raises for your usage.

Data source:


dbname=sales
host=sales.db
user=sales_reader


Own DB:


dbname=dwh
host=dwh.db
user=dwh_writer
~/.pgpass of the postgres OS user contains:        sales.db:*:sales:sales_reader:verysecret


Example session showing the difference between NOSUPERUSER and SUPERUSER:

CREATE SERVER sales FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'sales.db', dbname 'sales');
GRANT USAGE ON FOREIGN SERVER sales TO dwh_writer;
CREATE USER MAPPING FOR dwh_writer SERVER sales OPTIONS (user 'sales_reader');
CREATE USER MAPPING FOR postgres   SERVER sales OPTIONS (user 'sales_reader');

CREATE FOREIGN TABLE sales_summary (
    item_id text,
    amount bigint,
    date_sold date
)
SERVER sales
OPTIONS (schema_name 'sales_data', table_name 'sales_summary');

GRANT SELECT ON sales_summary TO dwh_writer;

\c dwh dwh_writer

dwh_writer@dwh=&gt; SELECT count(*) FROM sales_summary;
ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the user mapping.

\c dwh postgres

postgres@dwh=# SELECT count(*) FROM sales_summary;
 count
-------
     2
(1 row)