FDW Permissions error using MD5 encrypted password

3 votes

2 answers

1644 views

                          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?

                        

Asked by John (33 rep)

Feb 23, 2018, 01:00 PM
Last activity: Feb 24, 2018, 05:54 PM

FDW Permissions error using MD5 encrypted password

Related Questions