Postgresql – Rely on .pgpass in CREATE USER MAPPING

foreign-datapostgresqlpostgresql-9.4

I am trying to create a script which creates a postgres-fdw connection between two postgres 9.4 databases. The script (which is checked in under version control), has been relying on pgpass to do other things. Is there any option I can use to request that the password be looked up in pgpass? … in general, where is the documentation on what options are available for CREATE USER MAPPING? the reference just says that options depend on the server.

Best Answer

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=> 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)
Related Question