PostgreSQL FDW – Permissions Error with MD5 Encrypted Password

foreign-datapostgresql

When creating Foreign Data Wrappers in Postgres 9.6, if I use the following:

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

-- permissions
ALTER SERVER foreign_server OWNER TO data_owner;

-- Allow a user to access the foreign server if required
GRANT USAGE ON FOREIGN SERVER foreign_server to data_owner;

-- 4. Create foreign user mapping - using md5 encrypted password 
CREATE USER MAPPING FOR data_owner SERVER foreign_server 
OPTIONS ( USER 'jeff', PASSWORD 'md5de0366066f8d96ac5bb4872b1d77b0cb!');

-- 5. Create foreign table
IMPORT FOREIGN SCHEMA foo EXCEPT
(bar )
FROM SERVER foreign_server INTO foreign_tables_schema;

Why do I get an error, when I had the password as plain text everything is fine, but when I use the encrypted md5 password I cannot import the foreign tables.

Any thoughts?

Best Answer

Can you show us where you got the idea that this should work?

There is no provision for supplying passwords as md5 hashes to postgres_fdw. Maybe there ought to be, but someone will have to implement it for it to exist.

Related Question