BUG #8291: postgres_fdw does not re-read USER MAPING after change.

The following bug has been logged on the website:

Bug reference:      8291
Logged by:          Lloyd Albin
Email address:      lalbin@fhcrc.org
PostgreSQL version: Unsupported/Unknown
Operating system:   Windows 7 (64-bit)
Description:

Tested on
Windows 7 (64-bit) Postgres 9.3.0 Beta 2  -> Windows 7 (64-bit) Postgres
9.3.0 Beta 2
Windows 7 (64-bit) Postgres 9.3.0 Beta 1 -> SUSE Linux (64-bit) Postgres
9.0


I have found that if you change the password in the USER MAPPING, that
postgres_fdw will not use it unless the current password fails or you close
and re-open your postgres connection. I found this while testing to see if
the USER MAPPING's supports MD5 passwords and they appeared to until the
next day when I found that they no longer worked because I had closed and
re-opened my connection.


The second error that I found is in the documentation of ALTER USER MAPPING.
It incorrectly says how to update a users password.


CREATE DATABASE db1
  WITH ENCODING='UTF8'
       OWNER=postgres
       CONNECTION LIMIT=-1;


CREATE DATABASE db2
  WITH ENCODING='UTF8'
       OWNER=postgres
       CONNECTION LIMIT=-1;


-- LOG INTO db1


CREATE TABLE public.tbl_test
(
   field character varying,
   CONSTRAINT tbl_test_field_pkey PRIMARY KEY (field)
)
WITH (
  OIDS = FALSE
)
;
ALTER TABLE public.tbl_test
  OWNER TO postgres;


INSERT INTO public.tbl_test VALUES('Test Value');


-- LOG INTO db2


CREATE EXTENSION postgres_fdw;


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


CREATE USER MAPPING FOR postgres SERVER myserver OPTIONS (user 'postgres',
password 'password');


CREATE FOREIGN TABLE tbl_test (
    field character varying
)
SERVER myserver;


SELECT * FROM tbl_test;
-- This works, we should see the 'Test Value' returned.


ALTER USER MAPPING FOR postgres SERVER myserver OPTIONS (user 'postgres',
password 'badpass');


ERROR:  option "user" provided more than once
********** Error **********


ERROR: option "user" provided more than once
SQL state: 42710


-- http://www.postgresql.org/docs/9.3/static/sql-alterusermapping.html


-- Documentation is not correct, needs to be updated to show updating user
password as:


ALTER USER MAPPING FOR postgres SERVER myserver OPTIONS (SET password
'badpass');


SELECT * FROM pg_catalog.pg_user_mapping;


-- Verified that password was properly changed.


SELECT * FROM tbl_test;


Total query runtime: 1970 ms.
1 row retrieved.


-- This should have failed due to the bad password.
-- If you log out of the database and then reconnect, the query will then
fail.




Lloyd Albin
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Vaccine and Infectious Disease Division (VIDD)
Fred Hutchinson Cancer Research Center (FHCRC)